- 论坛徽章:
- 0
|
回复 wq_1228
如果升级到10g,所有的dml语句都可以监视,发生的SQL也可以被记录
---怎么做?
TOADLover 发表于 2010-06-02 08:21
以下面的例子说明一下设置吧
测试表
CREATE TABLE SAMPLE_TABLE
(
ID NUMBER(10,0) NOT NULL,
NAME VARCHAR2(250)
)
设置
BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_NAME => 'SAMPLE_TABLE',
POLICY_NAME => 'CHECK_DANGER',
AUDIT_COLUMN => 'NAME',
AUDIT_CONDITION => 'NAME = ''ADMIN'' ',
STATEMENT_TYPES => 'INSERT,UPDATE,DELETE'
);
END;
SQL操作
TRUNCATE TABLE SAMPLE_TABLE;
INSERT INTO SAMPLE_TABLE (ID, NAME) VALUES (1,'SCOTT');
INSERT INTO SAMPLE_TABLE (ID, NAME) VALUES (2,'TIGER');
-- Check
INSERT INTO SAMPLE_TABLE (ID, NAME) VALUES (3,'ADMIN');
INSERT INTO SAMPLE_TABLE (ID, NAME) VALUES (4,'ADMINISTRATOR');
UPDATE SAMPLE_TABLE SET ID=10 WHERE NAME='ADMINISTRATOR';
-- ↓ ※※※ Not Check ※※※ ↓
UPDATE SAMPLE_TABLE SET ID=11 WHERE ID=3;
-- Check
UPDATE SAMPLE_TABLE SET NAME='GUEST' WHERE NAME='ADMIN';
-- Check
UPDATE SAMPLE_TABLE SET NAME='ADMIN' WHERE NAME='SCOTT';
UPDATE SAMPLE_TABLE SET NAME='SCOTT' WHERE NAME='GUEST';
-- Check
DELETE FROM SAMPLE_TABLE WHERE NAME='ADMIN';
查看结果
SELECT DB_USER || ':' || SQL_TEXT FGA_LOG FROM DBA_FGA_AUDIT_TRAIL
WHERE TIMESTAMP >= SYSDATE - INTERVAL '1' MINUTE;
/* 结果 */
FGA_LOG
--------------------------------------------------------------------------------
TEST:INSERT INTO SAMPLE_TABLE (ID, NAME) VALUES (3,'ADMIN')
TEST:UPDATE SAMPLE_TABLE SET NAME='GUEST' WHERE NAME='ADMIN'
TEST:UPDATE SAMPLE_TABLE SET NAME='ADMIN' WHERE NAME='SCOTT'
TESTELETE FROM SAMPLE_TABLE WHERE NAME='ADMIN' |
|