This is an issue that I stumbled over comparatively recently. It looked to me like a bug, but if you track down the chapter and verse from IBM, it’s “working as advertised” (see https://www.ibm.com/docs/en/db2/11.5?topic=roles-compared-groups ).
The issue is that you might find that a User, who is a member of a Group with all required privileges and authorities, cannot create a View, Stored Procedure or a number of other objects, because “Privileges and authorities granted to groups are not considered ” when creating those objects.
Let me outline the ‘issue’ and show one way I can think of around this, although I can’t honestly say it’s one I can wholeheartedly endorse.
The Background
- I’ve got a TEST database I’ve just created on my local machine (Db2 v11.5.7000.1973 running on Windows 10 Pro).
- There are 2 users: DEV_1 and DEV_2.
- DEV_1 is a member of the Group Db2USERS
- DEV_2 is a member of a Group called LOGBOOK_DEV
- There is a ROLE called DEV_ROLE and it has SELECTIN on schema LOGBOOK
- Another ROLE called CR8_Exec_Role has SELECTIN, DELETEIN, UPDATEIN, INSERTIN, CREATEIN, DROPIN and EXECUTEIN ON schema LogBook
- DEV_ROLE is granted to USER DEV_1
- CR8_Exec_Role is granted to GROUP LOGBOOK_DEV
That’s a lot to visualise so here’s the output from a query against ROLEAUTH
The Problem
What I want to do is Create a View on a table I have called AIRCRAFT. I want to do this as DEV_2 which means I have all schema access up to and including CREATEIN; not directly but via the Group LOGBOOK_DEV. I can certainly run the SQL that forms the basis of my view:
But if I try and create a view based on that SQL, this is what happens
In other words, DEV_2, as a member of the Group LOGBOOK_DEV, does have the privilege to CREATE the View, but is getting an error saying it does not have the privilege to SELECT from the AIRCRAFT table. Now we know that’s not true because we just ran that SELECT.
What this error should be saying is that “you might have the required authorisation but if it’s via a GROUP we can’t check that. So, we’re assuming you don’t”.
My colleague Iqbal Goralwalla, who many of you will know from IDUG and various other Db2 outings, managed to run this past some of his old colleagues at IBM Toronto and got this response (my highlighting):
“Any time Db2 needs to record a dependency on the user maintaining a privilege for the object to continue to exist (such as select on base table for a view), we do not consider groups. 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.
Note that this is only for the CREATE statements for those objects. Any dynamic use of those objects, calling the proc, selecting from the view etc, will consider group membership.”
IBM solution
What IBM suggest is to “Avoid this restriction by using roles instead of groups.”. But that doesn’t really address the issue: what we’re trying to do here is to avoid allocating privileges and authorities to specific users, and we are using Roles to try and accomplish this.
What you don’t want to do is to get dragged into conferring or removing privileges and authorities for each and every user as they arrive, leave or change jobs. That is best handled on the customer side by adding or removing Users within Groups and giving the Groups the Roles that confer the appropriate access.
We can, of course, do this via a USER: for instance if I connect as DEV_1 (which has SELECTIN on the LOGBOOK schema), I can create the View.
But that demands that I give the access directly to the User. And I want to avoid that.
Revalidating and access
This doesn’t prevent the user whose access is conferred by being a member of a group (DEV_2 via LOGBOOK_DEV) from operating the View (or Proc, or whatever it is that has been created with direct user access).
And DEV_2 can even Revalidate the View after it has become inoperative (this demands that the LOGBOOK_DEV group has CONTROL access to the AIRCRAFT table).
DEV_1 doesn’t have that access, so it gets this
DEV_2, via the Group access, can Revalidate the Proc even though it can’t CREATE it.
When our contact at the Toronto Labs was referring to “dynamic use” they meant this sort of operation as well as simply referencing the View.
Possible Solutions
That is an outline of the problem and issues I think exist. In the next part of this blog, I’ll take a look at ways of getting around this.