Oracle Trigger to Deny Logon for select list of users
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 check the OS_USER in the deny list, 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_DENY_LOGON
AFTER LOGON ON DATABASE
DECLARE
--e_cannot_log_on EXCEPTION;
BEGIN
IF (SYS_CONTEXT('USERENV','OS_USER') IN ('Judy', 'BIGISLAND\Judy') AND SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('PSTRIIM')) THEN
SYS.DBMS_SYSTEM.KSDWRT(2,'ORA-30001 '||NVL(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),SYS_CONTEXT( 'USERENV', 'OS_USER' ))||' CONNECT BLOCKED FROM '||SYS_CONTEXT( 'USERENV', 'OS_USER' )||' '||SYS_CONTEXT( 'USERENV', 'HOST' ));
RAISE_APPLICATION_ERROR(-20001, SYS_CONTEXT('USERENV','OS_USER')||' connect as '||SYS_CONTEXT('USERENV', 'SESSION_USER')||' has been blocked! This incident will be reported!');
END IF;
END;
Watch video Oracle Trigger to Deny Logon for select list of users 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 116 once and liked it 0 people.