Oracle 10g Audit（审计） --- 记录登录用户在Oracle中的所有操作

ゝ一世哀愁。 2022-07-13 10:58 599阅读 0赞

由于项目平台管理的需要，最近在研究Oracle 10G的审计功能，以便记录和跟踪用户在Oracle数据库系统中的所有操作行为，进而提高Oracle的安全性。现在把审计功能的配置步骤分享出来，供大家参考：

**1、以DBA登录Oracle**

\# su - oracle  
$ sqlplus /nolog  
SQL> conn / as sysdba

**2、查看当前审计设置**

SQL> show parameter audit;

**参数说明**

AUDIT\_TRAIL = \{ none | os | db | db,extended | xml | xml,extended \}  
none or false - Auditing is disabled.  
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).  
db,extended - As db, but the SQL\_BIND and SQL\_TEXT columns are also populated.  
xml- Auditing is enabled, with all audit records stored as XML format OS files.  
xml,extended - As xml, but the SQL\_BIND and SQL\_TEXT columns are also populated.  
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

The AUDIT\_SYS\_OPERATIONS static parameter  
enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges,  
including the SYS user. All audit records are written to the OS audit trail.

The AUDIT\_FILE\_DEST parameter  
specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used.  
It is also the location for all mandatory auditing specified by the AUDIT\_SYS\_OPERATIONS parameter.

**3、修改audit参数，开启审计**

SQL> alter system set audit\_trail=db\_extended scope=spfile;  
注意，如果audit\_trail=db，不记录SQL\_BIND和SQL\_TEXT

**4、重启数据库**

SQL> shutdown immediate;  
SQL> startup;

**5、测试**

**创建用户**AUDIT\_TEST

$ sqlplus /nolog  
SQL> conn / as sysdba

SQL> audit all by audit\_test by access;  
SQL> audit select table, update table, insert table, delete table by audit\_test by access;  
SQL> audit execute procedure by audit\_test by access;

**分别对应以下三种：**  
DDL (CREATE, ALTER & DROP of objects)  
DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).  
SYSTEM EVENTS (LOGON, LOGOFF etc.)

SQL> conn audit\_test/password

SQL> create table test(id  number);

SQL> insert into test(id) values (1);  
SQL> insert into test(id) values (2);  
SQL> update test set id = 3 where id = 1;  
SQL> select \* from test;  
SQL> delete from test;  
SQL> commit;  
SQL> drop table test;

SQL> select view\_name from dba\_views where  view\_name like 'dba%audit%' order by view\_name;

VIEW\_NAME  
\------------------------------  
DBA\_AUDIT\_EXISTS  
DBA\_AUDIT\_OBJECT  
DBA\_AUDIT\_POLICIES  
DBA\_AUDIT\_POLICY\_COLUMNS  
DBA\_AUDIT\_SESSION  
DBA\_AUDIT\_STATEMENT  
DBA\_AUDIT\_TRAIL  
DBA\_COMMON\_AUDIT\_TRAIL  
DBA\_FGA\_AUDIT\_TRAIL  
DBA\_OBJ\_AUDIT\_OPTS  
DBA\_PRIV\_AUDIT\_OPTS  
DBA\_REPAUDIT\_ATTRIBUTE  
DBA\_REPAUDIT\_COLUMN  
DBA\_STMT\_AUDIT\_OPTS

**视图说明:**  
1. SYS.AUD$  
审计功能的底层视图,如果需要对数据进行删除,只需要对aud$视图进行删除既可,其他视图里的数据都是由aud$所得.  
2. DBA\_AUDIT\_EXISTS  
列出audit not exists和audit exists产生的审计跟踪,我们默认的都是audit exists.  
3. DBA\_AUDIT\_TRAIL  
可以在里面查处所有审计所跟踪的信息.  
4. DBA\_AUDIT\_OBJECT  
可以查询所有对象跟踪信息.(例如,对grant,revoke等不记录),信息完全包含于dba\_audit\_trail  
5. DBA\_AUDIT\_SESSION  
所得到的数据都是有关logon或者logoff的信息.  
6. DBA\_AUDIT\_STATEMENT  
列出grant ,revoke ,audit ,noaudit ,alter system语句的审计跟踪信息.  
7. DBA\_PRIV\_AUDIT\_OPTS  
通过系统和由用户审计的当前系统特权  
8. DBA\_OBJ\_AUDIT\_OPTS  
可以查询到所有用户所有对象的设计选项  
9. ALL\_DEF\_AUDIT\_OPTS  
10. AUDIT\_ACTIONS  
可以查询出在aud$等视图中actions列的含义  
11. SYSTEM\_PRIVILEGE\_MAP  
可以查询出aud$等视图中priv$used列的含义(注意前面加'-')

**常用视图：**  
DBA\_AUDIT\_TRAIL  
DBA\_FGA\_AUDIT\_TRAIL  
DBA\_COMMON\_AUDIT\_TRAIL

查看审计内容，主要字段：os\_username, userhost, timestamp, owner，sql\_bind, sql\_text  
SQL> select \* from dba\_audit\_trail where  owner = 'AUDIT\_TEST' order by timestamp;  
注意：owner的值必须大写，例如 owner = 'AUDIT\_TEST'

\-------------------------------------------------------------------------------  
**关闭审计**  
\-------------------------------------------------------------------------------  
SQL> alter system set audit\_trail=none scope=spfile;