--启用sys审计 alter system set audit_sys_operations='TRUE' --启用db审计 alter system set audit_trail='DB_EXTENDED' scope=spfile ; --迁移aud$表到用户自定义表空间 BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'DBADMIN'); END; / --迁移FGA_LOG$表到用户自定义表空间 BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'DBADMIN'); END; /   --ddl audit trigger CREATE OR REPLACE TRIGGER "SYS"."DDL_AUDIT_TRIGGER"  AFTER DDL ON DATABASE DECLARE    Session_Id_Var   NUMBER;    Os_User_Var      VARCHAR2(200);    PROGRAM_Var      VARCHAR2(200);    IP_Address_Var   VARCHAR2(200);    Terminal_Var     VARCHAR2(200);    Host_Var         VARCHAR2(200);    Cut              NUMBER;    Sql_Text         ORA_NAME_LIST_T;    L_Trace          NUMBER;    DDL_Sql_Var      VARCHAR2(2000);
BEGIN    SELECT   SYS_CONTEXT('USERENV','SESSIONID'),             SYS_CONTEXT('USERENV','OS_USER'),             SYS_CONTEXT('USERENV','MODULE'),             SYS_CONTEXT('USERENV','IP_ADDRESS'),             SYS_CONTEXT('USERENV','TERMINAL'),             SYS_CONTEXT('USERENV','HOST')    INTO     Session_Id_Var,             Os_User_Var,             PROGRAM_Var,             IP_Address_Var,             Terminal_Var,             Host_Var    FROM     DUAL;    BEGIN
      SELECT COUNT(*) INTO L_Trace FROM DUAL        WHERE ORA_DICT_OBJ_NAME NOT LIKE 'MLOG%'          AND ORA_DICT_OBJ_NAME NOT LIKE '%LOG'          AND UTL_INADDR.GET_HOST_ADDRESS IS NOT NULL          AND SYS_CONTEXT('USERENV','IP_ADDRESS') IS NOT NULL          AND SYS_CONTEXT('USERENV','IP_ADDRESS') <> UTL_INADDR.GET_HOST_ADDRESS;
      IF L_Trace > 0 THEN
         Cut := ORA_SQL_TXT(Sql_Text);
         FOR i IN 1..Cut LOOP             DDL_Sql_Var := SUBSTR(DDL_Sql_Var || Sql_Text(i),1,2000);          END LOOP;       END IF;
   EXCEPTION       WHEN OTHERS THEN          NULL;    END;
   INSERT INTO system.Audit_DDL_OBJ(                Opr_Time,                Session_Id,                OS_User,                PROGRAM,                IP_Address,                Terminal,                Host,                User_Name,                DDL_Type,                DDL_Sql,                Object_Type,                Owner,                Object_Name,                sys_time               )        VALUES( SYSDATE,                Session_Id_Var,                Os_User_Var,                PROGRAM_Var,                IP_Address_Var,                Terminal_Var,                Host_Var,                ORA_LOGIN_USER,                ORA_SYSEVENT,                DDL_Sql_Var,                ORA_DICT_OBJ_TYPE,                ORA_DICT_OBJ_OWNER,                ORA_DICT_OBJ_NAME,                to_char(sysdate,'yyyymmdd'));
   COMMIT;
EXCEPTION    WHEN OTHERS THEN       NULL;
END DDL_Audit_Trigger;
--ddl audit table CREATE TABLE SYSTEM.AUDIT_DDL_OBJ    (OPR_TIME DATE,     SESSION_ID NUMBER,     OS_USER VARCHAR2(200),     PROGRAM VARCHAR2(200),    IP_ADDRESS VARCHAR2(200),     TERMINAL VARCHAR2(200),     HOST VARCHAR2(200),     USER_NAME VARCHAR2(30),     DDL_TYPE VARCHAR2(30),     DDL_SQL VARCHAR2(2000),     OBJECT_TYPE VARCHAR2(18),     OWNER VARCHAR2(30),     OBJECT_NAME VARCHAR2(128),     SYS_TIME VARCHAR2(20)    )   TABLESPACE DBADMIN ;   CREATE INDEX SYSTEM.IX_AUDIT_DDL_OBJECTNAME ON SYSTEM.AUDIT_DDL_OBJ (OBJECT_NAME)    TABLESPACE DBADMIN ;   CREATE INDEX SYSTEM.IX_AUDIT_DDL_OPRTIME ON SYSTEM.AUDIT_DDL_OBJ (SYS_TIME)    TABLESPACE DBADMIN ;

--定期清理audit log ，保留最近3个月的数据 BEGIN IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN DBMS_OUTPUT.PUT_LINE('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP'); DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_DB_STD, default_cleanup_interval => 24); ELSE DBMS_OUTPUT.PUT_LINE('Cleanup for Audit was already initialized'); END IF; END; /
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',    job_type => 'PLSQL_BLOCK',     job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,LAST_ARCHIVE_TIME => sysdate-90); END;',     start_date => sysdate,     repeat_interval => 'FREQ=HOURLY;INTERVAL=24',     enabled => TRUE,    comments => 'Create an archive timestamp' );  END;  /

BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, AUDIT_TRAIL_PURGE_INTERVAL => 24,    AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',     USE_LAST_ARCH_TIMESTAMP => TRUE ); END; 
/