oracle数据库想要建一个触发器来实现禁止删除'SYS_'打头的约束。

我来答

积土为山

2022-05-09

50M

目前建的触发器是这样的，但是这个不能实现所需要的功能，删表什么的是会阻止，但是删除触发器不会被阻止。

create or replace trigger undropsysconstraint
before drop on database
begin
if ora_dict_obj_name like 'SYS_%'
then
raise_application_error(-20000,'forbid to drop constraint start with SYS_ !');
end if;
end;

请大佬指教。

我来答

添加附件

问题补充

7条回答

默认

最新

DarkAthena

2022-05-09

我用你这个触发器测了下，删除’SYS_'开头的触发器会被阻止，能否给个表和表上触发器的ddl模拟代码重现一下场景？

如果是删除约束的话，可以用下面这个

create or replace trigger undropsysconstraint
  before alter on database
begin
  DECLARE
    sql_text ora_name_list_t;
    n        PLS_INTEGER;
    v_stmt   VARCHAR2(2000);
  BEGIN
    n := ora_sql_txt(sql_text);
  
    FOR i IN 1 .. n LOOP
      v_stmt := v_stmt || sql_text(i);
    END LOOP;
    dbms_output.put_line(v_stmt);
    if upper(v_stmt) like '%ALTER%DROP%CONSTRAINT%SYS_%' then
      raise_application_error(-20000,
                              'forbid to drop constraint start with SYS_ !');
    end if;
  END;
end;

有用 1

打赏 0

积土为山

题主

2022-05-09

CREATE TABLE HR.TEST_001 ("NOTE" VARCHAR2(512)); ALTER TABLE HR.TEST_001 ADD CONSTRAINT SYS_TEST001 CHECK ('NOTE' IS NOT NULL); 删除语句是 alter table HR.TEST_001 drop constraint SYS_TEST001

积土为山

题主

2022-05-09

题目里面字打错了，是删除约束不会被阻止。不好意思。

积土为山

题主

2022-05-10

感谢大佬，最后的触发器，测试有效。

dbtiger

2022-05-09

您好，直接写个DDL触发器，如下

CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20005,‘scott用户禁止所有的DDL操作！’);
END;

有用 0

打赏 0

积土为山

题主

2022-05-09

不能全禁止，这是给研发的测试环境，他们需要能够正常DDL，这个需求是因为他们有人会删除SYS开头的系统生成约束，导致我ogg同步有问题。目标只需要禁止删除SYS开头的约束。

dbtiger

2022-05-09

您好，请将约束名称全部放到in当中,如下
create or replace trigger drop_trigger
before drop on schema
begin
if ora_dict_obj_name in (‘SYS_TEST’,‘SYS_TEST2’,‘PK_SYS_TEST’) then
raise_application_error(-20000, ‘connotdrop.’);
end if;
end;

有用 1

打赏 1

积土为山

题主

2022-05-09

大佬，我刚刚把测试约束名放在in中，创建trigger，尝试删除还是删除成功的，没有阻止。还有SYS打头的约束有7W+,并且会有新增，这个方案好像不太行。

dbtiger

答主

2022-05-09

if ora_dict_obj_name like 'SYS_%' 也可以。我测试的数据库版本：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

积土为山

题主

2022-05-10

版本一致，Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production，ora_dict_obj_name like 'SYS_%' 实测不行，对table这些对象是能阻止的，但是对约束没有阻止。

展开全部评论(1条)

dbtiger

2022-05-09

是哪个数据库版本？

有用 0

打赏 0

积土为山

题主

2022-05-10

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

杨卓

2022-05-09

SQL> create table a as select * from dba_objects;
alter table a add constraint pri_a primary key (OBJECT_ID);
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME=‘A’;
CONSTRAINT_NAME C

PRI_A P

create table ALERT_RESULT_EVENT_C
(
data_date VARCHAR2(8) not null,
object_id VARCHAR2(600) not null,
event_id VARCHAR2(20) not null,
ratio NUMBER(22,4),
pairing_object_id VARCHAR2(128),
index_value_1 VARCHAR2(128),
index_value_2 VARCHAR2(128),
constraint PK_ALERT_RESULT_EVENT_C primary key (DATA_DATE, OBJECT_ID, EVENT_ID)
);
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME=‘ALERT_RESULT_EVENT_C’;
CONSTRAINT_NAME C

