oracle触发器审计视频,【学习笔记】Oracle审计使用trigger触发器监控所有数据库DDL操作的方法...

最新推荐文章于 2021-04-11 19:27:06 发布

霜之暗伤

最新推荐文章于 2021-04-11 19:27:06 发布

阅读量251

点赞数

文章标签： oracle触发器审计视频

天萃荷净

实现trigger集中记录所有库ddl操作，运维DBA反映用户需求，监控所有Oracle数据库的DDL语句并存入数据表中。通过trigger触发器实现对所有数据库的DDL语句记录分析

用户需求:在一个库上的一张表记录所有库的ddl操作,实现方式：在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决.在我们的一起努力下,解决了该触发器在dblink同义词的库上出错的问题.我这里测试使用的是10g的库做为存储所有库的ddl记录的库,11g库做为一个通过dblink插入ddl操作记录的库.

1.在10g数据库库中操作

1.创建记录ddl操作表

SQL> conn chf/oracleplus

Connected.

SQL> create table t_ddl_audit(

2 db_name varchar2(30),

3 login_user varchar2(30),

4 ddl_time date,

5 ip_address varchar2(20),

6 audsid varchar2(20),

7 schema_user varchar2(30),

8 schema_object varchar2(40),

9 login_tool varchar2(40),

10 os_user varchar2(40),

11 ddl_sql varchar2(4000));

Table created.

2.创建触发器

SQL> create or replace trigger tri_ddl_audit

2 before ddl on database

3 declare

4 n number;

5 str_stmt varchar2(4000);

6 sql_text ora_name_list_t;

7 l_trace number;

8 v_module varchar2(50);

9 v_action varchar2(50);

10 str_session v$session%rowtype;

11 begin

12 n := ora_sql_txt(sql_text);

13 for i in 1 .. n loop

14 str_stmt := substr(str_stmt || sql_text(i), 1, 3000);

15 end loop;

16 dbms_application_info.READ_MODULE(v_module, v_action);

17 INSERT INTO chf.t_ddl_audit

18 (db_name,

19 login_user,

20 ddl_time,

21 ip_address,

22 audsid,

23 schema_user,

24 schema_object,

25 login_tool,

26 os_user,

27 ddl_sql)

28 VALUES

29 (sys_context('USERENV', 'db_name'),

30 ora_login_user,

31 SYSDATE,

32 sys_context('USERENV', 'IP_ADDRESS'),

33 userenv('SESSIONID'),

34 ora_dict_obj_owner,

35 ora_dict_obj_name,

36 v_module,

37 sys_context('userenv', 'os_user'),

38 str_stmt);

39 exception

40 when no_data_found then

41 null;

42 end;

43 /

Trigger created.

3.测试触发器

SQL> conn chf/oracleplus

Connected.

SQL> create table t_xff as select * from dba_tables where rownum=1;

Table created.

SQL> select db_name,login_user,ddl_sql from t_ddl_audit;

DB_NAME LOGIN_USER

------------------------------ ------------------------------

DDL_SQL

-----------------------------------------------------------------

XFF CHF

create table t_xff as select * from dba_tables where rownum=1

2.在11g数据库中操作

1.创建dblink和同义词

SQL> create database link "ora10g_dblink"

2 connect to chf

3 identified by "oracleplus"

4 using 'ora10g';

Database link created.

SQL> create synonym t_ddl_audit for t_ddl_audit@ora10g_dblink;

Synonym created.

2.第一次创建触发器

SQL> create or replace trigger tri_ddl_audit

2 before ddl on database

3 declare

4 n number;

5 str_stmt varchar2(4000);

6 sql_text ora_name_list_t;

7 l_trace number;

8 v_module varchar2(50);

9 v_action varchar2(50);

10 str_session v$session%rowtype;

11 begin

12 n := ora_sql_txt(sql_text);

13 for i in 1 .. n loop

14 str_stmt := substr(str_stmt || sql_text(i), 1, 3000);

15 end loop;

16 dbms_application_info.READ_MODULE(v_module, v_action);

17 INSERT INTO t_ddl_audit

18 (db_name,

19 login_user,

20 ddl_time,

21 ip_address,

22 audsid,

23 schema_user,

24 schema_object,

25 login_tool,

26 os_user,

27 ddl_sql)

28 VALUES

29 (sys_context('USERENV', 'db_name'),

30 ora_login_user,

31 SYSDATE,

32 sys_context('USERENV', 'IP_ADDRESS'),

33 userenv('SESSIONID'),

34 ora_dict_obj_owner,

35 ora_dict_obj_name,

36 v_module,

37 sys_context('userenv', 'os_user'),

38 str_stmt);

39 exception

40 when no_data_found then

41 null;

42 end;

43 /

Trigger created.

3.测试触发器

SQL> create table t_xff as select * from dba_objects where rownum<10;

create table t_xff as select * from dba_objects where rownum<10

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02070: database does not support in this context

ORA-06512: at line 15

出现ORA-02070错误,估计是类此sys_context(‘userenv’, ‘os_user’)导致。

4.第二次创建触发器

SQL> create or replace trigger tri_ddl_audit

2 before ddl on database

