Which application is making the connection?
Steve wanted to grant different roles (read-only for Excel, read-write for his app) depending on which client application connects. Suggestions: use a sysdbopen() procedure that checks sysmaster.syssesappinfo (DRDA) or syssessions.feprogram (SQLI) to identify the program and set the role or reject the connection; alternatives were separate database users per client, or encoding the app name in DBTEMP and reading it. Caveat raised: this is not real security, since users can spoof the app name and roles have no passwords. Steve found feprogram is only populated from 11.70 (with CSDK 3.70+), not his 11.50, so he was left evaluating the other workarounds — no final fix recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I would like a way to set role permissions on connection, based on the application making the connection. For example, if the user connects with Excel I want to set read-only role. If the user connects from the end-user application I would set read-write role. Is there a way to tell what application is making the connection to the database and set the role accordingly? Kind regards, Steve
No, I think that kind of information is not passed through ODBC or OLEDB connections, for any kind of database or source you are connected. You can do it, by configuring separated users from excel connections, or from your application ones, and then applying specific roles to each one in the engine. Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: steven_black@yahoo.com > Subject: Which application is making the connection? [35920] > Date: Wed, 21 Oct 2015 09:56:01 -0400 > > I would like a way to set role permissions on connection, based on the > application making the connection. > > For example, if the user connects with Excel I want to set read-only role. If > the user connects from the end-user application I would set read-write role. > > Is there a way to tell what application is making the connection to the > database and set the role accordingly? > > Kind regards, > Steve > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Recent versions of the engine and the drivers pass that information. It's kept in new fields in sysmaster:syssession if I recall correctly. Based on that you can use a sysdbopen() procedure to change the user role for example but keep in mind this is not safe... meaning it will not protect you from "interested" users. It will protect from mistakes... Reasons are: 1- A resourceful user can change it's application name 2- The roles don't have passwords, so a user can find it's roles and change the current one 3- An hacker can probably (for sure I'd say) change the communication on the fly and put in the name he wants Regards. On Wed, Oct 21, 2015 at 4:15 PM, Alexandre Marini <alexandre@briug.org> wrote: > No, I think that kind of information is not passed through ODBC or OLEDB > connections, for any kind of database or source you are connected. > > You can do it, by configuring separated users from excel connections, or > from > your application ones, and then applying specific roles to each one in the > engine. > > Hope it helps. > Regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > IBM Information Management Informix Technical Professional > > IBM Certified Developer - Informix Genero > BRIUG website administrator > Informix independent consultant > > > To: ids@iiug.org > > From: steven_black@yahoo.com > > Subject: Which application is making the connection? [35920] > > Date: Wed, 21 Oct 2015 09:56:01 -0400 > > > > I would like a way to set role permissions on connection, based on the > > application making the connection. > > > > For example, if the user connects with Excel I want to set read-only > role. > If > > the user connects from the end-user application I would set read-write > role. > > > > Is there a way to tell what application is making the connection to the > > database and set the role accordingly? > > > > Kind regards, > > Steve > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > 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... --047d7bdc0a4e7393c805229e732f
You can use the sysdbopen procedure and inside the procedure<= br>query sysmaster.syssesappinfo for drda connection or sysmaster.sysse= ssions.feprogram for SQLI connections to determine the application and = the set the desired role or return an error if you do not want the user= to connect. John F. Miller III STSM, Lead Archite= ct [1]miller3@us.ibm.com 503-7= 47-1366 IBM Informix Dynamic Server (IDS) [2]--= ---ids-bounces@iiug.org wrote: ----- >To: [3]ids@iiug.org >From: "STEV= EN BLACK" >Sent by: [4]ids-bounces@iiug.org >Date: 10/21/2015 06:57AM >= ;Subject: Which application is making the connection? [35920] > &= gt;I would like a way to set role permissions on connection, based on &g= t;the >application making the connection. > >For exampl= e, if the user connects with Excel I want to set read-only >role. If = >the user connects from the end-user application I would set >= read-write role. > >Is there a way to tell what application is= making the connection to >the >database and set the role acco= rdingly? > >Kind regards, >Steve > > &g= t;******************************************************************** * = >********** > Forum Note: Use "Reply" to post a response in the = discussion forum. > > > References 1. 3D"mailto:miller3@us.ibm.com" 2. 3D"mailto:-----ids-bounces@iiug.org" 3. 3D"mailto:ids@iiug.org" 4. 3D"mailto:ids-bounce=
Our extremely un-sophisticated solution is to set DBTEMP=/tmp:<The name of the app that's running> Then we can query the second field (following the ":") of DBTEMP for that session ... We left the /tmp at the front so as not to break Ace reports. Not sure if anything uses DBTEMP anymore, but this works for us ... John Fahey -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Miller iii Sent: Wednesday, October 21, 2015 11:01 AM To: ids@iiug.org Subject: Re: Which application is making the connection? [35923] You can use the sysdbopen procedure and inside the procedure<= br>query sysmaster.syssesappinfo for drda connection or sysmaster.sysse= ssions.feprogram for SQLI connections to determine the application and = the set the desired role or return an error if you do not want the user= to connect. John F. Miller III STSM, Lead Archite= ct [1]miller3@us.ibm.com 503-7= 47-1366 IBM Informix Dynamic Server (IDS) [2]--= ---ids-bounces@iiug.org wrote: ----- >To: [3]ids@iiug.org >From: "STEV= EN BLACK" >Sent by: [4]ids-bounces@iiug.org >Date: 10/21/2015 06:57AM >= ;Subject: Which application is making the connection? [35920] > &= gt;I would like a way to set role permissions on connection, based on &g= t;the >application making the connection. > >For exampl= e, if the user connects with Excel I want to set read-only >role. If = >the user connects from the end-user application I would set >= read-write role. > >Is there a way to tell what application is= making the connection to >the >database and set the role acco= rdingly? > >Kind regards, >Steve > > &g= t;******************************************************************** * = >********** > Forum Note: Use "Reply" to post a response in the = discussion forum. > > > References 1. 3D"mailto:miller3@us.ibm.com" 2. 3D"mailto:-----ids-bounces@iiug.org" 3. 3D"mailto:ids@iiug.org" 4. 3D"mailto:ids-bounce= **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. -- *CONFIDENTIALITY NOTICE*: This email communication may contain private, confidential, or legally privileged information intended for the sole use of the designated and/or duly authorized recipient(s). If you are not the intended recipient or have received this email in error, please notify the sender immediately by email and permanently delete all copies of this email including all attachments without reading them. If you are the intended recipient, secure the contents in a manner that conforms to all applicable state and/or federal requirements related to privacy and confidentiality of such information.
I'd say it's even "secure" in a "security by obscurity"sort of way :) On Wed, Oct 21, 2015 at 7:59 PM, John Fahey <jfahey@mandm.net> wrote: > Our extremely un-sophisticated solution is to set DBTEMP=/tmp:<The name of > the app that's running> > Then we can query the second field (following the ":") of DBTEMP for that > session ... > We left the /tmp at the front so as not to break Ace reports. > Not sure if anything uses DBTEMP anymore, but this works for us ... > > John Fahey > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Miller iii > Sent: Wednesday, October 21, 2015 11:01 AM > To: ids@iiug.org > Subject: Re: Which application is making the connection? [35923] > > You can use the sysdbopen procedure and inside the procedure<= > > br>query sysmaster.syssesappinfo for drda connection or > > sysmaster.sysse= ssions.feprogram for SQLI connections to > > determine the application and = the set the desired role or return > > an error if you do not want the user= to connect. > > John F. Miller III > > STSM, Lead Archite= ct > > [1]miller3@us.ibm.com > > 503-7= 47-1366 > > IBM Informix Dynamic Server (IDS) > > [2]--= ---ids-bounces@iiug.org wrote: ----- > > >To: [3]ids@iiug.org > > >From: "STEV= EN BLACK" > > >Sent by: [4]ids-bounces@iiug.org > > >Date: 10/21/2015 06:57AM > > >= ;Subject: Which application is making the connection? [35920] > > > > > &= gt;I would like a way to set role permissions on connection, based > > on > > &g= t;the > > >application making the connection. > > > > > >For exampl= e, if the user connects with Excel I want to set > > read-only > > >role. If = > > >the user connects from the end-user application I would set > > >= read-write role. > > > > > >Is there a way to tell what application is= making the connection to > > >the > > >database and set the role acco= rdingly? > > > > > >Kind regards, > > >Steve > > > > > > > > &g= > > t;******************************************************************** > > * > > = >********** > > > Forum Note: Use "Reply" to post a response in the = discussion > > forum. > > > > > > > > > > > References > > 1. 3D"mailto:miller3@us.ibm.com" > > 2. 3D"mailto:-----ids-bounces@iiug.org" > > 3. 3D"mailto:ids@iiug.org" > > 4. 3D"mailto:ids-bounce= > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- > > *CONFIDENTIALITY NOTICE*: This email communication may contain private, > confidential, or legally privileged information intended for the sole use > of the designated and/or duly authorized recipient(s). If you are not the > intended recipient or have received this email in error, please notify the > sender immediately by email and permanently delete all copies of this email > including all attachments without reading them. If you are the intended > recipient, secure the contents in a manner that conforms to all applicable > state and/or federal requirements related to privacy and confidentiality of > such information. > > > > ******************************************************************************* > 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... --001a1140ec24254f580522a1a3fd
Thank you for the quick responses. Forgot to say what version of Informix I'm on: IBM Informix Dynamic Server Version 11.50.FC6W2 SunOS 5.10 Generic_150400-26 sun4u sparc SUNW,SPARC-Enterprise I believe the syssessions.feprogram gets set starting with version 11.70. I checked the value of this column on various connections but it does not look like it gets set with 11.50. Will take the other suggestions into consideration as well. Best regards, Steve
Hello, Steve. Yes this feature was launched in 11.70 (I don't remember the release, but the version was 11.70). And all of your CSDKs must also be at least 3.70, of course. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator > To: ids@iiug.org > From: steven_black@yahoo.com > Subject: Re: Which application is making the connection? [35927] > Date: Thu, 22 Oct 2015 09:17:29 -0400 > > Thank you for the quick responses. > > Forgot to say what version of Informix I'm on: > > IBM Informix Dynamic Server Version 11.50.FC6W2 > > SunOS 5.10 Generic_150400-26 sun4u sparc SUNW,SPARC-Enterprise > > I believe the syssessions.feprogram gets set starting with version 11.70. I > checked the value of this column on various connections but it does not look > like it gets set with 11.50. > > Will take the other suggestions into consideration as well. > > Best regards, > Steve > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >