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

DBA_SYS_PRIVS view (Query Data dictionary)

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:

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