 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Ingres Topics / December 2006Audit trails and users...
This is a slightly long story. Bear with me. For "in house" applications, we use automatically generated rules/procedure pairs to audit changes to data. This is to say that a rule fires after an insert/update of the data and writes what amounts to "select 'now', user, * " to a shadow table with the appropriate columns. It is a mechanism that I like since it's handled server side and is transparent to the applications (nor can they "forget" to do anything). It is difficult to evade, and happens even if someone reaches in and makes an "ad hoc" change. Which happens occasionally. The only down-side is that it relies on "user" being correctly derived by INGRES. This is not normally a problem because the users all have accounts on the server as they are fully within our firewall. However, my fly in the ointment is that, for a new project, the bright idea is to have web based authentication - user's will not have operating system accounts, but will have soft accounts, created by a web interface. The aim is that they manage their own accounts and have no direct interaction with the server. Applications then log on, check the user's credentials against the web system and get on with life. However, as far as I can make out, they must all now log into INGRES as some mythical generic user (e.g. "webuser"), and the "select user" approach will not acquire their "soft" log in name. Which is a shame, as I'm rather fond of the rules/procedures approach. I *can* put the auditing into the client code instead, but I'd rather not. It's hassle, and not as robust. However, I cannot see any way of "caching" the "soft" username. My first stab was to cache the username into a session temporary table and use that in the procedure, but session tables are not visible to procedures (which is daft, but that's another story). My second stab was to wonder whether the "session ID", which is visible to the procedure, could be used somehow. However, some research suggests that the same session ID can be reused (which is bad), and the same session ID can be active simultaneously on two servers with shared caches, which is worse. So, if anyone's cracked this problem perhaps they could point me in the right direction? We take exactly the same approach using rules and procedures to create records in audit tables that mirror the main tables. We have put an updated_by_user_id field on the table that the application has been designed to populate with the 'correct' user id. The application is the only thing that knows who the user is, in a three tier environment Ingres can't know. Our audit tables then use this field to record the 'changee'. If the application doesn't populate it then the audit is unfortunately inaccurate. > We take exactly the same approach using rules and procedures to create > records in audit tables that mirror the main tables. [quoted text clipped - 5 lines] > Our audit tables then use this field to record the 'changee'. If the > application doesn't populate it then the audit is unfortunately inaccurate. Jason, thanks for the reply. That was the conclusion I was coming to, but I was hoping someone would point me towards some nook that I could store the information in so that a procedure could get at it "properly". >That was the conclusion I was coming to, but I was hoping someone would >point me towards some nook that I could store the information in so that >a procedure could get at it "properly". I feel your pain Mike. So, are programmers evil? OK, here's an easier one. Which one is your corporate asset? The data or the particular bit of plumbing that is fashionable this year? Roy >> That was the conclusion I was coming to, but I was hoping someone would >> point me towards some nook that I could store the information in so that [quoted text clipped - 6 lines] > > Roy <OBVIOUSLY> </OBVIOUSLY> >> That was the conclusion I was coming to, but I was hoping someone would >> point me towards some nook that I could store the information in so that >> a procedure could get at it "properly". > > I feel your pain Mike. So, are programmers evil? I believe it to be a part of the job description.... > OK, here's an easier one. Which one is your corporate asset? The > data or the particular bit of plumbing that is fashionable this year? Ah, but my job is to try and connect the two bits of that puzzle ;-), and I think Karl may just have pointed me in the right direction. -- Mike Lay email: newstrap @ Rees<secondpartname>.+.com (replace obvious symbols;-) >>> That was the conclusion I was coming to, but I was hoping someone would >>> point me towards some nook that I could store the information in so that [quoted text clipped - 8 lines] > > Ah, but my job is to try and connect the two bits of that puzzle ;-), Of course it is, and mine too. My point is just that these kinds of problems should ring an alarm bell with the product architects. I never met a programmer who didn't think more code was the answer, and in the short term it's a quick-fix. But in the long term the servers should be providing better integration. Since I fired off the above squib last week I have noticed an item in the Ingres 2007 roadmap that seems to suggest there might be a good fix coming Real Soon. Roy Hi Mike Quick idea, make the generic account with security admin privilege then get it to connect with the -u<user_id> flag. We should then be able to pick up the real user from iidbconstants. The -u<user_id> should be part of a connection string passed in by JDBC? I havent tested this. But it may work... Marty > This is a slightly long story. Bear with me. > [quoted text clipped - 43 lines] > Info-ingres mailing list Info-ingres@cariboulake.com > http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres > Quick idea, make the generic account with security admin privilege then > get it to connect with the -u<user_id> flag. We should then be able to [quoted text clipped - 4 lines] > > I havent tested this. But it may work... I thought about that, but, since the system is externally visible, it gives that account more "power" than I like the look of. The user ID also then has to exist on the INGRES system. This could be achieved quite easily though... I have used exactly the same approach in the past but can you not use journalling and auditdb ? >> Quick idea, make the generic account with security admin privilege then >> get it to connect with the -u<user_id> flag. We should then be able to [quoted text clipped - 9 lines] > The user ID also then has to exist on the INGRES system. This could be > achieved quite easily though... >For "in house" applications, we use automatically generated rules/procedure pairs to audit changes to data. This is to say that a rule fires after an insert/update of the data and writes what amounts to "select 'now', user, * " to a shadow table with the appropriate columns. >[snip] >Applications then log on, check the user's credentials against the web system and get on with life. However, as far as I can make out, they must all now log into INGRES as some mythical generic user (e.g. "webuser"), and the "select user" approach will not acquire their "soft" log in name. > >Which is a shame, as I'm rather fond of the rules/procedures approach. I *can* put the auditing into the client code instead, but I'd rather not. It's hassle, and not as robust. However, I cannot see any way of "caching" the "soft" username. My first stab was to cache the username into a session temporary table and use that in the procedure, but session tables are not visible to procedures (which is daft, but that's another story). Not really daft, although it is seriously annoying. Problem is you can't compile a DB procedure against a table that may not exist, or may not have the same column structure, when the dbp is actually executed. It's not theoretically impossible, but it would require some serious stomping on the existing DBP parsing mechanisms; you'd need to track session temp dependencies and reparse the DBP from scratch for every new temp table instantiation. >My second stab was to wonder whether the "session ID", which is visible to the procedure, could be used somehow. If you use both the session ID and dbmsinfo('ima_server'), you should get a combo that is unique installation wide at least for a point in time. If you can then arrange to have a soft name : session correspondence table that's filled in at connect time, you can join to that crossref table in the DBP. The connect time insert would have to be aware that there might be an old (obsolete) entry for that session+ima_server, and simply overwrite it. I was hoping that the session description was available via DBMSINFO, but apparently not. You can get it from IMA but that probably doesn't help you; my recollection is that IMA registrations can only be done in a database owned by $ingres. Karl >> For "in house" applications, we use automatically generated rules/procedure pairs to audit changes to data. This is to say that a rule fires after an insert/update of the data and writes what amounts to "select 'now', user, * " to a shadow table with the appropriate columns. >> [snip] [quoted text clipped - 9 lines] > you'd need to track session temp dependencies and reparse the DBP > from scratch for every new temp table instantiation. I'd be quite happy to have a copy of the temporary table "loafing about" as I create the procedure, and for the procedure to whine pathetically if I got it wrong ;-) >> My second stab was to wonder whether the "session ID", which is visible to the procedure, could be used somehow. > [quoted text clipped - 5 lines] > there might be an old (obsolete) entry for that session+ima_server, > and simply overwrite it. Ah, I think this may be what I'm looking for - I hadn't made the connection with IMA_SERVER. Ten points to Karl. I think this will do the trick. > I was hoping that the session description was available via DBMSINFO, > but apparently not. You can get it from IMA but that probably doesn't > help you; my recollection is that IMA registrations can only be done > in a database owned by $ingres. I also looked to see if you could get the session description via DBMSINFO (it's available in things like IPM) and came to the same conclusion. -- Mike Lay email: newstrap @ Rees<secondpartname>.+.com (replace obvious symbols;-) >> Not really daft, although it is seriously annoying. Problem is you >> can't compile a DB procedure against a table that may not exist, [quoted text clipped - 7 lines] > as I create the procedure, and for the procedure to whine pathetically if > I got it wrong ;-) There is a perfectly good ANSI standard solution to the problem. The standard describes three types of temporary table. Currently Ingres implements Type I temporary tables, which are intended to be entirely ephemeral. What you (and the rest of us) probably want, and is perhaps not very hard to implement, is Type II temporary tables. The definition of the table is permanent, but the body is ephemeral. This would ensure that DBPs work or fail gracefully as appropriate just as they do when a real table is missing. Roy |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.