11 Administering User Privileges, Roles, and Profiles

Creating Users

You create a database user with the CREATE USER statement.To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the CREATE USER system privilege.

Example 11-1 creates a user and specifies the user password, default tablespace, temporary tablespace where temporary segments are created, tablespace quotas, and profile.

CREATE USER jward
    IDENTIFIED BY AZ7BC2
    DEFAULT TABLESPACE data_ts
    QUOTA 100M ON test_ts
    QUOTA 500K ON data_ts
    TEMPORARY TABLESPACE temp_ts
    PROFILE clerk;
GRANT create session TO jward;

A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.

This section refers to the preceding example as it discusses the following aspects of creating a user:

• Specifying a Name

• Setting Up User Authentication

• Assigning a Default Tablespace

• Assigning Tablespace Quotas

• Assigning a Temporary Tablespace

• Specifying a Profile

• Setting Default Roles

  See Also:

  Granting System Privileges and Roles on page 25-11

Altering Users

Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege. Security administrators are typically the only users that have this system privilege, as it allows a modification of any user security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

You can alter user security settings with the ALTER USER statement. Changing user security settings affects the future user sessions, not current sessions.

The following statement alters the security settings for the user, avyrros:

ALTER USER avyrros
    IDENTIFIED EXTERNALLY
    DEFAULT TABLESPACE data_ts
    TEMPORARY TABLESPACE temp_ts
    QUOTA 100M ON data_ts
    QUOTA 0 ON test_ts
    PROFILE clerk;

The ALTER USER statement here changes the security settings for the user avyrros as follows:

• Authentication is changed to use the operating system account of the user avyrros.

• The default and temporary tablespaces are explicitly set for user avyrros.

• avyrros is given a 100M quota for the data_ts tablespace.

• The quota on the test_ts is revoked for the user avyrros.

• avyrros is assigned the clerk profile.

ALTER USER andy
   IDENTIFIED BY swordfish;

Dropping Users

When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped.

A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user sessions using the SQL statement ALTER SYSTEM with the KILL SESSION clause.

You can drop a user from a database using the DROP USER statement. To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is powerful, a security administrator is typically the only type of user that has this privilege.

If the user's schema contains any dependent schema objects, then use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user schema contains dependent objects, then an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.

The following statement drops the user, jones and all associated objects and foreign keys that depend on the tables owned by jones.

DROP USER jones CASCADE;

User and Profile Information in Data Dictionary Views

The following data dictionary views contain information about database users and profiles:

The following sections present some examples of using these views, and assume a database in which the following statements have been executed:

CREATE PROFILE clerk LIMIT
    SESSIONS_PER_USER 1
    IDLE_TIME 30
    CONNECT_TIME 600;

CREATE USER jfee
    IDENTIFIED BY wildcat
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA 500K ON users
    PROFILE clerk;

CREATE USER dcranney
    IDENTIFIED BY bedrock
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA unlimited ON users;

CREATE USER userscott
     IDENTIFIED BY scott1;

Listing All Users and Associated Information

The following query lists all users and their associated information as defined in the database:

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;
 
USERNAME        PROFILE         ACCOUNT_STATUS  
--------------- --------------- --------------- 
SYS             DEFAULT         OPEN            
SYSTEM          DEFAULT         OPEN            
USERSCOTT       DEFAULT         OPEN            
JFEE            CLERK           OPEN            
DCRANNEY        DEFAULT         OPEN            

All passwords are encrypted to preserve security. If a user queries the PASSWORD column, then that user is not able to determine the password of another user.

Listing All Tablespace Quotas

The following query lists all tablespace quotas specifically assigned to each user:

SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE    USERNAME    BYTES     MAX_BYTES    BLOCKS    MAX_BLOCKS
----------    ---------  --------   ----------   -------   ----------
USERS         JFEE              0       512000         0          250
USERS         DCRANNEY          0           -1         0           -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. Note that this number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by -1.

