What is a Privilege?
A privilege is the right to execute a particular type of SQL statement. It can allow a user to access database objects or execute stored programs that are owned by another user or to perform system level actions. There are two types of privileges:
- system privileges
- schema object privileges
What are system privileges?
The system privileges are not related to a certain object. They control the ability of a user to perform system level actions such as connecting to the database(creating a session), creating a table, altering a user, etc. or to run a certain type of SQL statement on any schema(select any table, create any procedure).
All the system privileges are listed in the SYSTEM_PRIVILEGE_MAP table:
SQL> select name from SYSTEM_PRIVILEGE_MAP; NAME ---------------------------------------- CREATE EXTERNAL JOB CHANGE NOTIFICATION READ ANY FILE GROUP MANAGE ANY FILE GROUP MANAGE FILE GROUP CREATE ANY SQL PROFILE ADMINISTER ANY SQL TUNING SET ADMINISTER SQL TUNING SET ... ALTER SYSTEM
What are object privileges?
The object privileges control the access to a certain objects. For different object types there are different privileges( for a procedure we do have an execute object privilege but do not have a select privilege).
What are roles?
A role is a named group of privileges. It facilitates the management of individual privileges. The users who are granted a role inherit all the privileges(and roles) that have been granted to that role. The roles can be secured so that they are enabled only if the user supplies a password or executes a certain PL/SQL procedure.
How to grant/revoke system privileges?
The system privileges can be granted to any user or role with the following command:
SQL> grant [privilege name] to [username or role] [WITH ADMIN OPTION];
They can be revoked with the following command:
SQL> revoke [privilege name] from [username or role];
The system privileges are very powerful and as such they must be granted with caution. Furthermore since GRANT ANY PRIVILEGE and WITH ADMIN OPTION allow a user to grant system privileges they must be guarded well.
All the system privileges granted to a certain user can be viewed in DBA_SYS_PRIVS.
SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='SCOTT'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- SCOTT CREATE JOB NO SCOTT SELECT ANY DICTIONARY NO SCOTT CREATE ANY PROCEDURE YES SCOTT CREATE PROCEDURE NO SCOTT UNLIMITED TABLESPACE NO SCOTT ALTER SESSION NO SCOTT CREATE TABLE NO SCOTT CREATE SESSION NO 8 rows selected.
What is the use of “GRANT ANY PRIVILEGE” privilege?
A user who is granted the “GRANT ANY PRIVILEGE” privilege(SYSDBA has this privilege by default) can grant or revoke any system privilege.
What is the meaning of “WITH ADMIN OPTION” while granting a privilege?
A user who is granted a certain system privilege WITH ADMIN OPTION can grant/revoke it to/from other users or roles. The WITH ADMIN OPTION used while granting a system privilege will make that user the “administrator” of the privilege.
How to grant/revoke object privileges?
The object privileges can be granted to any user or role with the following command:
SQL> grant [privilege name] on [schema].[object_name] to [username or role] [WITH GRANT OPTION];
SQL> revoke [privilege name] on [schema].[object_name] from [username or role];
All the object privileges granted to a certain user can be viewed in DBA_TAB_PRIVS.
SQL> select owner, table_name, privilege from dba_tab_privs where grantee='SYSTEM'; OWNER TABLE_NAME PRIVILEGE ----------- ---------- ----------------- SYS INCEXP FLASHBACK SYS INCEXP DEBUG SYS INCEXP QUERY REWRITE SYS INCEXP ON COMMIT REFRESH SYS INCEXP REFERENCES SYS INCEXP UPDATE SYS INCEXP SELECT SYS INCEXP INSERT SYS INCEXP INDEX SYS INCEXP DELETE SYS INCEXP ALTER ...
How to create a role?
To create a role, use the CREATE ROLE command:
SQL> create role [role name] [IDENTIFIED BY [password]/USING [package]/EXTERNALLY/GLOBALLY ];
How to grant/revoke a privilege to/from a role?
To grant privileges to a role use the GRANT command.
SQL> grant [object/system privilege] to [role name];
To revoke privileges from a role run the following statement:
SQL> revoke [privilege] from [role name];
How to grant a role to a user?
A role can be granted to a user or to another role with the following command:
SQL> grant [role] to [username or role];
How to enable/disable roles in a session?
Roles can be enabled or disabled selectively in a session. To enable a role explicitly in a session you can run one of the following commands:
SQL> set role [role name]; SQL> set role all; SQL> set role all except [list of roles];
The roles can be enabled automatically in a session if they are declared to be the default roles of a user:
SQL> alter user [username] default role [role list];