Oracle auditing – answer to auditors


Queries for audit information in Oracle 10gR2. The auditors are there…

They want to know that audit exists and what is audited:

select * from v$spparameter where name like ‘%audit%’; — equivalent to SQL> show parameter audit

select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from dba_STMT_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_OBJ_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_PRIV_AUDIT_OPTS a;
And they want to know who has access to what:
Roles per user:
set pages 1000
break on grantee;
select grantee, granted_role 
  from dba_role_privs, dba_users 
  where grantee=username
  order by 1,2; 
User per role:
set pages 0
break on granted_role
select granted_role,grantee username 
  from dba_role_privs, dba_users
  where grantee=username
  order by 1,2; 
Table privileges per role:
set lines 150
set pages 0
break on privilege on role_name
col role_name for a30
col privilege for a20
col table_name for a60
select grantee role_name,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b, dba_roles c
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’)
    and a.table_name=b.table_name
    and a.grantee=c.role
  order by 1,2,3;
Privileges given directly to users:
set pages 0
set lines 150
break on username
col username for a30
col privilege for a20
col table_name for a60
select grantee username,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’,’SYS’,’SYSTEM’,’DBSNMP’,’WMSYS’)
    and a.table_name=b.table_name
    and a.grantee not in (select role from dba_roles)

  order by 1,2,3;

Leave a comment

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