Listing All Profiles and Assigned Limits

The following query lists all profiles in the database and associated settings for each limit in each profile:

SELECT * FROM DBA_PROFILES
   ORDER BY PROFILE;

PROFILE             RESOURCE_NAME              RESOURCE   LIMIT             
-----------------   ---------------            ---------- --------------
CLERK               COMPOSITE_LIMIT            KERNEL     DEFAULT
CLERK               FAILED_LOGIN_ATTEMPTS      PASSWORD   DEFAULT
CLERK               PASSWORD_LIFE_TIME         PASSWORD   DEFAULT
CLERK               PASSWORD_REUSE_TIME        PASSWORD   DEFAULT
CLERK               PASSWORD_REUSE_MAX         PASSWORD   DEFAULT
CLERK               PASSWORD_VERIFY_FUNCTION   PASSWORD   DEFAULT
CLERK               PASSWORD_LOCK_TIME         PASSWORD   DEFAULT
CLERK               PASSWORD_GRACE_TIME        PASSWORD   DEFAULT
CLERK               PRIVATE_SGA                KERNEL     DEFAULT
CLERK               CONNECT_TIME               KERNEL     600    
CLERK               IDLE_TIME                  KERNEL     30     
CLERK               LOGICAL_READS_PER_CALL     KERNEL     DEFAULT
CLERK               LOGICAL_READS_PER_SESSION  KERNEL     DEFAULT
CLERK               CPU_PER_CALL               KERNEL     DEFAULT
CLERK               CPU_PER_SESSION            KERNEL     DEFAULT
CLERK               SESSIONS_PER_USER          KERNEL     1      
DEFAULT             COMPOSITE_LIMIT            KERNEL     UNLIMITED
DEFAULT             PRIVATE_SGA                KERNEL     UNLIMITED
DEFAULT             SESSIONS_PER_USER          KERNEL     UNLIMITED
DEFAULT             CPU_PER_CALL               KERNEL     UNLIMITED
DEFAULT             LOGICAL_READS_PER_CALL     KERNEL     UNLIMITED
DEFAULT             CONNECT_TIME               KERNEL     UNLIMITED
DEFAULT             IDLE_TIME                  KERNEL     UNLIMITED
DEFAULT             LOGICAL_READS_PER_SESSION  KERNEL     UNLIMITED
DEFAULT             CPU_PER_SESSION            KERNEL     UNLIMITED
DEFAULT             FAILED_LOGIN_ATTEMPTS      PASSWORD   UNLIMITED
DEFAULT             PASSWORD_LIFE_TIME         PASSWORD   UNLIMITED
DEFAULT             PASSWORD_REUSE_MAX         PASSWORD   UNLIMITED
DEFAULT             PASSWORD_LOCK_TIME         PASSWORD   UNLIMITED
DEFAULT             PASSWORD_GRACE_TIME        PASSWORD   UNLIMITED
DEFAULT             PASSWORD_VERIFY_FUNCTION   PASSWORD   UNLIMITED
DEFAULT             PASSWORD_REUSE_TIME        PASSWORD   UNLIMITED
32 rows selected. 

Viewing Memory Use for Each User Session

The following query lists all current sessions, showing the Oracle user and current User Global Area (UGA) memory use for each session:

SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
   FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
   AND stat.STATISTIC# = name.STATISTIC#
   AND name.NAME = 'session uga memory';

USERNAME                       Current UGA memory
------------------------------ ---------------------------------------------
                               18636bytes
                               17464bytes
                               19180bytes
                               18364bytes
                               39384bytes
                               35292bytes
                               17696bytes
                               15868bytes
USERSCOTT                      42244bytes
SYS                            98196bytes
SYSTEM                         30648bytes

11 rows selected.

To see the maximum UGA memory ever allocated to each session since the instance started, replace 'session uga memory' in the preceding query with 'session uga memory max'.

Dropping Profiles

