oracle 审计变换表空间_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;

/