To restrict Schema owner/user to perform any kind of DDL, the following TRIGGER can be useful:
CREATE OR REPLACE TRIGGER trigger_name
BEFORE ALTER OR CREATE OR DROP ON SCHEMA
RAISE_APPLICATION_ERROR(-20001,' give your custom message ');
END;
/
ALTER TRIGGER trigger_name { ENABLE | DISABLE };
but the problem of this script is the schema owner must have DBA role to enable/disable the trigger .
IF the schema owner does not have DBA role Please use the following script...
CREATE TABLE trigger_lock ( status NUMBER(1) );
INSERT INTO trigger_lock VALUES(1);
CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE ALTER OR CREATE OR DROP
ON SCHEMA
DECLARE
x NUMBER(1);
BEGIN
x:=0;
SELECT MAX(status) INTO x from trigger_lock;
IF (x=1) THEN
raise_application_error(-20001,' You are not authorize to perform DDL. Please contact DBA team.');
END IF;
END;
/
now you can enable/disable the trigger by insertion of updating trigger_lock table (1 for disable, 0 for enable)
No comments:
Post a Comment