RETRIEVING USER GRANT BY QUERYING A DATABASE VIEWS
Objects DDL
0
REQUEST
I want to get a list of all privileges given to a user by querying data dictionary.
RELATED
SOLUTION
3 data dictionary views provide this kind of information
The DBA_SYS_PRIVS lists system privileges granted to users and roles.
The ROLE_SYS_PRIVS lists system privileges granted to roles.
The DBA_TAB_PRIVS lists all grants on all objects in a database.
TESTCASE
-------------------
-- System grants
-------------------
COL GRANTEE FOR A10
COL PRIVILEGE FOR A40
SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='TESTCASE';
GRANTEE PRIVILEGE
---------- ----------------------------------------
TESTCASE UNLIMITED TABLESPACE
TESTCASE CREATE ANY DIRECTORY
-------------------
-- Role grants
-------------------
BREAK ON ROLE
COL ROLE FOR A10
COL PRIVILEGE FOR A20
SELECT ROLE,PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TESTCASE') ORDER BY ROLE;
ROLE PRIVILEGE
---------- --------------------
CONNECT SET CONTAINER
CREATE SESSION
RESOURCE CREATE TYPE
CREATE PROCEDURE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
-------------------
-- Ojbect grants
-------------------
COL PRIVILEGE FOR A10
COL OBJECT_NAME FOR A20
SELECT GRANTEE,PRIVILEGE,OWNER || '.' || TABLE_NAME OBJECT_NAME FROM DBA_TAB_PRIVS WHERE GRANTEE='TESTCASE';
GRANTEE PRIVILEGE OBJECT_NAME
---------- ---------- --------------------
TESTCASE SELECT SYS.V_$SESSION
TESTCASE SELECT SYS.V_$SESSION_EVENT
TESTCASE EXECUTE SYS.MYDIRECTORY
TESTCASE READ SYS.MYDIRECTORY
TESTCASE WRITE SYS.MYDIRECTORY
You can easy generate DDL statements by the following queries
-------------------
-- System grants
-------------------
COL SYSTEM_GRANTS FOR A50
SELECT 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE ||
DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', NULL) || ';' SYSTEM_GRANTS
FROM DBA_SYS_PRIVS WHERE GRANTEE='TESTCASE';
SYSTEM_GRANTS
--------------------------------------------------
GRANT UNLIMITED TABLESPACE TO TESTCASE;
GRANT CREATE ANY DIRECTORY TO TESTCASE;
-------------------
-- Role grants
-------------------
COL ROLE_GRANTS FOR A30
SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE ||
DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', NULL) || ';' ROLE_GRANTS
FROM DBA_ROLE_PRIVS WHERE GRANTEE='TESTCASE';
ROLE_GRANTS
------------------------------
GRANT RESOURCE TO TESTCASE;
GRANT CONNECT TO TESTCASE;
-------------------
-- Ojbect grants
-------------------
SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE ||
DECODE(GRANTABLE, 'YES', ' WITH GRANT OPTION', NULL) || ';' OBJECT_GRANTS
FROM DBA_TAB_PRIVS WHERE GRANTEE='TESTCASE';
OBJECT_GRANTS
--------------------------------------------------------------------------------
GRANT SELECT ON SYS.V_$SESSION TO TESTCASE;
GRANT SELECT ON SYS.V_$SESSION_EVENT TO TESTCASE;
GRANT EXECUTE ON SYS.MYDIRECTORY TO TESTCASE WITH GRANT OPTION;
GRANT READ ON SYS.MYDIRECTORY TO TESTCASE WITH GRANT OPTION;
GRANT WRITE ON SYS.MYDIRECTORY TO TESTCASE WITH GRANT OPTION;
Version : 13:14 17.01.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Tags In
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Certificates
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Cipher Suites (1)
- Database Errors (5)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum