Category审计

oracle精细策略dmbs_rls

–要求:YY只可以访问 scott.test ID=1 的信息 user zz 只可访问 scott.test id=2,3 的信息,
–当USER 为ZZ时候可以更新ID=3 的 信息 其他USER 不能更新任何, (sysdba可以 )

create user yy identified by oracle
default tablespace users ;

create user zz identified by oracle
default tablespace users ;

grant create any context to scott ;
grant connect,resource to yy,zz ;
grant select any table ,update any table to yy,zz ;

create table test(ID number(2) , NAME varchar2(10) ) ;
insert into test values (1,’xh’) ;
insert into test values (2,’hr’) ;
insert into test values (3,’cc’) ;
insert into test values (4,’dd’) ;
select * from test ;

–自定义环境
create or replace context actest using scott.test_pkg;

SQL> create or replace package scott.test_pkg as
procedure set_test;
end;
/

–用包scott.test_pkg 设置环境
create or replace package body scott.test_pkg
as
procedure set_test is
begin
if sys_context(‘userenv’,’session_user’)=’YY’ then
dbms_session.set_context(‘actest’,’yy_attr’,1);
elsif sys_context(‘userenv’,’session_user’)=’ZZ’ then
dbms_session.set_context(‘actest’,’zz_attr1′,2);
dbms_session.set_context(‘actest’,’zz_attr2′,3);
end if;
end;
end;
/

–创建一个安全策略函数
create or replace package scott.test_p as
function p_select(object_schema varchar2,object_name varchar2) return varchar2;
function p_update(object_schema varchar2,object_name varchar2) return varchar2;
end;
/

create or replace package body scott.test_p as
function p_select(object_schema varchar2,object_name varchar2) return varchar2
is
rtn_predicate varchar2(500);
begin
rtn_predicate :=’1=1′;
if user=’YY’ then
rtn_predicate := ‘id =sys_context(”actest”,”yy_attr”)’;
elsif user=’ZZ’ then
rtn_predicate := ‘id=sys_context(”actest”,”zz_attr1”)’||’ or ‘||’id=sys_context(”actest”,”zz_attr2”)’;
end if;
return rtn_predicate;
end;

function p_update(object_schema varchar2,object_name varchar2) return varchar2 is
rtn_predicate varchar2(500);
begin
rtn_predicate:=’1=2′;
if user=’ZZ’ then
rtn_predicate :=’id=sys_context(”actest”,”zz_attr2”)’;
end if;
return rtn_predicate;
end;

end;
/

desc dbms_rls.add_policy ;

SQL> exec dbms_rls.add_policy(‘scott’,’test’,’sel_policy’,’scott’,’test_p.p_select’,’select’);

SQL> exec dbms_rls.add_policy(‘scott’,’test’,’upd_policy’,’scott’,’test_p.p_update’,’insert,update,delete’);

create or replace trigger logon_t
after logon on database call scott.test_pkg.set_test
/

select * from scott.test ;
select * from test ;

update scott.test set name=’hello’ where id=1
update scott.test set name=’hello’ where id=3
select sys_context(‘actest’,’yy_attr’) from dual ;
select sys_context(‘actest’,’zz_attr1′) from dual ;
select sys_context(‘actest’,’zz_attr2′) from dual ;

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_codition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

 

创建监控表的DML的触发器–Oracle审计

在生产环境中,总是可能出现这样的情况:某张或者某些表的数据被莫名其妙的修改了,但是很难定位出是哪个用户、哪个过程修改的。这是一个很让DBA头痛的事情(往往DBA对于整个代码逻辑并不是非常了解)。要定位出”问题”语句,有几种方法可以选择:log miner;细节粒度审计;触发器。Log miner要求要有归档日志(这个并非所有系统都可以做),而且需要有相当的磁盘空间,好处就是可以离线做;细节粒度升级能够根据条件记录下表的DML操作(9i及之前只能记录SELECT语句),比较复杂的FGA需要较高权限的用户来实现;触发器比较灵活,能够按照比较复杂的条件来记录需要的信息。下面介绍触发器如何实现。

要建立这样的触发器,需要利用到几张系统视图:v$session, v$sql, v$cursor,(10g, 9.2.0.1中可以,9.2.0.5, 9.2.0.之前存在bug)
SQL> connect “/ as sysdba”
grant select on SYS.V_$SQL to demo;
grant select on SYS.V_$SQL_BIND_DATA to demo;
grant select on SYS.V_$SQL_CURSOR to demo;
grant select on SYS.V_$SESSION to demo;
grant create trigger to demo;
CREATE TABLE trig_sql(lt DATE, sid NUMBER, SERIAL# NUMBER,
USERNAME VARCHAR2(30), OSUSER VARCHAR2(64),
MACHINE VARCHAR2(32), TERMINAL VARCHAR2(16),
PROGRAM VARCHAR2(64), sqlText VARCHAR2(2000),
status VARCHAR2(30));
方法1:

create or replace trigger ttt_trig

after insert or update on pga_ttt

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

begin

INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘NONE’

from v$sql q, v$session s

where s.audsid=(select userenv(‘SESSIONID’) from dual)

and s.prev_sql_addr=q.address

AND s.PREV_HASH_VALUE = q.hash_value;

 

COMMIT;

end;
——————-2008-04-22—-改进版一————————————–
create table TRIG_SQL
(
LT DATE,
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(30),
OSUSER VARCHAR2(64),
MACHINE VARCHAR2(32),
TERMINAL VARCHAR2(16),
PROGRAM VARCHAR2(64),
SQLTEXT VARCHAR2(2000),
STATUS VARCHAR2(30),
CLIENT_IP VARCHAR2(60)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);

—————————————-触发器
create or replace trigger ttt_trig
after insert or update or delete on scott.schema
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘INSERT’
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF deleting then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘DELETE’
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF updating then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘UPDATE’
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
END IF;
END;

————————-改进版二 加Client IP地址
create or replace trigger ttt_trig
after insert or update or delete on scott.emp
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘INSERT’,
sys_context(‘userenv’,’ip_address’)
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF deleting then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘DELETE’,
sys_context(‘userenv’,’ip_address’)
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF updating then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘UPDATE’,
sys_context(‘userenv’,’ip_address’)
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
END IF;
END;

——————————- 简化版3——————————–
create or replace trigger ttt_trig
after insert or update or delete on scott.emp
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
DML_TYPE VARCHAR2(20);
BEGIN

IF inserting THEN
DML_TYPE:=’INSERT’;
ELSIF deleting THEN
DML_TYPE:=’DELETE’;
ELSIF updating THEN
DML_TYPE:= ‘UPDATE’;
END IF;

INSERT INTO trig_sql
SELECT sysdate,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.sql_text line,
DML_TYPE,
sys_context(‘userenv’,’ip_address’)
FROM v$sql q, v$session s
WHERE s.audsid=(SELECT userenv(‘SESSIONID’) FROM dual)
AND s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
END;

————————————–20080421————————————-
create sequence mtt_num_seq;

create sequence member_row_track_seq;

create or replace package dml_count is
v_mtt_count Member_Tran_Trace.Mtt_Count%TYPE:=0;
end;

第一个触发器:
create or replace trigger trig_member_row
after insert or delete or update on member
for each row
begin
if inserting then
insert into member_row_track
(member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:new.member_id,:new.name,:new.hire,:new.jikwi,:new.sal,:new.bonus,
:new.mgr,:new.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF deleting then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF updating then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

END IF;
end;

第二个触发器代码:
create or replace trigger trig_member_statement
after insert or delete or update on member
begin
if inserting THEN
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’insert’,dml_count.v_mtt_count);
ELSIF deleting then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’delete’,dml_count.v_mtt_count);
ELSIF updating then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’update’,dml_count.v_mtt_count);
END IF;
end;

3,
wan jia sheng :
member
wan jia sheng :
member_tran_trace
wan jia sheng :
member_row_track
—————————————————————
方法2:

create or replace trigger ttt_trig
after insert or update on pga_ttt
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
for cr in (select s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM,
q.sql_text line, c.status stat
from v$sql q, v$sql_cursor c, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND c.STATUS = ‘CURFETCH’)
loop
INSERT INTO trig_sql VALUES(SYSDATE, cr.sid, cr.SERIAL#,
cr.USERNAME, cr.OSUSER, cr.MACHINE,
cr.TERMINAL, cr.PROGRAM, cr.line,
cr.stat);
end loop;

COMMIT;
end;
第一种方法是通过前一SQL的地址(pre_sql_addr)和HASH(prev_hash_value)值来定位出发trigger的语句的,不能用sql_address和hash_value来定位,否则获取到是触发器里面向日志表插入记录数据的语句本身了。

