3.8 How do I…Determine user object privileges?Problem
I want to determine what object privileges have been granted to a user account. Because a user account’s object 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 object privilege, such as SELECT, INSERT, UPDATE, DELETE, and ALTER. How do I determine the user’s object privileges?
Technique
The DBA_TAB_PRIVS view contains object privileges granted directly to user accounts. The ROLE_TAB_PRIVS view can be used with other views to calculate the effective object privileges of a user account. Determining the privileges granted to a role can become complicated when role grants become nested. If roles have not been granted other roles, it is easy to determine the privileges granting a role provides. This How-To discusses both possibilities.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP3_9.sql creates the sample user accounts and roles used in this How-To. The script is explained in How-To 3.8. If you have not done so already, 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_14.sql into the SQL buffer. The file contains a query used to determine the object privileges granted directly to a user account. The DBA_TAB_PRIVS view contains all object privileges granted directly to user accounts.
SQL> GET CHP3_14.sql
1 SELECT OWNER ||’.’|| TABLE_NAME OBJECT,
2 PRIVILEGE FROM
3 DBA_TAB_PRIVS
4* WHERE GRANTEE = ‘&GRANTEE’
Line 1 concatenates the owner of the object and the object name to return the object in the format most often used to represent it. 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 specifies the user account granted the privileges with the &GRANTEE substitution variable.
3. Execute the query for the TERRI user account by replacing the &GRANTEE substitution variable with TERRI. The TERRI user account has been granted privileges directly on the table, so the DBA_TAB_PRIVS view contains data for the user account. If all object privileges were granted to the user account through roles, the query would return no rows.
SQL> /
Enter value for 1: TERRI
OBJECT PRIVILEGE
------------------------------ -----------
WAITE.DEPT03 DELETE
WAITE.DEPT03 INDEX
WAITE.DEPT03 INSERT
WAITE.DEPT03 SELECT
WAITE.DEPT03 UPDATE
WAITE.DEPT03 REFERENCES
WAITE.DEPT03 ALTER
7 rows selected.
4. Load CHP3_15.sql (shown in Figure 3.16) into the SQL buffer with the GET command. This file contains a query used to determine object privileges granted to the user account through roles.
Line 1 returns the owner and table name on which the user account was granted privileges through a role. ROLE_TAB_PRIVS and DBA_ROLE_PRIVS views are specified in line 3 as the source of the query. The WHERE clause in lines 4 through 6 joins the views and returns only records specified by the &GRANTEE substitution variable.
5. Execute the query for the TERRI user account by replacing the &GRANTEE substitution variable in both locations with TERRI. The user account’s privileges are listed one per row, making the output difficult to read. CHP3_16.sql, shown in the next step, formats the data as an easy-to-read report.
SQL> /
6. CHP3_16.sql contains a SQL*Plus report to print all privileges granted directly or indirectly to a user account. The report can be run by executing a START command.Enter value for grantee: TERRI
old 6: DP.GRANTEE = ‘&GRANTEE’
new 6: DP.GRANTEE = ‘TERRI’
Enter value for grantee: TERRI
old 12: UP.GRANTEE = ‘&GRANTEE’
new 12: UP.GRANTEE = ‘TERRI’
no rows selected
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 RP.OWNER ||’.’|| RP.TABLE_NAME OBJECT,
DECODE(SUM(DECODE(PRIVILEGE,’ALTER’,1,0)),1,’ALTER’,NULL) A,
DECODE(SUM(DECODE(PRIVILEGE,’INDEX’,1,0)),1,’INDEX’,NULL) B,
DECODE(SUM(DECODE(PRIVILEGE,’SELECT’,1,0)),1,’SELECT’,NULL) C,
DECODE(SUM(DECODE(PRIVILEGE,’ALTER’,1,0)),1,’INSERT’,NULL) D,
DECODE(SUM(DECODE(PRIVILEGE,’UPDATE’,1,0)),1,’UPDATE’,NULL) E,
DECODE(SUM(DECODE(PRIVILEGE,’DELETE’,1,0)),1,’DELETE’,NULL) F,
DECODE(SUM(DECODE(PRIVILEGE,’REFERENCE’,1,0)),1,’REFERENCE’,NULL) G FROM
ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
WHERE
RP.ROLE = DP.GRANTED_ROLE AND
DP.GRANTEE = ‘&&GRANTEE’
GROUP BY RP.OWNER, RP.TABLE_NAME
UNION
SELECT
UP.OWNER ||’.’|| UP.TABLE_NAME,
DECODE(SUM(DECODE(PRIVILEGE,’ALTER’,1,0)),1,’ALTER’,NULL) A,
DECODE(SUM(DECODE(PRIVILEGE,’INDEX’,1,0)),1,’INDEX’,NULL) B,
DECODE(SUM(DECODE(PRIVILEGE,’SELECT’,1,0)),1,’SELECT’,NULL) C,
DECODE(SUM(DECODE(PRIVILEGE,’ALTER’,1,0)),1,’INSERT’,NULL) D,
DECODE(SUM(DECODE(PRIVILEGE,’UPDATE’,1,0)),1,’UPDATE’,NULL) E,
DECODE(SUM(DECODE(PRIVILEGE,’DELETE’,1,0)),1,’DELETE’,NULL) F,
DECODE(SUM(DECODE(PRIVILEGE,’DELETE’,1,0)),1,’REFERENCE’,NULL) G
FROM
DBA_TAB_PRIVS UP
WHERE
UP.GRANTEE = ‘&&GRANTEE’
GROUP BY UP.OWNER, UP.TABLE_NAME
ORDER BY 1;
Run the report for the TERRI user account. Figure 3.17 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 object privileges granted directly to a user account by querying DBA_TAB_PRIVS. Steps 4 and 5 include object privileges granted to the user account through roles. Step 6 presents and runs the CHP3_15.sql file, which displays a report of all object 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_TAB_PRIVS contains the object privileges granted to the connected user account. USER_ROLE_PRIVS contains roles granted to the user account.