To drop a profile, you must have the DROP PROFILE system privilege. You can drop a profile (other than the default profile) using the SQL statement DROP PROFILE. To successfully drop a profile currently assigned to a user, use the CASCADE option.

The following statement drops the profile clerk, even though it is assigned to a user:

DROP PROFILE clerk CASCADE;

Any user currently assigned to a profile that is dropped is automatically assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped. When a profile is dropped, the drop does not affect currently active sessions. Only sessions created after a profile is dropped abide by any modified profile assignments.

System Privileges

There are over 100 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations.

Object Privileges

Each type of object has different privileges associated with it.

You can specify ALL [PRIVILEGES] to grant or revoke all available object privileges for an object. ALL is not a privilege, rather, it is a shortcut, or a way of granting or revoking all object privileges with one GRANT and REVOKE statement. Note that if all object privileges are granted using the ALL shortcut, then individual privileges can still be revoked.

Likewise, all individually granted privileges can be revoked by specifying ALL. However, if you REVOKE ALL, and revoking causes integrity constraints to be deleted (because they depend on a REFERENCES privilege that you are revoking), then you must include the CASCADE CONSTRAINTS option in the REVOKE statement.

User Roles

A role groups several privileges and roles, so that they can be granted to and revoked from users simultaneously. A role must be enabled for a user before it can be used by the user.

Oracle Database provides some predefined roles to help in database administration. These roles, listed in Table 11-1, are automatically defined for Oracle databases when you run the standard scripts that are part of database creation. You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles in the same way as you do with any role you define.

If you install other options or products, then other predefined roles may be created.

Creating a Role

You can create a role using the CREATE ROLE statement, but you must have the CREATE ROLE system privilege to do so. Typically, only security administrators have this system privilege.

You must give each role you create a unique name among existing user names and role names of the database. Roles are not contained in the schema of any user. In a database that uses a multibyte character set, Oracle recommends that each role name contain at least one single-byte character. If a role name contains only multibyte characters, then the encrypted role name and password combination is considerably less secure.

The following statement creates the clerk role, which is authorized by the database using the password bicentennial:

CREATE ROLE clerk IDENTIFIED BY bicentennial;

The IDENTIFIED BY clause specifies how the user must be authorized before the role can be enabled for use by a specific user to which it has been granted. If this clause is not specified, or NOT IDENTIFIED is specified, then no authorization is required when the role is enabled. Roles can be specified to be authorized by:

• The database using a password

• An application using a specified package

• Externally by the operating system, network, or other external source

• Globally by an enterprise directory service

These authorizations are discussed in following sections.

Later, you can set or change the authorization method for a role using the ALTER ROLE statement. The following statement alters the clerk role to specify that the user must have been authorized by an external source before enabling the role:

ALTER ROLE clerk IDENTIFIED EXTERNALLY;

To alter the authorization method for a role, you must have the ALTER ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

Specifying the Type of Role Authorization

The methods of authorizing roles are presented in this section. A role must be enabled for you to use it.

CREATE ROLE manager IDENTIFIED BY morework;

CREATE ROLE admin_role IDENTIFIED USING hr.admin;

CREATE ROLE accts_rec IDENTIFIED EXTERNALLY;

CREATE ROLE supervisor IDENTIFIED GLOBALLY;

Dropping Roles

In some cases, it may be appropriate to drop a role from the database. The security domains of all users and roles granted a dropped role are immediately changed to reflect the absence of the dropped role privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all user default role lists.

Because the creation of objects is not dependent on the privileges received through a role, tables and other objects are not dropped when a role is dropped.

You can drop a role using the SQL statement DROP ROLE. To drop a role, you must have the DROP ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

The following statement drops the role CLERK:

DROP ROLE clerk;

Granting System Privileges and Roles

You can grant system privileges and roles to other users and roles using the GRANT statement. The following privileges are required:

• To grant a system privilege, you must have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege.

