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
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
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’,’
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
from dba_tab_privs a, dba_tables b
where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’
and a.table_name=b.table_name
and a.grantee not in (select role from dba_roles)
order by 1,2,3;