一、审核的方法
1、应用服务器的日志
2、应用程序的审核
conn scott/password
创建基表emp_copy
create table emp_copy as select * from emp;
创建审核表aud_emp,用于审核emp_copy表上的所有数据操作
create table aud_emp
(username varchar2(30),
action varchar2(6),
empno number(4),
column_name varchar2(255),
call_stack varchar2(4000),
client_id varchar2(15),
old_value varchar2(10),
new_value varchar2(10),
action_date date default sysdate
);
/
创建过程记录更新信息
create or replace procedure audit_emp
(p_username in varchar2,
p_action in varchar2,
p_empno in number,
p_column_name in varchar2,
p_old_value in varchar2,
p_new_value in varchar2
)
as
begin
insert into aud_emp
(username,action,empno,column_name,call_stack,client_id,old_value,new_value,action_date)
values
(p_username,p_action,p_empno,p_column_name,dbms_utility.format_call_stack,sys_context
('userenv','client_identifier'),p_old_value,p_new_value,sysdate);
end;
/
创建执行更新的过程update_sal,并调用过程audit_emp记录更新信息
create or replace procedure update_sal
(p_empno in number,
p_salary in number)
as
l_old_sal varchar2(10);
begin
select sal into l_old_sal from emp_copy where empno=p_empno for update;
update emp_copy set sal=p_salary where empno=p_empno;
audit_emp
(p_username => user,
p_action => 'update',
p_empno => p_empno,
p_column_name => 'sal',
p_old_value => l_old_sal,
p_new_value => p_salary
);
end;
/
创建查看审核信息过程
create or replace procedure show_aud_emp
as
begin
for rec in (select * from aud_emp order by action_date desc) loop
dbms_output.put_line('user:'||rec.username);
dbms_output.put_line('Client_ID:'||rec.client_id);
dbms_output.put_line('Action:'||rec.action);
dbms_output.put_line('Emono:'||rec.empno);
dbms_output.put_line('Column:'||rec.column_name);
dbms_output.put_line('Old Value:'||rec.old_value);
dbms_output.put_line('New Value:'||rec.new_value);
dbms_output.put_line('Date:'||to_char(rec.action_date,'YYYY-MM-DD HH24:MI'));
dbms_output.put_line('---------------------------------------------------');
end loop;
end;
/
相关授权
conn sys/password as sysdba
grant execute on scott.update_sal to hjp;
grant select on scott.emp_copy to hjp;
create or replace trigger set_ip_in_id after logon on database
begin
dbms_session.set_identifier(sys_context('userenv','ip_address'));
end;
/
执行数据操作
conn hjp/password@ora10
select empno,sal from scott.emp_copy where ename = 'HJP';
exec scott.update_sal(8000,6000);
commit;
select empno,sal from scott.emp_copy where ename = 'HJP';
查询审核信息
conn scott/password@ora10
set serveroutput on
exec show_aud_emp
select username,call_stack from aud_emp;
3、触发器审核
conn scott/password@ora10
create or replace trigger update_emp_sal_trig
before update of sal on emp_copy for each row
declare
begin
audit_emp
(p_username => user,
p_action => 'update',
p_empno => :old.empno,
p_column_name => 'sal',
p_old_value => to_char(:old.sal),
p_new_value => to_char(:new.sal)
);
end;
/
grant update(sal) on emp_copy to jds;
conn jds/password@ora10
update scott.emp_copy set sal=5611 where empno=8002;
4、自治事务与审核
create or replace procedure audit_emp
(p_username in varchar2,
p_action in varchar2,
p_empno in number,
p_column_name in varchar2,
p_old_value in varchar2,
p_new_value in varchar2
)
as
pragma autonomous_transaction;--忽略回滚操作,审核更新操作
begin
insert into aud_emp
(username,action,empno,column_name,call_stack,client_id,old_value,new_value,action_date)
values
(p_username,p_action,p_empno,p_column_name,dbms_utility.format_call_stack,sys_context
('userenv','client_identifier'),p_old_value,p_new_value,sysdate);
commit;--提交数据
end;
/
二、回退版本查询(Flashback Version Query) and 回退事务查询(Flashback Transaction Query) 参考
Flashback
三、标准数据库审核
1、强制审核
2、审核SYS
为了能够审核SYS用户,必须将audit_sys_operations值设为true,然后这个值被写进(init.ora),必
须重起数据库。
alter system set audit_sys_operations=true scope=spfile;
3、标准审核
初始化参数audit_trail的值默认为NONE,从而可以允许使用标准审核。把参数设置为"OS",则会把审
核记录写进操作系统中;把参数设置为"DB"或"true",则会把审核记录存储为数据库内的记录(保存在sys.aud$
表中);在Oracle 10g中,设置为"DB_EXTENDED",也可以审核数据库,还会捕获审核记录中其他额外的信息。
alter system set audit_trail=db_extended scope=spfile;
审核用户、权限和对象
当开启审计功能后,可在三个级别对数据库进行审计:Statement(语句)、Privilege(权限)、object(
对象)。
a、Statement:按语句来审计,比如audit table 会审计数据库中所有的create table,drop
table,truncate table语句,audit session by hjp会审计hjp用户所有的数据库连接。
b、Privilege:按权限来审计,当用户使用了该权限则被审计,如执行grant select any table to a
,当执行了audit select any table语句后,当用户scott 访问了用户hjp的表时(如select * from hjp.t)会
用到select any table权限,故会被审计。注意用户是自己表的所有者,所以用户访问自己的表不会被审计。
c、Object:按对象审计,只审计on关键字指定对象的相关操作,如duit alter, delete, drop,
insert on hjp.t by scott; 这里会对hjp用户的t表进行审计,但同时使用了by子句,所以只会对scott用户发
起的操作进行审计。注意Oracle没有提供对schema中所有对象的审计功能,只能一个一个对象审计,对于后面
创建的对象,Oracle则提供on default子句来实现自动审计,比如执行audit drop on default by access;后
, 对于随后创建的对象的drop操作都会审计。但这个default会对之后创建的所有数据库对象有效,似乎没办
法指定只对某个用户创建的对象有效,相比trigger可以对schema的DDL进行“审计”,这个功能稍显不足。
审核连接
SQL> audit session;
SQL> select * from dba_audit_trail;
审计的一些其他选项
by access 每一个被审计的操作都会生成一条audit trail。
by session 一个会话里面同类型的操作只会生成一条audit trail,默认为by session。
whenever successful 操作成功才审计(dba_audit_trail中returncode字段为0)。
whenever not successful 反之。
audit select on scott.dept by access;对成功和不成功的访问都进行审核
audit delete on scott.dept by session;对成功和不成功的会话都进行审核
audit alter on scott.dept by access whenever successful;对成功访问进行审核
audit insert on scott.dept by session whenever not successful;对不成功的会话进行审核
和审计相关的视图
dba_audit_trail:保存所有的audit trail,实际上它只是一个基于aud$的视图。其它的视图
dba_audit_session, dba_audit_object, dba_audit_statement都只是dba_audit_trail的一个子集。
dba_stmt_audit_opts:可以用来查看statement审计级别的audit options,即数据库设置过哪些
statement级别的审计。dba_obj_audit_opts, dba_priv_audit_opts视图功能与之类似。
all_def_audit_opts:用来查看数据库用on default子句设置了哪些默认对象审计。
按用户查询审核
select * from dba_priv_audit_opts;
select * from dba_stmt_audit_opts;
按对象查询审核
select * from dba_obj_audit_opts;
select * from dba_common_audit_trail;
取消审计
将对应审计语句的audit改为noaudit即可,如audit session whenever successful对应的取消审计语
句为noaudit session whenever successful;
利用客户身份识别扩展审核数据
create or replace trigger set_default_client_info after logon on database
declare
l_module v$session.module%type;
begin
select upper(module) into l_module from v$process a,v$session b
where a.addr=b.paddr
and b.audsid=sys_context('userenv','sessionid');
dbms_session.set_identifier(sys_context('userenv','ip_address')||':'||l_module);
end;
/
select sys_context('userenv','client_identifier') from dual;
4、细粒度审核(FGA)
能进行布尔类型的条件检查
拥有SQL捕获的能力
拥有列敏感特性
拥有时间处理的能力
a、建立精细审计策略
conn sys/password as sysdba;
exec dbms_fga.add_policy
(object_schema => 'scott',
object_name => 'emp',
policy_name => 'emp_policy',
audit_condition => 'deptno=20', --可用函数代替
--audit_condition => 'function_name = 1',
statement_types=>'select,update,delete,insert'
);
begin
dbms_fga.add_policy
(object_schema => 'scott',
object_name => 'emp',
policy_name => 'emp_policy',
audit_condition => 'ename!=user',
audit_column => 'sal' , --敏感列
statement_types=>'select,update,delete,insert'
);
end;
/
b、执行相关SQL操作
conn scott/password;
update emp set sal=sal+100 where deptno=20;
select ename,sal from emp where deptno=20;
c、查询精细审计结果
conn sys/password as sysdba;
select * from fga_log$;
select db_user,sql_text from dba_fga_audit_trail where object_name='EMP';
d、禁止精细审计
conn sys/password as sysdba;
exec dbms_fga.disable_policy('scott','emp','emp_policy')
e、激活精细审计
conn sys/password as sysdba;
exec dbms_fga.enable_policy('scott','emp','emp_policy')
f、删除精细审计
conn sys/password as sysdba;
exec dbms_fga.drop_policy('scott','emp','emp_policy')
g、send email example
create replace procedure fga_notify
(object_schema varchar2,
object_name varchar2,
policy_name varchar2
)
as
l_message varchar2(32767);
l_mailhost varchar2(30):='www.126.com';
l_mail_conn utl_smtp.connection;
l_from varchar2(30):='hjian_ping@126.com';
l_to varchar2(30):='hjian_ping@126.com';
begin
l_message:='user'||user||'successfully accessed'||object_schema||'.'
||object_name||'at'||to_char(sysdate,'Month DD HH24:MI:SS')
||'with this statement:"'
||sys_context('userenv','current_sql')||'"';
l_mail_conn:=utl_smtp.open_connection(l_mailhost,25);
utl_smtp.helo(l_mail_conn,l_mailhost);
utl_smtp.mail(l_mail_conn,l_from);
utl_smtp.rcpt(l_mail_conn,l_to);
utl_smtp.data(l_mail_conn,utl_tcp.crlf||'subject:fga alter'||utl_tcp.crlf
||'to'||l_to||utl_tcp.CRLF||l_message);
utl_smtp.quit(l_mail_conn);
exception
when others then
utl_smtp.quit(l_mail_conn);
raise_application_error(-20000,'failed due to the following error:'||sqlerrm);
end;
/
begin
dbms_fga.add_policy
(object_schema => 'scott',
object_name => 'emp',
policy_name => 'emp_policy'
);
dbms_fga.add_policy
(object_schema => 'scott',
object_name => 'emp',
policy_name => 'emp_policy',
audit_condition => 'ename!=user',
audit_column => 'sal',
handler_schema => 'sec_mgr',
handler_module => 'fga_notify'
);
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-693810/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17012874/viewspace-693810/