Restricting user login by ip address

[Former Member]

How can I block connections from specific user if client ip not XXX ?

I tried this sp on login_procedure, but I get no info from event_parameter.

ALTER PROCEDURE "dba"."login_check"()
BEGIN
    declare usr char(128);
    declare commlink varchar(100);
    declare ip varchar(20);

    set usr = event_parameter('user');
    select CommLink, NodeAddr into commlink, ip from sa_conn_info() where Number = event_parameter('ConnectionID');

    message 'user = ' || usr || ' commlink = ' || commlink || ' ip = ' || ip || ' number = ' || event_parameter('ConnectionID');

    if (usr = 'dba') and (commlink = 'tcpip') then
        if (ip REGEXP '192\\.168\\.0' and ip not in ('192.168.0.25')) then
            SIGNAL INVALID_LOGON;
        end if;
    end if;
END

On message pane I get this message: user = commlink = ip = number =

What can I do?

[MarkCulp · Accepted solution]

I assume you are using your login_check procedure as the database login procedure.

If this is the case, then you need to use connection properties not event_parameter as the method to get the information about the current connection since the login procedure runs inline during the connection process. Note that event_parameter is only useful when running as an event.

[VolkerBarth]

I guess you will want to filter on the current connection id, not on an event connection, such as

select CommLink, NodeAddr into commlink, ip
from sa_conn_info() where Number = connection_property('Number')

as the login procedure runs in the context of the user who is about to log in.

---

That's different from the connection events (Connect/ConnectFailed/Disconnect) - these (as all events) run on their own connection.