• To grant a role, you must have been granted the role with the ADMIN OPTION or have been granted the GRANT ANY ROLE system privilege.

The following statement grants the system privilege CREATE SESSION and the accts_pay role to the user jward:

GRANT CREATE SESSION, accts_pay TO jward;

GRANT new_dba TO michael WITH ADMIN OPTION;

GRANT CONNECT TO ssmith IDENTIFIED BY p1q2r3;

Granting Object Privileges

You also use the GRANT statement to grant object privileges to roles and users. To grant an object privilege, you must fulfill one of the following conditions:

• You own the object specified.

• You possess the GRANT ANY OBJECT PRIVILEGE system privilege that enables you to grant and revoke privileges on behalf of the object owner.

• The WITH GRANT OPTION clause was specified when you were granted the object privilege by its owner.

  Note:

  System privileges and roles cannot be granted along with object privileges in the same GRANT statement.

The following statement grants the SELECT, INSERT, and DELETE object privileges for all columns of the emp table to the users, jfee and tsmith:

GRANT SELECT, INSERT, DELETE ON emp TO jfee, tsmith;

To grant all object privileges on the salary view to the user jfee, use the ALL keyword as shown in the following example:

GRANT ALL ON salary TO jfee;

GRANT SELECT ON hr.employees TO blake WITH GRANT OPTION;

SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
  2>     FROM DBA_TAB_PRIVS 
  3>     WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

GRANTEE  OWNER GRANTOR PRIVILEGE    GRANTABLE
-------- ----- ------- -----------  ----------
BLAKE    HR    HR       SELECT      YES       

GRANT SELECT ON hr.employees TO clark;

GRANTEE  OWNER GRANTOR  PRIVILEGE    GRANTABLE
-------- ----- -------- --------     ----------
BLAKE    HR    HR       SELECT       YES       
CLARK    HR    BLAKE    SELECT       NO        

GRANT INSERT (acct_no) ON accounts TO scott;

GRANT INSERT(ename, job) ON emp TO jfee, tsmith;

Revoking System Privileges and Roles

You can revoke system privileges and roles using the SQL statement REVOKE. Any user with the ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user or role. The revoker does not have to be the user that originally granted the privilege or role. Users with GRANT ANY ROLE can revoke any role.

The following statement revokes the CREATE TABLE system privilege and the accts_rec role from tsmith:

REVOKE CREATE TABLE, accts_rec FROM tsmith;

Revoking Object Privileges

To revoke an object privilege, you must fulfill one of the following conditions:

• You previously granted the object privilege to the user or role.

• You possess the GRANT ANY OBJECT PRIVILEGE system privilege that enables you to grant and revoke privileges on behalf of the object owner.

You can only revoke the privileges that you, the grantor, directly authorized, not the grants made by other users to whom you granted the GRANT OPTION. However, there is a cascading effect. The object privilege grants propagated using the GRANT OPTION are revoked if a grantor object privilege is revoked.

Assuming you are the original grantor, the following statement revokes the SELECT and INSERT privileges on the emp table from the users jfee and tsmith:

REVOKE SELECT, insert ON emp FROM jfee, tsmith;

The following statement revokes all object privileges for the dept table that you originally granted to the human_resource role

REVOKE ALL ON dept FROM human_resources;

GRANTEE  OWNER GRANTOR PRIVILEGE    GRANTABLE
-------- ----- ------- -----------  ----------
BLAKE    HR    HR       SELECT       YES       
CLARK    HR    BLAKE    SELECT       NO        
CLARK    HR    HR       SELECT       NO        

REVOKE  SELECT ON hr.employees FROM clark;

GRANTEE  OWNER GRANTOR PRIVILEGE    GRANTABLE
-------- ----- ------- -----------  ----------
BLAKE    HR    HR       SELECT      YES       
CLARK    HR    HR       SELECT      NO        

