简单使用oracle的审计语句

 

 

审计（Audit)用于监视用户所执行的数据库操作，并且Oracle会将审计跟踪结果存放到OS文件或数据库中。

1、使用审计，需要激活审计
SQL> conn /as sysdba
已连接。
SQL> show parameter audit_sys_operations;

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE                         
SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE                          
SQL> alter system set audit_sys_operations=TRUE scope=spfile;

系统已更改。

                    
SQL> alter system set audit_trail=db scope=spfile;

系统已更改。

SQL> startup force
ORACLE 例程已经启动。

Total System Global Area  289406976 bytes                                      
Fixed Size                  1248576 bytes                                      
Variable Size              79692480 bytes                                      
Database Buffers          201326592 bytes                                      
Redo Buffers                7139328 bytes                                      
数据库装载完毕。
数据库已经打开。
SQL> show parameter audit;

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\ADUMP                   
audit_sys_operations                 boolean     TRUE                          
audit_trail                          string      DB                            

 

 

2、审计dept表

SQL> audit all on dept;

审计已成功。
                                                              

SQL> conn mzl/mzl
已连接。
SQL> select * from dept;

    DEPTNO DNAME          LOC                                                  
---------- -------------- -------------                                        
        10 ACCOUNTING     NEW YORK                                             
        20 RESEARCH       DALLAS                                               
        30 SALES          CHICAGO                                              
        40 OPERATIONS     PanJin                                               
        80 mengzhaoliang  beijing                                              

SQL> insert into dept
  2  values (90,'test','PanJin');

已创建 1 行。

SQL> commit;

提交完成。

SQL> conn scott/mzl
已连接。

SQL> insert into dept
  2  values(60,'June','ShangHai');

已创建 1 行。

SQL> commit;

提交完成。

3、查看审计结果
SQL> conn /as sysdba
已连接。
SQL> select count(*) from dba_audit_trail;

  COUNT(*)                                                                     
----------                                                                     
         2                                                                     

SQL> select username,ses_actions,obj_name,
  2  to_char(timestamp,'YYYY-MM-DD HH24:MI:SS')
  3  from dba_audit_trail;

USERNAME                       SES_ACTIONS                                     
------------------------------ -------------------                             
OBJ_NAME                                                                       
--------------------------------------------------------------------------------
TO_CHAR(TIMESTAMP,'                                                            
-------------------                                                            
MZL                            ------S--S------                                
DEPT                                                                           
2008-10-20 10:28:08                                                            
                                                                               
SCOTT                          ------B---------                                
DEPT                                                                           
2008-10-20 10:29:04                                                            

USERNAME                       SES_ACTIONS                                     
------------------------------ -------------------                             
OBJ_NAME                                                                       
--------------------------------------------------------------------------------
TO_CHAR(TIMESTAMP,'                                                            
-------------------                                                            
                                                                               

                                                                

                                                                               

 

 

SQL> conn mzl/mzl
已连接。

4、禁止审计
SQL> noaudit all on dept;

审计未成功。

                                                                

5、mzl用户为dba的用户，使用精细审计，这样可以知道具体的用户更新的数据
SQL> show user;
USER 为 "MZL"

6、审计dept表
SQL> exec dbms_fga.add_policy(object_name=>'dept',policy_name=>'chk_dept',-
> statement_types=>'insert,update,delete,select');

PL/SQL 过程已成功完成。

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)                                                                     
----------                                                                     
         0                                                                     

SQL> select * from dept;

    DEPTNO DNAME          LOC                                                  
---------- -------------- -------------                                        
        10 ACCOUNTING     NEW YORK                                             
        20 RESEARCH       DALLAS                                               
        30 SALES          CHICAGO                                              
        40 OPERATIONS     PanJin                                               
        60 June           ShangHai                                             
        80 mengzhaoliang  beijing                                              
        90 test           PanJin                                               

已选择7行。

SQL> delete dept where deptno=90;

已删除 1 行。

SQL> commit;

提交完成。

SQL> conn scott/mzl
已连接。

SQL> update dept set loc='PanJin' where deptno=60;

已更新 1 行。

SQL> commit;

提交完成。

SQL>  conn mzl/mzl
已连接。
SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)                                                                     
----------                                                                     
         3                                                                     

SQL> col sql_text for a40
SQL> l
  1* select db_user,sql_text from dba_fga_audit_trail
SQL> /

DB_USER                        SQL_TEXT                                        
------------------------------ ----------------------------------------        
MZL                            select * from dept                              
MZL                            delete dept where deptno=90                     
SCOTT                          update dept set loc='PanJin' where deptn        
                               o=60                                            
                                                                               
7、取消精细审计                                
SQL> exec dbms_fga.disable_policy(object_name=>'dept',-
> policy_name=>'chk_dept');

PL/SQL 过程已成功完成。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-473207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-473207/