第二个方法是通过通过地址加游标的方法,按照视图各个字段的解释,应该是可以通过v$sql_cursor.parent_handle来定位的。但是通过测试发现,只有当前一条语句和查找前一条语句的语句在一个PLSQL块中的时候才有效,

SQL> set serveroutput on
SQL> declare
2 v_date date;
3 v_sql varchar2(2000);
4 begin
5 select sysdate into v_date from dual;
6
7 select q.sql_text into v_sql
8 from v$sql q, v$sql_cursor c, v$session s
9 where s.audsid=(select userenv(‘SESSIONID’) from dual)
10 and s.prev_sql_addr=q.address and q.address=c.parent_handle;
11
12 dbms_output.put_line(v_sql);
13 end;
14 /
SELECT SYSDATE FROM DUAL

PL/SQL procedure successfully completed.
因为触发器本身是一个PLSQL块,所以总是无法获得正确语句,最后只有通过cursor的状态来获取。下面简单了解一下CURSOR各个状态的含义:

· CURNULL:游标已经存在,但没有任何SQL语句在使用它(即cache在每个session内存中的游标)

· CURSYNTAX:解析SQL语句过程的一个游标状态,说明调用游标的SQL语句语法正确,但是没有解析完成。

· CURPARSE:调用游标的语句解析完毕

· CURBOUND:游标使用了帮定变量,并定义好了帮定变量

· CURFETCH:游标执行完毕,并fetch了数据

· CURROW:游标正指向某一行

· ERROR:游标错误,一般是有BUG了。

当一条INSERT或者UPDATE语句执行以后才会触发触发器,所以这时候的游标状态是CURFETCH,我们这就通过状态为CURFETCH来定位。

比较郁闷的是,我的系统是9.2.0.5的,因为存在BUG,这样的触发器做不了,FGA又无法监控INSERT和UPDATE语句。还没找到好的办法

——————————————
create sequence mtt_num_seq;

create sequence member_row_track_seq;

create or replace package dml_count is
v_mtt_count Member_Tran_Trace.Mtt_Count%TYPE:=0;
end;

第一个触发器:
create or replace trigger trig_member_row
after insert or delete or update on member
for each row
begin
if inserting then
insert into member_row_track
(member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:new.member_id,:new.name,:new.hire,:new.jikwi,:new.sal,:new.bonus,
:new.mgr,:new.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF deleting then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF updating then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

END IF;
end;

第二个触发器代码:
create or replace trigger trig_member_statement
after insert or delete or update on member
begin
if inserting THEN
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’insert’,dml_count.v_mtt_count);
ELSIF deleting then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’delete’,dml_count.v_mtt_count);
ELSIF updating then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’update’,dml_count.v_mtt_count);
END IF;
end;

3,
wan jia sheng :
member
wan jia sheng :
member_tran_trace
wan jia sheng :
member_row_track

————————————————–

用来记录审计所有的DDL操作
1:事先在SYS下建了一个表ddl$trace。
2:创建tr_trace_ddl,创建时可以通过,创建后显示状态不正常,
CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER ddl
ON database
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;

INSERT INTO ddl$trace(login_user,audsid,ipaddress,
schema_user,schema_object,ddl_time,ddl_sql)
VALUES(ora_login_user,’0′, ora_client_ip_address,
ora_dict_obj_owner,ora_dict_obj_name,SYSDATE,state_sql);
EXCEPTION
WHEN OTHERS THEN
sp_write_log( ‘捕获DDL语句异常错误: ‘||SQLERRM);
END tr_trace_ddl;
3:在TOAD里面重新编译时,提示出错,
RIGGER SYS.TR_TRACE_DDL
On line: 2
PLS-00103: 出现符号 ” “在需要下列之一时:
begin function package pragma
procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor

那位知道为是什么
4,
以下两个过程呢,其实是辅助过程,一个是完成写日志,一个是完成发邮件,在以后的程序中,可能会经常用到。

1、写日志过程

name:sp_Write_log
parameter:textContext in varchar2 日志内容
create date:2003-06-01
creater:chen jiping
desc: &#8226;写日志,把内容记到服务器指定目录下
&#8226;必须配置Utl_file_dir初始化参数,
并保证日志路径与Utl_file_dir路径一致或者是其中一个

