sydbopen() issue
Vikas (IDS 11.10 on Solaris 10) wanted to log every ODBC connection, including the client hostname, via a public.sysdbopen() procedure, and worried that a failing procedure would lock everyone out of the database. Mark Tyrer showed a simple version inserting username/hostname from sysmaster:syssessions filtered on DBINFO('sessionid') (suggesting a RAW log table and explicit column lists), and advised setting IFX_NODBPROC during development. Vikas confirmed IFX_NODBPROC=1 let him back in after locking himself out; Mark suggested handling a dropped log table with an ON EXCEPTION (-206) block or a systables check, or accepting the risk.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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
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
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
Hi Mark,
Thanks a lot for your help but i still have a doubt, What if the table used
for inserting the row is droped by mistake? Will the Store procedure fail and
lock the database? I tried similar scenario and the database is LOCKED for
some reason & giving me error when i try to oepn it.
111: ISAM error: no record found. t in the database.
Why does the database gets locked and is there any way then to UNLOCK it?
Thanks again !
Regards,
Vikas
****************************************************************************
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
Hi,
I used export IFX_NODBPROC=1 and avoided the sysdbopen() procedure from
executing which allowed me to UNLOCK/OPEN the database :)
But still I need to find reasons that would make this procedure fail and do
some serious execption handling in the procedure..
Thanks
Vikas
*******************************************************************************
Hi Mark,
Thanks a lot for your help but i still have a doubt, What if the table used
for inserting the row is droped by mistake? Will the Store procedure fail and
lock the database? I tried similar scenario and the database is LOCKED for
some reason & giving me error when i try to oepn it.
111: ISAM error: no record found. t in the database.
Why does the database gets locked and is there any way then to UNLOCK it?
Thanks again !
Regards,
Vikas
****************************************************************************
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
Hi Vikas,
You are so right, there is a huge risk of someone coming along and deleting
your tables just for the heck of it. This is probably a fear that keeps
DBAs awake at night. However, the wise ones could handle the situation in
one of several different ways:
1. They might ignore the risk, since only the owner or someone with DBA
privaledges can drop the table.
2. They might try to catch the exception with an ON EXCEPTION BLOCK. Thereby
creating the table or avoid calling the insert.
Eg
CREATE PROCEDURE markty.sysdbopen()
DEFINE l_sid INTEGER; -- Session ID
DEFINE l_curr_sessions INTEGER;
ON EXCEPTION IN (-206)
CREATE RAW table log(username char(20), hostname char(20));
INSERT INTO LOG (username, hostname)
SELECT t1.username, t1.hostname
FROM sysmaster:syssessions t1
WHERE t1.sid = DBINFO('sessionid');
END EXCEPTION WITH RESUME;
INSERT INTO LOG (username, hostname)
SELECT t1.username, t1.hostname
FROM sysmaster:syssessions t1
WHERE t1.sid = DBINFO('sessionid');
END PROCEDURE;
3. They may even test if the table exists in systables before trying to
insert into it.
Personnally I would tend to go for option 1. If someone is running around
removing tables then you are wasting your time doing development anyway.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VIKAS
HIVARKAR
Sent: 07 April 2009 03:52 PM
To: ids@iiug.org
Subject: Re: RE: RE: sydbopen() issue [15444]
Hi Mark,
Thanks a lot for your help but i still have a doubt, What if the table used
for inserting the row is droped by mistake? Will the Store procedure fail
and lock the database? I tried similar scenario and the database is LOCKED
for some reason & giving me error when i try to oepn it.
111: ISAM error: no record found. t in the database.
Why does the database gets locked and is there any way then to UNLOCK it?
Thanks again !
Regards,
Vikas
****************************************************************************
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 @@D
To unlock the database you have to set the environment variable DEBUG to
NODBPROC then connect to the database and drop the procedure.
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Apr 7, 2009 at 9:51 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote:
> Hi Mark,
>
> Thanks a lot for your help but i still have a doubt, What if the table used
> for inserting the row is droped by mistake? Will the Store procedure fail
> and
> lock the database? I tried similar scenario and the database is LOCKED for
> some reason & giving me error when i try to oepn it.
> 111: ISAM error: no record found. t in the database.>
> Why does the database gets locked and is there any way then to UNLOCK it?
>
> Thanks again !
>
> Regards,
> Vikas
>
>
> ****************************************************************************
> 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.
>
>
--0016e64355de5b27a00466f89e13
Hi Mark !
I really appreciate your help and most importantly the way in which you
explained things.
Ofcourse I was worried when the database was locked due to execption/failure
of the public.sysdbopen() procedure and it made me really behave like a
paranoid dba but then i found "IFX_NODBPROC=1" which works well and opens the
database :)
You are so right with the first point, No one except the user and informix
should be able to drop the table.
Thanks for your help and a wonderful explanation.
Regards,
Vikas
****************************************************************************
Hi Vikas,
You are so right, there is a huge risk of someone coming along and deleting
your tables just for the heck of it. This is probably a fear that keeps
DBAs awake at night. However, the wise ones could handle the situation in
one of several different ways:
1. They might ignore the risk, since only the owner or someone with DBA
privaledges can drop the table.
2. They might try to catch the exception with an ON EXCEPTION BLOCK. Thereby
creating the table or avoid calling the insert.
Eg
CREATE PROCEDURE markty.sysdbopen()
DEFINE l_sid INTEGER; -- Session ID
DEFINE l_curr_sessions INTEGER;
ON EXCEPTION IN (-206)
CREATE RAW table log(username char(20), hostname char(20));
INSERT INTO LOG (username, hostname)
SELECT t1.username, t1.hostname
FROM sysmaster:syssessions t1
WHERE t1.sid = DBINFO('sessionid');
END EXCEPTION WITH RESUME;
INSERT INTO LOG (username, hostname)
SELECT t1.username, t1.hostname
FROM sysmaster:syssessions t1
WHERE t1.sid = DBINFO('sessionid');
END PROCEDURE;
3. They may even test if the table exists in systables before trying to
insert into it.
Personnally I would tend to go for option 1. If someone is running around
removing tables then you are wasting your time doing development anyway.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VIKAS
HIVARKAR
Sent: 07 April 2009 03:52 PM
To: ids@iiug.org
Subject: Re: RE: RE: sydbopen() issue [15444]
Hi Mark,
Thanks a lot for your help but i still have a doubt, What if the table used
for inserting the row is droped by mistake? Will the Store procedure fail
and lock the database? I tried similar scenario and the database is LOCKED
for some reason & giving me error when i try to oepn it.
111: ISAM error: no record found. t in the database.
Why does the database gets locked and is there any way then to UNLOCK it?
Thanks again !
Regards,
Vikas
****************************************************************************
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 ca