利用Oracle FGA实现审计
1.建立测试表(用户EDMS下)
Create Table T_AUDIT_DEMO
(
CID INT NOT NULL, CNAME VARCHAR2(20) NULL, ENAME VARCHAR2(20) NULL,
CONSTRAINT PK_T_AUDIT_DEMO PRIMARY KEY (CID)
);
2.创建审计策略(用户SYS下)
begin
dbms_fga.add_policy
(
object_schema=>'EDMS', object_name=>'T_AUDIT_DEMO',
policy_name=>'T_AUDIT_DEMO_AUDIT'
);
end;
3.测试审计效果(用户EDMS下)
INSERT INTO T_AUDIT_DEMO VALUES(1,'曾勋','ZENG XUN');
INSERT INTO T_AUDIT_DEMO VALUES(2,'翁黎明','WENG LI MING');
INSERT INTO T_AUDIT_DEMO VALUES(3,'刘帝勇','LIU DI YONG');
4.使用Select查询测试表(用户EDMS下)
SQL> SELECT * FROM T_AUDIT_DEMO;
CID CNAME ENAME
----- -------------------- -------
1曾勋ZENG XUN
2翁黎明WENG LI MING
3刘帝勇LIU DI YONG
5.再次查看审计效果(用户SYS下)
SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;
STATEMENT_TYPE SQL_TEXT
-------------- -----------
SELECT SELECT * FROM T_AUDIT_DEMO
注意:之前的Insert语句并不在审计中。默认的只对Select进行审计。在Oracle 9i中它只捕获 SELECT 语句。而在Oracle 10i中进行了扩展,支持对所有类型的DML进行审计。
6.修改审计粒度(用户SYS下)
begin
dbms_fga.add_policy
(
object_schema=>'EDMS', object_name=>'T_AUDIT_DEMO', policy_name=>'T_AUDIT_DEMO_AUDIT', statement_types=>'INSERT, UPDATE, DELETE, SELECT'
);
end;
注意:不能实现对Truncat Table的审计。
7.测试审计(用户EDMS、SYS下)
INSERT INTO T_AUDIT_DEMO VALUES(4,'黄智洪','HUANG ZHI HONG');
DELETE FROM T_AUDIT_DEMO WHERE CID < 4;
SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;
STATEMENT_TYPE SQL_TEXT
-------------- --------------------
SELECT SELECT * FROM T_AUDIT_DEMO
INSERT INSERT INTO T_AUDIT_DEMO VALUES(4,'黄智洪','HUANG ZHI HONG')
DELETE DELETE FROM T_AUDIT_DEMO WHERE CID < 4
SELECT SELECT * FROM T_AUDIT_DEMO
至此,我们已经实现了对表T_AUDIT_DEMO的审计。与FGA相关的表或者视图:
select * from fga$
select * from fga_log$
select * from fgacol$
select * from dba_fga_audit_trail
select * from dba_common_audit_trail
select * from dba_audit_policies
select * from dba_fga_audit_trail
与FGA相关的包或者过程:
dbms_fga.add_policy dbms_fga.drop_policy
至于这些表、视图、包的列或者参数的使用方法,可以Describe或者查看相关文档
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9236282/viewspace-1029232/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9236282/viewspace-1029232/