The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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"()
    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 ('')) then
        end if;
    end if;

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

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 04 May '12, 09:34

question was seen: 1,544 times

last updated: 04 May '12, 09:51