审计相关语句
1. 审计session
session audit;
查看表DBA_AUDIT_TRAIL
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 column LOGIN_TIME format a20 column LOGOFF_TIME format a20 select OS_USERNAME, USERNAME, USERHOST, TERMINAL, SESSIONID, OS_PROCESS, ACTION_NAME, TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') AS LOGIN_TIME,TO_CHAR(LOGOFF_TIME,'YYYY-MM-DD HH24:MI:SS') AS LOGOFF_TIME, RETURNCODE from DBA_AUDIT_TRAIL; OS_USERNAME USERNAME USERHOST TERMINAL SESSIONID OS_PROCESS ACTION_NAME LOGIN_TIME LOGOFF_TIME RETURNCODE -------------------- -------------------- ------------------------------ -------------------- ---------- ------------ -------------------- -------------------- -------------------- ---------- oracle PUBLIC ec2-dbatest-02 -1 3699 LOGON 2019-05-25 12:21:10 0 oracle PUBLIC ec2-dbatest-02 -1 3703 LOGON 2019-05-25 12:21:41 0 oracle PUBLIC ec2-dbatest-02 -1 2966 LOGON 2019-06-29 08:47:54 0 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2669 3603 DROP TABLE 2019-06-30 10:41:28 0 oracle SCOTT ec2-dbatest-02 pts/0 552 2743 DELETE 2019-05-22 10:33:59 0 oracle ZHANGSAN ec2-dbatest-02 pts/0 941 4161 DELETE 2019-05-22 16:26:56 0 oracle SCOTT ec2-dbatest-02 pts/0 935 4124 UPDATE 2019-05-22 16:20:29 0 oracle ZHANGSAN ec2-dbatest-02 pts/0 941 4161 UPDATE 2019-05-22 16:27:24 0 oracle SCOTT ec2-dbatest-02 pts/0 552 2743 SELECT 2019-05-22 10:33:20 0 oracle SCOTT ec2-dbatest-02 pts/0 552 2743 SELECT 2019-05-22 10:33:31 0 oracle SCOTT ec2-dbatest-02 pts/0 935 4124 SELECT 2019-05-22 16:19:55 0 oracle ZHANGSAN ec2-dbatest-02 pts/0 941 4161 SELECT 2019-05-22 16:26:14 0 oracle ZHANGSAN ec2-dbatest-02 pts/0 941 4161 SELECT 2019-05-22 16:26:38 0 oracle SCOTT ec2-dbatest-02 pts/0 552 2743 INSERT 2019-05-22 10:35:14 0 oracle ZHANGSAN ec2-dbatest-02 pts/0 941 4161 INSERT 2019-05-22 16:26:35 0 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2665 3591 CREATE TABLE 2019-06-30 10:39:35 922 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2669 3603 LOGON 2019-06-30 10:41:00 0 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2665 3591 CREATE TABLE 2019-06-30 10:39:40 0 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2669 3603 CREATE TABLE 2019-06-30 10:41:09 0 oracle ZHANGSAN ec2-dbatest-01 pts/3 1412 2978 LOGOFF BY CLEANUP 2019-05-30 10:02:09 2019-05-30 11:29:33 0 oracle SCOTT ec2-dbatest-02 pts/0 578 2821 LOGOFF 2019-05-22 10:35:45 2019-05-22 10:41:13 0 oracle SCOTT ec2-dbatest-02 pts/0 609 2934 LOGOFF 2019-05-22 11:05:29 2019-05-22 14:34:59 0 oracle SCOTT ec2-dbatest-02 pts/0 935 4124 LOGOFF 2019-05-22 16:19:47 2019-05-22 16:24:34 0 oracle ZHANGSAN ec2-dbatest-02 pts/0 941 4161 LOGOFF 2019-05-22 16:24:40 2019-05-22 17:33:06 0 oracle SCOTT ec2-dbatest-02 pts/0 1013 4454 LOGOFF 2019-05-22 17:33:08 2019-05-22 17:33:10 0 oracle ZHANGSAN ec2-dbatest-02 pts/0 1014 4456 LOGOFF 2019-05-22 17:33:12 2019-05-22 17:33:13 0 oracle LISI ec2-dbatest-02 pts/0 1015 4458 LOGOFF 2019-05-22 17:33:18 2019-05-22 17:33:19 0 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2617 3473 LOGOFF 2019-06-30 09:57:08 2019-06-30 10:14:02 0 flli LISI CHINA\LT-FLLI LT-FLLI 2636 3533 LOGOFF 2019-06-30 10:14:14 2019-06-30 10:16:52 0 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2641 3542 LOGOFF 2019-06-30 10:17:20 2019-06-30 10:39:21 0 flli ZHANGSAN CHINA\LT-FLLI LT-FLLI 2665 3591 LOGOFF 2019-06-30 10:39:23 2019-06-30 10:40:05 0 flli LISI CHINA\LT-FLLI LT-FLLI 2667 3600 LOGOFF 2019-06-30 10:40:19 2019-06-30 10:40:53 0 32 rows selected.
查看表DBA_COMMON_AUDIT_TRAIL
column AUDIT_TYPE format a30 column TIME format a20 column DB_USER format a20 column OS_USER format a20 column OBJECT_SCHEMA format a20 column OBJECT_NAME format a30 column STATEMENT_TYPE format a30 select AUDIT_TYPE, SESSION_ID, TO_CHAR(EXTENDED_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') AS TIME, DB_USER, OS_USER, USERHOST, OS_PROCESS, TERMINAL, OBJECT_SCHEMA, OBJECT_NAME, STATEMENT_TYPE from DBA_COMMON_AUDIT_TRAIL ORDER BY TIME; AUDIT_TYPE SESSION_ID TIME DB_USER OS_USER USERHOST OS_PROCESS TERMINAL OBJECT_SCHEM OBJECT_NAME STATEMENT_TYPE ------------------------------ ---------- -------------------- ---------------- -------------- ---------------------- ----------- ---------------- -------------- ------------------- ------------------------ Standard Audit 552 2019-05-22 10:33:20 SCOTT oracle ec2-dbatest-02 2743 pts/0 SCOTT TB1 SELECT Standard Audit 552 2019-05-22 10:33:30 SCOTT oracle ec2-dbatest-02 2743 pts/0 SCOTT TB1 SELECT Standard Audit 552 2019-05-22 10:33:58 SCOTT oracle ec2-dbatest-02 2743 pts/0 SCOTT TB1 DELETE Standard Audit 552 2019-05-22 10:35:13 SCOTT oracle ec2-dbatest-02 2743 pts/0 SCOTT TB1 INSERT Standard Audit 578 2019-05-22 10:35:44 SCOTT oracle ec2-dbatest-02 2821 pts/0 LOGOFF Standard Audit 609 2019-05-22 11:05:28 SCOTT oracle ec2-dbatest-02 2934 pts/0 LOGOFF Standard Audit 935 2019-05-22 16:19:47 SCOTT oracle ec2-dbatest-02 4124 pts/0 LOGOFF Standard Audit 935 2019-05-22 16:19:55 SCOTT oracle ec2-dbatest-02 4124 pts/0 SCOTT TB1 SELECT Standard Audit 935 2019-05-22 16:20:28 SCOTT oracle ec2-dbatest-02 4124 pts/0 SCOTT TB1 UPDATE Standard Audit 941 2019-05-22 16:24:40 ZHANGSAN oracle ec2-dbatest-02 4161 pts/0 LOGOFF Standard Audit 941 2019-05-22 16:26:14 ZHANGSAN oracle ec2-dbatest-02 4161 pts/0 ZHANGSAN TB2 SELECT Standard Audit 941 2019-05-22 16:26:35 ZHANGSAN oracle ec2-dbatest-02 4161 pts/0 ZHANGSAN TB2 INSERT Standard Audit 941 2019-05-22 16:26:38 ZHANGSAN oracle ec2-dbatest-02 4161 pts/0 ZHANGSAN TB2 SELECT Standard Audit 941 2019-05-22 16:26:56 ZHANGSAN oracle ec2-dbatest-02 4161 pts/0 ZHANGSAN TB2 DELETE Standard Audit 941 2019-05-22 16:27:23 ZHANGSAN oracle ec2-dbatest-02 4161 pts/0 ZHANGSAN TB2 UPDATE Standard Audit 1013 2019-05-22 17:33:08 SCOTT oracle ec2-dbatest-02 4454 pts/0 LOGOFF Standard Audit 1014 2019-05-22 17:33:11 ZHANGSAN oracle ec2-dbatest-02 4456 pts/0 LOGOFF Standard Audit 1015 2019-05-22 17:33:18 LISI oracle ec2-dbatest-02 4458 pts/0 LOGOFF Standard Audit -1 2019-05-25 12:21:10 PUBLIC oracle ec2-dbatest-02 3699 LOGON Standard Audit -1 2019-05-25 12:21:40 PUBLIC oracle ec2-dbatest-02 3703 LOGON Standard Audit 1412 2019-05-30 10:02:08 ZHANGSAN oracle ec2-dbatest-01 2978 pts/3 LOGOFF BY CLEANUP Standard Audit -1 2019-06-29 08:47:54 PUBLIC oracle ec2-dbatest-02 2966 LOGON Standard Audit 2617 2019-06-30 09:57:08 ZHANGSAN flli CHINA\LT-FLLI 3473 LT-FLLI LOGOFF Standard Audit 2636 2019-06-30 10:14:13 LISI flli CHINA\LT-FLLI 3533 LT-FLLI LOGOFF Standard Audit 2641 2019-06-30 10:17:20 ZHANGSAN flli CHINA\LT-FLLI 3542 LT-FLLI LOGOFF Standard Audit 2665 2019-06-30 10:39:23 ZHANGSAN flli CHINA\LT-FLLI 3591 LT-FLLI LOGOFF Standard Audit 2665 2019-06-30 10:39:35 ZHANGSAN flli CHINA\LT-FLLI 3591 LT-FLLI ZHANGSAN TEST1 CREATE TABLE Standard Audit 2665 2019-06-30 10:39:40 ZHANGSAN flli CHINA\LT-FLLI 3591 LT-FLLI ZHANGSAN TEST1 CREATE TABLE Standard Audit 2667 2019-06-30 10:40:19 LISI flli CHINA\LT-FLLI 3600 LT-FLLI LOGOFF Standard Audit 2669 2019-06-30 10:40:59 ZHANGSAN flli CHINA\LT-FLLI 3603 LT-FLLI LOGON Standard Audit 2669 2019-06-30 10:41:08 ZHANGSAN flli CHINA\LT-FLLI 3603 LT-FLLI ZHANGSAN TEST12 CREATE TABLE Standard Audit 2669 2019-06-30 10:41:28 ZHANGSAN flli CHINA\LT-FLLI 3603 LT-FLLI ZHANGSAN TEST12 DROP TABLE 32 rows selected.
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
审计结果查看
===================来自一泽涟漪的博客,转载请标明出处 www.cnblogs.com/ilifeilong===================
