User and Group permissions for Procs, Views and Triggers – Part 2
In the first part of this blog I outlined an issue I’d come across with the conferring of Role-based permissions to Groups for certain objects. Here’s one way around it and some further observations on the underlying issue.
Possible workaround
I did say I didn’t entirely like this option but it’s all I have been able to come up with so far.
If you want to enforce the structure of Users being allocated to Groups by your Windows or Unix Admins / Customers, you might have to keep the CREATE option up at the Database Administrator level. Once a View, Proc or whatever is in existence, the subsequent dynamic operations will be validated against the Group credentials and that can be operated by the Users who are members of appropriate Groups. This lets your User population stay fluid, whilst the Groups and Roles can stay relatively, if not entirely, static.
Why
Basically, as your DBA, I would like my involvement to involve only that part below the red line. And the extra layer of organisation conferred by having Groups eases the adding, removing and altering of permissions that a User can have.
If you cannot operate in this fashion because Groups are not considered when Creating objects like Views, Procs, Triggers etc. this whole pack of cards come tumbling down. The point that our contact in Toronto made was that this is “ because we do not know when a user would lose membership in a group since it is outside Db2, and we wouldn’t know when to invalidate those objects”.
But surely that is true of any privilege? If I have CREATEIN and SELECTIN on schema LogBook and I create a view which selects from a LOGBOOK table, then the permissions for both could be checked as the object is created. The issue then is how to make sure it is still valid when next executed (or instantiated I guess in the case of a view).
But if my SELECT privileges have been revoked since the CREATE, by my ID having been removed from the Group with those privileges, wouldn’t that appear at execution time?
An experiment
Let’s try it out.
We’ll manage the membership of Groups via the Windows Computer Management doo-hickey. Here you can see that
- user DEV_2 is a member of the LOGBOOK_DEV Group and
- LOGBOOK_DEV has the CR8_EXEC_ROLE
- which gives pretty full access to all objects in the LOGBOOK schema.
- At this point I take DEV_2 out of the LOGBOOK_DEV Group and make it a member of Db2USERS.
- I also give Db2USERS the AIRCRAFT_ONLY Role
- This means that DEV_2 can still use that View
- But if I remove DEV_2 from the Db2USERS group, thereby removing from the AIRCRAFT_ONLY Role that gave it SELECT access to the FLOWN_RECENTLY View, it will fail
Conclusion
I think that this indicates that post-CREATE validation can be done and that you should be able to CREATE a VIEW and check that your required SELECT access is valid at CREATE time, knowing that any subsequent changes to Group membership will be reflected in what the VIEW is trying to do.
But I am not privy to what Db2 does under the covers and I am not sure (yet) that this same experiment would apply to other objects.
As always, I’m happy to discuss so please get in touch with me
E-mail: Mark.Gillis@triton.co.uk
Leave a Reply