Home arrow Oracle arrow 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).

  1. System Privileges for Oracle Database XE
  2. Granting System Privileges
By: Apress Publishing
Rating: starstarstarstarstar / 2
January 13, 2011

print this article



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 Description
DBA_SYS_PRIVS System privileges assigned to roles and users
SESSION_PRIVS All system privileges in effect for this user for the session, granted directly or via a role  
ROLE_SYS_PRIVS Current session privileges granted to a user via a role


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

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: