Why does this logon trigger work in one server but not another

oracleoracle-11gtrigger

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.

Best Answer

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.

Related Question