环境:Oracle 11.2.0.4 我这里测试A用户为JINGYU,要审计的表为B用户SCOTT下的EMP表。通过FGA来实现。
官方文档语法:
DBMS_FGA.ADD_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, audit_condition VARCHAR2, audit_column VARCHAR2, handler_schema VARCHAR2, handler_module VARCHAR2, enable BOOLEAN, statement_types VARCHAR2, audit_trail BINARY_INTEGER IN DEFAULT, audit_column_opts BINARY_INTEGER IN DEFAULT);
添加审计策略,目标是审计JINGYU用户有更新SCOTT用户下EMP表的操作。
--审计JINGYU用户有更新SCOTT用户下EMP表的操作
BEGIN
dbms_fga.add_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'FGA_TEST_01',
audit_condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''JINGYU'' ',
statement_types => 'UPDATE');
END;
/
这里主要是audit_condition的写法,有一个SYS_CONTEXT('USERENV','SESSION_USER')写法:
select * from dual where SYS_CONTEXT('USERENV','SESSION_USER') = 'JINGYU';
测试一下,证明这样写where条件是有效的:
SQL> show user
USER is "JINGYU"
SQL> select * from dual where SYS_CONTEXT('USERENV','SESSION_USER') = 'JINGYU';
D
-
X
SQL> conn ludan
Enter password:
Connected.
SQL> show user
USER is "LUDAN"
SQL> select * from dual where SYS_CONTEXT('USERENV','SESSION_USER') = 'JINGYU';
no rows selected
SQL>
查询FGA_LOG$,确认审计效果:
select * from FGA_LOG$ where OBJ$NAME = 'EMP';
测试审计效果:
3.1 禁用/启用审计策略 官方文档语法:
--禁用审计策略 DBMS_FGA.DISABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ); --启用审计策略 DBMS_FGA.ENABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, enable BOOLEAN);
--禁用审计策略
BEGIN
DBMS_FGA.DISABLE_POLICY (
object_schema => 'scott',
object_name => 'emp',
policy_name => 'FGA_TEST_01');
END;
/
--启用审计策略
BEGIN
DBMS_FGA.ENABLE_POLICY (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'FGA_TEST_01',
enable => TRUE);
END;
/
3.2 删除审计策略 官方文档语法:
DBMS_FGA.DROP_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 );
--删除policy
BEGIN
DBMS_FGA.DROP_POLICY(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'FGA_TEST_01');
END;
/