| Team Fly | |
table, or execute an individual function, package, or procedure are object privileges as opposed to the ability to execute any procedure or select any table, which are system-level privileges.
What is given can be taken away. In order to take privileges away from a user, we use the REVOKE command and the syntax is very similar to the syntax we use when issuing a grant. Here are two examples of a REVOKE operation:
REVOKE INSERT ON ''TABLE_NAME" FROM "NEWUSER"; REVOKE EXECUTE ON "TABLE_NAME" FROM "NEWUSER";
When you think of the number of privileges that need to be managed in situations where you have thousands of database objects as well as thousands of users, you quickly realize that it would be nice to organize the privileges into groups that can be easily managed. This is where roles come into play.
A "role" is used to group privileges together into a predefined group that can be granted to users. So, rather than granting object and system privileges individually to every user in your system, you can grant them to a role, which in turn is granted to the user.
Some special roles are created by Oracle through the install process or by running Oracle-supplied scripts. The DBA, Connect, Resource, Imp_Full_Database, and Select_Catalog_Role are some examples of roles that are supplied by Oracle and should not be changed.
Roles are created using the create statement in the same manner as creating users. We can also revoke privileges from roles and drop roles when they are no longer needed. Roles can also be granted to other roles. You can see an example of this next where the Oracle role CONNECT is granted to the newly created role TESTROLE, along with a system and object privilege.
CREATE ROLE "TESTROLE"; GRANT CONNECT TO "TESTROLE" GRANT EXECUTE ANY PROCEDURE TO "TESTROLE" GRANT SELECT ON "table_name" TO "TESTROLE"
The new role can then be granted to a user as shown next, where "testrole" is granted to user "Testuser."
Grant "testrole" to "Testuser";
| Team Fly | |