HOW TO FIND SYSTEM, ROLES AND OBJECTS PRIVILEGES THAT HAVE BEEN GRANTED TO A USER
Privilegies, Queries
How to find the privileges that have been granted to a user by querying data dictionary views.
DEMO
DEMO: HOW TO FIND SYSTEM, ROLES AND OBJECTS PRIVILEGES THAT HAVE BEEN GRANTED TO A USER
RELATED
HOW TO RETRIEVE AN OBJECT DDL OR A USER DDL USING PL/SQL
HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE
QUERY
The DBT_UPRIVSI script is a set of queries to data dictionary views that retrieve all DIRECTLY and INDIRECTLY granted privileges to a user.
The DBT_UPRIVSD script is a set of queries to data dictionary views that retrieve only DIRECTLY granted privileges to a user.
-- DBT_UPRIVSI
-------------------------------------
-- A variable for keeping a username
-------------------------------------
SET LINES 200
SET VERIFY OFF
SET SQLNUMBER OFF
SET FEEDBACK OFF
SET PAGES 999
SET LINES 200
PROMPT
ACCEPT USERNAME PROMPT 'USERNAME: '
PROMPT
------------------------------------
-- All user roles
------------------------------------
PROMPT
PROMPT -- All user roles
COL GRANTEE FOR A20
COL GRANTED_ROLE FOR A40
SELECT DISTINCT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
ORDER BY 1,2;
------------------------------------
-- System privileges: DBA_SYS_PRIVS
------------------------------------
PROMPT
PROMPT -- System privileges: DBA_SYS_PRIVS
COL PRIVILEGE FOR A40
WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT GRANTEE,PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE=UPPER('&USERNAME') OR
GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST)
ORDER BY 1,2;
------------------------------------
-- Object privileges: DBA_TAB_PRIVS
------------------------------------
PROMPT
PROMPT -- Object privileges: DBA_TAB_PRIVS
COL PRIVILEGE FOR A10
COL OBJECT_NAME FOR A40
WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT GRANTEE, PRIVILEGE,
OWNER || '.' || TABLE_NAME OBJECT_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE=UPPER('&USERNAME') OR
GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST)
ORDER BY 1,2,3;
Written At
18 NOV 202122:30
OEL Release
7.9 x64
Database Release
19.12.0.0.0
Tags In
Share
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- ORA-01031
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Sessions
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum