User Name from Connection Pool

Hi, We are running 11.70.FC7W2 on Solaris. We have a Java application that uses a connection pool for Informix DB connections. However, we have many triggers that log user activity on certain tables. We would like to log the user that made the change, however from the DB side we only see the application user from the connection pool and not the actual user id. onstat -u only shows "java_app" in the user column and we want the actual user name. How can we get the user name connecting to the connection pool so that the trigger can log the real user name. Thank You, --Dave --001a1145b3ea698281053d0680d4

See the SET SESSION AUTHORIZATION ... statement. Your app server can change its perceived user id to the user requesting the DML or DDL statement execution that way. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Wed, Sep 21, 2016 at 11:50 AM, Informix DBA <in4mixdba@gmail.com> wrote: > Hi, > > We are running 11.70.FC7W2 on Solaris. We have a Java application that > uses a connection pool for Informix DB connections. However, we have many > triggers that log user activity on certain tables. We would like to log > the user that made the change, however from the DB side we only see the > application user from the connection pool and not the actual user id. > onstat -u only shows "java_app" in the user column and we want the actual > user name.> > How can we get the user name connecting to the connection pool so that the > trigger can log the real user name. > > Thank You, > > --Dave > > --001a1145b3ea698281053d0680d4 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114b1ccaaf4a4b053d06dd14

Short answer, you can't... at most the driver could capture the OS user running the process, but obviously that wouldn't solve your problem. For situations like that we implemented in Informix a similar functionality that DB2 and Oracle had for some times. In Informix (and DB2) it's called "trusted context". Basically it's the possibility of a session to change it's user within the database without having to reconnect or re-authenticate. I can't remember exactly what / how it needs to be implemented in the applciation server. I think the J2EE server has some features/class to do this, but I'd jhave to look. On the database server side you need to use SQL to create the trusted context and you can see the syntax in the SQL syntax guide. There are a few restrictions which I personally believe may inhibit many informix customers from using it. Specifically a session where the SET SESSION AITHORIZATION was run cannot do remote operations (by remote I mean on another database, even within the same instance). Please investigate and return if you have doubts. I should be able to find some info abut the app server side if you're interested. Note that what you want is not trivial, because there is no standard way to make the final user identity to "flow" to the database layer... specially because the concept of end user is not defined... it can be Mr. John Doe, or a userid validated inside an application, or a user identity from a single signon infra-structure etc... Auditing tools (like Guardium) try to find this through queries sent by the application.... "SELECT COUNT(*) FROM my_user_table WHERE username='john' and password ='$%/#($' (this would allow it to infer the user is "john" Regards. On Wed, Sep 21, 2016 at 4:50 PM, Informix DBA <in4mixdba@gmail.com> wrote: > Hi, > > We are running 11.70.FC7W2 on Solaris. We have a Java application that > uses a connection pool for Informix DB connections. However, we have many > triggers that log user activity on certain tables. We would like to log > the user that made the change, however from the DB side we only see the > application user from the connection pool and not the actual user id. > onstat -u only shows "java_app" in the user column and we want the actual > user name.> > How can we get the user name connecting to the connection pool so that the > trigger can log the real user name. > > Thank You, > > --Dave > > --001a1145b3ea698281053d0680d4 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114ac5dc77bbee053d06ff7c

Hi Art, Thank you. Yes we considered using set session authorization and that is precisely what we wish to accomplish. However, the concern is that the user needs to be DBA on Informix. From the doc, "Both the DBA and SETSESSIONAUTH access privilege are required to execute this statement.". I would like to do exactly that, however I do not want to give them DBA privilege since they would then have access to every DB object. Any other suggestions to accomplish this? Thank You, --Dave On Wed, Sep 21, 2016 at 12:16 PM, Art Kagel <art.kagel@gmail.com> wrote: > See the SET SESSION AUTHORIZATION ... statement. Your app server can change > its perceived user id to the user requesting the DML or DDL statement > execution that way. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. 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 Wed, Sep 21, 2016 at 11:50 AM, Informix DBA <in4mixdba@gmail.com> > wrote: > > > Hi, > > > > We are running 11.70.FC7W2 on Solaris. We have a Java application that > > uses a connection pool for Informix DB connections. However, we have many > > triggers that log user activity on certain tables. We would like to log > > the user that made the change, however from the DB side we only see the > > application user from the connection pool and not the actual user id. > > onstat -u only shows "java_app" in the user column and we want the actual > > user name.> > > > How can we get the user name connecting to the connection pool so that > the > > trigger can log the real user name. > > > > Thank You, > > > > --Dave > > > > --001a1145b3ea698281053d0680d4 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a114b1ccaaf4a4b053d06dd14 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c072bb6a18744053d07d85d

Only the shared user id used by the app server needs to have DBA privilege and setsessionauth privilege, not the user ids that it will be changing to. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Wed, Sep 21, 2016 at 1:26 PM, Informix DBA <in4mixdba@gmail.com> wrote: > Hi Art, > > Thank you. Yes we considered using set session authorization and that is > precisely what we wish to accomplish. However, the concern is that the > user needs to be DBA on Informix. From the doc, "Both the DBA and > SETSESSIONAUTH access privilege are required to execute this statement.". > > I would like to do exactly that, however I do not want to give them DBA > privilege since they would then have access to every DB object. > > Any other suggestions to accomplish this? > > Thank You, > > --Dave > > On Wed, Sep 21, 2016 at 12:16 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > See the SET SESSION AUTHORIZATION ... statement. Your app server can > change > > its perceived user id to the user requesting the DML or DDL statement > > execution that way. > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. 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 Wed, Sep 21, 2016 at 11:50 AM, Informix DBA <in4mixdba@gmail.com> > > wrote: > > > > > Hi, > > > > > > We are running 11.70.FC7W2 on Solaris. We have a Java application that > > > uses a connection pool for Informix DB connections. However, we have > many > > > triggers that log user activity on certain tables. We would like to log > > > the user that made the change, however from the DB side we only see the > > > application user from the connection pool and not the actual user id. > > > onstat -u only shows "java_app" in the user column and we want the> actual > > > user name. > > > > > > How can we get the user name connecting to the connection pool so that > > the > > > trigger can log the real user name. > > > > > > Thank You, > > > > > > --Dave > > > > > > --001a1145b3ea698281053d0680d4 > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a114b1ccaaf4a4b053d06dd14 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --94eb2c072bb6a18744053d07d85d > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b5d3e0c5334af053d083da5

With a trusted context (see my previous response) that requirement is lifted. Regards On Wed, Sep 21, 2016 at 6:54 PM, Art Kagel <art.kagel@gmail.com> wrote: > Only the shared user id used by the app server needs to have DBA privilege > and setsessionauth privilege, not the user ids that it will be changing to. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. 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 Wed, Sep 21, 2016 at 1:26 PM, Informix DBA <in4mixdba@gmail.com> wrote: > > > Hi Art, > > > > Thank you. Yes we considered using set session authorization and that is > > precisely what we wish to accomplish. However, the concern is that the > > user needs to be DBA on Informix. From the doc, "Both the DBA and > > SETSESSIONAUTH access privilege are required to execute this statement.". > > > > I would like to do exactly that, however I do not want to give them DBA > > privilege since they would then have access to every DB object. > > > > Any other suggestions to accomplish this? > > > > Thank You, > > > > --Dave > > > > On Wed, Sep 21, 2016 at 12:16 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > See the SET SESSION AUTHORIZATION ... statement. Your app server can > > change > > > its perceived user id to the user requesting the DML or DDL statement > > > execution that way. > > > > > > Art > > > > > > Art S. Kagel, President and Principal Consultant > > > ASK Database Management > > > www.askdbmgt.com > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on the IIUG, nor any other organization with which I > > am > > > associated either explicitly, implicitly, or by inference. 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 Wed, Sep 21, 2016 at 11:50 AM, Informix DBA <in4mixdba@gmail.com> > > > wrote: > > > > > > > Hi, > > > > > > > > We are running 11.70.FC7W2 on Solaris. We have a Java application > that > > > > uses a connection pool for Informix DB connections. However, we have > > many > > > > triggers that log user activity on certain tables. We would like to > log > > > > the user that made the change, however from the DB side we only see > the > > > > application user from the connection pool and not the actual user id. > > > > onstat -u only shows "java_app" in the user column and we want the> > actual > > > > user name. > > > > > > > > How can we get the user name connecting to the connection pool so > that > > > the > > > > trigger can log the real user name. > > > > > > > > Thank You, > > > > > > > > --Dave > > > > > > > > --001a1145b3ea698281053d0680d4 > > > > > > > > > > > > ************************************************************ > > > > ******************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a114b1ccaaf4a4b053d06dd14 > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --94eb2c072bb6a18744053d07d85d > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7b5d3e0c5334af053d083da5 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c1246daa05e90053d0879a9

