8.5 How do I…Determine the privileges associated with a role?

Problem

I want to determine which privileges have been granted to a role. This will let me see whether the role requires more privileges or whether it is the right role to grant a user account. How do I determine which privileges have been granted to a role?

Technique

The ROLE_TAB_PRIVS data dictionary view contains the object privileges granted to a role. The ROLE_ROLE_PRIVS view contains the roles granted to another role, and the ROLE_SYS_PRIVS view contains system privileges granted to a role. Determining the privileges granted to a role can become complicated when role grants become nested. If roles have not been granted other ones, it is easy to determine the privileges that granting a role provides. Figure 8.17 shows the description of the data dictionary views within SQL*Plus.

Steps

1. Run SQL*Plus and connect as the WAITE user account. If you were in SQL*Plus already from the previous How-To, then you must exit and run SQL*Plus again to enable all roles. CHP8_17.SQL, shown in Figure 8.18 , creates a table and two roles that are used throughout this How-To.

The first statement creates the WAITRESS role and the second statement creates the BARTENDER role. The CREATE TABLE statement creates a table with privileges on it granted in the first GRANT statement. The second GRANT statement grants the CONNECT default role to the BARTENDER role.

2. Run the file to create the sample roles.

SQL> START CHP8_17.sql

Role created.

Role created.

Table dropped.

Table created.

Grant succeeded.

Grant succeeded.

3. Load CHP8_18.SQL into the SQL buffer. The file contains a query used to determine the object privileges granted to a role. The ROLE_TAB_PRIVS view contains all the object privileges granted to roles.

SQL> GET CHP8_18.sql

1 SELECT OWNER | | ’.’ | | TABLE_NAME OBJECT,

2 PRIVILEGE FROM

3 DBA_TAB_PRIVS

4* WHERE GRANTEE = ‘&ROLE’

Line 1 concatenates the OWNER and TABLE_NAME columns to return the object in the format in which it is usually used. Line 2 returns the privilege granted to the object. Line 3 specifies the DBA_TAB_PRIVS data dictionary view as the source of the query. Line 4 causes information for the role specified by the &ROLE substitution variable to be returned.

3. Format the output of the column using the COLUMN command. Execute the query for the WAITRESS role created in Step 1 by replacing the &ROLE substitution variable with WAITRESS.

SQL> COLUMN OBJECT FORMAT A30

SQL> COLUMN PRIVILEGE FORMAT A30

SQL> /

Enter value for 1: WAITRESS

old 4: where role = ‘&ROLE’

new 4: where role = ‘WAITRESS’

OBJECTPRIVILEGE
----------------------------------------------------------
WAITE.DEPT8DELETE
WAITE.DEPT8INSERT
WAITE.DEPT8SELECT
WAITE.DEPT8UPDATE

4. Load CHP8_19.SQL into the SQL buffer and run it for the BARTENDER role to view roles granted to this role. Replace the &ROLE substitution variable with BARTENDER, as shown in Figure 8.19. The file contains a query that identifies roles granted to a role.

Lines 1 and 2 return the GRANTED_ROLE column from the ROLE_ROLE_PRIVS. Line 3 causes information to be returned for the role specified by the &ROLE substitution variable. The ROLE_ROLE_PRIVS view returns roles that have been granted to other roles.

5. Load CHP8_20.SQL into the SQL buffer and run it, as shown in Figure 8.20. Execute the statement for the CONNECT system role by specifying CONNECT as the &ROLE substitution variable. The file contains a query that identifies system privileges granted to a role.

Line 1 returns the PRIVILEGE and ADMIN_OPTION columns. The ADMIN_OPTION contains YES if the privilege can be granted by users of the role to other user accounts and roles. Line 3 specifies the ROLE_SYS_PRIVS data dictionary view as the source of the query. Line 5 returns information for the role specified by the &ROLE substitution variable.

How It Works

The DBA_TAB_PRIVS data dictionary view contains object privileges granted to a role. ROLE_SYS_PRIVS contains system privileges granted to the role and ROLE_ROLE_PRIVS contains other roles granted to a role. Steps 1 and 2 list the object privileges granted to a role by querying the ROLE_TAB_PRIVS view. Steps 3 and 4 list the roles granted to a role by querying the ROLE_ROLE_PRIVS view. Step 5 queries ROLE_SYS_PRIVS to determine the system privileges granted to the role.

Comments

To determine the privileges granted to a role, take all the system and object privileges granted directly to it and add the privileges provided by other roles. The data dictionary is the source for information regarding privileges and roles. How-To’s 8.1 through 8.7 provide additional information about privileges and roles.