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.
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.
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**$$$$$$$
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**$$$$$$$
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
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and its related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.
Thanks for the visits!
Share Learn Grow!