IDS Forum

RE: RE: sydbopen() issue

Posted By: Mark Tyrer
Date: Tuesday, 7 April 2009, at 8:19 a.m.

In Response To: Re: RE: sydbopen() issue (VIKAS HIVARKAR)

Perhaps a little complicated for what you need, try

CREATE PROCEDURE public.sysdbopen()

SET LOCK MODE TO WAIT 15;

INSERT INTO LOG (username, hostname)

SELECT t1.username, t1.hostname

FROM sysmaster:syssessions t1

WHERE t1.sid = DBINFO('sessionid');

END PROCEDURE;

Note: I think that it is safer to explicitly defined the fields of your log
table to insert into.

Also you might consider using a raw table...

create RAW table log(username char(20), hostname char(20));

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VIKAS
HIVARKAR
Sent: 07 April 2009 02:03 PM
To: ids@iiug.org
Subject: Re: RE: sydbopen() issue [15440]

Hi Mark,

I've read the article by Fernando and also seen the procedure in your
response.

I tried Your procedure with a small modified to do a insert,

Please check the Insert logic for its correctness and let me know

create table log(username char(20), hostname char(20));

CREATE PROCEDURE public.sysdbopen()

DEFINE l_sid INTEGER; -- Session ID
DEFINE l_curr_sessions INTEGER;

LET l_sid = DBINFO('sessionid');

INSERT INTO LOG
SELECT t1.username, t1.hostname
FROM sysmaster:syssessions t1, sysmaster:syssessions t2 WHERE t1.sid = l_sid

AND t1.username = t2.username
AND t1.uid = t2.uid
AND t1.pid = t2.pid
AND t1.hostname = t2.hostname
AND t1.tty = t2.tty;

END PROCEDURE;

It worked for me for now but "I'm NOT NOT sure about this failure of
procedure and database locking issue" Hence very reluctant to use this
sysdbopen() procedure.

Thanks for your response and ofcourse your procedure !

Regards,
Vikas

***************************************************************************
Ok,

A1. Sure, if your user access the database with the login, ODBC

A2.

Several things to remember here.

1. By selecting from the syssessions table in the sysmaster database, you
won't be locking the table

2. It is as safe to use the procedure as the code that you put into it. If
you put invalid logic into the procedure, you can deny access to everyone.

3. When you develop the procedure, always set the environment variable
IFX_NODBPROC. This prevents the sysdbopen stored procedure from executing.
If you have locked the database, you can always get in to the database so
that you can change the stored procedure. It might be better to develop the
procedure using a user other than public, until you are happy with it.

4. I would look at your INSERT statement to make sure that it is not raising
an error. Any Error that gets raised will cause the connection to be denied.

5. syssessions is the easiest place to get the additional values that you
are looking for.

You might want to read the article by Fernando Nunes

http://informix-technology.blogspot.com/search?q=sysdbopen

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VIKAS
HIVARKAR
Sent: 07 April 2009 01:15 PM
To: ids@iiug.org
Subject: sydbopen() issue [15437]

Hello all,

IDS 11.10 FC3 on solaris 10

I need to log every odbcusr connection to our databases, No auditing so i
tried sydbopen() and sysdbclose() procedures.

The following works for me:
CREATE PROCEDURE "public".sysdbopen()
DEFINE SID INTEGER;
LET SID = DBINFO('sessionid');

INSERT INTO LOG VALUES(USER, "Open", CURRENT::DATETIME YEAR TO SECOND,SID);
END PROCEDURE;

This works but I also need the client hostname from where the odbc user was
connecting, I'm trying to use sysmater.syssessions with a where clause.

But my first try failed and locked the database (ofcourse it was a test
db)and as i have NOT set the DEBUG parameter, the db remained locked.

Q1: Can i get the hostname of the odbc user opening the database using the
odbc.sysdbopen()?

Q2: Is it safe to use this sysdbopen() procedure, i read

Warning:
If a sysdbopen( ) procedure fails, the database cannot be opened. If a
sysdbclose( ) procedure fails, the failure is ignored. While you are writing
and debugging a sysdbopen( ) procedure, set the DEBUG environment variable
to NODBPROC before you connect to the database. When DEBUG is set to
NODBPROC, the procedure is not executed, and failures cannot prevent the
database from opening. Failures from these procedures can be generated by
the system or simulated by the procedures with the RAISE EXCEPTION statement
of SPL. For more information, refer to the description of RAISE EXCEPTION in
Chapter 3.

it says When DEBUG is set to NODBPROC, the procedure is not executed. so how
does it work for me, I want that even if the sysdbopen() procedure fails the
database should not be locked.

Any better idea for what I'm trying to achieve is appreciated.

Regards
Vikas

****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.

==================
Please read our Email Disclaimer :
http://www.thefuelgroup.com/disclaimer.html

Messages In This Thread

• sydbopen() issue
  VIKAS HIVARKAR -- Tuesday, 7 April 2009, at 7:15 a.m.
  • RE: sydbopen() issue
    Mark Tyrer -- Tuesday, 7 April 2009, at 7:48 a.m.
    • Re: RE: sydbopen() issue
      VIKAS HIVARKAR -- Tuesday, 7 April 2009, at 8:02 a.m.
      • RE: RE: sydbopen() issue
        Mark Tyrer -- Tuesday, 7 April 2009, at 8:19 a.m.
        • Re: RE: RE: sydbopen() issue
          VIKAS HIVARKAR -- Tuesday, 7 April 2009, at 9:51 a.m.
          • Re: RE: RE: sydbopen() issue
            VIKAS HIVARKAR -- Tuesday, 7 April 2009, at 10:27 a.m.
          • RE: RE: RE: sydbopen() issue
            Mark Tyrer -- Tuesday, 7 April 2009, at 10:59 a.m.
            • Re: RE: RE: RE: sydbopen() issue
              VIKAS HIVARKAR -- Tuesday, 7 April 2009, at 11:48 a.m.
          • Re: RE: RE: sydbopen() issue
            Art Kagel -- Tuesday, 7 April 2009, at 11:25 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.