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"()
    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,296 times

last updated: 04 May '12, 09:51