HomeOracle Page 2 - System Privileges for Oracle Database XE
Granting System Privileges - Oracle
In this sixth part of a ten-part series on securing Oracle Database XE, you'll learn how to use and grant system privileges. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
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.
Table 31-7. System Privilege Data Dictionary Views
Data Dictionary View
System privileges assigned to roles and users
All system privileges in effect for this user for the session, granted directly or via a role
Current session privileges granted to a user via a role
Please check back next week for the continuation of this article.