1.使用审计,需要激活审计
sys@ora10> conn / as sysdba
Connected.
sys@ora10> show parameter audit_sys_operations;
NAME TYPE VALUE
--------------------- ------- -----
audit_sys_operations boolean FALSE
sys@ora10> show parameter audit_trail;
NAME TYPE VALUE
----------- ------- -------
audit_trail string NONE
sys@ora10> alter system set audit_sys_operations=TRUE scope=spfile;
System altered.
sys@ora10> alter system set audit_trail=db scope=spfile;
System altered.
sys@ora10> startup force
ORACLE instance started.
Total System Global Area 146800640 bytes
Fixed Size 1260576 bytes
Variable Size 130024416 bytes
Database Buffers 12582912 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> show parameter audit;
sys@ora10> show parameter audit;
NAME TYPE VALUE
--------------------- --------- ------------------------------------
audit_file_dest string /oracle/app/oracle/admin/ora10/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
2.审计test_tab表
sys@ora10> create user sec identified by sec;
User created.
sys@ora10> grant dba to sec;
Grant succeeded.
sys@ora10> conn sec/sec
Connected.
sec@ora10> create table test_tab (a number,b number);
Table created.
sec@ora10> insert into test_tab values (1,1);
sec@ora10> insert into test_tab values (2,2);
sec@ora10> insert into test_tab values (3,3);
sec@ora10> insert into test_tab values (4,4);
sec@ora10> insert into test_tab values (5,5);
sec@ora10> insert into test_tab values (6,6);
sec@ora10> audit all on test_tab;
Audit succeeded.
sec@ora10> select * from test_tab;
A B
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
6 rows selected.
sec@ora10> insert into test_tab values (7,7);
1 row created.
sec@ora10> commit;
Commit complete.
3.查看审计结果
sec@ora10> conn /as sysdba
Connected.
sys@ora10> select count(*) from dba_audit_trail;
COUNT(*)
----------
1
sys@ora10> col OBJ_NAME for a20
sys@ora10> select username,ses_actions,obj_name,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') from dba_audit_trail;
USERNAME SES_ACTIONS OBJ_NAME TO_CHAR(TIMESTAMP,'
--------- ---------------- ---------- -------------------
SEC -S----B--S------ TEST_TAB 2008-11-20 01:13:35
4.禁止审计
sec@ora10> noaudit all on test_tab;
Noaudit succeeded.
5.sec用户为dba的用户,使用精细审计,这样可以知道具体的用户更新的数据
sec@ora10> sho user
USER is "SEC"
6.审计test_tab表
sys@ora10> exec dbms_fga.add_policy(object_schema=>'SEC', object_name=> 'TEST_TAB', policy_name=> 'check_test_tab',statement_types => 'INSERT, UPDATE, DELETE, SELECT');
PL/SQL procedure successfully completed.
sec@ora10> select count(*) from dba_fga_audit_trail;
COUNT(*)
----------
0
sec@ora10> select * from test_tab;
A B
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
7 rows selected.
sec@ora10> delete test_tab where a=1;
1 row deleted.
sec@ora10> commit;
Commit complete.
SQL> select count(*) from dba_fga_audit_trail;
COUNT(*)
----------
3
SQL> col sql_text for a40
SQL> select db_user,sql_text from dba_fga_audit_trail
7.取消和启用精细审计
sys@ora10> exec dbms_fga.disable_policy(object_schema=>'SEC', object_name=> 'TEST_TAB', policy_name=> 'check_test_tab');
sys@ora10> exec dbms_fga.enable_policy(object_schema=>'SEC', object_name=> 'TEST_TAB', policy_name=> 'check_test_tab');
-- The End --
sys@ora10> conn / as sysdba
Connected.
sys@ora10> show parameter audit_sys_operations;
NAME TYPE VALUE
--------------------- ------- -----
audit_sys_operations boolean FALSE
sys@ora10> show parameter audit_trail;
NAME TYPE VALUE
----------- ------- -------
audit_trail string NONE
sys@ora10> alter system set audit_sys_operations=TRUE scope=spfile;
System altered.
sys@ora10> alter system set audit_trail=db scope=spfile;
System altered.
sys@ora10> startup force
ORACLE instance started.
Total System Global Area 146800640 bytes
Fixed Size 1260576 bytes
Variable Size 130024416 bytes
Database Buffers 12582912 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> show parameter audit;
sys@ora10> show parameter audit;
NAME TYPE VALUE
--------------------- --------- ------------------------------------
audit_file_dest string /oracle/app/oracle/admin/ora10/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
2.审计test_tab表
sys@ora10> create user sec identified by sec;
User created.
sys@ora10> grant dba to sec;
Grant succeeded.
sys@ora10> conn sec/sec
Connected.
sec@ora10> create table test_tab (a number,b number);
Table created.
sec@ora10> insert into test_tab values (1,1);
sec@ora10> insert into test_tab values (2,2);
sec@ora10> insert into test_tab values (3,3);
sec@ora10> insert into test_tab values (4,4);
sec@ora10> insert into test_tab values (5,5);
sec@ora10> insert into test_tab values (6,6);
sec@ora10> audit all on test_tab;
Audit succeeded.
sec@ora10> select * from test_tab;
A B
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
6 rows selected.
sec@ora10> insert into test_tab values (7,7);
1 row created.
sec@ora10> commit;
Commit complete.
3.查看审计结果
sec@ora10> conn /as sysdba
Connected.
sys@ora10> select count(*) from dba_audit_trail;
COUNT(*)
----------
1
sys@ora10> col OBJ_NAME for a20
sys@ora10> select username,ses_actions,obj_name,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') from dba_audit_trail;
USERNAME SES_ACTIONS OBJ_NAME TO_CHAR(TIMESTAMP,'
--------- ---------------- ---------- -------------------
SEC -S----B--S------ TEST_TAB 2008-11-20 01:13:35
4.禁止审计
sec@ora10> noaudit all on test_tab;
Noaudit succeeded.
5.sec用户为dba的用户,使用精细审计,这样可以知道具体的用户更新的数据
sec@ora10> sho user
USER is "SEC"
6.审计test_tab表
sys@ora10> exec dbms_fga.add_policy(object_schema=>'SEC', object_name=> 'TEST_TAB', policy_name=> 'check_test_tab',statement_types => 'INSERT, UPDATE, DELETE, SELECT');
PL/SQL procedure successfully completed.
sec@ora10> select count(*) from dba_fga_audit_trail;
COUNT(*)
----------
0
sec@ora10> select * from test_tab;
A B
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
7 rows selected.
sec@ora10> delete test_tab where a=1;
1 row deleted.
sec@ora10> commit;
Commit complete.
SQL> select count(*) from dba_fga_audit_trail;
COUNT(*)
----------
3
SQL> col sql_text for a40
SQL> select db_user,sql_text from dba_fga_audit_trail
7.取消和启用精细审计
sys@ora10> exec dbms_fga.disable_policy(object_schema=>'SEC', object_name=> 'TEST_TAB', policy_name=> 'check_test_tab');
sys@ora10> exec dbms_fga.enable_policy(object_schema=>'SEC', object_name=> 'TEST_TAB', policy_name=> 'check_test_tab');
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-557306/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-557306/