The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
22.3k9129262
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
29.3k287438645
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:

×12
×2
×1

question asked: 04 May '12, 09:34

question was seen: 1,296 times

last updated: 04 May '12, 09:51