男人天堂久久,精品无人乱码高清在线观看,成人区精品一区二区婷婷

如何使用觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)庫(kù)級(jí)守護(hù),防止DDL操作

--對(duì)于重要對(duì)象,實(shí)施DDL拒絕,防止create,drop,truncate,alter等重要操作

Last Updated: Sunday, 2004-10-31 12:06 Eygle

不管是有意還是無(wú)意的,你可能會(huì)遇到數(shù)據(jù)庫(kù)中重要的數(shù)據(jù)表等對(duì)象被drop掉的情況,這可能會(huì)給我們帶來(lái)巨大的損失.

通過(guò)觸發(fā)器，我們可以實(shí)現(xiàn)對(duì)于表等對(duì)象的數(shù)據(jù)庫(kù)級(jí)守護(hù)，禁止用戶(hù)drop操作.

以下是一個(gè)簡(jiǎn)單的范例，供參考:

                              REM this script can be used to monitor a object
REM deny any drop operation on it.
CREATE OR REPLACE TRIGGER trg_dropdeny
   BEFORE DROP ON DATABASE
BEGIN
   IF LOWER (ora_dict_obj_name ()) = 'test'
   THEN
      raise_application_error (num      => -20000,
                               msg      =>    '你瘋了,想刪除表 '
                                           || ora_dict_obj_name ()
                                           || ' ?!!!!!'
                                           || '你完了，警察已在途中.....'
                              );
   END IF;
END;
/

測(cè)試效果:

                            SQL> connect scott/tiger
Connected.
SQL> create table test as select * from dba_users;

Table created.

SQL> connect / as sysdba
Connected.
SQL> create or replace trigger trg_dropdeny
  2     before drop on database   
  3  begin
  4        if lower(ora_dict_obj_name()) = 'test'        
  5        then
  6        raise_application_error(
  7           num => -20000,
  8           msg => '你瘋了,想刪除表 ' || ora_dict_obj_name() || ' ?!!!!!' ||'你完了，警察已在途中.....');
  9        end if;
 10     end;
 11  /

Trigger created.

SQL> connect scott/tiger
Connected.
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: 你瘋了,想刪除表 TEST ?!!!!!你完了，警察已在途中.....
ORA-06512: at line 4

Oracle從Oracle8i開(kāi)始,允許實(shí)施DDL事件trigger，可是實(shí)現(xiàn)對(duì)于DDL的監(jiān)視及控制,以下是一個(gè)進(jìn)一步的例子:

                            create or replace trigger ddl_deny
before create or alter or drop or truncate on database
declare
  l_errmsg varchar2(100):= 'You have no permission to this operation';
begin
  if ora_sysevent = 'CREATE' then
     raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  elsif ora_sysevent = 'ALTER' then
    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  elsif ora_sysevent = 'DROP' then
    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  elsif ora_sysevent = 'TRUNCATE' then
    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  end if;

exception
  when no_data_found then
    null;
end;
/

我們看一下效果:

                            
                            
                            
                              [oracle@jumper tools]$ sqlplus "/ as sysdba"
                            
                          
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 31 11:38:25 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> set echo on
SQL> @ddlt
SQL> create or replace trigger ddl_deny
2 before create or alter or drop or truncate on database
3 declare
4 l_errmsg varchar2(100):= 'You have no permission to this operation';
5 begin
6 if ora_sysevent = 'CREATE' then
7 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
8 elsif ora_sysevent = 'ALTER' then
9 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
10 elsif ora_sysevent = 'DROP' then
11 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
12 elsif ora_sysevent = 'TRUNCATE' then
13 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
14 end if;
15
16 exception
17 when no_data_found then
18 null;
19 end;
20 /

Trigger created.

SQL>
SQL>
SQL> connect scott/tiger
Connected.
SQL> create table t as select * from test;
create table t as select * from test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.T You have no permission to this operation
ORA-06512: at line 5

SQL> alter table test add (id number);
alter table test add (id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 7

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 9

SQL> truncate table test;
truncate table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 11
                          

我們可以看到,ddl語(yǔ)句都被禁止了,如果你不是禁止,可以選擇把執(zhí)行這些操作的用戶(hù)及時(shí)間記錄到另外的臨時(shí)表中.以備查詢(xún).

本文作者:
eygle,Oracle技術(shù)關(guān)注者,來(lái)自中國(guó)最大的Oracle技術(shù)論壇 itpub .
www.eygle.com 是作者的個(gè)人站點(diǎn).你可通過(guò) Guoqiang.Gai@gmail.com 來(lái)聯(lián)系作者.歡迎技術(shù)探討交流以及鏈接交換.

原文出處:

http://www.eygle.com/faq/Use.Trigger.To.implement.ddl.deny.htm

如欲轉(zhuǎn)載,請(qǐng)注明作者與出處.并請(qǐng)保留本文的連接.

回首頁(yè)

如何使用觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)庫(kù)級(jí)守護(hù),防止DDL操作

更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號(hào)聯(lián)系： 360901061

您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對(duì)您有幫助就好】元

2元

5元

10元

20元

自定義

欧美三区_成人在线免费观看视频_欧美极品少妇xxxxⅹ免费视频_a级毛片免费播放_鲁一鲁中文字幕久久_亚洲一级特黄