Current Role ~ Answered: amber (solid confidence) — Fernando Nunes gave a concrete, complete method (onaudit + the STRL mnemonic) after earlier replies (no alternative exists / CURRENT_ROLE operator) missed what the OP actually needed; never confirmed by the OP.

Advisory only.

I've been asked by our security staff to produce a list of userids that set a particular role. We have applications that routinely set this role which is ok, but we also have 'smart' users that connect with a 3rd party front end and query the database. These are the connections that we wish to discover. The onstat -g ses 0 works but produces lots of output which includes ... Current Role : rolename ... Is there a better way to get this information? I understand the relationship of sysusers to syseroleauth to sysmaster.sysessions, but that only shows who has permission to set a role, not that they have actually set it. Thanks! Environment: IBM Informix Dynamic Server Version 11.50.FC9W (w/ plans to upgrade soon!) SunOS 5.10 Generic_150400-20 sun4v sparc sun4v

I don't think that this information about running sessions is available anywhere else. 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 Tue, Feb 21, 2017 at 12:36 PM, DONALD KORTHAUER < don.korthauer@ama-assn.org> wrote: > I've been asked by our security staff to produce a list of userids that > set a > particular role. > > We have applications that routinely set this role which is ok, but we also > have 'smart' users that connect with a 3rd party front end and query the > database. These are the connections that we wish to discover. > > The onstat -g ses 0 works but produces lots of output which includes > > .... > > Current Role : rolename > > .... > > Is there a better way to get this information? > > I understand the relationship of sysusers to syseroleauth to > sysmaster.sysessions, but that only shows who has permission to set a role, > not that they have actually set it. > > Thanks! > > Environment: > > IBM Informix Dynamic Server Version 11.50.FC9W (w/ plans to upgrade soon!) > > SunOS 5.10 Generic_150400-20 sun4v sparc sun4v > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11442ee471ae4505490ebefe

Thanks Art, We'll have to parse the output from onstat -g ses 0. dk

I believe there is a CURRENT=5FROLE operator, at least in version 12.10. I am not sure which version this was introduced in. John F. Miller III miller3@us.ibm.com 503-747-1366 ids-bounces@iiug.org wrote on 02/21/2017 10:42:41 AM: > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 02/21/2017 10:43 AM > Subject: Re: Current Role [38659] > Sent by: ids-bounces@iiug.org > > I don't think that this information about running sessions is available > anywhere else. > > 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 Tue, Feb 21, 2017 at 12:36 PM, DONALD KORTHAUER < > don.korthauer@ama-assn.org> wrote: > > > I've been asked by our security staff to produce a list of userids that > > set a > > particular role. > > > > We have applications that routinely set this role which is ok, but we also > > have 'smart' users that connect with a 3rd party front end and query the > > database. These are the connections that we wish to discover. > > > > The onstat -g ses 0 works but produces lots of output which includes > > > > .... > > > > Current Role : rolename > > > > .... > > > > Is there a better way to get this information? > > > > I understand the relationship of sysusers to syseroleauth to > > sysmaster.sysessions, but that only shows who has permission to set a role, > > not that they have actually set it. > > > > Thanks! > > > > Environment: > > > > IBM Informix Dynamic Server Version 11.50.FC9W (w/ plans to upgrade soon!) > > > > SunOS 5.10 Generic=5F150400-20 sun4v sparc sun4v > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11442ee471ae4505490ebefe > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

That is true John, but IB the OP wants to be able to query the roles set for all current sessions from outside those sessions for audit purposes. 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 Tue, Feb 21, 2017 at 3:32 PM, John Miller iii <miller3@us.ibm.com> wrote: > I believe there is a CURRENT=5FROLE operator, at least in version 12.10. I > am not sure which version this was > introduced in. > > John F. Miller III > miller3@us.ibm.com > 503-747-1366 > > ids-bounces@iiug.org wrote on 02/21/2017 10:42:41 AM: > > > From: "Art Kagel" <art.kagel@gmail.com> > > To: ids@iiug.org > > Date: 02/21/2017 10:43 AM > > Subject: Re: Current Role [38659] > > Sent by: ids-bounces@iiug.org > > > > I don't think that this information about running sessions is available > > anywhere else. > > > > 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 Tue, Feb 21, 2017 at 12:36 PM, DONALD KORTHAUER < > > don.korthauer@ama-assn.org> wrote: > > > > > I've been asked by our security staff to produce a list of userids that > > > > set a > > > particular role. > > > > > > We have applications that routinely set this role which is ok, but we > also > > > have 'smart' users that connect with a 3rd party front end and query > the > > > database. These are the connections that we wish to discover. > > > > > > The onstat -g ses 0 works but produces lots of output which includes > > > > > > .... > > > > > > Current Role : rolename > > > > > > .... > > > > > > Is there a better way to get this information? > > > > > > I understand the relationship of sysusers to syseroleauth to > > > sysmaster.sysessions, but that only shows who has permission to set a > role, > > > not that they have actually set it. > > > > > > Thanks! > > > > > > Environment: > > > > > > IBM Informix Dynamic Server Version 11.50.FC9W (w/ plans to upgrade > soon!) > > > > > > SunOS 5.10 Generic=5F150400-20 sun4v sparc sun4v > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a11442ee471ae4505490ebefe > > > > > > > ************************************************************ > ***************= > **** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11442ee421a2070549112c54

I believe the answers so far are missing the obvious. If I understood correctly the OP want to audit the SET ROLE. So the simplest way to do it it to turn on auditing and activate the STRL mnemonic on the _default mask. In order to do that, follow this steps (test on non-production first obviously): 1- Create a directory owned by informix for the audit trail (/usr/informix/audit) 2- Activate auditing for the instance: onaudit -l 1 -e 0 -p /usr/informix/audit -s 50000000 (audit level 1 - check manual - , continue working even if audit fails, put the audit trail files in /usr/informix/audit with each file up to 50MB 3- Set STRL option on the _default mask (applied to all users): onaudit -a -u _default -e STRL 4- Check the audit files generated in /usr/informix/audit/$INFORMIXSERVER.n As an example: ONLN|2017-02-21 22:54:02.545|primary|4372|castelo|fnunes|0:STRL:stores:fnunes :test_role ONLN|2017-02-21 23:11:26.729|MyPC|14824|castelo|informix|0:STRL:stores:informix :test_role Line 1: user "fnunes" did a successful "set role test_role" on INFORMIXSERVER=castelo. Client running on "primary" host. PID was 4372Line 2: user "informix" did a successful "set role test_role" on INFORMIXSERVER=castelo. Client running on "MyPC" host. PID was 14824 It will not provide program name. If needed open an RFE or simply complain to IBM (we should improve audit information) PID = -1 is Java. And they become indistinguishable from other Java sessions. Vote for an RFE requesting SID to be on the audit log. It's your responsibility to manage the audit logs Don't ask about performance impact. You'll never notice it... unless your users/application keep doing SET ROLE in a loop. Ask about anything else that's not clear to you. Regards. On Tue, Feb 21, 2017 at 9:36 PM, Art Kagel <art.kagel@gmail.com> wrote: > That is true John, but IB the OP wants to be able to query the roles set > for all current sessions from outside those sessions for audit purposes. > > 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 Tue, Feb 21, 2017 at 3:32 PM, John Miller iii <miller3@us.ibm.com> > wrote: > > > I believe there is a CURRENT=5FROLE operator, at least in version 12.10. > I > > am not sure which version this was > > introduced in. > > > > John F. Miller III > > miller3@us.ibm.com > > 503-747-1366 > > > > ids-bounces@iiug.org wrote on 02/21/2017 10:42:41 AM: > > > > > From: "Art Kagel" <art.kagel@gmail.com> > > > To: ids@iiug.org > > > Date: 02/21/2017 10:43 AM > > > Subject: Re: Current Role [38659] > > > Sent by: ids-bounces@iiug.org > > > > > > I don't think that this information about running sessions is available > > > anywhere else. > > > > > > 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 Tue, Feb 21, 2017 at 12:36 PM, DONALD KORTHAUER < > > > don.korthauer@ama-assn.org> wrote: > > > > > > > I've been asked by our security staff to produce a list of userids > that > > > > > > set a > > > > particular role. > > > > > > > > We have applications that routinely set this role which is ok, but we > > also > > > > have 'smart' users that connect with a 3rd party front end and query > > the > > > > database. These are the connections that we wish to discover. > > > > > > > > The onstat -g ses 0 works but produces lots of output which includes > > > > > > > > .... > > > > > > > > Current Role : rolename > > > > > > > > .... > > > > > > > > Is there a better way to get this information? > > > > > > > > I understand the relationship of sysusers to syseroleauth to > > > > sysmaster.sysessions, but that only shows who has permission to set a > > role, > > > > not that they have actually set it. > > > > > > > > Thanks! > > > > > > > > Environment: > > > > > > > > IBM Informix Dynamic Server Version 11.50.FC9W (w/ plans to upgrade > > soon!) > > > > > > > > SunOS 5.10 Generic=5F150400-20 sun4v sparc sun4v > > > > > > > > > > > > ************************************************************ > > > > ******************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a11442ee471ae4505490ebefe > > > > > > > > > > > ************************************************************ > > ***************= > > **** > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11442ee421a2070549112c54 > > > ************************************************************ > ******************* > 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... --001a1144c3402c8cc2054912baea