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
GRANTED_ROLE
--------------------
DBA
RESOURCE
System privileges for user MYUSER
PRIVILEGE
----------------------------------------
SELECT ANY TABLE
UNLIMITED TABLESPACE
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_RANDOMI 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
PRIVILEGE
----------------------------------------
ADMINISTER ANY SQL TUNING SET
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
...
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
ACCOUNT_STATUS LAST_PASSWORD_CHNG
-------------------------------- -------------------
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.
Bobby
