随着数据的安全意识的不断加深，现在很多的企业已经开始逐步深化对数据安全的管控措施。作为数据库龙头老大的O记自当在其自身产品中充分的考虑到数据安全的问题，本篇文章将简单介绍一下基于Oracle数据库的数据审计入门配置。
查看数据库审计功能状态
//使用sqlplus通过sysdba角色登陆Oracle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:32:00 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 654314880 bytes
Database Buffers 171966464 bytes
Redo Buffers 6590464 bytes
Database mounted.
Database opened.
SQL>
//查看审计参数是否开启
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/fs11g/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
其中audit_trail代表数据库审计功能的状态，需设置db,extended，各项值含义如下：
-
none
Disables database auditing.
-
os
Enables database auditing and directs all audit records to the operating system’s audit trail.
-
db
Enables database auditing and directs all audit records to the database audit trail (the
SYS.AUD$table). -
db,extended
Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.
-
xml
Enables database auditing and writes all audit records to XML format OS files.
-
xm,extended
Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.
开启审计
//开启审计功能
SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile;
System altered.
//关闭数据库实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
//启动数据实例
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 654314880 bytes
Database Buffers 171966464 bytes
Redo Buffers 6590464 bytes
Database mounted.
Database opened.
//查看审计参数是否开启
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/fs11g/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB, EXTENDED
查看审计日志表
SQL> set line 120
SQL> col obj_name format a5
SQL> col obj_name for a10
SQL> col SQL_TEXT format a30
SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp, obj_name, action_name, sql_text from dba_audit_trail where obj_name is not null;
USERN TIMESTAMP OBJ_NAME ACTION_NAME SQL_TEXT
----- -------------- ---------- ---------------------------- ---------------------------
0 rows selected.
验证准备
$ sqlplus stl/sun
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:51:01 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test (c1 varchar2(20));
Table created.
开启指定对象审计
[ora11g@db ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:52:49 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> audit insert, select, update, delete on stl.test by access whenever successful;
Audit succeeded.
验证审计
$ sqlplus stl/sun
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:51:01 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test (c1 varchar2(20));
Table created.
SQL> select * from test;
no rows selected
SQL> insert into test values(1);
1 row created.
SQL> update test set c1 = 2;
1 row updated.
SQL> select * from test;
C1
--------------------
2
SQL> delete from test;
1 row deleted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:52:49 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp, obj_name, action_name, sql_text from dba_audit_trail where obj_name ='TEST';
USERN TIMESTAMP OBJ_NAME ACTION_NAME SQL_TEXT
----- -------------- ---------- -------------------------- -----------------------------
STL 10/16/19 16:55 TEST INSERT insert into test values(1)
STL 10/16/19 16:55 TEST SELECT select * from test
STL 10/16/19 16:54 TEST SELECT select * from test
STL 10/16/19 16:55 TEST UPDATE update test set c1 = 2
STL 10/16/19 16:55 TEST DELETE delete from test
其他常用命令
--取消指定对象审计
SQL> noaudit all on test;
--删除审计日志
SQL> DELETE FROM SYS.AUD$ WHERE obj$name='TEST';
参考链接: