Question: I want to display all of the privileges for an Oracle user, including direct grants and role grants. How do I display everything that has been granted to user?
Answer: For full scripts, download the Oracle script collection. There are many views that contain the privileges for a user:
- dba_sys_privs
- dba_tab_privs
- dba_role_privs
- table_privileges
This query shows all table-level granted privileges for a user named 'MYUSER':
select
owner,
table_name,
select_priv,
insert_priv,
delete_priv,
update_priv,
references_priv,
alter_priv,
index_priv
from
table_privileges
where
grantee = 'USER_A'
order by
owner,
table_name;
This query shows all role privileges for a user:
select distinct
owner,
table_name,
privilege
from
dba_role_privs rp,
role_tab_privs rtp
where
rp.granted_role = rtp.role
and
rp.grantee = 'MYUSER'
order by
owner,
table_name;
The following example will display all system and role privileges for a user named MYUSER:
select
privilege
from
sys.dba_sys_privs
where
grantee = 'MYUSER'
union
select
privilege
from
dba_role_privs rp
join
role_sys_privs rsp
on (rp.granted_role = rsp.role)
where rp.grantee = 'MYUER'
order by 1;