a collection of scripts to ease the day-to-day work of an Oracle DBA
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 

78 lines
3.4 KiB

-- =============================================================================
-- List all users with their privileges;
-- Recursively, so resolve Grants received via Roles;
-- Exclude Oracle managed users (SYS, SYSTEM etc).
-- You can adjust the filter at the end of the script (last WHERE clause)
--
-- Adapted from https://stackoverflow.com/a/51320661/2533433
-- (which was "for a given user") to cover all "non-system" users
-- by Izzy.
-- =============================================================================
COL USERNAME FOR A20 HEADING UserName
COL PRIVILEGE FOR A40 HEADING Privilege
COL OBJ_OWNER FOR A20 HEADING 'Object Owner'
COL OBJ_NAME FOR A30 HEADING 'Object Name'
COL GRANT_SOURCES FOR A38 HEADING 'Grant Sources'
COL ADMIN_OR_GRANT_OPT FOR A6 HEADING 'Admin|Grant|Option'
COL HIERARCHY_OPT FOR A6 HEADING 'Hier-|archy|Option'
SET LINES 160
SET PAGES 0 EMBEDDED ON
SELECT
USERNAME,
PRIVILEGE,
OBJ_OWNER,
OBJ_NAME,
LISTAGG(GRANT_TARGET, ',') WITHIN GROUP (ORDER BY GRANT_TARGET) AS GRANT_SOURCES, -- Lists the sources of the permission
MAX(ADMIN_OR_GRANT_OPT) AS ADMIN_OR_GRANT_OPT, -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
MAX(HIERARCHY_OPT) AS HIERARCHY_OPT -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
FROM (
-- Gets all roles a user has, even inherited ones
WITH ALL_ROLES_FOR_USER AS (
SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
)
SELECT
PRIVILEGE,
OBJ_OWNER,
OBJ_NAME,
USERNAME,
REPLACE(GRANT_TARGET, USERNAME, 'Direct to user') AS GRANT_TARGET,
ADMIN_OR_GRANT_OPT,
HIERARCHY_OPT
FROM (
-- System privileges granted directly to users
SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION --ALL
-- System privileges granted users through roles
SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
FROM DBA_SYS_PRIVS
JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_SYS_PRIVS.GRANTEE
UNION --ALL
-- Object privileges granted directly to users
SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, GRANTABLE, HIERARCHY
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION --ALL
-- Object privileges granted users through roles
SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, GRANTEE AS USERNAME, ALL_ROLES_FOR_USER.GRANTED_ROLE AS GRANT_TARGET, GRANTABLE, HIERARCHY
FROM DBA_TAB_PRIVS
JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_TAB_PRIVS.GRANTEE
) ALL_USER_PRIVS
-- Adjust your filter here
WHERE
USERNAME IN (SELECT USERNAME FROM DBA_USERS WHERE oracle_maintained='N') AND
USERNAME NOT IN ('EXFSYS')
) DISTINCT_USER_PRIVS
GROUP BY
PRIVILEGE,
OBJ_OWNER,
OBJ_NAME,
USERNAME
ORDER BY
username, obj_owner, obj_name, privilege
;