Db2 – Adding group rights in db2 for a stored procedure

access-controldb2permissionsusers

I have a list of user accounts on AIX server, where the db2 database resides in. The user accounts all belong to a specific group which I can confirm from aix shell.

User accounts work in DB2. However, I cannot seem to be able to add execution rights to the group so I wouldn't have to grant execution rights to individual user accounts.

I have tried this:

GRANT EXECUTE ON PROCEDURE MYSCHEMA.MYPROCEDURE TO GROUP MYGROUP WITH GRANT OPTION;

using a user that has created the stored procedures, and it succeeds. However, when trying to execute that procedure using another account that belongs to that group in aix server, I get

"useraccounthere" does not have the required authorization or privilege to perform
operation "EXECUTE" on object "MYSCHEMA.MYPROCEDURE".. SQLCODE=-551, SQLSTATE=42501,
DRIVER=4.14.113

Any ideas what could be wrong, or what I could check?

Best Answer

I ran into a similar problem and while this wasn't what happened here, this answer may help others running into this.

Make sure the OS group mygroup is all lowercase. DB2 on AIX does not recognize uppercase letters in the group name and will fail to validate a user belongs to a group if that group name has uppercase letters. eg:

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(mygroup) <- this works

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(MyGroup) <- this does not work

This is documented here: DB2 UDB Security

Related Solutions

Db2 – How to grant all privileges on all tables in a schema to a user in IBM DB2

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.

db2 grant dataaccess on database to user winuser1

If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be accomplished through scripting.

You could do the following

db2 -tnx "select distinct 'GRANT ALL ON TABLE '||
    '\"'||rtrim(tabschema)||'\".\"'||rtrim(tabname)||'\" TO USER winuser1;'
    from syscat.tables
    where tabschema = 'myschema' "  >> grants.sql

db2 -tvf grants.sql

This makes use of querying the system catalogs to dynamically generate a script to permission things. This is a lot of how we permission for users we don't want to give dataaccess to.

Here is a good page of the authorities for DB2.

DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM

Ok, I think I actually figured out how to handle this. I needed to grant the ability for my DBADM,SECADM to transfer to any ID through the group not the specific ID. In this way I get around the SECADM restrictions.

db2 grant setsessionuser on public to group DB2ADMNS

Since my ID db2admin is in this group, it can now switch to any ID (the public part). If I only wanted to be able to switch to that ID, I could have used:

db2 grant setsessionuser on user bobabob97 to group DB2ADMNS

So now I can perform

db2 set session_user=bobabob97

and it works.

To switch back I only need to run

db2 set session_user=system_user

Best Answer

Related Solutions

Db2 – How to grant all privileges on all tables in a schema to a user in IBM DB2

DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM

Related Question