Ver las auditorias actuales de la base ORACLE

-- Name : audits.sql prompt prompt Purpose : auditorias actuales de la base ORACLE prompt -- -- Oracle Version: 11g -- Other Oracle versions: should work on 9i and later ---------------------------------------------------------------------------------------- spool audits.rpt prompt col value for a20 col display_value for a50 col update_comment for a50 col policy_text for a30 prompt Instances prompt ---------- select * from gv$instance order by inst_id; prompt Audit parameters prompt ----------------- col name for a20 col value for a40 break on inst_id skip 1 select * from gv$parameter where upper( name ) like '%AUDIT%' order by inst_id; clear breaks prompt prompt DBA_STMT_AUDIT_OPTS prompt -------------------- select * from sys.dba_stmt_audit_opts order by nvl( user_name, '0' ), audit_option; prompt prompt DBA_PRIV_AUDIT_OPTS prompt -------------------- select * from sys.dba_priv_audit_opts order by privilege; prompt prompt DBA_OBJ_AUDIT_OPTS prompt ------------------- prompt S=Session, A=Access prompt S/- = audit whenever successful prompt -/S = audit whenever unsuccessful col owner for a30 select * from sys.dba_obj_audit_opts where ALT <> '-/-' or AUD <> '-/-' or COM <> '-/-' or DEL <> '-/-' or GRA <> '-/-' or IND <> '-/-' or INS <> '-/-' or LOC <> '-/-' or REN <> '-/-' or SEL <> '-/-' or UPD <> '-/-' or REF <> '-/-' or EXE <> '-/-' or CRE <> '-/-' or REA <> '-/-' or WRI <> '-/-' order by owner, object_name; -- default settings prompt prompt ALL_DEF_AUDIT_OPTS prompt ------------------- select * from all_def_audit_opts; prompt prompt DBA_AUDIT_TRAIL (breakdown) prompt ---------------------------- select action_name, returncode, count(*) from dba_audit_trail group by action_name, returncode order by action_name, returncode; prompt prompt DBA_AUDIT_POLICIES prompt ------------------- select * from dba_audit_policies; prompt prompt SYS.FGA_LOG$ prompt declare cursor csr_main_cursor is select * from SYS.DBA_AUDIT_POLICIES order by policy_name; begin dbms_output.put_line( 'Details from SYS.DBA_AUDIT_POLICIES' ); dbms_output.put_line( '-----------------------------------' ); for i in csr_main_cursor loop dbms_output.put_line( 'Object Schema : ' || i.object_schema ); dbms_output.put_line( 'Object Name : ' || i.object_name ); dbms_output.put_line( 'Policy Name : ' || i.policy_name ); dbms_output.put_line( 'Policy Text : ' || i.policy_text ); dbms_output.put_line( 'Policy Column : ' || i.policy_column ); dbms_output.put_line( 'Pf Schema : ' || i.pf_schema ); dbms_output.put_line( 'Pf Package : ' || i.pf_package ); dbms_output.put_line( 'Pf Function : ' || i.pf_function ); dbms_output.put_line( 'Enabled : ' || i.enabled ); dbms_output.put_line( 'Sel : ' || i.sel ); dbms_output.put_line( 'Ins : ' || i.ins ); dbms_output.put_line( 'Upd : ' || i.upd ); dbms_output.put_line( 'Del : ' || i.del ); dbms_output.put_line( 'Audit Trail : ' || i.audit_trail ); dbms_output.put_line( 'Policy Column Options : ' || i.policy_column_options ); dbms_output.put_line( chr(10) ); dbms_output.put_line( rpad( '-', 80, '-' ) ); end loop; end; / col obj$name for a30 prompt prompt SYS.FGA_LOG$ summary prompt --------------------- select policyname, obj$schema, obj$name, stmt_type, count(*) from sys.fga_log$ group by policyname, obj$schema, obj$name, stmt_type order by policyname, obj$schema, obj$name, stmt_type; prompt prompt SYS.GV_$XML_AUDIT_TRAIL summary prompt ------------------------------- col db_user for a20 col os_user for a20 col "Audit Type" for a25 col "Action (Code)" for a20 select a.db_user, a.os_user, b.name || ' (' || a.action || ')' "Action (Code)", a.returncode, a.audit_type || decode( a.audit_type, 1, ' Standard XML Audit', 2, ' Fine Grained XML Audit', 4, ' SYS XML Audit', 8, ' Mandatory XML Audit', a.audit_type || ' ????' ) "Audit Type", count(*) from SYS.GV_$XML_AUDIT_TRAIL a, audit_actions b where b.action (+)= a.action group by a.db_user, a.os_user, b.name || ' (' || a.action || ')', a.returncode, a.audit_type || decode( a.audit_type, 1, ' Standard XML Audit', 2, ' Fine Grained XML Audit', 4, ' SYS XML Audit', 8, ' Mandatory XML Audit', a.audit_type || ' ????' ) order by 1, 2; select sql_text, count(*) from gv$xml_audit_trail group by sql_text order by upper( sql_text ); spool off

___________________________________________________________

SALIDA

Instances --------- INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO ---------- --------------- ---------------- -------------------- ----------------- ------------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- 1 1 DEV1 linux01a 11.2.0.3.0 25/09/2015 19:41:47 OPEN YES 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 2 2 DEV2 linux01b 11.2.0.3.0 25/09/2015 20:01:08 OPEN YES 2 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 2 rows selected. Audit parameters ---------------- INST_ID NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION UPDATE_COMMENT HASH ---------- ---------- -------------------- ---------- ---------------------------------------- -------------------------------------------------- --------- ----- --------- ----- ---------- ----- ----- ----- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------- 1 1677 audit_sys_operations 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable sys auditing 2991425711 1840 audit_file_dest 2 /opt/oracle/admin/admin/DEV0A/adump /opt/oracle/admin/admin/DEV0A/adump FALSE FALSE DEFERRED TRUE FALSE FALSE FALSE FALSE Directory in which auditing files are to reside 2188534560 1841 audit_syslog_level 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Syslog facility and level 1189300432 1869 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system auditing 4289193100 2 1677 audit_sys_operations 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable sys auditing 2991425711 1840 audit_file_dest 2 /opt/oracle/admin/admin/DEV0A/adump /opt/oracle/admin/admin/DEV0A/adump FALSE FALSE DEFERRED TRUE FALSE FALSE FALSE FALSE Directory in which auditing files are to reside 2188534560 1841 audit_syslog_level 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Syslog facility and level 1189300432 1869 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system auditing 4289193100 8 rows selected. DBA_STMT_AUDIT_OPTS ------------------- USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS 28 rows selected. DBA_PRIV_AUDIT_OPTS ------------------- USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS AUDIT SYSTEM BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS 23 rows selected. DBA_OBJ_AUDIT_OPTS ------------------ S=Session, A=Access S/- = audit whenever successful -/S = audit whenever unsuccessful OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK ------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- DEV_0001 COUNTRIES TABLE -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- CODE_002 PROC_UPDATE_TEAMS PROCEDURE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- 2 rows selected. ALL_DEF_AUDIT_OPTS ------------------ ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- 1 row selected. DBA_AUDIT_TRAIL (breakdown) --------------------------- ACTION_NAME RETURNCODE COUNT(*) ---------------------------- ---------- ---------- DELETE 0 1045 LOGOFF 0 697 LOGOFF BY CLEANUP 0 475 LOGON 0 1188 LOGON 28000 1 LOGON 28001 2 6 rows selected. DBA_AUDIT_POLICIES ------------------ no rows selected SYS.FGA_LOG$ Details from SYS.DBA_AUDIT_POLICIES ----------------------------------- PL/SQL procedure successfully completed. SYS.FGA_LOG$ summary -------------------- no rows selected SYS.GV_$XML_AUDIT_TRAIL summary ------------------------------ no rows selected no rows selected