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;
请大佬指教。
我用你这个触发器测了下，删除’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;
评论 
有用 1
打赏 0 
您好，直接写个DDL触发器，如下
CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20005,‘scott用户禁止所有的DDL操作！’);
评论
有用
0
打赏
0
您好，请将约束名称全部放到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;
评论
有用
1
打赏
1
展开全部评论(1条)
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_%’
raise_application_error(-20000,‘forbid to drop constraint start with SYS_ !’);
end if;
–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
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’
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;
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
感谢大佬，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.如果有可以实现的方案，可以不局限于触发器。