oracle能够对数据库中发生的一切进行审计。审计的记录可以记录到操作系统中，也可以保存到SYS.AUD$表中。利用审计信息，可以审查可疑的数据库活动，发现非法操作。
oracle中值得审计的操作行为主要有三大类：登陆尝试、对象存取及数据库动作。在默认设置中，oracle审计功能激活后，oracle的审计功能能把成功和不成功的命令都记录下来，但实际应用中，常常不需要对两种行为都进行跟踪。
准备审计
在默认情况下，oracle系统关闭了审计功能，所以在审计前必须先激活审计功能，为进行审计做好准备。
由于部分对象或动作被审计后，可能将影响oracle系统的性能，同时由于审计日志的迅速增大，将占用许多存贮空间，所以在开始审计前必须先对被审计的内容进行设计和规划。
要想激活数据库的审计功能，需要在这个数据库的初始化参数文件中设置audit_trail的参数值。
none 禁用审计功能
false 禁用审计功能
true 激活审计功能，审计记录将写到SYS.AUD$表中
db 激活审计功能，审计记录将写到SYS.AUD$表中
db,extended 激活审计功能，审计记录将写到SYS.AUD$表中
os 激活审计功能，审计记录将写到操作系统的审计跟踪中
审计表安装在SYSTEM表空间。所以要确保SYSTEM表空间又足够的空间存放审计信息。
安装后要重启数据库
将审计相关的表移动到其他表空间
由于AUD$表等审计相关的表存放在SYSTEM表空间，因此为了不影响系统的性能，保护
SYSTEM表空间，最好把AUD$移动到其他的表空间上。
可以使用下面的语句来进行移动：
sql>connect / as sysdba;
sql>alter table aud$ move tablespace <new tablespace>;
sql>alter index I_aud1 rebuild online tablespace <new tablespace>;
SQL> alter table audit$ move tablespace <new tablespace>;
SQL> alter index i_audit rebuild online tablespace <new tablespace>;
SQL> alter table audit_actions move tablespace <new tablespace>;
SQL> alter index i_audit_actions rebuild online tablespace <new tablespace>;
登录审计
数据库攻击者往往采用猜测口令的方法来尝试登录到各种账户上，为了提高数据库的安全性，可以对数据库的每一次登录尝试都进行审计
SQL> audit session;
Audit succeeded.
SQL> select * from dba_stmt_audit_opts ;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
[root@hadoop-m1]su - oracle
[oracle@hadoop-m1 ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 21 09:47:56 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
SQL> select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,returncode from aud$;
SQL> select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,returncode from aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP# USERID USERHOST TERMINAL ACTION# RETURNCODE
---------- ---------- ---------- ---------- ------------------------------ -------------------- -------------------- ---------- ----------
114481 1 1 SCOTT hadoop-m1 pts/2 100 0
114482 1 1 SYSTEM hadoop-m1 101 0
SQL> conn scott/heizi;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,returncode from aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP# USERID USERHOST TERMINAL ACTION# RETURNCODE
---------- ---------- ---------- ---------- ------------------------------ -------------------- -------------------- ---------- ----------
114481 1 1 SCOTT hadoop-m1 pts/2 101 0
114482 1 1 SYSTEM hadoop-m1 101 0
114483 1 1 SCOTT hadoop-m1 pts/2 100 1017
SQL> noaudit session;
Noaudit succeeded.
SQL> truncate table aud$;
Table truncated.
SQL> audit session by scott whenever not successful;
Audit succeeded.
Warning: You are no longer connected to ORACLE.
SQL> conn scott/tiger
Connected.
SQL> select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,returncode from aud$;
no rows selected
SQL> conn scott/heizi
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,returncode from aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP# USERID USERHOST TERMINAL ACTION# RETURNCODE
---------- ---------- ---------- ---------- ------------------------------ -------------------- -------------------- ---------- ----------
114488 1 1 SCOTT hadoop-m1 pts/2 100 1017
当然也可以审计所有用户的登录
SQL> audit session whenever not successful;
Audit succeeded.
SQL> audit session whenever successful;
Audit succeeded.
SQL> audit session whenever not successful;
Audit succeeded.
SQL> audit session whenever successful;
Audit succeeded.
select * from dba_stmt_audit_opts
select * from dba_priv_audit_opts
可以查看当前审计的内容
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
CREATE SESSION NOT SET BY ACCESS
SQL> select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
CREATE SESSION NOT SET BY ACCESS
查看审计记录
select os_username,username,decode(returncode,'0','登录成功','1005','密码为空','1017','登录失败',returncode) from DBA_AUDIT_SESSION
关闭审计
SQL> noaudit session;
Noaudit succeeded.
SQL> select * from dba_stmt_audit_opts;
no rows selected
SQL> select * from dba_priv_audit_opts;
no rows selected
SQL> truncate table aud$;
Table truncated.
操作审计
SQL> audit user;
Audit succeeded.
SQL> select * from aud$;
no rows selected
SQL> create user zgaoqiang identified by zgaoqiang;
User created.
SQL> select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,returncode from aud$;
no rows selected
奇怪发现没有被审计呢
原来是因为，这个是直接conn / as sysdba登录的，是os Authenticcation ,如果用Database Authenticcation就会记录下alter user的操作
用navicat用scott/tiger登录
alter user scott IDENTIFIED by heizi
SQL> select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,returncode from aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP# USERID USERHOST TERMINAL ACTION# RETURNCODE
---------- ---------- ---------- ---------- ------------------------------ -------------------- -------------------- ---------- ----------
114518 1 6 SCOTT WORKGROUP\PC-2013030 PC-201303011352 43 0
11352
果真可以审计了
SQL> select os_username,username,terminal,owner,obj_name,action_name,decode(returncode,'0','执行成功',returncode),to_char(timestamp,'YYYY-MON-DD HH24:MI:SS') from dba_audit_object;
OS_USERNAME USERNAME TERMINAL OWNER OBJ_NAME ACTION_NAME DECODE(RETURNCODE,'0','执ETU TO_CHAR(TIMESTAMP,'YYYY
-------------------- ---------- -------------------- ----- ------------------------- ---------------------------- ---------------------------------------- -----------------------
Administrator SCOTT PC-201303011352 SCOTT ALTER USER 执行成功 2013-NOV-21 10:24:16
Administrator SCOTT PC-201303011352 SCOTT ALTER USER 执行成功 2013-NOV-21 10:29:24
SQL>
对象审计
oracle 不仅能对数据库对象上的系统级操作行为进行审计，还可以对数据库对象上的数据库行为进行审计。
数据库操作行为主要对表的select、insert、update、和delete操作。
对数据库行为的审计与对系统级操作行为的审计命令语法格式相似。
对用户scott和system查询表和更新表的操作进行审计：
audit select table,update table by scott,system;
对表中的删除操作进行审计：
audit delete any table;
对数据库新建对象的alter、grant、insert、update和delete操作停止审计的命令：
noaudit alter,grant,insert,update,delete on default;
对象审计工作可以通过使用子句by session或by access来控制会话或访问来进行审计。
by session表示对象审计记录将每个会话写一次，by access表示对象审计记录将在该对象每访问一次时写一次。
以下命令激活了scott.emp上所有delete操作，表每次delete操作都将被记录到审计记录中：
audit delete on scott.emp by access;
激活审计功能后，要保护好sys.aud$表中的所有审计记录，银威攻击者可能会删除有关的审计记录。保护好sys.aud$表中的审计记录可以使用以下命令：
audit all on sys.aud$ by access;
此外，如果dba不能及时对审计跟踪做出检查和分析，那么审计就没太大的实际意义。
FGA 细粒度审计
-- 审计表SQL>grant resource,connect to bank identified by bank;
create table bank.accounts
(
acct_no number primary key,
cust_id number not null ,
balance number(15,2) null
);
insert into bank.accounts values(1,1,10000);
insert into bank.accounts values(2,2,20000);
commit;
Begin
dbms_fga.add_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
audit_column => 'BALANCE',
enable => TRUE,
statement_types=>'SELECT,INSERT,UPDATE,DELETE',
audit_trail=> SYS.DBMS_FGA.DB+SYS.DBMS_FGA.EXTENDED,
policy_name=>'ACCOUNTS_ACCESS');
end;
/
SQL> select t.object_schema,t.object_name,t.policy_name from dba_audit_policies t;
OBJECT_SCH OBJECT_NAM POLICY_NAME
---------- ---------- ------------------------------
BANK ACCOUNTS ACCOUNTS_ACCESS
审计策略默认开启
另开一会话
SQL> conn bank/bank
Connected.
SQL> select BALANCE from bank.accounts where BALANCE>=11000;
BALANCE
----------
20000
SQL> select BALANCE from bank.accounts where BALANCE=20000;
BALANCE
----------
20000
SQL> conn / as sysdba
SQL> select timestamp, db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail; TIMESTAMP DB_USER OS_USER OBJECT_SCH OBJECT_NAM SQL_TEXT
---------- ---------- ---------- ---------- ---------- ------------------------------------------------------------
2013/11/21 SCOTT oracle BANK ACCOUNTS select BALANCE from bank.accounts where BALANCE>=11000
2013/11/21 SCOTT oracle BANK ACCOUNTS select BALANCE from bank.accounts where BALANCE=20000
FGA_LOG$是基表，审计记录存在里面，dba_fga_audit_trail是视图。可以truncate table FGA_LOG$清理空间
停止审计
Begin
dbms_fga.drop_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
policy_name=>'ACCOUNTS_ACCESS');
end;
/
可以置为不可用
begin
dbms_fga.enable_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS',
enable => FALSE );
end;
/
SQL> desc dbms_fga;
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
PROCEDURE DISABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT
SQL>
测试过程中，一直用 sys as sysdba操作查询bank.accounts，dba_fga_audit_trail;表里没反应，切换到bank用户才可以
oracle参数audit_sys_operations默认为false，当设置为true时，所有sys用户（包括以sysdba, sysoper身份登录的用户）的操作都会被记录，audit trail不会写在aud$表中，而写在audit_file_dest 中。
SQL> alter system set audit_sys_operations=TRUE scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 83887720 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select BALANCE from bank.accounts where BALANCE=20000;
[root@hadoop-m1]more ora_14068.aud
Audit file /opt/oracle/admin/duxiu/adump/ora_14068.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
Thu Nov 21 13:59:25 2013
ACTION : 'select BALANCE from bank.accounts where BALANCE=20000'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/5
STATUS: 0
Thu Nov 21 13:59:32 2013
ACTION : 'select timestamp, db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/5
STATUS: 0
713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
