8.6 How do I…Determine a user’s access to a particular object?

Problem

I need to determine whether a user has access to an object. It is important to know whether the user account can ALTER, INSERT, UPDATE, SELECT, or DELETE the data for this table. How do I determine a user’s access to a particular object?

Technique

The DBA_TAB_PRIVS data dictionary view contains the object privileges granted to all users and roles. Despite having TAB in the name of the view, it is valid for tables, sequences, procedures, packages, functions, views, database links, and other objects.

The DBA_ROLE_PRIVS data dictionary view contains which user accounts are associated with roles. Object privileges can be granted directly to a user account, or indirectly through a role or through the PUBLIC default role. If roles have not been granted other roles, it is easy to determine a user account’s access to an object. Figure 8.21 shows the description of the data dictionary views within SQL*Plus.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Run CHP8_21.SQL to create the sample table, user account, and role that will be used with this How-To (see Figure 8.22).

2. Assign the JODI user account to the READ_ONLY role and grant privileges on the DEPT8 table to the JODI user account by running CHP8_22.SQL, as shown in Figure 8.23. 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.

3. At this point, all privileges have been granted for this How-To. There are several methods to determine access to an object. The first is by determining all privileges granted directly to a user account. Run CHP8_23.SQL and pass JODI for username, DEPT8 for table_name, and WAITE for table_owner as shown in Figure 8.24.

4. The second method to determine privileges granted for an object is to determine all PUBLIC grants for that object. Run CHP8_24.SQL and pass DEPT8 for table_name, and WAITE for table_owner as shown in Figure 8.25.

5. The third method to determine privileges granted for an object is to determine all privileges on the object granted to a role of which that the user account belongs. Run CHP8_25.SQL and pass JODI for username, DEPT8 for table_name, and WAITE for table_owner as shown in Figure 8.26. The inner select returns all roles of which the user belongs. The outer select determines whether those roles have been granted any object privileges for the desired object.

6. It can be cumbersome to run several statements to determine the direct and indirect privileges for a user account on an object. All the previous three statements can be combined into one. Begin by running CHP8_26.SQL to determine the user’s access to a particular object, shown in Figure 8.27. Pass WAITE for table_owner, DEPT8 for table_name, and JODI for username. In one statement, you can see whether the user account has access to the object directly, or through a role or PUBLIC.

SQL> START CHP8_26.SQL

Enter value for table_owner: WAITE

old 3: WHERE OWNER = ‘&table_owner’ AND

new 3: WHERE OWNER = ‘WAITE’ AND

Enter value for table_name: DEPT8

old 4: TABLE_NAME = ‘&table_name’ and

new 4: TABLE_NAME = ‘DEPT8’ and

Enter value for username: JODI

old 6: GRANTEE = ‘&username’ OR

new 6: GRANTEE = ‘JODI’ OR

Enter value for username: JODI

old 10: WHERE GRANTEE = ‘&username’))

new 10: WHERE GRANTEE = ‘JODI’))

GRANTEE PRIVILEGE
--------------- ---------------
PUBLICINSERT
READ_ONLYSELECT
JODIUPDATE

How It Works

Step 1 creates the sample table, user account, and role used in this How-To. Step 2 grants privileges both directly and indirectly to the JODI user account, and assigns the READ_ONLY role to JODI. Step 3 determines the object privileges granted directly to a user account. Step 4 determines the object privileges granted indirectly to a user account through a PUBLIC grant. Step 5 determines the object privileges granted indirectly to a user account by granting to a role of which the user account belongs. Step 6 determines all object privileges granted to a particular object by combining Steps 3 through 6 into one SELECT statement.

Comments

If an object privilege has been granted through a role that has been granted another role that the user account is assigned to, then discovering object privileges is more difficult. If this is the case, you would have to traverse the role structure and determine whether the user account belongs to a role that has privileges on the table. Also, if a user account owns an object, then that user account has all object privileges associated with it. All user accounts have all privileges, both system and object, that are granted to PUBLIC.