Oracle 12cR2 - After Logon Exception

Question

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,

score 2 · Answer 1 · answered Dec 22 '21 at 11:49

I want to define read only (select) permission only on its own objects (USER30) if it comes from HOST2.

This isn't MySQL.
You cannot grant a User different permissions based on where that User connects from.

A User will always have change permissions on their own schema.
There is no way around this.

This is why any Data that matters should live in its own schema[s], where it can be properly isolated from everybody else that wants to use it.

score 1 · Answer 2 · answered Dec 22 '21 at 10:30

Permissions should be granted by Privileges.

By default every user can read and write his own objects. I don't think there is an easy way to prevent that. Solution would be another user account where you only GRANT SELECT ON USER30.table_name TO USER30_RO). Unfortunately there is no privilege in Oracle which covers all tables/view within a schema. Privilege SELECT ANY TABLE gives access to all tables in all schemas, that's not what you like to get.

Using SYS_CONTEXT('USERENV', 'HOST') is not secure. Depending on the driver the user can set this attribute by himself as free text.

Oracle 12cR2 - After Logon Exception

2 Answers2