SYS_C0011504 C
SYS_C0011505 C
SYS_C0011506 C
PK_ALERT_RESULT_EVENT_C P
https://www.easck.com/cos/2022/0507/930019.shtml
https://www.easck.com/cos/2022/0507/930019.shtml
----------------https://www.modb.pro/issue/14726
create or replace trigger undropsysconstraint
before drop on database
begin
if ora_dict_obj_name like ‘SYS_%’
then
raise_application_error(-20000,‘forbid to drop constraint start with SYS_ !’);
end if;
end;
/

–sys exec
ALTER TABLE yz.ALERT_RESULT_EVENT_C DROP CONSTRAINT SYS_C0011506;
Table altered.
？？？不好使

create table yz.DDL_TYPE_TEST (cc timestamp,c2 varchar2(4000),c3 varchar2(4000),c4 varchar2(4000),c5 varchar2(4000) );
CREATE OR REPLACE TRIGGER insert_ddl_type
AFTER DDL ON database
BEGIN
INSERT INTO YZ.DDL_TYPE_TEST VALUES
(systimestamp,ora_sysevent, ora_login_user,
ora_dict_obj_type, ora_dict_obj_name);
END insert_ddl_type;
/
ALTER TABLE yz.ALERT_RESULT_EVENT_C DROP CONSTRAINT SYS_C0011505;

select * from yz.DDL_TYPE_TEST;
CC C2 C3 C4 C5

09-MAY-22 12.17.52.341044 PM ALTER SYS TABLE ALERT_RESULT_EVENT_C

CREATE OR REPLACE TRIGGER
AFTER DDL ON database
BEGIN
INSERT INTO YZ.DDL_TYPE_TEST VALUES
(systimestamp,ora_sysevent, ora_login_user,
ora_dict_obj_type, ora_dict_obj_name);
END insert_ddl_type;
/

有用 0

打赏 0

杨卓

2022-05-09

1.你这个能否通过别的手段去控制让开发不要删除SYS的约束？通过测试发现SYS打头的约束一般都是not null约束,开发为什么会删除这个约束，是否为了写入测试数据？那么OGG为什么数据报错，能否把报错的数据跳过或者强行写入；

2.if ora_dict_obj_name like ‘SYS_%’ 写法不可以，尝试ddl insert测试表，可以发现ddl drop 约束的操作，ora_dict_obj_name 记录的是表名称，而不是约束SYS…, 记录的操作类型是ora_dict_obj_type TABLE,ora_sysevent ALTER 从这里看基本上最多多加几个if 满足ALTER ,TABLE的操作禁用！！！其实和禁用DDL已经差不多了！！！

https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS2014
systimestamp
ora_sysevent
ora_login_user
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner
ora_dict_obj_name_list
drop trigger INSERT_DDL_TYPE;

drop table yz.DDL_TYPE_TEST;
create table yz.DDL_TYPE_TEST
(exec_time timestamp,
v_ora_sysevent varchar2(4000),
v_ora_login_user varchar2(4000),
v_ora_dict_obj_type varchar2(4000),
v_ora_dict_obj_name varchar2(4000),
v_ora_dict_obj_owner varchar2(4000)
);
CREATE OR REPLACE TRIGGER INSERT_DDL_TYPE
AFTER DDL ON database
BEGIN
INSERT INTO YZ.DDL_TYPE_TEST VALUES
(systimestamp,
ora_sysevent,
ora_login_user,
ora_dict_obj_type,
ora_dict_obj_name,
ora_dict_obj_owner);
END insert_ddl_type;
/

ALTER TABLE yz.ALERT_RESULT_EVENT_C DROP CONSTRAINT SYS_C0011504;
select * from yz.DDL_TYPE_TEST;

EXEC_TIME 09-MAY-22 12.45.56.946006 PM
V_ORA_SYSEVENT ALTER
V_ORA_LOGIN_USER SYS
V_ORA_DICT_OBJ_TYPE TABLE
V_ORA_DICT_OBJ_NAME ALERT_RESULT_EVENT_C
V_ORA_DICT_OBJ_OWNER YZ

CREATE OR REPLACE TRIGGER err_alter_table
AFTER DDL ON database
BEGIN
if ora_sysevent=‘ALTER’ and
ora_dict_obj_type=‘TABLE’ and
ora_dict_obj_owner=‘YZ’
then
raise_application_error(-20000,'forbid to alter all schema yz sql ,please call DBA to disable TRIGGER err_alter_table ');
end if;
END err_alter_table;
/

–sys

