创建审计表空间
SQL> create tablespace audittbs datafile ‘/u01/app/oracle/oradata/orcl11g/audit.dbf’ size 3G;
Tablespace created.
connect / as sysdba;
SQL>alter table aud$ move tablespace audittbs;
Table altered.
SQL> alter table audit$ move tablespace audittbs;
Table altered.
SQL> alter index i_audit rebuild online tablespace audittbs;
Index altered.
SQL> alter table audit_actions move tablespace audittbs;
Table altered.
SQL> alter index i_audit_actions rebuild online tablespace audittbs;
Index altered.
SQL> show parameter audit;
NAME TYPE VALUE
audit_file_dest string /u01/app/oracle/admin/orcl11g/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
Audit_sys_operations:
默认为false,当设置为true时,所有sys用户(包括以sysdba, sysoper身份登录的用户)的操作都会被记录
Audit_trail:
None:是默认值,不做审计;
DB:将audit trail 记录在数据库的审计相关表中,如aud$,审计的结果只有连接信息;
DB,Extended:这样审计结果里面除了连接信息还包含了当时执行的具体语句;
OS:将audit trail 记录在操作系统文件中,文件名由audit_file_dest参数指定;
SQL> alter system set audit_trail=db,extended scope=spfile;
System altered.
SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.
测试细粒度审计
对表的增,删,改进行审计
CREATE TABLE audit_test
(
invoice_id NUMBER,
invoice_num VARCHAR (50),
invoice_desc VARCHAR (200)
) ;
加入细粒度审计策略
insert 审计策略
BEGIN
DBMS_FGA.ADD_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_INSERT’,
audit_column => ‘invoice_id, invoice_num,invoice_desc’,
enable => FALSE,
statement_types => ‘INSERT’);
END;
update审计策略
BEGIN
DBMS_FGA.ADD_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_UPDATE’,
audit_column => ‘invoice_id, invoice_num,invoice_desc’,
enable => FALSE,
statement_types => ‘UPDATE’);
END;
delete审计策略
BEGIN
DBMS_FGA.ADD_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_DELETE’,
audit_column => ‘invoice_id, invoice_num,invoice_desc’,
enable => FALSE,
statement_types => ‘DELETE’);
END;
启动细粒度审计
BEGIN
DBMS_FGA.ENABLE_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_INSERT’);
END;
BEGIN
DBMS_FGA.ENABLE_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_UPDATE’);
END;
BEGIN
DBMS_FGA.ENABLE_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_DELETE’);
END;
进行增,删,改操作
SQL> insert into AUDIT_TEST values (2,‘2014-08-22 001’, ‘PAY THE TAXI FOR 2014-0822’);
1 row created.
SQL> commit;
Commit complete.
SQL> update AUDIT_TEST set invoice_id = 1 where invoice_id = 2;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from AUDIT_TEST where invoice_id = 1;
1 row deleted.
SQL> commit;
Commit complete.
查询审计信息
select a.session_id,a.timestamp,a.db_user,a.dbid,a.userhost,a.object_schema,a.object_name,a.policy_name,a.sql_text from dba_fga_audit_trail a;
SESSION_ID TIMESTAMP DB_USER DBID USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
2760086 2019/5/1 17 TEST 1113199678 rhel6 TEST AUDIT_TEST TEST_AUD_UPDATE update AUDIT_TEST set invoice_id = 1 where invoice_id = 2
2760086 2019/5/1 17 TEST 1113199678 rhel6 TEST AUDIT_TEST TEST_AUD_DELETE delete from AUDIT_TEST where invoice_id = 1
关闭细粒度审计
BEGIN
DBMS_FGA.DISABLE_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_INSERT’);
END;
BEGIN
DBMS_FGA.DISABLE_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_UPDATE’);
END;
BEGIN
DBMS_FGA.DISABLE_POLICY (object_schema => ‘TEST’,
object_name => ‘AUDIT_TEST’,
policy_name => ‘TEST_AUD_DELETE’);
END;