A report of all users, grants and privileges
This SQL creates a report of all users and their grants, roles and privileges.
Sometimes you need to know all the privileges your users have in a database. This collection of scripts summarize...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
and detail all users, roles and grants in the database.
A few remarks:
- The list of users excludes those users that are part of the Oracle schemas. You might want to edit the list to support your needs.
- For my convenience, the list of roles excludes those that are part of the Oracle roles. Again, you might want to edit the list to support your needs.
- You may spool the report to a file or cut any piece of code you like.
- This has been tested in 8.0.5 and all versions of 8i.
----------------------------- -- A report of all grantee -- ----------------------------- set pagesize 2000 set echo off set feed off set verify off -- Users list ttitle 'All users' select username "Users" from dba_users where username not in ('SYS','SYSTEM','OUTLN', 'DBSNMP','SCOTT','DB_CONTROL', 'OPS$ORACLE','XXX') / -- All user's granted break on user skip 1 on user col user format a15 col grant format a30 ttitle 'All users granted' select grantee "User" ,granted_role "Grant", 'role' "Type" from dba_role_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM', 'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX')) and granted_role not in ('CONNECT') union all select grantee "User", privilege "Grant", 'priv' "Type" from dba_sys_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM', 'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX')) and privilege not in ('CONNECT') union all select grantee "User", owner||'.'||table_name "Grant", lower(privilege) "Type" from dba_tab_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM', 'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX')) order by 1 / -- All role's granted break on role skip 1 on role col role format a15 col grant format a30 ttitle 'All roles granted' select grantee "Role", granted_role "Grant", 'role' "Type" from dba_role_privs where grantee not in (select username from dba_users) and grantee not in ('EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','DBA', 'IMP_FULL_DATABASE','SELECT_CATALOG_ROLE', 'AQ_ADMINISTRATOR_ROLE','CONNECT', 'HS_ADMIN_ROLE','DELETE_CATALOG_ROLE', 'AQ_USER_ROLE','RECOVERY_CATALOG_OWNER', 'SNMPAGENT') and granted_role not in ('CONNECT') union all select grantee "Role", privilege "Grant", 'priv' "Type" from dba_sys_privs where grantee not in (select username from dba_users) and grantee not in ('EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','DBA', 'IMP_FULL_DATABASE','SELECT_CATALOG_ROLE', 'AQ_ADMINISTRATOR_ROLE','CONNECT', 'HS_ADMIN_ROLE','DELETE_CATALOG_ROLE', 'AQ_USER_ROLE','RECOVERY_CATALOG_OWNER', 'SNMPAGENT') union all select grantee "Role", owner||'.'||table_name "Grant", lower(privilege) "Type" from dba_tab_privs where grantee not in (select username from dba_users) and grantee not in ('EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','DBA', 'IMP_FULL_DATABASE','SELECT_CATALOG_ROLE', 'AQ_ADMINISTRATOR_ROLE','CONNECT','PUBLIC', 'HS_ADMIN_ROLE','DELETE_CATALOG_ROLE', 'AQ_USER_ROLE','RECOVERY_CATALOG_OWNER', 'SNMPAGENT') order by 1 / -- Objects owned by users break on user skip 1 on user col user format a15 ttitle 'All users objects' select owner "User", object_name "Object name", object_type "Type" from all_objects where owner in (select username from dba_users where username not in ('SYS','SYSTEM', 'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE8','XXX')) /
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.