Oracle Trigger to Block DDL by Schema Owner
In this series I want to talk about how to deal with password sharing. Users are connecting to the database using schema owner login and making database changes. Schema Owner by default has full permission on its own schema to create any objects. This trigger will disable DDL, write a custom message to the Alert.log, then display a warning message to the user.
I hope you find this information useful.
If you like the content hit the like button and subscribe.
create or replace TRIGGER SYS.TRIG_BLOCK_DDL
BEFORE DDL ON DATABASE
DECLARE
l_sysevent varchar2(25);
BEGIN
SELECT ora_sysevent into l_sysevent from dual;
IF(l_sysevent in ('DROP','CREATE') )
THEN
IF (SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('PSTRIIM')) THEN
SYS.DBMS_SYSTEM.KSDWRT(2,'ORA-30000 '||NVL(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),SYS_CONTEXT( 'USERENV', 'OS_USER' ))||' '||l_sysevent||' '||ora_dict_obj_type||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' DDL BLOCKED FROM '||SYS_CONTEXT( 'USERENV', 'OS_USER' )||' '||SYS_CONTEXT( 'USERENV', 'HOST' ));
RAISE_APPLICATION_ERROR(-20001,'PSTRIIM DDL is unauthorized and has been disabled! This incident will be reported!');
END IF;
END IF;
IF (l_sysevent='ALTER') THEN
IF (SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('PSTRIIM')) THEN
SYS.DBMS_SYSTEM.KSDWRT(2,'ORA-30000 '||NVL(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),SYS_CONTEXT( 'USERENV', 'OS_USER' ))||' '||l_sysevent||' '||ora_dict_obj_type||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' DDL BLOCKED FROM '||SYS_CONTEXT( 'USERENV', 'OS_USER' )||' '||SYS_CONTEXT( 'USERENV', 'HOST' ));
RAISE_APPLICATION_ERROR(-20001,'PSTRIIM DDL is unauthorized and has been disabled! This incident will be reported!');
END IF;
END IF;
END;
Watch video Oracle Trigger to Block DDL by Schema Owner online without registration, duration hours minute second in high quality. This video was added by user Ken Kim 26 May 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 209 once and liked it 0 people.