Sample Header Ad - 728x90

Oracle 12cR2 - After Logon Exception

0 votes
2 answers
268 views
Logon trigger as below: create or replace TRIGGER USER3.MY_TRIGGER AFTER LOGON ON DATABASE BEGIN IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('USER30') THEN IF SYS_CONTEXT ('USERENV', 'HOST') NOT IN ('HOST1', 'HOST2', 'HOST3', 'HOST4') THEN INSERT INTO USER3.MY_TABLE (USERNAME, SID, OS_USER, HOST, IP, TERMINAL, DB_NAME, INSTANCE, INSTANCE_NAME, MODULE, SERVER_HOST, SERVICE_NAME, TIMESTAMP) VALUES (sys_context('USERENV', 'SESSION_USER'), sys_context('USERENV', 'SID'), sys_context('USERENV', 'OS_USER'), sys_context('USERENV', 'HOST'), sys_context('USERENV', 'IP_ADDRESS'), sys_context('USERENV', 'TERMINAL'), sys_context('USERENV', 'DB_NAME'), sys_context('USERENV', 'INSTANCE'), sys_context('USERENV','INSTANCE_NAME'), sys_context('USERENV','MODULE'), sys_context('USERENV','SERVER_HOST'), sys_context('USERENV','SERVICE_NAME'), SYSTIMESTAMP); COMMIT; RAISE_APPLICATION_ERROR(-20000, 'Denied! You are not allowed to logon.'); END IF; END IF; END; Without disturbing the existing structure in any way, I want to define read only (select) permission only on its own objects (USER30) if it comes from HOST2. Can you help with this issue? Best Regards,
Asked by jrdba123 (29 rep)
Dec 22, 2021, 08:56 AM
Last activity: Dec 22, 2021, 11:49 AM