Managing Roles   «Prev  Next»

Lesson 6 The DBA_SYS_PRIVS view
ObjectiveQuery the data dictionary to determine user-privileges.

DBA_SYS_PRIVS view (Query Data dictionary)

To use the `DBA_SYS_PRIVS` view to query the Oracle Data Dictionary, you can retrieve information about system privileges granted to users and roles. Here's a basic example of a query: 
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'USERNAME';
  • `grantee`: The user or role that has been granted the privilege.
  • `privilege`: The specific system privilege granted.
  • `admin_option`: Indicates if the grantee has the ability to grant the privilege to others.

You can modify the `WHERE` clause to specify a particular user or role, or adjust the query to your needs.

How do you know what your users can do?

Querying the DBA_SYS_PRIVS view in the data dictionary reveals the privileges held by a database user. You can query it to get a list of privileges held by a particular user, or you can query it to see which users hold a specific privilege. The following MouseOver explains the columns in the view:
Analyze the following uploaded image. Print off the text and code in the image.
DBA_SYS_PRIVS view 
Name         Null?    Type
-----------------------------
GRANTEE      NOT NULL VARCHAR2(30)
PRIVILEGE    NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
DBA_SYS_PRIVS view
  1. GRANTEE: This row contains either a username or a rolename, and indicates who holds the privilege.
  2. PRIVILEGE: This row contains the name of the privilege being held.
  3. ADMIN_OPTION: This row indicates whether or not the grantee holds the privilege with the admin option. A value of YES means that he does have the admin option, while a value of NO means that he does not.


Query DBA_SYS_PRIVS example
The following example shows how you can query DBA_SYS_PRIVS to see who holds the CREATE TABLESPACE privilege: 
SQL> SELECT * FROM dba_sys_privs
  2  WHERE privilege = 'CREATE TABLESPACE';
GRANTEE              PRIVILEGE            ADM
-------------------- -------------------- ---
CTXSYS               CREATE TABLESPACE    NO
DBA                  CREATE TABLESPACE    YES
IMP_FULL_DATABASE    CREATE TABLESPACE    NO
JEFF                 CREATE TABLESPACE    YES
JENNY                CREATE TABLESPACE    YES
ASHLEY               CREATE TABLESPACE    NO
7 rows selected.

In this example, the users DBA, JEFF, and JENNY have 
CREATE TABLESPACE
with the admin option. They can grant the privilege to other users. The remaining users in the list have the privilege, but without the admin option.

Note: The data dictionary view DBA_SYS_PRIVS lists all the system privileges granted in the database.

Role Privileges

Role privileges confer on the grantee a group of system, object, and other role privileges. Users who have been granted a role inherit the privileges that have been granted to that role. Roles can be password protected, so users may have a role granted to them yet not be able to use that role in all database sessions.

Creating and Managing Roles

A role is a tool for administering privileges. Privileges can be granted to a role, and then that role can be granted to other roles and users. Users can, therefore, inherit privileges via roles. Roles serve no other purpose than to administer privileges. To create a role, use the CREATE ROLE statement. You can optionally include an 
IDENTIFIED BY
clause that requires users to authenticate themselves before enabling the role. Roles requiring authentication are typically used inside an application, where a user's activities are controlled by the application. To create the role APPL_DBA, execute the following: 

CREATE ROLE appl_dba;

To enable a role, execute a SET ROLE statement, like this: 
SET ROLE appl_dba IDENTIFIED BY seekwrit;

Note: The data dictionary view DBA_ROLE_PRIVS lists all the role privileges granted in the database.

DBA System Privileges - Exercise

Click the Exercise link below practice querying the DBA_SYS_PRIVS view.
DBA System Privileges - Exercise