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;
Смотрите видео Oracle Trigger to Block DDL by Schema Owner онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Ken Kim 26 Май 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 209 раз и оно понравилось 0 людям.