-- The table DBA_Role_Privs can be used analyze the privileges an account has. When role 
-- changes occur, some privileges may need to be revoked. 

-- The last option is to drop the user using
--  DROP USER username CASCADE;

-- The query shown below can be used to create DDL string from DBA_Tab_Privs.

SELECT 'GRANT '||
 LISTAGG(privilege,',') WITHIN GROUP (ORDER BY table_name)||' ON '||table_name||
    ' TO '||grantee||';' ddl_priv
FROM dba_tab_privs
WHERE grantee = UPPER('&i_grantee')
AND   owner = user
GROUP BY table_name, grantee
ORDER BY table_name;

-- Privileges of a database role

SELECT
 role, owner, table_name,
 LISTAGG(privilege,',') WITHIN GROUP (ORDER BY table_name) privilege_list 
FROM  role_tab_privs
WHERE role = UPPER('&i_rolename')
GROUP BY
 role, owner, table_name
;

-- All privileges query

WITH qt AS
(SELECT   -- c.column_name, t.grantable, t.hierarchy,
  t.owner, t.table_name, 
  t.grantee, t.grantor, t.privilege
 FROM dba_tab_privs t
 JOIN dba_col_privs c 
 ON (t.owner = c.owner AND t.grantee = c.grantee)
), q_dr AS
(SELECT DISTINCT
  qt.owner||'.'||qt.table_name  object_name,
  qt.privilege,
  qt.grantee, qt.grantor
 FROM qt), q_ddl AS
(SELECT   q_dr.object_name,
  LISTAGG(q_dr.privilege,', ') WITHIN GROUP (ORDER BY q_dr.object_name)
   privilege,
  q_dr.grantee, q_dr.grantor
 FROM q_dr
 GROUP BY q_dr.object_name, q_dr.grantee, q_dr.grantor)
SELECT 'GRANT '||
 q_ddl.privilege||' ON '||q_ddl.object_name||' TO '||q_ddl.grantee||';'
 str_ddl
FROM q_ddl;



-- The query below shows a list of all the privileges

WITH  qt_rl AS
(SELECT   'SYS' priv_type,
   NULL  as owner, NULL as table_name, NULL as column_name,
   grantee,  privilege, NULL as granted_role,
   admin_option, NULL as default_role,
   NULL as grantor, NULL as grantable, NULL as hierarchy
 FROM dba_sys_privs
 UNION
 SELECT   'ROLE' priv_type,
   NULL as owner, NULL as table_name, NULL as column_name,
   grantee,  NULL as privilege, granted_role,
   admin_option,
   default_role,
   NULL as grantor, NULL as grantable, NULL as hierarchy
 FROM dba_role_privs
 UNION
 SELECT   'TAB' priv_type,
   owner, table_name, NULL as column_name,
   grantee, privilege,
   NULL as granted_role,
   NULL as admin_option,
   NULL as default_role,
   grantor,
   grantable,
   hierarchy  
 FROM dba_tab_privs
 UNION
 SELECT   'COL' priv_type,
   owner, table_name, column_name,
   grantee, privilege,
   NULL as granted_role,
   NULL as admin_option,
   NULL as default_role,
   grantor,
   grantable,
   NULL as hierarchy  
 FROM dba_col_privs
) SELECT * FROM qt_rl
WHERE 
-- grantee = 'IMP_FULL_DATABASE'  AND   priv_type = 'COLUMN'
table_name = 'DBA_VIEWS' -- 'DBMS_ALERT'
;