8.7 How do I…Determine which users have access to a particular object?

Problem

I have a sensitive object in my database. It is important to know which users can ALTER, INSERT, UPDATE, SELECT, or DELETE the data if the object is a table, or which users can EXECUTE the object if it is a package, stored procedure, or function. How do I determine which users have access to a particular object?

Technique

User accounts are granted access to an object either indirectly through a grant or directly. By querying the DBA_TAB_PRIVS and DBA_ROLE_PRIVS data dictionary views, all users that have access to a particular object, along with their privileges, can be determined. The DBA_TAB_PRIVS data dictionary view contains the object privileges granted to all users and roles for tables, sequences, procedures, packages, functions, views, and database links among all other objects. The DBA_ROLE_PRIVS data dictionary view contains which user accounts are associated with roles. How-To 8.6 provides additional information about roles.

Steps

1. Run SQL*Plus and connect as the WAITE user account. If you have not already done so in How-To 8.6, run CHP8_21.SQL to create the sample table, user account, and role that will be used with this How-To.

2. Grant the object privileges to the JODI user account and role by running CHP8_22.SQL, if you have not already done so. The JODI user account is assigned to the READ_ONLY role. The first statement assigns the READ_ONLY role to the JODI user account. The second statement grants UPDATE privileges on the DEPT8 table directly to the JODI user account. The third statement grants SELECT privileges on the DEPT8 table indirectly to the JODI user account by granting to the READ_ONLY role, of which the JODI account belongs. The fourth statement indirectly grants INSERT privileges on the DEPT8 table to the DEPT8 table by granting the privilege to PUBLIC. Granting to PUBLIC enables all user accounts to use the privilege.

To determine which user accounts have access to an object, one of three conditions must be true. If PUBLIC has been granted, then ALL user accounts in the database have the privilege. If the user account has been explicitly granted a privilege, then the user has the privilege. Also, if a role has been granted a privilege, then all user accounts in that role have the privilege.

3. CHP8_27.SQL, shown in Figure 8.28, determines which users have access to a particular object. In the example, you will pass DEPT8 for object_name and WAITE for object_owner. If the query returns PUBLIC as a grantee, then all user accounts in the database have the corresponding privilege for the object.

4. Run the script to get the results.

SQL> START CHP8_27.sql

Enter value for object_name: DEPT8

old 4: TABLE_NAME = ‘&object_name’ AND

new 4: TABLE_NAME = ‘DEPT8’ AND

Enter value for object_owner: WAITE

old 5: OWNER = ‘&object_owner’

new 5: OWNER = ‘WAITE’

Enter value for object_name: DEPT8

old 9: WHERE B.TABLE_NAME = ‘&object_name’ AND

new 9: WHERE B.TABLE_NAME = ‘DEPT8’ AND

Enter value for object_owner: WAITE

old 10: B.OWNER = ‘&object_owner’ AND

new 10: B.OWNER = ‘WAITE’ AND

GRANTEE PRIVILEGE
------- -------
JODI SELECT
JODI UPDATE
PUBLIC INSERT
READ_ONLY SELECT
WAITE SELECT
SQL>

How It Work

Step 1 creates the sample table, role, and user account used in this How-To. Step 2 assigns the user account to the role, and issues several grants, which are used to demonstrate selecting from data dictionary views. Steps 3 and 4 run the CHP8_27.SQL to query the DBA_ROLE_PRIVS and DBA_TAB_PRIVS data dictionary view.

Lines 1 through 5 query from the DBA_TAB_PRIVS, providing all users and roles (including PUBLIC). Because a role can be granted to several users, it is important to see all user accounts that belong to all roles returned in the query. Line 6 issues the UNION clause, which joins the query in lines 1 through 5 with the query in lines 7 through 11, which queries from both the DBA_TAB_PRIVS and the DBA_ROLE_PRIVS data dictionary views. A query similar to lines 1 through 5 is used, joining the two tables where the granted role of the DBA_ROLE_PRIVS equals the grantee of the DBA_TAB_PRIVS. The DBA_ROLE_PRIVS contains the GRANTED_ROLE and the GRANTEE columns, of which the GRANTED_ROLE column represents the role and the GRANTEE column represents the user account assigned to the role.

Comments

Several object privileges are available that can be returned from the CHP8_27.SQL query. The available object privileges in Oracle8 are: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, EXECUTE, CREATE, READ, and WRITE. The CHP8_27.SQL query works well unless a user has been granted a privilege through a role that has been granted another role, which has been granted the object privilege. If this is the case, you would have to traverse the role structure, using the information gathered from CHP8_27.SQL as a list of all roles with access to the object.