3.7 How do I…Determine user system privileges?Problem
Because a user account’s privileges are the sum of its directly granted privileges and its role privileges, it’s hard to tell whether a user account has a specific privilege. How do I determine which system privileges have been granted to a user account?
Technique
The DBA_ROLE_PRIVS view contains the roles granted to user accounts, and the DBA_SYS_PRIVS view contains system privileges granted to user accounts. The ROLE_SYS_PRIVS view can be used with the other views to calculate the effective privileges of a user account. Figure 3.11 describes the data dictionary views within SQL*Plus.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP3_9.sql, shown in Figure 3.12, creates the sample user accounts and roles used throughout both this How-To and How-To 3.9.
The first statement creates the TERRI user account with a CREATE USER statement. The second statement creates the ROOFER role, and the third statement creates the DEPT3_1 table. The first GRANT statement grants all privileges on the DEPT3_1 table to the TERRI user account. The second GRANT statement grants the CONNECT and RESOURCE default roles, along with the ROOFER role, to the TERRI user account. Run the file to create the sample objects.
SQL> START CHP3_9.sql
User created.
Role created.
Table created.
Grant succeeded.
Grant succeeded.
2. Load CHP3_10. SQL into the SQL buffer. The file contains a query used to determine the system privileges granted directly to a user.
SQL> GET CHP3_10.sql
1 SELECT PRIVILEGE, ADMIN_OPTION
2 FROM SYS.DBA_SYS_PRIVS
3* WHERE GRANTEE = ‘&GRANTEE’
Line 1 returns the PRIVILEGE granted to the user account, along with the ADMIN_OPTION column. Line 2 specifies the SYS.DBA_SYS_PRIVS data dictionary view as the source of the query. Line 3 returns data for the user account specified by the &GRANTEE substitution variable.
3. Execute the query for the TERRI user account, and replace the &GRANTEE substitution variable with TERRI.
SQL> /
Enter value for GRANTEE: TERRI
PRIVILEGE ADM
------------------------------------- ---
UNLIMITED TABLESPACE NO
The only system privileges granted to TERRI are from the CONNECT and RESOURCE system roles. This produces only the UNLIMITED TABLESPACE privilege in the DBA_SYS_PRIVS view, which does not accurately represent TERRI’s system privileges. The ROLE_SYS_PRIVS view must be included in the query to show system privileges granted through roles.
4. Load CHP3_11. SQL (shown in Figure 3.13) into the SQL buffer with the GET command. The file contains a statement that queries privileges from both the DBA_SYS_PRIVS and ROLE_SYS_PRIVS views. The DBA_ROLE_PRIVS view is used to determine which roles the user account has been granted.
Lines 1 through 5 return the system privileges granted directly to a user account by querying the DBA_SYS_PRIVS data dictionary view. Lines 7 through 12 return system privileges granted to the user account through roles. The UNION operator in line 6 connects the two queries to generate a single result.
5. Execute the statement for the TERRI user account by replacing the &GRANTEE substitution variable with TERRI each time you are prompted to do so. Because the system privileges provided by the CONNECT and RESOURCE roles display, the output of the query is more useful in determining the privileges of the user account. Figure 3.14 shows the execution of this step in SQL*Plus.
6. Load the file CHP3_12.sql into the SQL buffer. The file contains a query used to determine the roles granted to a user account. The DBA_ROLE_PRIVS view contains roles granted to user accounts. The DBA_ROLE_PRIVS view was used in Step 4 to determine the system privileges provided to a user account through a role.
SQL> GET CHP3_12.sql
1 SELECT GRANTED_ROLE, DEFAULT_ROLE
2 FROM DBA_ROLE_PRIVS
3* WHERE GRANTEE = ‘&GRANTEE’
Line 1 returns the role granted to the user account and its status as a default role. If a role is a default role, it is enabled automatically when the user account connects to the database. Line 2 specifies the DBA_ROLE_PRIVS data dictionary view as the source of the query. Line 3 specifies the user account granted access to the role with the &GRANTEE substitution variable.
7. Execute the query for the TERRI user account by specifying TERRI for the substitution variable.
SQL> /
Enter value for 1: TERRI
GRANTED_ROLE DEF
---------------------------- ---
CONNECT YES
RESOURCE YES
ROOFER YES
CHP3_13.sql contains a SQL*Plus report to print all system privileges granted directly or indirectly to a user account. The report can be run by executing a START command.
COLUMN OBJECT FORMAT A20 HEADING ‘OBJECT/PRIV’
COLUMN PRIVILEGE FORMAT A10
COLUMN A FORMAT A6 HEADING ‘ ‘ TRUNCATE
COLUMN B FORMAT A6 HEADING ‘ ‘ TRUNCATE
COLUMN C FORMAT A6 HEADING ‘ ‘ TRUNCATE
COLUMN D FORMAT A6 HEADING ‘ ‘ TRUNCATE
COLUMN E FORMAT A6 HEADING ‘ ‘ TRUNCATE
COLUMN F FORMAT A6 HEADING ‘ ‘ TRUNCATE
COLUMN G FORMAT A6 HEADING ‘ ‘ TRUNCATE
SELECT PRIVILEGE OBJECT,
DECODE(ADMIN_OPTION,’YES’,’ADMIN’,NULL) A,
NULL B, NULL C, NULL D, NULL E, NULL F, NULL G
FROM
DBA_SYS_PRIVS
WHERE
GRANTEE = ‘&&GRANTEE’
UNION
SELECT PRIVILEGE OBJECT,
DECODE(ROLE_SYS_PRIVS.ADMIN_OPTION,’YES’,’ADMIN’,NULL) A,
NULL B, NULL C, NULL D, NULL E, NULL F, NULL G
FROM
ROLE_SYS_PRIVS, DBA_ROLE_PRIVS
WHERE
ROLE_SYS_PRIVS.ROLE = DBA_ROLE_PRIVS.GRANTED_ROLE AND
DBA_ROLE_PRIVS.GRANTEE = ‘&&GRANTEE’
GROUP BY UP.OWNER, UP.TABLE_NAME
ORDER BY 1;
8. Run the report for the TERRI user account. Figure 3.15 shows the results of the operation in SQL*Plus.
How It Works
Step 1 creates the user account, role, and table used throughout this How-To. Steps 2 and 3 list the system roles granted directly to a user account by querying DBA_SYS_PRIVS. The query used in Steps 4 and 5 includes the system privileges provided by roles granted to the user account. Steps 6 and 7 list the roles granted to a user account. Step 8 presents and runs the CHP3_13.sql file, which displays a report of all privileges granted to a user account either directly or through roles.
Comments
The data dictionary views provide information about privileges granted to a user account. Most views used in this How-To are DBA views. There is a complete set of corresponding USER views that enable a user account to query its own privileges. USER_ROLE_PRIVS contains roles granted to the user account, and USER_SYS_PRIVS contains system privileges granted to a user account.