HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE
Privilegies, Queries
Here is a query to a data dictionary table for finding out all the object privileges that a particular user or a role has. It also displays if a privilege is transferred directly or through a role.
DEMO
DEMO: HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE
RELATED
HOW TO RETRIEVE AN OBJECT DDL OR A USER DDL USING PL/SQL
HOW TO FIND SYSTEM, ROLES AND OBJECTS PRIVILEGES THAT HAVE BEEN GRANTED TO A USER
QUERY
The DBT_OPFAU script fetches all objects, including objects permissions granted to a specific user. In addition, it provides information on how the privileges are transferred to a user: directly or through a role.
------------------------------------------------------------------------
-- Victor Khalturin
--
-- Version 13:12 21-Nov-2021 (001)
--
-- dbt_opfau.sql - Objects Privileges For A User.
--
-- The DBT_OPFAU fetches all objects, including objects permissions
-- granted to a specific user. In addition, it provides information on how
-- the privileges are transferred to a user: directly or through a role.
------------------------------------------------------------------------
SET PAGES 999
SET LINES 300
COL OBJECT_TYPE FOR A15
COL OWNER FOR A15
COL OBJECT_NAME FOR A15
COL GRANTED_TO FOR A15
COL GRANTED_DIRECTLY FOR A16
COL GRANTED_THROUGH FOR A15
COL PRIVILEGE FOR A10
COL GRANTABLE FOR A9
SET VERIFY OFF
SET SQLNUMBER OFF
SET COLSEP " | "
UNDEFINE USERNAME
WITH ROLES AS (
-- FETCHING ALL ROLES ASSIGNED TO A USER THROUGH A HIERARCHICAL QUERY
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT OWNER,
TABLE_NAME OBJECT_NAME,
TYPE OBJECT_TYPE,
'&USERNAME' GRANTED_TO,
DECODE(GRANTEE,'&USERNAME','YES','NO') GRANTED_DIRECTLY,
DECODE(GRANTEE,'&USERNAME','DIRECT GRANT',GRANTEE) GRANTED_THROUGH,
PRIVILEGE,
GRANTABLE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='&USERNAME' OR
GRANTEE IN (SELECT GRANTED_ROLE FROM ROLES)
ORDER BY
OWNER,GRANTED_THROUGH;
Written At
21 NOV 202113:20
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