【實驗】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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- DM7審計之語句序列審計
- DM7審計之語句級審計
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- ORACLE常用語句:Oracle
- Oracle Audit 審計功能的認識與使用Oracle
- 實驗6迴圈結構程式設計(for語句的應用)程式設計
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- Oracle審計(轉)Oracle
- Oracle:審計清理Oracle
- Oracle Linux 7使用syslog來管理Oracle ASM的審計檔案OracleLinuxASM
- oracle中的條件語句Oracle
- Oracle基本SQL語句OracleSQL
- Oracle 建立序列語句Oracle
- Oracle實驗(03):number的使用Oracle
- Oracle 統一審計- Best 實踐三Oracle
- Oracle 統一審計- Best 實踐一Oracle
- ORACLE AUDIT審計(1)Oracle
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- 【SQL】Oracle SQL join on語句and和where使用區別SQLOracle
- Oracle select 語句字串拼接小例項-quote使用Oracle字串
- SQL查詢語句 (Oracle)SQLOracle
- Oracle Linux 7使用cron來管理Oracle ASM審計檔案目錄的增長OracleLinuxASM
- Oracle OCP(01):使用SQL SELECT語句檢索資料OracleSQL
- oracle v$sqlare 分析SQL語句使用資源情況OracleSQL
- oracle審計-細粒度(轉)Oracle
- oracle 細粒度審計(fga)Oracle
- ORACLE多表關聯UPDATE語句Oracle
- Oracle資料庫語句大全Oracle資料庫
- 列出oracle dbtime得sql語句OracleSQL
- oracle檢視物件DDL語句Oracle物件
- 【LOB】Oracle lob管理常用語句Oracle
- oracle語句練習--初級Oracle
- oracle資料庫常用語句Oracle資料庫
- 5、控制語句的使用
- 判斷語句的使用
- Oracle中如何查詢未使用繫結變數的SQL語句?Oracle變數SQL
- oracle匯出使用者、表空間和角色的建立語句Oracle
- 實驗5 迴圈結構程式設計(while、do-while語句的應用)程式設計While
- 實驗5迴圈結構程式設計(while、do-while語句的應用)程式設計While