Re: Unix Environment values from SPL/UDR

Rob Burba wrote: > OK, a challenge for all you gurus and highly intelligent Informix > folks, yes that includes you Obnoxio!!! It's one or the other. > > 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 http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.dapif.doc/dapif274.htm#sii-02get-27984 -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

On Oct 6, 4:34 pm, Obnoxio The Clown <obno...@serendipita.com> wrote: > Rob Burba wrote: > > OK, a challenge for all you gurus and highly intelligent Informix > > folks, yes that includes you Obnoxio!!! > > It's one or the other. > > > > > > > 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 > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.da... > > -- > Cheers, > Obnoxio the Clown > > http://obotheclown.blogspot.com I doubt that will work. The person is sudo to a "service" account. You're not going to get the individual or be guaranteed to get it that way.