REVOKE UPDATE ON dept FROM human_resources;
GRANT UPDATE (dname) ON dept TO human_resources;

REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;

Cascading Effects of Revoking Privileges

Depending on the type of privilege, there may be cascading effects when a privilege is revoked. This is discussed in the following sections:

• System Privileges

• Object Privileges

The SET ROLE Statement

During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. You must already have been granted the roles that you name in the SET ROLE statement. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES.

This example enables the role clerk, which you have already been granted, and specifies the password.

SET ROLE clerk IDENTIFIED BY bicentennial;

You can disable all roles with the following statement:

SET ROLE NONE;

Specifying Default Roles

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the default roles of the user.

A list of default roles for a user can be set and altered using the ALTER USER statement. The ALTER USER statement enables you to specify roles that are to be enabled when a user connects to the database, without requiring the user to specify the role passwords. The user must have already been directly granted the roles with a GRANT statement. You cannot specify as a default role any role managed by an external service including a directory service (external roles or global roles).

The following example establishes default roles for user jane:

ALTER USER jane DEFAULT ROLE payclerk, pettycash;

You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to limit the default user roles.

Restricting the Number of Roles that a User Can Enable

A user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES. All indirectly granted roles enabled as a result of enabling a primary role are included in this count. The database administrator can alter this limitation by modifying the value for this parameter. Higher values permit each user session to have more concurrently enabled roles, but these values also cause more memory to be used for each user session. This occurs because the PGA size requires four bytes for each role in each session. Determine the highest number of roles that will be concurrently enabled by any one user and use this value for the MAX_ENABLED_ROLES parameter.

Using Operating System Role Identification

To operate a database so that it uses the operating system to identify each user's database roles when a session is created, set the initialization parameter OS_ROLES to TRUE (and restart the instance, if it is currently running). When a user attempts to create a session with the database, Oracle Database initializes the user security domain using the database roles identified by the operating system.

To identify database roles for a user, each Oracle user's operating system account must have operating system identifiers (these may be called groups, rights identifiers, or other similar names) that indicate which database roles are to be available for the user. Role specification can also indicate which roles are the default roles of a user and which roles are available with the ADMIN OPTION. No matter which operating system is used, the role specification at the operating system level follows the format:

ora_ID_ROLE[_[d][a]]

where:

• ID has a definition that varies on different operating systems. For example, on VMS, ID is the instance identifier of the database, on MVS, it is the machine type, and on UNIX, it is the system ID.

  Note:

  ID is case-sensitive to match your ORACLE_SID. ROLE is not case-sensitive.

• ROLE is the name of the database role.

• d is an optional character that indicates this role is to be a default role of the database user.

• a is an optional character that indicates this role is to be granted to the user with the ADMIN OPTION. This allows the user to grant the role to other roles only. Roles cannot be granted to users if the operating system is used to manage roles.

  Note:

  If either the d or a character is specified, then they must be preceded by an underscore.

For example, an operating system account might have the following roles identified in its profile:

ora_PAYROLL_ROLE1
ora_PAYROLL_ROLE2_a
ora_PAYROLL_ROLE3_d
ora_PAYROLL_ROLE4_da

When the corresponding user connects to the payroll instance of Oracle, role3 and role4 are defaults, while role2 and role4 are available with the ADMIN OPTION.

Using Operating System Role Management

When you use operating system managed roles, it is important to note that database roles are being granted to an operating system user. Any database user to which the operating system user is able to connect will have the authorized database roles enabled. For this reason, you should consider defining all Oracle users as IDENTIFIED EXTERNALLY if you are using OS_ROLES = TRUE, so that the database accounts are tied to the operating system account that was granted privileges.

Granting and Revoking Roles When OS_ROLES=TRUE

If OS_ROLES is set to TRUE, then the operating system completely manages the grants and revokes of roles to users. Any previous grants of roles to users using GRANT statements do not apply, however, they are still listed in the data dictionary. Only the role grants made at the operating system level to users apply. Users can still grant privileges to roles and users.

