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.