MVSFORUMS.com

[bauer]

Hi all,

is it possible (DB2 Catalog table ?) to get the information dependig on which access permit's a view, index .... was created ????


Background:

User A passes permit for table TEST to user X
User X creates view for table TEST
User B passes permit for table TEST to user X
User A revokes permit for table TEST or user A will be deleted

Now, User X has permit for table TEST (granted by User B), but view for table TEST is automaticly dropped.

How to avoid the automatic drop for the view ?

Any comments ?


Regards,
bauer

[kolusu]

bauer,

You can try this. Whenever a view is created a row is inserted into SYSIBM.SYSTABLES and into SYSIBM.SYSTABAUTH tables to record the owner's privileges on the view.

See if you can update these 2 tables to put in the new user as creator. Once updated I don't think it will be a problem even if you dropped the old user.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[bauer]

kolusu,

thank you for your answer.

This solution doesn't work. I don't like to change the creator of the table.

The problem is:

1) UserA creates table test.table1
2) grant select on test.table1 to UserX (executed by UserA)
3) create view userx.myview (executed by UserX)
4) grant select on test.table1 to UserX (executed by UserB)
5) revoke select on test.table1 (executed by UserA)

Result is:

1) UserX has select permit for test.table1
2) userx.myview has gone


How to avoid the automatioc drop of view userx.myview ??? UserX has still permit for test.table1 ?!

Where stores Db2 the information, that userx.myview was created depeding on the first issued grant ? This informations seems to be used to drop userx.myview during executiun of revoke select on test.table1 from userx executed by userA ?

any additional comments ?

Wasn't there any post who to drop admin keys without any lost of tables created by the admin key ??? I cann't find this post.

regrads,
bauer