How to Find User’s Privileges in Oracle

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.

 220 total views,  1 views today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *

twenty − seven =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru