【實驗】Oracle審計語句的使用演示
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 簡單使用oracle的審計語句Oracle
- Oracle使用審計監控使用者執行過的SQL語句OracleSQL
- 【實驗】【審計】【FGA】使用Oracle的審計功能監控資料庫中的可疑操作Oracle資料庫
- Oracle使用系統級觸發器審計重要帳號的DDL語句Oracle觸發器
- Oracle11gr2 審計語句增強(二)Oracle
- Oracle11gr2 審計語句增強(一)Oracle
- 【實驗】【MySQL】MySQL的DDL語言演示MySql
- 【實驗】【MySQL】MySQL的DML語言演示MySql
- 【實驗】【Tom】runstats工具使用演示
- Audit裡審計SQL語句與審計系統許可權的區別SQL
- Oracle11g安全審計–重要帳號的DDL語句操作記錄Oracle
- 經驗教訓,慎用Oracle的審計Oracle
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- 審計Oracle資料庫的使用Oracle資料庫
- 語句審計相關的表是stmt_audit_option_map
- oracle之PLSql語言(二)sql語句的使用OracleSQL
- 實驗6迴圈結構程式設計(for語句的應用)程式設計
- oracle中merge 語句使用Oracle
- 利用Oracle FGA實現審計Oracle
- oracle的sql語句OracleSQL
- oracle語句Oracle
- ORACLE的 審計功能Oracle
- Oracle審計Oracle
- oracle 審計Oracle
- Oracle Audit 審計功能的認識與使用Oracle
- 【實驗】【Tom】show_space儲存過程的使用演示儲存過程
- OEM模組審計查詢語句佔用較大資源
- 實驗-審計資訊的清理和策略關閉.txt
- Oracle Hints語句的用法Oracle
- Oracle的多表插入語句Oracle
- oracle的表分析語句Oracle
- 常用的oracle基本語句Oracle
- oracle的connect by語句Oracle
- Oracle 審計的初步操作Oracle
- oracle dblink實現insert語句的同步Oracle
- 關於評審開發人員的sql語句SQL
- oracle 使用explain plan分析查詢語句OracleAI
- oracle使用profiler分析語句執行效率Oracle