create or replace PROCEDURE sp_Write_log(textContext VARCHAR2)
IS
file_handle   UTL_FILE.file_type;
Write_content  VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
–打开文件
–Write_file_name := rtrim(to_char(SYSDATE,’YYYY-MM-DD’))||’.log’;
Write_file_name := ‘db108_alert.log’;
file_handle   := UTL_FILE.FOPEN(‘/u01/product/admin/ora81/logs’,Write_file_name,’a’);
Write_content  := to_char(SYSDATE,’yyyy-mm-dd hh24:mi:ss’)||’||’||textContext;
–写文件
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.PUT_LINE(file_handle,Write_content);
END IF;
–关闭文件
UTL_FILE.Fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.Fclose(file_handle);
END IF;
END sp_Write_log;

2、发送Email的过程

name:sp_Send_mail
parameter: Rcpter in varchar2 接收者邮箱
Mail_Content in Varchar2 邮件内容
create date:2003-06-01
creater:chen jiping
desc: &#8226;发送邮件到指定邮箱
&#8226;只能指定一个邮箱,如果需要发送到多个邮箱,需要另外的辅助程序

create or replace procedure sp_send_mail(
Rcpter IN VARCHAR2,
Mail_Content IN VARCHAR2)
IS
conn utl_smtp.connection;
PROCEDURE send_header(NAME IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME || ‘: ‘ || header || utl_tcp.CRLF);
END;

