How to get client information(authorization ID)

Posted in 2007

Summary

The poster wanted triggers to log the end user of a web application, asking for an Informix equivalent of DB2's CURRENT USER / CLIENT_USERID. Replies pointed to USER (e.g. SELECT USER FROM systables WHERE tabid=1), but that only returns the database login the app uses. Since the web app connects via a single shared account, the server has no knowledge of the actual web client; the consensus was that the application or middleware must pass or log that identity itself (e.g. to an application-level audit table). No Informix-side feature was offered.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Triggers, Constraints & Referential Integrity

PRADEEP KUMAR — Tue, 03 Jul 2007 15:39:56 — source: IIUG Forums & Mailing Lists

Do we have any system table or UD function from which we can get the information of the client who is connecting to the application ( in turn to its database). My Requirement is to create triggers on a couple of tables to record the user id(client authorization ID connecting through web based application) that is trying to update records in these tables. I can not find any way to identify the user name. DB2 has CURRENT USER (or CURRENT_USER) registers which records this info. Any help will be greatly appreciated

↪ replying to PRADEEP KUMAR

ART KAGEL, — Tue, 03 Jul 2007 16:07:56 — source: IIUG Forums & Mailing Lists

select USER from systables where tabid = 1; Art S. Kagel ----- Original Message ----- From: Pradeep Kumar <ids@iiug.org> At: 7/03 11:40:13 Do we have any system table or UD function from which we can get the information of the client who is connecting to the application ( in turn to its database). My Requirement is to create triggers on a couple of tables to record the user id(client authorization ID connecting through web based application) that is trying to update records in these tables. I can not find any way to identify the user name. DB2 has CURRENT USER (or CURRENT_USER) registers which records this info. Any help will be greatly appreciated ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to ART KAGEL,

PRADEEP — Tue, 03 Jul 2007 16:14:02 — source: IIUG Forums & Mailing Lists

Thanks for response. select USER from syastable where tabid=1 this not serve to my requirement since the application internally connecting database using a common User ( Unix) but I am concern about the windows/web client id which actuually initiating the connection ( running the application)

↪ replying to PRADEEP

ART KAGEL, — Tue, 03 Jul 2007 16:18:18 — source: IIUG Forums & Mailing Lists

Then there's no way for the IDS server to know what user asked the web server/middleware to connect to the database for him/her/it! Your web server or middleware's going to have to encode that somehow or write out a user-connect record to an application level audit table or file if you want to be able to track those accesses. Just like my emailer ONLY knows that your message was posted through the IDS forum at IIUG.org, IDS ONLY knows the userid that was used to connect to it, not the requesting user. Art S. Kagel ----- Original Message ----- From: Pradeep Kumar <ids@iiug.org> At: 7/03 12:14:21 Thanks for response. select USER from syastable where tabid=1 this not serve to my requirement since the application internally connecting database using a common User ( Unix) but I am concern about the windows/web client id which actuually initiating the connection ( running the application) ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to PRADEEP KUMAR

Richard — Tue, 03 Jul 2007 17:02:28 — source: IIUG Forums & Mailing Lists

Hi, The equivalent in IDS is USER. Works the same way as CURRENT USER in DB2. Its coverd in the Guide to SQL:Syntax manual. Standard warning: a user can connect using any userid that exists and has at least connect privileges. A user is not limited to a single userid. The CONNECT statement let's you specifiy the userid for the connection. You have the challenge of assuring that you know the connection between the useid and the actual person. The DBMS can not do that for you. Cheers, Dick Snoke IBM Software Group - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 "PRADEEP KUMAR" <pkyadav1@hotmail.com> Sent by: ids-bounces@iiug.org 07/03/2007 11:39 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject How to get client information(authorization ID) [9497] Do we have any system table or UD function from which we can get the information of the client who is connecting to the application ( in turn to its database). My Requirement is to create triggers on a couple of tables to record the user id(client authorization ID connecting through web based application) that is trying to update records in these tables. I can not find any way to identify the user name. DB2 has CURRENT USER (or CURRENT_USER) registers which records this info. Any help will be greatly appreciated ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Richard

PRADEEP — Wed, 04 Jul 2007 07:16:22 — source: IIUG Forums & Mailing Lists

Hi Dick, I am looking for the client inforamation, not the USER which is connecting the database. Requirement is to create triggers on a couple of tables to record the client id (client authorization ID connecting through web based application) that is trying to update records in these tables. I can not find any way to identify this information in Informix. Other DB products are already having way to get the client info : Oracle has Built in Package : DBMS_APPLICATION_INFO.READ_CLIENT_INFO procedure Sybase : clientname (system catalog info.) SQL Server : context_info DB2 : CURRENT CLIENT_USERID ( special register values)

↪ replying to PRADEEP

ART KAGEL, — Thu, 05 Jul 2007 13:12:56 — source: IIUG Forums & Mailing Lists

Asked and answered several days ago. Art S. Kagel ----- Original Message ----- From: Pradeep Kumar <ids@iiug.org> At: 7/04 3:16:53 Hi Dick, I am looking for the client inforamation, not the USER which is connecting the database. Requirement is to create triggers on a couple of tables to record the client id (client authorization ID connecting through web based application) that is trying to update records in these tables. I can not find any way to identify this information in Informix. Other DB products are already having way to get the client info : Oracle has Built in Package : DBMS_APPLICATION_INFO.READ_CLIENT_INFO procedure Sybase : clientname (system catalog info.) SQL Server : context_info DB2 : CURRENT CLIENT_USERID ( special register values) ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.