User Privileges Script

I keep running into situations on Oracle databases where I need to dump out the privileges an Oracle user has. I have been just manually putting together SQL statements like:

select * from dba_role_privs where grantee='MYUSER';
select * from dba_sys_privs where grantee='MYUSER';
select * from dba_tab_privs where grantee='MYUSER';
select * from dba_users where username='MYUSER';

This captures the three kinds of grants the user could have in the first three queries and the last query just shows if the user exists and things like whether it is locked. Really this simple set of queries is good enough in most cases.

But I had also wrote a script that would show all the system and object grants that were included in the roles. Because you can have roles granted to roles, you must loop through all the roles until you get down to the base system and object privileges. I rewrote this logic from scratch several times until I finally convinced myself to make a script and save it on my GitHub site. The current version of the script is here:


The interesting part of the script is where we keep looping through the roles in table my_role_privs deleting each role and then inserting the role’s system, object, and role privileges into the my_sys_privs, my_tab_privs, and my_role_privs tables. Eventually you run out of roles to delete and the loop finishes. I guess this works because you cannot have a circular role grant situation:

SQL> create role a;

Role created.

SQL> create role b;

Role created.

SQL> grant a to b;

Grant succeeded.

SQL> grant b to a;
grant b to a
ERROR at line 1:
ORA-01934: circular role grant detected

In the past I have put a loop counter in the code just in case there was something circular or a really long chain of roles, but this version does not have it.

To make the output useful I put it in three sections. The first section just has the direct grants and corresponds to the first three queries listed above.

Privileges granted directly to user MYUSER
Role privileges for user MYUSER

System privileges for user MYUSER

Summarized table privileges for user MYUSER

OWNER                PRIVILEGE                                  COUNT(*)
-------------------- ---------------------------------------- ----------
SYS                  EXECUTE                                           1
Detailed table privileges for user MYUSER

PRIVILEGE                                OWNER                TABLE_NAME
---------------------------------------- -------------------- -----------
EXECUTE                                  SYS                  DBMS_RANDOM

I put counts of each type of object grants in case there was a bunch. I called them table privileges because view is named dba_tab_privs but I really should have called them object privileges because they can be grants on objects which are not tables.

The second section has the output of the loop showing all the system and object privileges implied by the role grants as well as those granted directly to the user:

Privileges granted through a role or directly to user MYUSER
System privileges for user MYUSER

Summarized table privileges for user MYUSER

OWNER                PRIVILEGE                                  COUNT(*)
-------------------- ---------------------------------------- ----------
AUDSYS               EXECUTE                                           1
GSMADMIN_INTERNAL    EXECUTE                                           1
OUTLN                SELECT                                            3
SYS                  DELETE                                           11
SYS                  EXECUTE                                         169
SYS                  FLASHBACK                                        14
SYS                  INSERT                                           12
SYS                  READ                                             15
SYS                  SELECT                                         4759

Detailed table privileges for user MYUSER

PRIVILEGE                                OWNER                TABLE_NAME
---------------------------------------- -------------------- ------------------------
DELETE                                   SYS                  AUX_STATS$
DELETE                                   SYS                  DBA_REGISTRY_SQLPATCH
DELETE                                   SYS                  EXPIMP_TTS_CT$
DELETE                                   SYS                  INCEXP
DELETE                                   SYS                  INCFIL

I use this a lot of times to see if a user has CREATE SESSION either directly or through a role so that I will know whether the user can login.

Lastly, I included a couple of details about the user at the end:

Account status, last password change for user ZBL6050

-------------------------------- -------------------
OPEN                             2023-10-10 11:01:01

You need to give the user that runs userprivs.sql SELECT on sys.user$ to get the last password changed date and time. Otherwise, this query returns an error.

I mainly use this script to validate if a user has the correct permissions and if they can log in, so putting this information at the end in addition to the grant information above just fills in some details I would have to query anyway. I.e., Is the user locked? How long since they changed their password?

I thought about bringing down some statement about CREATE SESSION here. As it is written now, I have to visually scan the system privileges for CREATE SESSION to get the full picture on the user’s ability to login. It might be nice to add a column “Has CREATE SESSION” to this screen.

There are probably fancier scripts and tools to do all this, but this is what I have been using and the reasoning behind it. Maybe it will be useful to others and a reminder to myself to document it here.


About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.