Thank you Fernando. I will read up on using the "trusted context" functionality. Thank You, --Dave On Wed, Sep 21, 2016 at 12:25 PM, Fernando Nunes <domusonline@gmail.com> wrote: > Short answer, you can't... at most the driver could capture the OS user > running the process, but obviously that wouldn't solve your problem. > For situations like that we implemented in Informix a similar functionality > that DB2 and Oracle had for some times. > In Informix (and DB2) it's called "trusted context". Basically it's the > possibility of a session to change it's user within the database without > having to reconnect or re-authenticate. > > I can't remember exactly what / how it needs to be implemented in the > applciation server. I think the J2EE server has some features/class to do > this, but I'd jhave to look. > On the database server side you need to use SQL to create the trusted > context and you can see the syntax in the SQL syntax guide. > > There are a few restrictions which I personally believe may inhibit many > informix customers from using it. Specifically a session where the SET > SESSION AITHORIZATION was run cannot do remote operations (by remote I mean > on another database, even within the same instance). > > Please investigate and return if you have doubts. I should be able to find > some info abut the app server side if you're interested. > > Note that what you want is not trivial, because there is no standard way to > make the final user identity to "flow" to the database layer... specially > because the concept of end user is not defined... it can be Mr. John Doe, > or a userid validated inside an application, or a user identity from a > single signon infra-structure etc... > > Auditing tools (like Guardium) try to find this through queries sent by the > application.... "SELECT COUNT(*) FROM my_user_table WHERE username='john' > and password ='$%/#($' > > (this would allow it to infer the user is "john" > > Regards. > > On Wed, Sep 21, 2016 at 4:50 PM, Informix DBA <in4mixdba@gmail.com> wrote: > > > Hi, > > > > We are running 11.70.FC7W2 on Solaris. We have a Java application that > > uses a connection pool for Informix DB connections. However, we have many > > triggers that log user activity on certain tables. We would like to log > > the user that made the change, however from the DB side we only see the > > application user from the connection pool and not the actual user id. > > onstat -u only shows "java_app" in the user column and we want the actual > > user name.> > > > How can we get the user name connecting to the connection pool so that > the > > trigger can log the real user name. > > > > Thank You, > > > > --Dave > > > > --001a1145b3ea698281053d0680d4 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a114ac5dc77bbee053d06ff7c > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c072bb6e98d89053d089a88

Hi Fernando, Thank you for pointing us in the direction of Trusted Context. That is precisely what we were looking for. I am confirming that we tested and it works to allow non dba users to run "SET SESSION AUTHORIZATION". In addition to the Informix Documentation, here are 2 links, a developer works and IBM support doc that we found very useful in configuring the trusted context object. http://www.ibm.com/developerworks/data/library/techarticle/dm-1205trustedcontext informix/ http://www-01.ibm.com/support/docview.wss?uid=swg21620630 Thank you and have a great day, --Dave On Wed, Sep 21, 2016 at 2:20 PM, Informix DBA <in4mixdba@gmail.com> wrote: > Thank you Fernando. I will read up on using the "trusted context" > functionality. > > Thank You, > > --Dave > > On Wed, Sep 21, 2016 at 12:25 PM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > Short answer, you can't... at most the driver could capture the OS user > > running the process, but obviously that wouldn't solve your problem. > > For situations like that we implemented in Informix a similar > functionality > > that DB2 and Oracle had for some times. > > In Informix (and DB2) it's called "trusted context". Basically it's the > > possibility of a session to change it's user within the database without > > having to reconnect or re-authenticate. > > > > I can't remember exactly what / how it needs to be implemented in the > > applciation server. I think the J2EE server has some features/class to do > > this, but I'd jhave to look. > > On the database server side you need to use SQL to create the trusted > > context and you can see the syntax in the SQL syntax guide. > > > > There are a few restrictions which I personally believe may inhibit many > > informix customers from using it. Specifically a session where the SET > > SESSION AITHORIZATION was run cannot do remote operations (by remote I > mean > > on another database, even within the same instance). > > > > Please investigate and return if you have doubts. I should be able to > find > > some info abut the app server side if you're interested. > > > > Note that what you want is not trivial, because there is no standard way > to > > make the final user identity to "flow" to the database layer... specially > > because the concept of end user is not defined... it can be Mr. John Doe, > > or a userid validated inside an application, or a user identity from a > > single signon infra-structure etc... > > > > Auditing tools (like Guardium) try to find this through queries sent by > the > > application.... "SELECT COUNT(*) FROM my_user_table WHERE username='john' > > and password ='$%/#($' > > > > (this would allow it to infer the user is "john" > > > > Regards. > > > > On Wed, Sep 21, 2016 at 4:50 PM, Informix DBA <in4mixdba@gmail.com> > wrote: > > > > > Hi, > > > > > > We are running 11.70.FC7W2 on Solaris. We have a Java application that > > > uses a connection pool for Informix DB connections. However, we have > many > > > triggers that log user activity on certain tables. We would like to log > > > the user that made the change, however from the DB side we only see the > > > application user from the connection pool and not the actual user id. > > > onstat -u only shows "java_app" in the user column and we want the> actual > > > user name. > > > > > > How can we get the user name connecting to the connection pool so that > > the > > > trigger can log the real user name. > > > > > > Thank You, > > > > > > --Dave > > > > > > --001a1145b3ea698281053d0680d4 > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --001a114ac5dc77bbee053d06ff7c > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --94eb2c072bb6e98d89053d089a88 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c1b6545e9bc0053d1d68d5