Enabling and Disabling Roles When OS_ROLES=TRUE

If OS_ROLES is set to TRUE, then any role granted by the operating system can be dynamically enabled using the SET ROLE statement. This still applies, even if the role was defined to require a password or operating system authorization. However, any role not identified in a user's operating system account cannot be specified in a SET ROLE statement, even if a role has been granted using a GRANT statement when OS_ROLES = FALSE. (If you specify such a role, then Oracle ignores it.)

When OS_ROLES = TRUE, a user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES.

Using Network Connections with Operating System Role Management

If you choose to have the operating system to manage roles, then by default users cannot connect to the database through the shared server. This restriction is the default because a remote user could impersonate another operating system user over a nonsecure connection.

If you are not concerned with this security risk and want to use operating system role management with the shared server, or any other network connection, then set the initialization parameter REMOTE_OS_ROLES in the database's initialization parameter file to TRUE. The change will take effect the next time you start the instance and mount the database. The default setting of this parameter is FALSE.

Listing All System Privilege Grants

The following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                    NO
JWARD              CREATE SESSION                    NO

Listing All Role Grants

The following query returns all the roles granted to users and other roles:

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---
SWILLIAMS          SECURITY_ADMIN                       NO

Listing Object Privileges Granted to a User

The following query returns all object privileges (not including column-specific privileges) granted to the specified user:

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
    WHERE GRANTEE = 'JWARD';

TABLE_NAME   PRIVILEGE    GRANTABLE
-----------  ------------ ----------
EMP          SELECT       NO
EMP          DELETE       NO

To list all the column-specific privileges that have been granted, use the following query:

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
    FROM DBA_COL_PRIVS;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
-----------  ------------   -------------    --------------
SWILLIAMS    EMP            ENAME            INSERT
SWILLIAMS    EMP            JOB              INSERT
JWARD        EMP            NAME             INSERT
JWARD        EMP            JOB              INSERT

Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

SELECT * FROM SESSION_ROLES;

If swilliams has enabled the security_admin role and issues this query, then Oracle Database returns the following information:

ROLE
------------------------------
SECURITY_ADMIN

The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;

If swilliams has the security_admin role enabled and issues this query, then Oracle returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE

If the security_admin role is disabled for swilliams, then the first query would return no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

Listing Roles of the Database

The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

ROLE                  PASSWORD
----------------      --------
CONNECT               NO
RESOURCE              NO
DBA                   NO
SECURITY_ADMIN        YES

Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles. For example, the following query lists all the roles granted to the system_admin role:

SELECT GRANTED_ROLE, ADMIN_OPTION
   FROM ROLE_ROLE_PRIVS
   WHERE ROLE = 'SYSTEM_ADMIN';

GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO

The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

ROLE                    PRIVILEGE                      ADM
----------------------- -----------------------------  ---
SECURITY_ADMIN           ALTER PROFILE                 YES
SECURITY_ADMIN           ALTER USER                    YES
SECURITY_ADMIN           AUDIT ANY                     YES
SECURITY_ADMIN           AUDIT SYSTEM                  YES
SECURITY_ADMIN           BECOME USER                   YES
SECURITY_ADMIN           CREATE PROFILE                YES
SECURITY_ADMIN           CREATE ROLE                   YES
SECURITY_ADMIN           CREATE USER                   YES
SECURITY_ADMIN           DROP ANY ROLE                 YES
SECURITY_ADMIN           DROP PROFILE                  YES
SECURITY_ADMIN           DROP USER                     YES
SECURITY_ADMIN           GRANT ANY ROLE                YES

The following query lists all the object privileges granted to the security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
    WHERE ROLE = 'SECURITY_ADMIN';

TABLE_NAME                     PRIVILEGE
---------------------------    ----------------
AUD$                           DELETE
AUD$                           SELECT