A report of all users, grants and privileges

and detail all users, roles and grants in the database.

A few remarks:

1. 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.
2. 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.
3. You may spool the report to a file or cut any piece of code you like.
4. 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.