0

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,

2 Answers 2

2

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.

1

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.

3
  • Hello, I know all the information you have specified (HOST options is not secure and privileges, etc.). After the USER connects, it should only be able to select over its own schema. Isn't there a solution left in the corner?
    – jrdba123
    Commented Dec 22, 2021 at 10:48
  • E.g; an exception that will prevent the user coming from this host if they run a command other than select on their schema.
    – jrdba123
    Commented Dec 22, 2021 at 10:58
  • No, there is not. The owner of an object will always have complete control of that object and there is no way to limit that. Ideally, in Oracle an application never connects directly to the schema that owns the application tables/objects. It connects to a separate runtime user account that has the appropriate DML privileges and no DDL privileges on the application objects.
    – pmdba
    Commented Dec 23, 2021 at 23:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.