create table ALERT_RESULT_EVENT_C
(
data_date VARCHAR2(8) not null,
object_id VARCHAR2(600) not null,
event_id VARCHAR2(20) not null,
ratio NUMBER(22,4),
pairing_object_id VARCHAR2(128),
index_value_1 VARCHAR2(128),
index_value_2 VARCHAR2(128),
constraint PK_ALERT_RESULT_EVENT_C primary key (DATA_DATE, OBJECT_ID, EVENT_ID)
);
select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME=‘ALERT_RESULT_EVENT_C’;
CONSTRAINT_NAME C

SYS_C0011508 C
SYS_C0011509 C
SYS_C0011510 C
PK_ALERT_RESULT_EVENT_C P
ALTER TABLE SYS.ALERT_RESULT_EVENT_C DROP CONSTRAINT SYS_C0011510;

create table yz.cc
(
data_date VARCHAR2(8) not null,
object_id VARCHAR2(600) not null,
event_id VARCHAR2(20) not null,
ratio NUMBER(22,4),
pairing_object_id VARCHAR2(128),
index_value_1 VARCHAR2(128),
index_value_2 VARCHAR2(128),
constraint SFDSFD primary key (DATA_DATE, OBJECT_ID, EVENT_ID)
);
select CONSTRAINT_NAME,CONSTRAINT_TYPE from DBA_constraints where TABLE_NAME=‘CC’ AND OWNER=‘YZ’;
CONSTRAINT_NAME C

SFDSFD P
SYS_C0011517 C
SYS_C0011516 C
SYS_C0011515 C

ALTER TABLE yz.cc DROP CONSTRAINT SYS_C0011516;
ALTER TABLE yz.cc DROP CONSTRAINT SYS_C0011516
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: forbid to alter all schema yz sql ,please call DBA to disable TRIGGER err_alter_table
ORA-06512: at line 6

ALTER TABLE yz.cc add test varchar2(20);
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: forbid to alter all schema yz sql ,please call DBA to disable TRIGGER err_alter_table
ORA-06512: at line 6

SQL>ALTER TABLE sys.ALERT_RESULT_EVENT_C add test varchar2(20);
Table altered.

有用 0

打赏 0

积土为山

题主

2022-05-10

感谢大佬，1.环境是给开发的测试环境，他们在做测试数据的时候，会做一些约束的调整测试，我推荐他们用的语法是：ALTER TABLE XXX.TABLE_NAME MODIFY COLUMN_NAME NULL;但是因为有几个地区的研发还有人员变动，一次交流后，还是会有人直接drop；导致我OGG报错是因为，我们有多个测试环境，DDL是同步的，但是SYS开头的约束名是系统生成的，每个环境是不一致的，在这个环境叫SYS001，可能在另外一个环境可能就叫SYS002，通过alter table XXX drop constraint SYS001 删除的话，其他环境同步不了，而且不能跳过，因为测试环境DDL需要保持一致，目前出现这种问题都是我手动去rename 对应的constraint 来给他同步 2.意思看明白了，但是这样操作就是你说的接近禁止DDL了，on database的话，只要是alter table的都不行了，因为是测试环境，开发需要能正常DDL，感谢。想要只禁止删除SYS_开头的约束。3.如果有可以实现的方案，可以不局限于触发器。

杨卓

2022-05-10

你设计的这种同步就不合理，正常情况下很少说有OGG同步约束类的语句，OGG只同步对表数据的调整，本身就不是很完美的支持DDL。就算是支持DDL也是允许有不一致的，你可以考虑配置OGG参数跳过DDL报错就完事了，这就是大部分人的做法！如果一定要纠结同步，那你把目标库数据铲了重新同步，完全一致，约束为啥不能一致，约束也整个一样的名称。
https://blog.csdn.net/lmocm/article/details/42971747
DDLERROR DEFAULT IGNORE RETRYOP
https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters036.htm#GWURF448

另外还有个操作流程规范的问题，作为OGG的源端，目标端怎么能让开发随便做DDL，你又要保障数据一致性，还不能严格审核这种操作这不是给自己挖坑！控制不严格数据就不用保证一致，使用参数跳过就行。要保证一致性，就整体流程控制好，不给权限或者有审核机制！确认不影响OGG才能让开发执行！

有用 0

打赏 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏

ALTER TABLE yz.ALERT_RESULT_EVENT_C DROP CONSTRAINT SYS_C0011504; select * from yz.DDL_TYPE_TEST;

ALTER TABLE yz.ALERT_RESULT_EVENT_C DROP CONSTRAINT SYS_C0011504;
select * from yz.DDL_TYPE_TEST;