Trigger authorisation

[stoggers]

Hi,

If I have a trigger on TABLE_A that stores information in TABLE_B whenever an INSERT/DELETE or UPDATE takes place, what access, if any, is required to TABLE_B.

What I would like is for the user to be able to directly update TABLE_A but only read from TABLE_B.

Thanks,

Mike.

 

[blom0344]

How about revoking the rights on control,update,insert and delete for table B for that user?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[stoggers]

Hi,

Yes - I would revoke the rights to TABLE_B but what would happen when an update to TABLE_A (to which they have explicit rights) triggered an update to TABLE_B (to which they DON'T have explicit rights).

I am assuming that both TABLE_A & TABLE_B would be updated but this lead me to thinking that if rights were ignored if the update was in a trigger then I could apply a trigger to any table that I have update rights to in order to update any other table.

I must be missing something.

Thanks,

Mike.

 

[blom0344]

You are right. Just tested it. My suggestion will fail in updating table B through the trigger. Nasty one....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[stoggers]

Hi,

A bit of background...

TABLE_B is effectively an audit trail of changes to TABLE_A so I wouldn't be happy with update access to TABLE_B.

How about I don't insert directly into TABLE_B but do this via a stored procedure. I could then revoke access to TABLE_A but grant access to the stored procedure...

Mike.

 

[blom0344]

Funny, that is exactly the same set-up I was using the trigger for !!!!

However the trigger-action is always an insert, isn't it?
How about revoking just the update rights. If you allow the trigger to insert a real timestamp with the data or the ASCII code of the first , say , 3 positions you can easily validate if anyone has done a manual insert.

I used this for comparing items-lists:

ASCII(SUBSTR(RPAD(CODE,10,'A'),1,1)+
ASCII(SUBSTR(RPAD(CODE,10,'A'),2,1)+
ASCII(SUBSTR(RPAD(CODE,10,'A'),3,1)

The padding with the default 'A'is to ensure you do not run the ASCII function on a null value. The outcome is a meaningless value, but no user will come up with how to create the correct value for a given string!!!

Problem is that not all DB2 environments support the ASCII function though....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[stoggers]

Hi,

Just seen the following statement in a manual:

"The trigger body is executed under the authority of the creator (not the executor). This is similar to static and dynamic SQL with the bind parameter DYNAMICRULES(BIND) for plans and packages."

This seems to imply that if I (with UPDATE privileges to all tables) create a trigger on TABLE_A then another user with UPDATE access to TABLE_A but only SELECT on TABLE_B can invoke the trigger and implicitly update TABLE_B.

Comments?

Regards,

Mike.

 

[blom0344]

A simple test would confirm this I guess. Seems like a 100% fitting solution for your case.
I actually remember the statement you are refering to, but with datamarts I never have to worry about users manually manipulating data in the first place.....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[stoggers]

Hi,

Yes!!!!!

Just ran a test in UDB with the following scenario:

Two tables T1 & T2

Two users schema owner & me

I have update/insert access to T1 but only select on T2

Schema owner creates a trigger on T1 that updates T2

Now, when I insert into T1 the corresponding information appears in T2 and I can verify this by performing a select. I cannot, however, update T2.

This is exactly what I needed - the clue being the statement about the trigger create authorisation.

Just need confirmation that it works the same way on the mainframe.

Thanks,

Mike.