{"id":39,"date":"2010-11-12T23:05:00","date_gmt":"2010-11-12T23:05:00","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/2010\/11\/12\/oracle-auditing-answer-to-auditors\/"},"modified":"2015-11-17T20:27:25","modified_gmt":"2015-11-17T19:27:25","slug":"oracle-auditing-answer-to-auditors","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2010\/11\/12\/oracle-auditing-answer-to-auditors\/","title":{"rendered":"Oracle auditing – answer to auditors"},"content":{"rendered":"
Queries for audit information in Oracle 10gR2. The auditors are there…<\/span><\/span><\/div>\n

\n<\/span><\/span><\/div>\n
They want to know that audit exists and what is audited:<\/span><\/span><\/span><\/div>\n

\nselect<\/span><\/span> *<\/span><\/span> from<\/span><\/span> v$spparameter<\/span><\/span> where<\/span><\/span> name<\/span><\/span> like<\/span><\/span> ‘%audit%’<\/span><\/span>;<\/span><\/span> — equivalent to SQL> show parameter audit<\/span><\/span><\/i><\/span> <\/p>\n

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

  order by 1,2,3;<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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; […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,6,8],"tags":[],"class_list":["post-39","post","type-post","status-publish","format-standard","category-audit","category-oracle","category-oracle-10g","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/39","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=39"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/39\/revisions"}],"predecessor-version":[{"id":183,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/39\/revisions\/183"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=39"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=39"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=39"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}