3 declare

4 n number;

5 str_stmt varchar2(4000);

6 sql_text ora_name_list_t;

7 l_trace number;

8 v_module varchar2(50);

9 v_action varchar2(50);

10 v_db_name varchar2(50);

11 v_ip_addr varchar2(50);

12 v_os varchar2(50);

13 v_session_id varchar2(50);

14 str_session v$session%rowtype;

15 begin

16 n := ora_sql_txt(sql_text);

17 for i in 1 .. n loop

18 str_stmt := substr(str_stmt || sql_text(i), 1, 3000);

19 end loop;

20 dbms_application_info.READ_MODULE(v_module, v_action);

21 v_db_name :=sys_context('USERENV', 'db_name');

22 v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');

23 v_os:=sys_context('userenv', 'os_user');

24 v_session_id:=userenv('SESSIONID');

25 INSERT INTO t_ddl_audit

26 (db_name,

27 login_user,

28 ddl_time,

29 ip_address,

30 audsid,

31 schema_user,

32 schema_object,

33 login_tool,

34 os_user,

35 ddl_sql)

36 VALUES

37 (v_db_name,

38 ora_login_user,

39 SYSDATE,

40 v_ip_addr,

41 v_session_id,

42 ora_dict_obj_owner,

43 ora_dict_obj_name,

44 v_module,

45 v_os,

46 str_stmt);

47 exception

48 when no_data_found then

49 null;

50 end;

51 /

Trigger created.

5.继续测试触发器

SQL> drop table t3;

drop table t3

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02069: global_names parameter must be set to TRUE for this operation

ORA-06512: at line 23

根据ORA-02069,查询资料发现是通过dblink插入数据使用了变量和常量的方式混合使用导致该错误,修改触发器全部使用变量方式

6.第三次创建触发器

SQL> create or replace trigger tri_ddl_audit

2 before ddl on database

3 declare

4 n number;

5 str_stmt varchar2(4000);

6 sql_text ora_name_list_t;

7 l_trace number;

8 v_module varchar2(50);

9 v_action varchar2(50);

10 v_db_name varchar2(50);

11 v_ip_addr varchar2(50);

12 v_os varchar2(50);

13 v_session_id varchar2(50);

14 v_loginuser varchar2(50);

15 v_obj_name varchar2(50);

16 v_owner varchar2(50);

17 str_session v$session%rowtype;

18 begin

19 n := ora_sql_txt(sql_text);

20 for i in 1 .. n loop

21 str_stmt := substr(str_stmt || sql_text(i), 1, 3000);

22 end loop;

23 dbms_application_info.READ_MODULE(v_module, v_action);

24 v_db_name :=sys_context('USERENV', 'db_name');

25 v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');

26 v_os:=sys_context('userenv', 'os_user');

27 v_session_id:=userenv('SESSIONID');

28 v_loginuser:= ora_login_user;

29 v_owner:=ora_dict_obj_owner;

30 v_obj_name:=ora_dict_obj_name;

31 INSERT INTO t_ddl_audit

32 (db_name,

33 login_user,

34 ddl_time,

35 ip_address,

36 audsid,

37 schema_user,

38 schema_object,

39 login_tool,

40 os_user,

41 ddl_sql)

42 VALUES

43 (v_db_name,

44 v_loginuser,

45 SYSDATE,

46 v_ip_addr,

47 v_session_id,

48 v_owner,

49 v_obj_name,

50 v_module,

51 v_os,

52 str_stmt);

53 exception

54 when no_data_found then

55 null;

56 end;

57 /

Trigger created.

7.测试触发器

SQL> create table t_xff11 as select * from dba_tables where rownum<10;

Table created.

SQL> select db_name,login_user,ddl_sql from t_ddl_audit;

DB_NAME LOGIN_USER

------------------------------ ------------------------------

DDL_SQL

-----------------------------------------------------------------

ora11g CHF

create table t_xff11 as select * from dba_tables where rownum<10

XFF CHF

create table t_xff as select * from dba_tables where rownum=1

3.补充说明

这个方案个人感觉是一个实验室中的方案,在实际的生成环境中很难应用上

1.trigger记录ddl操作本身效率不高

2.如果某个库不能访问存储ddl操作的表的数据库,将导致该数据库所有ddl操作hang住,从而可能使得该数据库hang住的风险.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle审计使用trigger触发器监控所有数据库DDL操作的方法

霜之暗伤

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle触发器审计视频,【学习笔记】Oracle审计使用trigger触发器监控所有数据库DDL操作的方法...

天萃荷净实现trigger集中记录所有库ddl操作，运维DBA反映用户需求，监控所有Oracle数据库的DDL语句并存入数据表中。通过trigger触发器实现对所有数据库的DDL语句记录分析用户需求:在一个库上的一张表记录所有库的ddl操作,实现方式：在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决....
复制链接

扫一扫

oracle触发器审计视频,【学习笔记】Oracle审计 使用trigger触发器监控所有数据库DDL操作的方法...

“相关推荐”对你有帮助么？

oracle触发器审计视频,【学习笔记】Oracle审计使用trigger触发器监控所有数据库DDL操作的方法...