Unix Environment values from SPL/UDR

OK, a challenge for all you gurus and highly intelligent Informix folks, yes that includes you Obnoxio!!! I have a system that has recently been "upgraded" to control access to the database through service accounts. Each user logs into the Unix system with their id and then runs the application using the sudo command to force the database engine to see them as the service account rather than an individual. This limit the overhead of security maintained at the individual user level. However, the desire remains to know the "real" user ID of all users who are adding/updating/deleting records. Previously, this worked fine just referencing the "user" variable in any select statement or as the return value from a stored proceedure. However, life is not so fun any more. Each user creates a record a database table tracking the session which associates a "real" user id with a TTY along with the department the user is using to access their data. Through a store procedure, the "real" user can be identified using DBINFO to get the session ID from the sysmaster:syssessions table and use the TTY to look up the user's database session record. From an interactive program perspective, this is minimal cost and not a problem. But this is where the problem gets dicey. Many of the tables in the database have triggers on the table to populate fields identifying the user who created the record and the user who last modified the record. These triggers call stored procedures to get the value. Previously the store procedures just returned "user" and that was correct. However, now the store procedures must perform the join described above. The result is a much slower system and folks sharpening their blades to cut off the heads of unsuspecting developers. My mission is to find a way for the stored procedures to get the "real" user id (the $LOGIN environment variable) since the "user" value returned by the engine is the service account name rather than the real user (because that is the current user id of the Unix session that connected to the database). Does anyone have any tricks or suggestions on how to get a stored procedure/UDR/Datablade or any other methodology other than 4gl to return the value of Unix environment variables? Help, please!!!!!! Technical info. Informix 10.00.FC8, AIX 5.3 Thanks!!! R

Hi, Rob, I am not sure if I understand your problem corretly but it seems to me that the sysmaster:syssessions solution works for you but is too slow for some uses, right? Cant you just store the real user name in a SPL global variable and limit the expensive calculation to once per session with that? Something like: CREATE PROCEDURE GetRealUser() RETURNING CHAR(...)DEFINE GLOBAL realuser CHAR(...) DEFAULT " "; IF realuser=" " THEN LET realuser=GetRealUserExpensive(); END IF RETURN realuser; END PROCEDURE; Regards, Dirk -- -- -- Dipl.-Math. Dirk Gunsthövel -- -professional services- -- -- Dirk Gunsthövel IT Systemanalyse - GunCon -- Hammer Str. 13 -- D-48153 Muenster -- phone: +49 (0) 251 28446- 0 -- fax: +49 (0) 251 28446-55 -- web: http://www.GunCon.de -- email: info@GunCon.de -- UStId: DE 189527667 -- -- 'One now understands why some animals eat their young.' -- (Andrew in 'Bicentennial Man' 1999) "Rob Burba" <inf4glguru@gmail.com> schrieb im Newsbeitrag news:mailman.37.1223327749.874.informix-list@iiug.org... OK, a challenge for all you gurus and highly intelligent Informix folks, yes that includes you Obnoxio!!! Each user creates a record a database table tracking the session which associates a "real" user id with a TTY along with the department the user is using to access their data. Through a store procedure, the "real" user can be identified using DBINFO to get the session ID from the sysmaster:syssessions table and use the TTY to look up the user's database session record. From an interactive program perspective, this is minimal cost and not a problem. But this is where the problem gets dicey.

On Oct 6, 4:15 pm, "Rob Burba" <inf4glg...@gmail.com> wrote: > My mission is to find a way for the stored procedures to get the "real" user > id (the $LOGIN environment variable) since the "user" value returned by the > engine is the service account name rather than the real user (because that > is the current user id of the Unix session that connected to the database). > Does anyone have any tricks or suggestions on how to get a stored > procedure/UDR/Datablade or any other methodology other than 4gl to return > the value of Unix environment variables? > > Help, please!!!!!! > > Technical info.  Informix 10.00.FC8, AIX 5.3 Why not write an SPL that when the user logs in, you authenticate the user? You can create a user table that has the user name , and an encrypted password. The user logs in, gets authenticated and a session ID is created and tracked while the application is being run. By passing the session token, you will know the user, and what they are doing. BTW, this is more secure than relying on a shell variable.

This sounds like a very interesting solution. I will test it out and let you know how it works. Initial tests look very promising. When calling a stored procedure that just returns the "user" value, the routine takes .00030 to .00050 seconds -- CURRENT fraction(5). The function to find the "real" user takes .00700 to .01000 seconds (sometimes up to 20 times longer). Returning the global variable when set takes .00018 to .00030 seconds. If this proves out across the system, these are fantastic results. Thanks!!!! On Mon, Oct 6, 2008 at 5:53 PM, Dirk Gunsthövel <dirk@guncon.de> wrote: > Hi, Rob, > > I am not sure if I understand your problem corretly but it seems to me > that the sysmaster:syssessions solution works for you but is too slow > for some uses, right? > > Cant you just store the real user name in a SPL global variable and > limit the expensive calculation to once per session with that? > > Something like: > > CREATE PROCEDURE GetRealUser() RETURNING CHAR(...)> DEFINE GLOBAL realuser CHAR(...) DEFAULT " "; > IF realuser=" " THEN > LET realuser=GetRealUserExpensive(); > END IF > RETURN realuser; > END PROCEDURE; > > Regards, > Dirk > > -- > -- > -- Dipl.-Math. Dirk Gunsthövel > -- -professional services- > -- > -- Dirk Gunsthövel IT Systemanalyse - GunCon > -- Hammer Str. 13 > -- D-48153 Muenster > -- phone: +49 (0) 251 28446- 0 > -- fax: +49 (0) 251 28446-55 > -- web: http://www.GunCon.de <http://www.guncon.de/>; > -- email: info@GunCon.de > -- UStId: DE 189527667 > -- > -- 'One now understands why some animals eat their young.' > -- (Andrew in 'Bicentennial Man' 1999) > > > > "Rob Burba" <inf4glguru@gmail.com> schrieb im Newsbeitrag > news:mailman.37.1223327749.874.informix-list@iiug.org... > OK, a challenge for all you gurus and highly intelligent Informix folks, > yes that includes you Obnoxio!!! > > Each user creates a record a database table tracking the session which > associates a "real" user id with a TTY along with the department the user is > using to access their data. Through a store procedure, the "real" user can > be identified using DBINFO to get the session ID from the > sysmaster:syssessions table and use the TTY to look up the user's database > session record. From an interactive program perspective, this is minimal > cost and not a problem. But this is where the problem gets dicey. > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >