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?
asked 04 May '12, 09:34
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.
answered 04 May '12, 09:50
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.