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
Last activity: Dec 22, 2021, 11:49 AM