пятница, 17 марта 2017 г.

List all privileges for user in oracle

-- You can filter results in last lines of query
WITH username(username) AS (
-- fill user name here
    SELECT UPPER('&USERNAME') FROM dual
  ),
  all_user_roles AS (
     SELECT (SELECT username FROM username) || sys_connect_by_path(granted_role, '->') PATH, granted_role, admin_option
     FROM dba_role_privs p
     START WITH grantee IN (SELECT username FROM username)
     CONNECT BY PRIOR granted_role = grantee
  ),
  grantee AS (
    SELECT granted_role NAME, PATH FROM all_user_roles
    UNION
    SELECT username, NULL AS PATH FROM username
  ),
  priv_list AS (
    SELECT 'ROLE' priv_type, granted_role priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option grantable, PATH
    FROM all_user_roles
    UNION
    SELECT 'SYSTEM' priv_type, privilege priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option, PATH
    FROM dba_sys_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'TABLE' priv_type, PRIVILEGE, owner, table_name, NULL AS column_name, grantable, PATH
    FROM dba_tab_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'COLUMN' priv_type, PRIVILEGE, owner, table_name, column_name, grantable, PATH
    FROM dba_col_privs, grantee
    WHERE grantee = grantee.name)
SELECT * 
FROM priv_list
-- optional filter
--WHERE table_name = 'MY_TABLE_NAME'
--AND priv = 'DELETE';  

Комментариев нет: