User auditing
Stuart asked how to audit data changes per individual user, given all connections currently use one shared OS account, and wanted to move to Active Directory/domain users (OpenSUSE Linux, Informix 11.70 heading to 12.10). No single fix, but clear guidance: configure PAM with pam_ldap against AD for external authentication (Fernando's blog posts given, plus tips — test remote connections, errors always show as 1809); alternatively use internal database users, which Ben called easier. For auditing, options discussed were Informix's native onaudit (user- and object-level, but with limited output and possible onstat slowdowns), sysdbopen logon triggers, custom triggers, or the paid IBM InfoSphere Guardium. No feedback on the final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I want to know if something is possible. We would like to audit data changes down to a user level. Currently our systems all connect to the Informix database as an operating system user. We want to change to using domain users to centralise user administration. I am sure the solution is there but wondered what others have used in the Informix world to achieve this.
Your question is a bit confusing (for me at least). You mention auditing and external users. These are completely separate topics... But if I interpreted your question correctly you're saying all your users share the same OS account. And that you would like to have individual user accounts, preferably using "domain" users? In order to provide a better answer we would need to know: 1- The OS platform you're using 2- The Informix version you're using 3- The external authentication mechanism you'd like to use Depending on the answers and your confirmation about the requirements, I think we'll be able to point you in the right direction. To the best of my knowledge Informix on windows can authenticat Windows "domain" users. There are some configurations that can be done to check against different domains. If you're using Informix on a Linux/Unix platform you can integrate the user authentication with an LDAP (or Active Directory) through PAM (pluggable authentication modules). Recent versions of Informix can even use internal database users, although I think it still needs more features to be really useful. In any case, after you have individual user accounts there are several ways to address the auditing... native auditing, some people use triggers and there are external solutions that work with Informix (IBM InfoSphere Guardium for example) Regards On Wed, Feb 11, 2015 at 9:30 AM, STUART STEPHENS < stuart.stephens@monitorsoft.com> wrote: > I want to know if something is possible. > > We would like to audit data changes down to a user level. Currently our > systems all connect to the Informix database as an operating system user. > We > want to change to using domain users to centralise user administration. > > I am sure the solution is there but wondered what others have used in the > Informix world to achieve this. > > > > ******************************************************************************* > 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... --047d7b5d41da6ef7c9050ecce71c
Hello Fernando, thank you for replying. 1- The OS platform you're using -- OpenSuSE 13.2 Linux 2- The Informix version you're using -- Currently 11.70 but we will soon be upgrading to 12.10 3- The external authentication mechanism you'd like to use -- Active Directory/domain users Our objective is to audit data changes and we currently use triggers which records our application's user. We would like to extend or replace that by recoding the domain user that made the change.
Ok... If you're using Linux you'll need to configure PAM to connect to the LDAP (AD acting as LDAP). This is done through the use of pam_ldap module which you probably have already in your OpenSUSE Linux. The way to do it can be a bit confusing... I'd suggest the following articles I wrote: http://informix-technology.blogspot.com/2007/11/informix-user-authentication-pam -for.html http://informix-technology.blogspot.pt/2008/02/informix-user-authentication-pam- for.html Please test on a non-production or at least with a different listener. Some alerts: 1- Never test with a local connection. Always attempt with a remote connection 2- The error you'll get is always 1809. This is nasty and makes it difficult to debug, but the way PAM works don't allow better error exposure from Informix Please send feedback as you progress. With specific issues it will be easier to help. Regards On Wed, Feb 11, 2015 at 10:03 AM, STUART STEPHENS < stuart.stephens@monitorsoft.com> wrote: > Hello Fernando, > > thank you for replying. > > 1- The OS platform you're using > -- OpenSuSE 13.2 Linux > > 2- The Informix version you're using > -- Currently 11.70 but we will soon be upgrading to 12.10 > > 3- The external authentication mechanism you'd like to use > -- Active Directory/domain users > > Our objective is to audit data changes and we currently use triggers which > records our application's user. We would like to extend or replace that by > recoding the domain user that made the change. > > > > ******************************************************************************* > 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... --001a1138ea2425868e050ecdb0e0
Thank you Fernando I will take a look
You can enable secure auditing at the user level, yes. 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, Feb 11, 2015 at 4:30 AM, STUART STEPHENS < stuart.stephens@monitorsoft.com> wrote: > I want to know if something is possible. > > We would like to audit data changes down to a user level. Currently our > systems all connect to the Informix database as an operating system user. > We > want to change to using domain users to centralise user administration. > > I am sure the solution is there but wondered what others have used in the > Informix world to achieve this. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c348644b55a5050ece447b
Hi Stuart, You can audit at the user level. Whether the things you can audit and the way you report on them are even remotely useful is a completely different matter. :-) I did once do a fairly comprehensive auditing exercise for a bank, if you want to chat through stuff... -- Regards Spokey > On 11 Feb 2015, at 09:30, STUART STEPHENS <stuart.stephens@monitorsoft.com> wrote: > > I want to know if something is possible. > > We would like to audit data changes down to a user level. Currently our > systems all connect to the Informix database as an operating system user. We > want to change to using domain users to centralise user administration. > > I am sure the solution is there but wondered what others have used in the > Informix world to achieve this. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Way to go is IBM Guardium Sent from my iPhone > On 11 Feb 2015, at 17:45, Spokey Wheeler <spokey.wheeler@gmail.com> wrote: > > Hi Stuart, > > You can audit at the user level. Whether the things you can audit and the way > you report on them are even remotely useful is a completely different matter. > :-) > > I did once do a fairly comprehensive auditing exercise for a bank, if you want > to chat through stuff... > > -- > Regards > Spokey > >>> On 11 Feb 2015, at 09:30, STUART STEPHENS <stuart.stephens@monitorsoft.com> >> wrote: >> >> I want to know if something is possible. >> >> We would like to audit data changes down to a user level. Currently our >> systems all connect to the Informix database as an operating system user. We >> want to change to using domain users to centralise user administration. >> >> I am sure the solution is there but wondered what others have used in the >> Informix world to achieve this. > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > Stefan Sammut Manager - Solutions & Innovation [PTL Ltd] Nineteen Twenty Three, Valletta Road Marsa, MRS 3000, MT T +35621445566 +356 99405444 stefan.sammut@ptl.com.mt | www.ptl.com.mt<http://www.ptl.com.mt>; [Facebook]<https://www.facebook.com/PTLMalta/app_349313058487732>; [LinkedIn] <https://www.linkedin.com/company/ptl-ltd?trk=tyah&trkInfo=tarId%3A1401716999276 %2Ctas%3APTL%2Cidx%3A2-3-8> [Twitter] <https://twitter.com/PTL_Malta>; [Youtube] <https://www.youtube.com/channel/UCuXrJBO_54kd9HttG8S89iQ/feed?view_as =public> [Google Plus] <https://plus.google.com/+PTLMalta>;
I would "kill" for some more auditing features... We provide great things like complete role separation out of the box with no extra costs.... But on the other hand the auditing output is somehow limited and I've always had issues understanding the reasons not to implement some of the features like: - Have a "end session" event - Have the SID on the audit trail... we have PID but PID is totally useless for Java clients and without SID there's no way to cross reference with the logical logs - ReadROW, InsertROW, UpdateROW and DeleteROW generated information is clearly not enough... Either ROWID or the Primary Key (if it's an integer) is very poor from an audit perspective With version 11.70 we introduced the ability to define the above operations also on an object level, besides the user, which was a great step forward to make it much more usable. But that was the last change we did... But don't get me started on auditing... :) On Wed, Feb 11, 2015 at 4:44 PM, Spokey Wheeler <spokey.wheeler@gmail.com> wrote: > Hi Stuart, > > You can audit at the user level. Whether the things you can audit and the > way > you report on them are even remotely useful is a completely different > matter. > :-) > > I did once do a fairly comprehensive auditing exercise for a bank, if you > want > to chat through stuff... > > -- > Regards > Spokey > > > On 11 Feb 2015, at 09:30, STUART STEPHENS < > stuart.stephens@monitorsoft.com> > wrote: > > > > I want to know if something is possible. > > > > We would like to audit data changes down to a user level. Currently our > > systems all connect to the Informix database as an operating system > user. We > > want to change to using domain users to centralise user administration. > > > > I am sure the solution is there but wondered what others have used in the > > Informix world to achieve this. > > > > > > > > ******************************************************************************* > > 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... --047d7b2e405a7ef53a050ed39207
True... Guardium can do everything you need from an auditing perspective... and can do it virtually to any database. But naturally it means extra cost. On Wed, Feb 11, 2015 at 5:07 PM, Stefan Sammut <stefan.sammut@ptl.com.mt> wrote: > Way to go is IBM Guardium > > Sent from my iPhone > > > On 11 Feb 2015, at 17:45, Spokey Wheeler <spokey.wheeler@gmail.com> > wrote: > > > > Hi Stuart, > > > > You can audit at the user level. Whether the things you can audit and the > way > > you report on them are even remotely useful is a completely different > matter. > > :-) > > > > I did once do a fairly comprehensive auditing exercise for a bank, if you > want > > to chat through stuff... > > > > -- > > Regards > > Spokey > > > >>> On 11 Feb 2015, at 09:30, STUART STEPHENS > <stuart.stephens@monitorsoft.com> > >> wrote: > >> > >> I want to know if something is possible. > >> > >> We would like to audit data changes down to a user level. Currently our > >> systems all connect to the Informix database as an operating system > user. > We > >> want to change to using domain users to centralise user administration. > >> > >> I am sure the solution is there but wondered what others have used in > the > >> Informix world to achieve this. > > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > Stefan Sammut > Manager - Solutions & Innovation > > [PTL Ltd] > > Nineteen Twenty Three, Valletta Road > Marsa, MRS 3000, MT > > T +35621445566 +356 99405444 > stefan.sammut@ptl.com.mt | www.ptl.com.mt<http://www.ptl.com.mt>; > > [Facebook]<https://www.facebook.com/PTLMalta/app_349313058487732>; > [LinkedIn] > < > https://www.linkedin.com/company/ptl-ltd?trk=tyah&trkInfo=tarId%3A1401716999276% 2Ctas%3APTL%2Cidx%3A2-3-8 > > > [Twitter] <https://twitter.com/PTL_Malta>; [Youtube] > < > https://www.youtube.com/channel/UCuXrJBO_54kd9HttG8S89iQ/feed?view_as=public > > > [Google Plus] <https://plus.google.com/+PTLMalta>; > > > > ******************************************************************************* > 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... --e89a8fb1ec5ede51fe050ed398d3
Hi Stuart,
We need to catch up :)
Fernando has already linked to his excellent post on PAM. You might want to
consider, as you're on 11.70, creating users in the db unless you have your
Linux servers integrated into active directory already. It's a lot easier :)
The only advantage of integrating into AD is that users' passwords are the
same everywhere and password policies and so on are enforced at the AD level.
However, AD integration only does the authentication and doesn't remove the
requirement to grant database-level privileges to users unless your database
has "grant connect to public" and similar grants at table level.
Once you have users logging in using their own IDs you can look at using
onaudit. Some of its shortcomings can be addressed by using a logon trigger
(public.sysdbopen) but you need to make sure the logon trigger never fails or
no-one can access the database.
Onaudit can work nicely but it does have the side effect of slowing down the
response of the onstat commands, which can be a problem if you have a lot of
monitoring using these. Certainly it's possible to log all updates and access
by non-application users using onaudit.
Ben.