Sometimes DBA needs to generate the report to provide audit team or management about the users access information on database. In this article, I will show how to get list of access for oracle user.
Query to find all privileges for all users in Database:
SQL> COL USERNAME FOR A15
COL PRIVILEGE FOR A25
COL OWNER FOR A15
COL TABLENAME FOR A30
COL COLUMN_NAME FOR A25
COL ADMIN_OPTION FOR A15
SET LINESIZE 200
SQL> SELECT A.*
FROM (SELECT GRANTEE USERNAME,
GRANTED_ROLE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM DBA_ROLE_PRIVS RP JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'SYSTEM' ACCESS_TYPE
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
'--' COLUMN_NAME,
GRANTABLE ADMIN_OPTION,
'TABLE' ACCESS_TYPE
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION
SELECT DP.GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
'--' ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
WHERE RP.ROLE = DP.GRANTED_ROLE
AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
GRANTABLE ADMIN_OPTION,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
'COLUMN' ACCESS_TYPE
FROM DBA_COL_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)) A
ORDER BY USERNAME,
A.TABLENAME,
CASE
WHEN A.ACCESS_TYPE = 'SYSTEM' THEN 1
WHEN A.ACCESS_TYPE = 'TABLE' THEN 2
WHEN A.ACCESS_TYPE = 'COLUMN' THEN 3
WHEN A.ACCESS_TYPE = 'ROLE' THEN 4
ELSE 5
END,
CASE
WHEN A.PRIVILEGE IN ('EXECUTE') THEN 1
WHEN A.PRIVILEGE IN ('SELECT', 'INSERT', 'DELETE') THEN 3
ELSE 2
END,
A.COLUMN_NAME,
A.PRIVILEGE;
Output:
USERNAME PRIVILEGE OWNER TABLENAME COLUMN_NAME ADMIN_OPTION ACCESS --------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------ BI ALTER SESSION -- -- -- NO SYSTEM BI CREATE CLUSTER -- -- -- NO SYSTEM BI CREATE DATABASE LINK -- -- -- NO SYSTEM BI CREATE SEQUENCE -- -- -- NO SYSTEM BI CREATE SESSION -- -- -- NO SYSTEM BI CREATE SYNONYM -- -- -- NO SYSTEM BI CREATE TABLE -- -- -- NO SYSTEM BI CREATE VIEW -- -- -- NO SYSTEM BI UNLIMITED TABLESPACE -- -- -- NO SYSTEM BI RESOURCE -- -- -- NO ROLE BI SELECT OE BOMBAY_INVENTORY -- NO TABLE BI SELECT SH CAL_MONTH_SALES_MV -- NO TABLE BI SELECT SH CHANNELS -- NO TABLE BI SELECT SH COSTS -- NO TABLE BI SELECT SH COUNTRIES -- NO TABLE BI SELECT SH CUSTOMERS -- NO TABLE BI SELECT OE CUSTOMERS -- NO TABLE BI SELECT SH FWEEK_PSCAT_SALES_MV -- NO TABLE BI SELECT OE INVENTORIES -- NO TABLE BI SELECT OE ORDERS -- NO TABLE BI SELECT OE ORDER_ITEMS -- NO TABLE BI SELECT OE PRODUCTS -- NO TABLE BI SELECT SH PRODUCTS -- NO TABLE BI SELECT OE PRODUCT_DESCRIPTIONS -- NO TABLE BI SELECT OE PRODUCT_INFORMATION -- NO TABLE BI SELECT OE PRODUCT_PRICES -- NO TABLE BI SELECT OE PROMOTIONS -- NO TABLE BI SELECT SH PROMOTIONS -- NO TABLE BI SELECT SH SALES -- NO TABLE BI SELECT OE SYDNEY_INVENTORY -- NO TABLE BI SELECT SH TIMES -- NO TABLE BI SELECT OE TORONTO_INVENTORY -- NO TABLE BI SELECT OE WAREHOUSES -- NO TABLE HR ALTER SESSION -- -- -- NO SYSTEM HR CREATE DATABASE LINK -- -- -- NO SYSTEM HR CREATE SEQUENCE -- -- -- NO SYSTEM HR CREATE SESSION -- -- -- NO SYSTEM HR CREATE SYNONYM -- -- -- NO SYSTEM HR CREATE VIEW -- -- -- NO SYSTEM HR UNLIMITED TABLESPACE -- -- -- NO SYSTEM HR RESOURCE -- -- -- NO ROLE HR EXECUTE SYS DBMS_STATS -- NO TABLE SCOTT UNLIMITED TABLESPACE -- -- -- NO SYSTEM SCOTT CONNECT -- -- -- NO ROLE
Multiple users access information:
SQL> COL USERNAME FOR A15
COL PRIVILEGE FOR A25
COL OWNER FOR A15
COL TABLENAME FOR A30
COL COLUMN_NAME FOR A25
COL ADMIN_OPTION FOR A15
SET LINESIZE 200
SQL> SELECT A.*
FROM (SELECT GRANTEE USERNAME,
GRANTED_ROLE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM DBA_ROLE_PRIVS RP JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'SYSTEM' ACCESS_TYPE
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
'--' COLUMN_NAME,
GRANTABLE ADMIN_OPTION,
'TABLE' ACCESS_TYPE
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
UNION
SELECT DP.GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
'--' ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
WHERE RP.ROLE = DP.GRANTED_ROLE
AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
GRANTABLE ADMIN_OPTION,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
'COLUMN' ACCESS_TYPE
FROM DBA_COL_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))) A
ORDER BY USERNAME,
A.TABLENAME,
CASE
WHEN A.ACCESS_TYPE = 'SYSTEM' THEN 1
WHEN A.ACCESS_TYPE = 'TABLE' THEN 2
WHEN A.ACCESS_TYPE = 'COLUMN' THEN 3
WHEN A.ACCESS_TYPE = 'ROLE' THEN 4
ELSE 5
END,
CASE
WHEN A.PRIVILEGE IN ('EXECUTE') THEN 1
WHEN A.PRIVILEGE IN ('SELECT', 'INSERT', 'DELETE') THEN 3
ELSE 2
END,
A.COLUMN_NAME,
A.PRIVILEGE;
Access list for a user:
SQL> COL USERNAME FOR A15
COL PRIVILEGE FOR A25
COL OWNER FOR A15
COL TABLENAME FOR A30
COL COLUMN_NAME FOR A25
COL ADMIN_OPTION FOR A15
SET LINESIZE 200
SQL> SELECT A.*
FROM (SELECT GRANTEE USERNAME,
GRANTED_ROLE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM DBA_ROLE_PRIVS RP JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'SYSTEM' ACCESS_TYPE
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
'--' COLUMN_NAME,
GRANTABLE ADMIN_OPTION,
'TABLE' ACCESS_TYPE
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
UNION
SELECT DP.GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
'--' ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
WHERE RP.ROLE = DP.GRANTED_ROLE
AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
GRANTABLE ADMIN_OPTION,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
'COLUMN' ACCESS_TYPE
FROM DBA_COL_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))) A
ORDER BY USERNAME,
A.TABLENAME,
CASE
WHEN A.ACCESS_TYPE = 'SYSTEM' THEN 1
WHEN A.ACCESS_TYPE = 'TABLE' THEN 2
WHEN A.ACCESS_TYPE = 'COLUMN' THEN 3
WHEN A.ACCESS_TYPE = 'ROLE' THEN 4
ELSE 5
END,
CASE
WHEN A.PRIVILEGE IN ('EXECUTE') THEN 1
WHEN A.PRIVILEGE IN ('SELECT', 'INSERT', 'DELETE') THEN 3
ELSE 2
END,
A.COLUMN_NAME,
A.PRIVILEGE;Output Log:
Enter value for user_name: BI
old 10: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new 10: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old 20: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new 20: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old 30: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new 30: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old 41: AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new 41: AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old 51: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))) A
new 51: WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))) A
USERNAME PRIVILEGE OWNER TABLENAME COLUMN_NAME ADMIN_OPTION ACCESS
--------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------
BI ALTER SESSION -- -- -- NO SYSTEM
BI CREATE CLUSTER -- -- -- NO SYSTEM
BI CREATE DATABASE LINK -- -- -- NO SYSTEM
BI CREATE SEQUENCE -- -- -- NO SYSTEM
BI CREATE SESSION -- -- -- NO SYSTEM
BI CREATE SYNONYM -- -- -- NO SYSTEM
BI CREATE TABLE -- -- -- NO SYSTEM
BI CREATE VIEW -- -- -- NO SYSTEM
BI UNLIMITED TABLESPACE -- -- -- NO SYSTEM
BI RESOURCE -- -- -- NO ROLE
BI SELECT OE BOMBAY_INVENTORY -- NO TABLE
USERNAME PRIVILEGE OWNER TABLENAME COLUMN_NAME ADMIN_OPTION ACCESS
--------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------
BI SELECT SH CAL_MONTH_SALES_MV -- NO TABLE
BI SELECT SH CHANNELS -- NO TABLE
BI SELECT SH COSTS -- NO TABLE
BI SELECT SH COUNTRIES -- NO TABLE
BI SELECT OE CUSTOMERS -- NO TABLE
BI SELECT SH CUSTOMERS -- NO TABLE
BI SELECT SH FWEEK_PSCAT_SALES_MV -- NO TABLE
BI SELECT OE INVENTORIES -- NO TABLE
BI SELECT OE ORDERS -- NO TABLE
BI SELECT OE ORDER_ITEMS -- NO TABLE
BI SELECT OE PRODUCTS -- NO TABLE
USERNAME PRIVILEGE OWNER TABLENAME COLUMN_NAME ADMIN_OPTION ACCESS
--------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------
BI SELECT SH PRODUCTS -- NO TABLE
BI SELECT OE PRODUCT_DESCRIPTIONS -- NO TABLE
BI SELECT OE PRODUCT_INFORMATION -- NO TABLE
BI SELECT OE PRODUCT_PRICES -- NO TABLE
BI SELECT OE PROMOTIONS -- NO TABLE
BI SELECT SH PROMOTIONS -- NO TABLE
BI SELECT SH SALES -- NO TABLE
BI SELECT OE SYDNEY_INVENTORY -- NO TABLE
BI SELECT SH TIMES -- NO TABLE
BI SELECT OE TORONTO_INVENTORY -- NO TABLE
BI SELECT OE WAREHOUSES -- NO TABLE
33 rows selected.
