oracle细粒度审计

创建审计表空间

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;