BEGIN
conn := utl_smtp.open_connection(‘smtp.ur.net.cn’);
utl_smtp.helo(conn, ‘oracle’);
utl_smtp.mail(conn, ‘oracle info’);
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
send_header(‘From’,  ’Oracle Database’);
send_header(‘To’,   '”Recipient” <‘||Rcpter||’>’);
send_header(‘Subject’, ‘Hello’);
utl_smtp.write_data(conn, utl_tcp.CRLF || Mail_Content);
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; — When the SMTP server is down or unavailable, we don’t have
— a connection to the server. The quit call will raise an
— exception that we can ignore.
END;
raise_application_error(-20000,
‘Failed to send mail due to the following error: ‘ || SQLERRM);
END sp_send_mail;

3、监控数据库关闭/启动的触发器

create or replace trigger TR_DB_SHUTDOWN
before shutdown ON DATABASE
DECLARE
msMsg VARCHAR2(500);
BEGIN
msMsg :=’user ‘||ora_login_user||’ in ‘
||ora_client_ip_address||’ ready shutdown database ‘
||ora_database_name|| ‘ now’;
sp_send_mail(‘urmail@mail.com’,msMsg);
EXCEPTION
WHEN OTHERS THEN
sp_send_mail(‘urmail@mail.com’,ora_database_name||’ shutdown error’);
END;
说明:当数据库关闭之前,发送Mail到指定邮箱.不要在关闭/启动数据库的触发器中调用utl_file包写文件,可能会导致BUG,引起数据库不能启动,如果实在有必要,则关闭数据库与启动数据库不要使用同一个会话。

4、监控登录用户的触发器

先需要建立一张表,用于存放登陆信息。
create table LOG$INFORMATION
(
ID    NUMBER(10),
USERNAME VARCHAR2(30),
LOGINTIME DATE,
TERMINAL VARCHAR2(50),
IPADRESS VARCHAR2(20),
OSUSER  VARCHAR2(30),
MACHINE  VARCHAR2(64),
PROGRAM  VARCHAR2(64),
SID    NUMBER,
SERIAL#  NUMBER,
AUSID   NUMBER
)

然后需要创建一个序列,才产生连续的序列号,根据序列的信息,可以更好的得到登录的信息。

create sequence SQ_LOGIN
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;

最后创建触发器,记载登录信息。
CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD
AFTER logon ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
SELECT * FROM v$session
WHERE audsid = iiQuerySid;
BEGIN
OPEN cSession(userenv(‘SESSIONID’));
FETCH cSession INTO mtSession;
IF cSession%FOUND THEN
INSERT INTO log$information(id,username,logintime,terminal,ipadress,osuser,machine,
program,sid,serial#,ausid)
VALUES(sq_login.nextval,USER,SYSDATE,mtSession.Terminal,
SYS_CONTEXT (‘USERENV’,’IP_ADDRESS’),mtSession.Osuser,
mtSession.Machine,mtSession.Program,mtSession.
Sid,mtSession.Serial#,userenv(‘SESSIONID’));
ELSE
sp_write_log(‘session信息错误:’||SQLERRM);
raise_application_error(-20099,’登录异常错误’,FALSE);
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
sp_write_log(‘登记登录信息错误:’||SQLERRM);
RAISE;
END;

说明:这个触发器监控所有登录用户,并把其信息存入到以上表中。根据表中记载的信息,可以获得所有登录信息,用于审计用户的登陆是否许可。

 

利用Oracle FGA实现审计

在《初识企业内容管理平台-Documentum》中提到,由于Documentum的极其保守,所以无法对其系统,尤其是数据库设计部分进行深入了解(不提供对数据库表的说明书)。为了对数据库部分进行了解,需要进行一下反向的工作:通过在Webtop/Da中进行操作,观察数据库表的变化。这就需要实现对Oracle数据库表操作的审计。当然,这样的反向工作,对了解其它未知的应用系统也同样有作用。

Oracle9i Database 推出了一种称为细粒度审计 (FGA) 的新特性。现在下面就利用FGA实现审计对表的审计。其中,EDMS是数据库的一个测试帐户。

1.建立测试表(用户EDMS下)
Create Table T_AUDIT_DEMO
(
CID INT NOT NULL, CNAME VARCHAR2(20) NULL, ENAME VARCHAR2(20) NULL,
CONSTRAINT PK_T_AUDIT_DEMO PRIMARY KEY (CID)
);

2.创建审计策略(用户SYS下)
begin
dbms_fga.add_policy
(
object_schema=>’EDMS’, object_name=>’T_AUDIT_DEMO’,
policy_name=>’T_AUDIT_DEMO_AUDIT’
);
end;

3.测试审计效果(用户EDMS下)
INSERT INTO T_AUDIT_DEMO VALUES(1,’曾勋’,’ZENG XUN’);
INSERT INTO T_AUDIT_DEMO VALUES(2,’翁黎明’,’WENG LI MING’);
INSERT INTO T_AUDIT_DEMO VALUES(3,’刘帝勇’,’LIU DI YONG’);

4.使用Select查询测试表(用户EDMS下)
SQL> SELECT * FROM T_AUDIT_DEMO;
CID CNAME ENAME
—– ——————– ——-
1曾勋ZENG XUN
2翁黎明WENG LI MING
3刘帝勇LIU DI YONG

5.再次查看审计效果(用户SYS下)
SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;
STATEMENT_TYPE SQL_TEXT
————– ———–
SELECT SELECT * FROM T_AUDIT_DEMO

注意:之前的Insert语句并不在审计中。默认的只对Select进行审计。在Oracle 9i中它只捕获 SELECT 语句。而在Oracle 10i中进行了扩展,支持对所有类型的DML进行审计。

6.修改审计粒度(用户SYS下)
begin
dbms_fga.add_policy
(
object_schema=>’EDMS’, object_name=>’T_AUDIT_DEMO’, policy_name=>’T_AUDIT_DEMO_AUDIT’, statement_types=>’INSERT, UPDATE, DELETE, SELECT’
);
end;
注意:不能实现对Truncat Table的审计。

7.测试审计(用户EDMS、SYS下)
INSERT INTO T_AUDIT_DEMO VALUES(4,’黄智洪’,’HUANG ZHI HONG’);
DELETE FROM T_AUDIT_DEMO WHERE CID < 4;
SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;
STATEMENT_TYPE SQL_TEXT
————– ——————–
SELECT SELECT * FROM T_AUDIT_DEMO
INSERT INSERT INTO T_AUDIT_DEMO VALUES(4,’黄智洪’,’HUANG ZHI HONG’)
DELETE DELETE FROM T_AUDIT_DEMO WHERE CID < 4
SELECT SELECT * FROM T_AUDIT_DEMO
至此,我们已经实现了对表T_AUDIT_DEMO的审计。与FGA相关的表或者视图:

select * from fga$
select * from fga_log$
select * from fgacol$
select * from dba_fga_audit_trail
select * from dba_common_audit_trail
select * from dba_audit_policies
select * from dba_fga_audit_trail
与FGA相关的包或者过程:
dbms_fga.add_policy dbms_fga.drop_policy
至于这些表、视图、包的列或者参数的使用方法,可以Describe或者查看相关文档。

© 2019 Init dba

Theme by Anders NorenUp ↑