Granting System Privileges - Oracle

You grant privileges to a user, role, orPUBLIC using theGRANTcommand. Privileges are revoked using theREVOKEcommand.PUBLICis a special group that includes all database users, and it’s convenient shorthand for granting privileges to everyone in the database.

To grant the userKELLYCthe ability to create stored procedures and synonyms, you can use a command like the following:

SQL> grant create procedure, create synonym to kellyc;
Grant succeeded.

Revoking privileges is just as easy:

SQL> revoke create synonym from kellyc; Revoke succeeded.

If you wish to allow grantees the right to grant the same privilege to someone else, you includeWITH ADMIN OPTIONwhen you grant the privilege. In the preceding example, you want the userKELLYCto be able to grant theCREATE PROCEDUREprivilege to other users. To accomplish this, you need to regrant theCREATE PROCEDUREprivilege:

SQL> grant create procedure to kellyc with admin option;
Grant succeeded.

NowKELLYCmay in turn issue theGRANT CREATE PROCEDUREcommand to another user. Note that ifKELLYC’s permission to grant her privileges to others is revoked, the users she has granted privileges to retain the privileges.

System Privilege Data Dictionary Views

Table 31-7 contains the data dictionary views related to system privileges.  

 Please check back next week for the continuation of this article.