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> /

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

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.

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.