How to Find User’s Privileges in Oracle

December 27, 2020

How to Find User’s Privileges in Oracle

In this article, we are going to find all forms of privileges of users in Oracle. Commonly users have the below types of access in databases which are important to perform day-by-day activities according to their roles & responsibilities.

System Privileges
Role Privileges
Object Privileges

Privileges are authentication to execute any activities in the database by the user like SQL statements, PL/SQL, etc or a user can access to some other schema’s objects with appropriate access on objects.

System Privileges:

Below is the SQL command to find SYSTEM PRIVILEGES, click here for sample output.

SET LINES 333
SET PAGES 55
COL ADMIN_OPTION FOR A15

SELECT 'SYSTEM PRIVS' TYPE, GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE='TESTUSER12';
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Role Privileges

Below is the SQL command to find ROLE PRIVILEGES, click here for sample output.

SET LINES 333
SET PAGES 55
COL GRANTEE FOR A15
COL GRANTED_ROLE FOR A21
COL ADMIN_OPTION FOR A11
COL DEFAULT_ROLE FOR A15

SELECT 'ROLE PRIVS' TYPE, GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TESTUSER12' ORDER BY GRANTED_ROLE;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Object Privileges

Below is the SQL command to find OBJECT PRIVILEGES, click here for sample output.

SET LINES 333
SET PAGES 55
col grantee for a11
COL OWNER FOR A11
COL TABLE_NAME FOR A21
COL GRANTOR FOR A11
COL PRIVILEGE FOR A11
COL TYPE FOR A15

SELECT 'OBJECT PRIVS' TYPE, GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,TYPE FROM DBA_TAB_PRIVS WHERE GRANTEE='TESTUSER12';
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.

DATABASE, ORACLE, SCRIPTS