Kick out on LOGON trigger

[julian]

Why doesn't this trigger kick out the user B after log on:

create or replace trigger logon_from_linux
after logon on B.schema
declare
W_NAME varchar2(20);
W_IP varchar2(20);
begin
select USERNAME into W_NAME from USER_USERS;
select Sys_Context('USERENV','IP_ADDRESS') into W_IP from dual;
insert into table values(W_NAME,W_IP);
if W_NAME = 'B' and W_IP = 'XX.XX.XX' then
EXECUTE IMMEDIATE 'DISCONNECT';
-- dbms_utility.exec_ddl_statement('DISCONNECT');
end if;
end;
/

What's wrong with both:

EXECUTE IMMEDIATE 'DISCONNECT';

and

dbms_utility.exec_ddl_statement('DISCONNECT');

[pando]

is disconnect a ddl...?

[julian]

Originally posted by pando
is disconnect a ddl...?

I guess not but what then is wrong with EXECUTE IMMEDIATE 'DISCONNECT'; ?

[jmodic]

AFAIK neither DISCONNECT nor CONNECT are even standard SQL commands! They are SQL*Plus (or maybe any other client tool's) commands.

To restrict user access you simply need to raise application error. So inside your trigger, instead of

....
EXECUTE IMMEDIATE 'DISCONNECT';
....

use

....
RAISE_APPLICATION_ERROR(-20001, 'Sorry, you are not allowed here!');
....

[julian]

Thanks a lot Jurij! Works just fine.

[Solstice]

Yeah, I know... this is a really old thread, and everyone in the thread is happy with the solution.

It's just that recently I had to solve a similar problem: only allow a user to login from a specific IP address. After Googling pages similar to this, I was able to figure it out and get it to work... but my understanding is that some of the information here is wrong.

First: if the trigger is "after logon on B.schema", then it will only be triggered when you logon as "B". There really is no reason to test the username inside the trigger since it can only be "B".

Second: There are several ways to get a "username", but the one used in the trigger above: "select USERNAME into W_NAME from USER_USERS;" will return the name of the user that executes the trigger. If the trigger is owned by "B", then "B" will execute the trigger, and it will always return "B" for the username. It makes more sense though for SYSTEM to own this trigger, so SYSTEM will execute the trigger and the USERNAME will always be "SYSTEM". The above IF will always fail.

Third: If you want the USERNAME of the schema just logged into, use:

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') INTO W_NAME FROM DUAL;

which will always return "B" for this trigger, regardless of who owns is.

So it would look like this:

create or replace trigger logon_from_linux
after logon on B.schema
DECLARE
W_IP VARCHAR2 (20);

BEGIN
SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') INTO W_IP FROM DUAL;
IF W_IP = 'xx.xx.xx.xx' THEN
RAISE_APPLICATION_ERROR (-20001, 'Sorry, you are not allowed here!');
END IF;

END;