|
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? |
|
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. |
|
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. |
