0

I created the below trigger in the same manner in the SYS schema on two different Oracle 11g servers, without any errors.

CREATE OR REPLACE TRIGGER LOGON_TRG AFTER LOGON ON DATABASE BEGIN if (user in ('mytestro', 'mytestrw')) then EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = mytest'; end if; exception when others then null; -- prevent a login failure due to an exception END logon_trg; /

On one server I can tell it is working since I can select any table in mytest as soon as I logon with either account. On the other server it says the table does not exist (ORA-00942). If I issue ALTER SESSION SET CURRENT_SCHEMA = mytest; manually I can access the table, so it seems the trigger is not firing.

  • Just to confirm: you've double-checked to be sure that the server is seeing your login name as one of those two names on the other server? – RDFozz Oct 6 '17 at 19:59
  • If you mean to ask if the login names are identical, and have been verified by logging in, yes. – FloorDivision Oct 6 '17 at 21:06
  • Make sure auditing is enabled, then check DBA_AUDIT_TRAIL. I strongly suspect that in spite of your assertion otherwise, you are not logging on as who you think you are. – EdStevens Oct 6 '17 at 22:12
1

Assuming your trigger is enabled, I am surprised at your trigger working at all.

Usernames are case-insensitive by default and treated as uppercase strings internally. If you create a user called mytestro or mytestrw, the actual usernames will be MYTESTRO and MYTESTRW. Normally, a condition like the one in your if statement, will be false.

Sure, it is possible to create case-sensivite, lowercase usernames, but that is quite rare and I consider it bad practice, because it has no benefits, it just makes administration cumbersome.

Instead of:

create user mytestro ...

You can use:

create user "mytestro" ...

The former creates a user called MYTESTRO. The latter creates a user mytestro. These are not identical.

The first one behaves as:

SQL> show user
USER is "MYTESTRO"

While the second one behaves as:

SQL> show user
USER is "mytestro"

You can use the above to check the actual usernames when logged in (or select user from dual if your client does not support show). Your trigger in its current form does not work with the first user above, and does work with the second user above.

  • This was indeed the case! I got USER is "MYTESTRO". Changing the trigger to look for that did the trick. Of course the mystery now is how this works on my other server, with the same SHOW USER output and the old trigger code. – FloorDivision Oct 9 '17 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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