Oracle: Show user-privileges

Here’s a script to show you the privileges given to one or more specified users. Done quick & dirty so please feel free to beautify it.
The script shows all SYSTEM-privileges (granted by role or direct) and OBJECT-privileges and also the tablespace-quotas granted to the given user-schema.

To hand over more than one user to the query, you have to use a little odd syntax:

USER1','USER2','USER3

Notice that you have to omit the leading and trailing single quotation marks.

undefine Users;

SELECT a.grantee,
  b.privilege,
  a.granted_role AS GRANTED_BY_ROLE,
  NULL           AS OBJECT,
  NULL           AS OBJ_OWNER
FROM dba_role_privs a
JOIN dba_sys_privs b
ON a.granted_role=b.grantee
WHERE a.grantee IN ('&&Users')
UNION
SELECT grantee,
  privilege,
  'n/a',
  NULL,
  NULL
FROM dba_sys_privs
WHERE grantee IN ('&&Users')
UNION
SELECT grantee,
  privilege,
  ' ',
  table_name,
  owner
FROM DBA_TAB_PRIVS
WHERE grantee IN ('&&Users') 
UNION
SELECT username,
  'QUOTA',
  DECODE(max_bytes, -1, 'unlimited', max_bytes/1024/1024) MAX_MB,
  'TABLESPACE: ' || tablespace_name,
  null
FROM DBA_TS_QUOTAS
where username in ('&&Users')
order by grantee, privilege;

Bear in mind, that this script doesn’t give an all complete listing of the user’s privileges. For example access-privileges to directory-objects or roles granted by roles are not yet implemented.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: