oracle 10g/11g 审计启用，审计表表空间迁移以及审计log定时清理

--启用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; 

/