System Privileges

A system privilege allows a user to perform certain actions within the database ”such as executing DDL statements. For example, CREATE TABLE allows a user to create a table in their schema. Some of the commonly used system privileges are shown in Table 9-1.

Table 9-1: Commonly Used System Privileges

System Privilege	Allows You to
CREATE SESSION	Connect to a database.
CREATE SEQUENCE	Create a sequence. A sequence is a series of numbers , which are typically used to automatically populate a primary key column. You'll learn about sequences in the next chapter.
CREATE SYNONYM	Create a synonym. A synonym allows you to reference a table in another schema. You'll learn about synonyms later in this chapter.
CREATE TABLE	Create a table.
CREATE ANY TABLE	Create a table in any schema.
DROP TABLE	Drop a table.
DROP ANY TABLE	Drop a table from any schema.
CREATE PROCEDURE	Create a stored procedure.
EXECUTE ANY PROCEDURE	Execute a procedure in any schema.
CREATE USER	Create a user.
DROP USER	Drop a user.
CREATE VIEW	Create a view. A view is a stored query that allows you to access multiple tables and columns . You may then query the view as you would a table. You'll learn about views in the next chapter.

Note  

You can get the full list of system privileges in the Oracle SQL Reference manual.

Granting System Privileges to a User

As mentioned, you use GRANT to grant a system privilege to a user. The following example grants some system privileges to steve using GRANT ( assuming you re still connected to the database as system ):

 GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO steve; 

You can also use WITH ADMIN OPTION to enable a user to grant a privilege to another user. The following example grants the EXECUTE ANY PROCEDURE privilege with the ADMIN option to steve :

 GRANT EXECUTE ANY PROCEDURE TO steve WITH ADMIN OPTION; 

The EXECUTE ANY PROCEDURE can then be granted to another user by steve . The following example connects as steve and grants EXECUTE ANY PROCEDURE to gail :

 CONNECT steve/button GRANT EXECUTE ANY PROCEDURE TO gail; 

You can grant a privilege to all users by granting to PUBLIC . The following example connects as system and grants the EXECUTE ANY PROCEDURE privilege to PUBLIC :

 CONNECT system/manager GRANT EXECUTE ANY PROCEDURE TO PUBLIC; 

Every user in the database now has the EXECUTE ANY PROCEDURE privilege.

Checking System Privileges Granted to a User

You can check which system privileges a user has by querying user_sys_privs . Table 9-2 describes the columns in user_sys_privs .

Table 9-2: Some Columns in user_sys_privs

Column	Type	Description
username	VARCHAR2(30)	Name of the current user.
privilege	VARCHAR2(40)	System privilege.
admin_option	VARCHAR2(3)	Whether the user is able to grant the privilege to another user.

Note  

user_sys_privs forms part of the Oracle database s data dictionary. The data dictionary stores information on the database.

The following example connects as steve and queries user_sys_privs :

  CONNECT steve/button   SELECT *   FROM user_sys_privs;  USERNAME PRIVILEGE ADM ---------------------- -------------------------------- --- PUBLIC EXECUTE ANY PROCEDURE NO STEVE CREATE SESSION NO STEVE CREATE TABLE NO STEVE CREATE USER NO STEVE EXECUTE ANY PROCEDURE YES 

The next example connects as gail and queries user_sys_privs :

  CONNECT gail/seymour   SELECT *   FROM user_sys_privs;  USERNAME PRIVILEGE ADM -------------------- ---------------------------------- --- GAIL CREATE SESSION NO GAIL EXECUTE ANY PROCEDURE NO PUBLIC EXECUTE ANY PROCEDURE NO 

Notice gail has EXECUTE ANY PROCEDURE that was granted earlier by steve .

Making Use of System Privileges

Once a user has been granted a system privilege, they can use it to perform the specified task. For example, steve has the CREATE USER privilege, so he is able to create a user:

 CONNECT steve/button CREATE USER roy IDENTIFIED BY williams; 

If steve were to attempt to use a system privilege he doesn t have, the database will return the error ORA-01031: insufficient privileges . For example, steve doesn t have the DROP USER privilege, and in the following example steve attempts to drop roy and fails:

 SQL>  DROP USER roy;  DROP USER roy * ERROR at line 1: ORA-01031: insufficient privileges 

Revoking System Privileges from a User

You revoke system privileges from a user using REVOKE . The following example connects as system and revokes the CREATE TABLE privilege from steve :

 CONNECT system/manager REVOKE CREATE TABLE FROM steve; 

The next example revokes EXECUTE ANY PROCEDURE from steve :

 REVOKE EXECUTE ANY PROCEDURE FROM steve; 

When you revoke EXECUTE ANY PROCEDURE from steve ”who has already passed on this privilege to gail ” gail still gets to keep the privilege:

  CONNECT gail/seymour   SELECT *   FROM user_sys_privs;  USERNAME PRIVILEGE ADM ------------------- ------------------------------ --- GAIL CREATE SESSION NO GAIL EXECUTE ANY PROCEDURE NO PUBLIC EXECUTE ANY PROCEDURE NO