Users’ privilege management is usually done with a few catalogs. To name few, it is dba_roles_privs, dba_sys_privs, dba_tab_privs, and dba_col_privs. Check out those scripts below waiting to help you out 🙂
-- concise version .. good for an overall and high-level checking
SELECT a.*
FROM (SELECT 'ROLE' typ
,grantee grantee
,granted_role priv
,admin_option ad
,'--' tabnm
,'--' colnm
,'--' owner
,r.password_required pwd
FROM dba_role_privs rp
--
JOIN dba_roles r
ON rp.granted_role = r.role
--
WHERE 1 = 1
UNION
SELECT 'SYSTEM' typ
,grantee grantee
,privilege priv
,admin_option ad
,'--' tabnm
,'--' colnm
,'--' owner
,'--' pwd
FROM dba_sys_privs
--
WHERE 1 = 1
UNION
SELECT 'TABLE' typ
,grantee grantee
,privilege priv
,grantable ad
,table_name tabnm
,'--' colnm
,owner owner
,'--' pwd
FROM dba_tab_privs
--
WHERE 1 = 1
UNION
SELECT 'COLUMN' typ
,grantee grantee
,privilege priv
,grantable ad
,table_name tabnm
,column_name colnm
,owner owner
,'--' pwd
FROM dba_col_privs
--
WHERE 1 = 1
) a
WHERE 1 = 1
AND grantee = 'JOHN_DOE'
--
ORDER BY DECODE(a.typ,
'ROLE',4,
'SYSTEM',1,
'TABLE',2,
'COLUMN',3,
5)
,CASE
WHEN a.priv IN ('EXECUTE') THEN 1
WHEN a.priv IN ('SELECT', 'UPDATE', 'INSERT', 'DELETE') THEN 3
ELSE 2
END
,a.tabnm
,a.colnm
,a.priv;
If you are serious about privilege checking, have a look at this monster 🙂 It takes a little longer to proceed but will give you a very detailed overview of a particular user and his access privileges. The last row (ROLES) will show you through which role is that particular privilege granted/inherited (NULL = direct grant).
-- users --------------------------------------------------------------------------
WITH w_usr AS (
SELECT username
FROM dba_users
WHERE 1=1
--AND username in ('JOHN_DOE')
)
-- objects ------------------------------------------------------------------------
, w_obj AS (
SELECT p.grantee
,p.owner
,p.table_name
,p.privilege
--,p.grantor
--,p.grantable
--,p.hierarchy
FROM dba_tab_privs p
UNION ALL
SELECT grantee
,NULL owner
,NULL table_name
,privilege
--,admin_option
FROM dba_sys_privs
)
-- roles hierachy -----------------------------------------------------------------
, w_rol AS (
SELECT connect_by_root r.grantee grantee
,r.granted_role
,LEVEL llevel
,sys_connect_by_path(r.granted_role, ' -> ') roles
FROM dba_role_privs r
START WITH r.grantee IN (SELECT username FROM w_usr)
CONNECT BY r.grantee = PRIOR r.granted_role
)
-- Direct grant -------------------------------------------------------------------
, w_drc AS (
SELECT o.grantee
, /*+ PARALLEL(4) */t.object_type
,o.owner
,o.table_name
,o.privilege
,0 llevel
,NULL
FROM w_obj o
,dba_objects t
WHERE grantee IN (SELECT username FROM w_usr)
AND o.owner = t.owner
AND o.table_name = t.object_name
AND t.subobject_name IS NULL
)
--------------------------------------------------------------------------------------------------
SELECT a.*
FROM (
SELECT /*+ PARALLEL(4) */
r.grantee
,t.object_type
,o.owner
,o.table_name object_name
,o.privilege
,r.llevel
,substr(r.roles, 5) roles
FROM w_rol r
,w_obj o
,dba_objects t
WHERE r.granted_role = o.grantee(+)
AND o.owner = t.owner(+)
AND o.table_name = t.object_name(+)
AND t.subobject_name IS NULL
UNION ALL
SELECT *
FROM w_drc
WHERE 1=1
) a
WHERE 1 = 1
AND grantee IN ('JOHN_DOE')
--AND owner = 'SCHEMA_NAME'
You can limit the list of users right in the W_USR query in order to speed up the result retrieval. You can also use some masks to filter out only some user “categories” (application accounts, user accounts, system accounts only, … )