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

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%


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.

permanent link

answered 04 May '12, 09:50

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

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.

permanent link

answered 04 May '12, 09:48

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662
accept rate: 32%

edited 04 May '12, 09:51

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×2
×1

question asked: 04 May '12, 09:34

question was seen: 1,361 times

last updated: 04 May '12, 09:51