• Arun Kumar

Check user, roles and privileges

Below queries will help you check details regarding different roles and privileges granted to Oracle database users.


Roles granted to user


Query to check the granted roles to a user

SELECT * 
 FROM DBA_ROLE_PRIVS 
 WHERE GRANTEE = '&USER';
 


Privileges granted to user


Query to check privileges granted to a user

SELECT * 
 FROM DBA_TAB_PRIVS 
 WHERE GRANTEE = 'USER';
 

Privileges granted to role


Privileges granted to a role which is granted to a user

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN 
(SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = '&USER') order by 3;


System privileges granted to user


Query to check if user is having system privileges

SELECT * 
 FROM DBA_SYS_PRIVS 
 WHERE GRANTEE = '&USER';
 

Permissions granted to role


Query to check permissions granted to a role

select * from ROLE_ROLE_PRIVS where ROLE = '&ROLE_NAME';
select * from ROLE_TAB_PRIVS where ROLE = '&ROLE_NAME';
select * from ROLE_SYS_PRIVS where ROLE = '&ROLE_NAME';



485 views

Recent Posts

See All

Temp Tablespace utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp

Estimate flashback destination size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashbac

Datafile utilization check

When you want to shrink a datafile, you must always check the single datafile utilization. In case if you shrink datafile more than the used size, it will fail. Below query gives the datafile utilizat

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback