审计相关语句

session audit

set pagesize 5000 linesize 300

column OS_USERNAME format a20
column USERNAME format a20
column USERHOST format a30
column TERMINAL format a20
column OWNER format a20
column OBJ_NAME format a20
column ACTION_NAME format a20
column OS_PROCESS format a15


select OS_USERNAME,USERNAME,USERHOST,TERMINAL,SESSIONID,OS_PROCESS,ACTION_NAME,TIMESTAMP AS LOGON_TIME,LOGOFF_TIME,RETURNCODE,SESSION_CPU from DBA_AUDIT_TRAIL;




object audit

set pagesize 5000 linesize 300
column OS_USERNAME format a20
column USERNAME format a20
column USERHOST format a30
column TERMINAL format a20
column TIMESTAMP format a20
column OWNER format a20
column OBJ_NAME format a20
column ACTION_NAME format a20
column SESSIONID format 99999
column STATEMENTID format 99999
column EXTENDED_TIMESTAMP format a40


select OS_USERNAME,USERNAME,USERHOST,TERMINAL,OWNER,OBJ_NAME,ACTION_NAME,SESSIONID,STATEMENTID,to_char(EXTENDED_TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS') EXTENDED_TIMESTAMP from DBA_AUDIT_OBJECT;


SELECT * FROM DBA_AUDIT_OBJECT;




AUDIT DELETE,UPDATE,INSERT,SELECT ON scott.tb1 BY ACCESS; 

AUDIT DELETE,UPDATE,INSERT,SELECT ON zhangsan.tb2 by access;







SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
set pagesize 5000 linesize 300
ttitle center '<a style="font-weight:bold;font-size:18px;">MIS AUDIT REPORT FOR FINMART DATABASE</a>' skip 2
btitle center '<span style="background-color:#c90421;color:#ffffff;border:1px solid black;">Confidential</span>'
column OS_USERNAME format a20 
column USERNAME format a20 heading USER_NAME
column USERHOST format a30 heading USER_HOST
column TERMINAL format a20 
column TIMESTAMP format a20
column OWNER format a20 heading OBJECT_OWNER
column OBJ_NAME format a20 heading OBJECT_NAME
column ACTION_NAME format a20 heading OPERATION
column SESSIONID format 99999 heading SESSION_ID
column STATEMENTID format 99999 heading STATEMENT_ID
column EXTENDED_TIMESTAMP format a40 heading EXTENDED_TIME
SET MARKUP HTML ON SPOOL ON ENTMAP OFF -
HEAD "<TITLE>MIS Audit Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#330000'" -
TABLE "WIDTH='90%' BORDER='5'"
SPOOL report.html
select OS_USERNAME,USERNAME,USERHOST,TERMINAL,OWNER,OBJ_NAME,
        case when ACTION_NAME = 'DELETE' OR ACTION_NAME = 'UPDATE' then '<span style="background-color:#c90421;display:block;overflow:auto">' || to_char(ACTION_NAME) || '</span>' else to_char(ACTION_NAME) END AS ACTION,
        SESSIONID,STATEMENTID,to_char(EXTENDED_TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS') EXTENDED_TIMESTAMP from DBA_AUDIT_OBJECT;
SPOOL OFF
exit









SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
set pagesize 5000 linesize 300
ttitle center '<a style="font-weight:bold;font-size:18px;">MIS DML AUDIT REPORT FOR FINMART DATABASE</a>' skip 2
btitle center '<span style="background-color:#c90421;color:#ffffff;border:1px solid black;">Confidential</span>'
column OS_USERNAME format a20 
column USERNAME format a20 heading USER_NAME
column USERHOST format a30 heading USER_HOST
column TERMINAL format a20 
column TIMESTAMP format a20
column OWNER format a20 heading OBJECT_OWNER
column OBJ_NAME format a20 heading OBJECT_NAME
column ACTION_NAME format a20 heading OPERATION
column SESSIONID format 99999 heading SESSION_ID
column STATEMENTID format 99999 heading STATEMENT_ID
column EXTENDED_TIMESTAMP format a40 heading EXTENDED_TIME
column OS_PROCESS format a15
SET MARKUP HTML ON SPOOL ON ENTMAP OFF -
HEAD "<TITLE>MIS Audit Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#330000'" -
TABLE "WIDTH='90%' BORDER='5'"
SPOOL report.html
select OS_USERNAME,USERNAME,USERHOST,TERMINAL,OWNER,OBJ_NAME,
        case when ACTION_NAME = 'DELETE' OR ACTION_NAME = 'UPDATE' then '<span style="background-color:#c90421;display:block;overflow:auto">' || to_char(ACTION_NAME) || '</span>' else to_char(ACTION_NAME) END AS ACTION,
        SESSIONID,STATEMENTID,to_char(EXTENDED_TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS') EXTENDED_TIMESTAMP from DBA_AUDIT_OBJECT;

ttitle center '<a style="font-weight:bold;font-size:18px;">MIS CONNECTION AUDIT REPORT FOR FINMART DATABASE</a>' skip 2
btitle center '<span style="background-color:#c90421;color:#ffffff;border:1px solid black;">Confidential</span>'        
select OS_USERNAME,USERNAME,USERHOST,TERMINAL,SESSIONID,OS_PROCESS,ACTION_NAME,TIMESTAMP AS LOGON_TIME,LOGOFF_TIME,RETURNCODE,SESSION_CPU from DBA_AUDIT_SESSION;        
    
SPOOL OFF
exit

审计结果查看