-- 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' ;
52894