System Privileges for Oracle Database XE

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).

Using System Privileges

A system privilege is a right to perform an action on any object in the database, as well as other actions that do not involve objects at all but rather such tasks as running batch jobs, altering system parameters, creating roles, and even connecting to the database itself. There are 166 system privileges in Oracle Database XE. You can find them in the data dictionary table SYSTEM_PRIVILEGE_MAP.

Table 31-6 lists some of the more common system privileges, along with a brief description of each.

Table 31-6. Common System Privileges   

System Privilege

Capability

ALTER DATABASE

Make changes to the database, such as changing the state of the database from MOUNTto OPEN, or recovering a database.

ALTER SYSTEM

Issue ALTER SYSTEMstatements: switch to the next redo log group and change system-initialization parameters in the SPFILE.

AUDIT SYSTEM

Issue AUDITstatements.

CREATE DATABASE LINK

Create database links to remote databases.

CREATE ANY INDEX

Create an index in any schema; CREATE INDEXis granted along with CREATE TABLEfor the user’s schema.

CREATE PROFILE

Create a resource/password profile.

CREATE PROCEDURE

Create a function, procedure, or package in your own schema.

CREATE ANY PROCEDURE

Create a function, procedure, or package in any schema.

CREATE SESSION

Connect to the database.

CREATE SYNONYM

Create a private synonym in your own schema.

CREATE ANY SYNONYM

Create a private synonym in any schema.

CREATE PUBLIC SYNONYM

Create a public synonym.

DROP ANY SYNONYM

Drop a private synonym in any schema.

DROP PUBLIC SYNONYM

Drop a public synonym.

CREATE TABLE

Create a table in your own schema.

CREATE ANY TABLE

Create a table in any schema.

CREATE TABLESPACE

Create a new tablespace in the database.

CREATE USER

Create a user account/schema.

ALTER USER

Make changes to a user account/schema.

Table 31-6. Common System Privileges (Continued)  

System Privilege

Capability

CREATE VIEW

Create a view in your own schema.

SYSDBA

Create an entry in the external password file, if enabled; also, perform

 

startup/shutdown, alter a database, create a database, recover a

 

database, create an SPFILE, and connect when the database is in

 

RESTRICTED SESSIONmode.

SYSOPER

Create an entry in the external password file, if enabled; also, perform

 

startup/shutdown, alter a database, recover a database, create an

 

SPFILE, and connect when the database is in RESTRICTED SESSION

 

mode.

{mospagebreak title=Granting System Privileges}

You grant privileges to a user, role, or PUBLIC using the GRANT command. Privileges are revoked using the REVOKE command. PUBLIC is a special group that includes all database users, and it’s convenient shorthand for granting privileges to everyone in the database.

To grant the user KELLYC the 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 include WITH ADMIN OPTION when you grant the privilege. In the preceding example, you want the user KELLYC to be able to grant the CREATE PROCEDURE privilege to other users. To accomplish this, you need to regrant the CREATE PROCEDURE privilege:

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

Now KELLYC may in turn issue the GRANT CREATE PROCEDURE command to another user. Note that if KELLYC ’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.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort