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.

Jason wrote:
> 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.

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".

At 2:30 PM +0000 12/1/06, Mike Lay wrote:
>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

On Fri, 01 Dec 2006 15:47:57 +0000, Mike Lay
<ctsu0009@herald.ox.ac.uk> wrote:


>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

Roy Hann wrote:
>> 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
<OBVIOUSLY>

</OBVIOUSLY>

Karl & Betty Schendel wrote:
> At 2:30 PM +0000 12/1/06, Mike Lay wrote:
>> 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.

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.
>
> 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.

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;-)

Roy Hann wrote:
> On Fri, 01 Dec 2006 15:47:57 +0000, Mike Lay
> <ctsu0009@herald.ox.ac.uk> wrote:
>
>
>> 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;-)

"Mike Lay (News)" <mike@nospam.com> wrote in message
news:4576c72e$0$8731$ed2619ec@ptn-nntp-reader02.plus.net...
> Karl & Betty Schendel wrote:
>> 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.
>
> 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 ;-)

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

"Mike Lay (News)" <mike@nospam.com> wrote in message
news:4576c7cf$0$8731$ed2619ec@ptn-nntp-reader02.plus.net...
> Roy Hann wrote:
>> On Fri, 01 Dec 2006 15:47:57 +0000, Mike Lay
>> <ctsu0009@herald.ox.ac.uk> wrote:
>>
>>
>>> 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 ;-),

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