Windows OS
--
Questions
--
Followers
Top Experts
Using AD Groups with DB2
DB2 10.5 on Windows Server 2012 R2
I am trying to get DB2 to work with AD groups in windows and am having some issues. Â I have a active directory group named "DB2 Users". Â It has 3 people in it. Â I want to grant access to DB2 based on that AD group so that if new people need access I can just add them to the group and not have to worry about it. Â I do this all the time in MS SQL Server and from what I have read, I should be able to do it in DB2.
I can grant access to the 3 users if I create a ROLE (ReadOnlyUsers) and then assign them to that ROLE using their network ID and assign select rights to that ROLE.
I had thought  I could create a GROUP and assign the AD group to it and grant it privileges like I did with the ROLE but that does not work.  I can create a GROUP called TESTUSERS and assign it privileges but I can't seem to figure out how to assign the AD group to the DB2 GROUP. Â
What am I doing wrong?
Thanks!!!
Jim
I am trying to get DB2 to work with AD groups in windows and am having some issues. Â I have a active directory group named "DB2 Users". Â It has 3 people in it. Â I want to grant access to DB2 based on that AD group so that if new people need access I can just add them to the group and not have to worry about it. Â I do this all the time in MS SQL Server and from what I have read, I should be able to do it in DB2.
I can grant access to the 3 users if I create a ROLE (ReadOnlyUsers) and then assign them to that ROLE using their network ID and assign select rights to that ROLE.
I had thought  I could create a GROUP and assign the AD group to it and grant it privileges like I did with the ROLE but that does not work.  I can create a GROUP called TESTUSERS and assign it privileges but I can't seem to figure out how to assign the AD group to the DB2 GROUP. Â
What am I doing wrong?
Thanks!!!
Jim
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Did you post install db2extsec.exe?
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.qb.server.doc/doc/t0011930.html
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.qb.server.doc/doc/t0011930.html
Hi Jim,
There's a ton of documentation available on this. Â I've not tried it, nor do I have admin rights to AD in my current environment so I can't offer much good advise here except that the solution should be somewhere in the documentation chain starting here:
 https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0006010.html
Good Luck!
There's a ton of documentation available on this. Â I've not tried it, nor do I have admin rights to AD in my current environment so I can't offer much good advise here except that the solution should be somewhere in the documentation chain starting here:
 https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0006010.html
Good Luck!
I have been reading the documentation all day and I am still clueless. Â I read a document and then have to read 2 others to try to understand the first one and it just goes downhill from there. Â The problem is that our system is on Windows and a lot of the documentation is for Linux or z/os.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
The initial link I sent states how to add Active Directory Groups to DB2. Â The caveat being if you did not enable the DB2 extension during initial installation you simply turn it on post-install. Â Did you review it?
Did you post install db2extsec.exe?
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.qb.server.doc/doc/t0011930.html
Did you post install db2extsec.exe?
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.qb.server.doc/doc/t0011930.html
Yes, and ran the db2extsec.exe again just in case but I have the DB2ADMIN and DB2USER groups on the local server and added the AD group to the DB2USER group. Â I also have those two groups in DB2.
When I add the AD group the user can connect but has no other permissions. Â It says "The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation."
Same when I add the AD group to the local DB2ADMIN group as well.
Jim
When I add the AD group the user can connect but has no other permissions. Â It says "The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation."
Same when I add the AD group to the local DB2ADMIN group as well.
Jim
Okay. Â
This sounds like access permission at NTFS level to the directory structure. Â The articles don't do a good job of explaining this IMO but this article here although it does not give you the specifics such as needing to change the NTFS Security permissions that is in fact what it is stating.
But there are other permissions as well being those Groups whether server or domain are still Windows specific and without granular permission sets have no effect on DB2.
For example, this one in addition to NTFS which is under your Local Group Policy or Domain GPO's for that server hosting DB2. Â Easier to change the local GPO policy rather than creating Central Policy hosted policy for one server. Â
Access this computer from the network (SeNetworkLogonRight) = Add DB2User to this "right"
(And)
Create global objects (SeCreateGlobalPrivilege)
Per: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0023391.html
Unless you have done this step, I believe this will solve or get us closer.
This sounds like access permission at NTFS level to the directory structure. Â The articles don't do a good job of explaining this IMO but this article here although it does not give you the specifics such as needing to change the NTFS Security permissions that is in fact what it is stating.
But there are other permissions as well being those Groups whether server or domain are still Windows specific and without granular permission sets have no effect on DB2.
For example, this one in addition to NTFS which is under your Local Group Policy or Domain GPO's for that server hosting DB2. Â Easier to change the local GPO policy rather than creating Central Policy hosted policy for one server. Â
Access this computer from the network (SeNetworkLogonRight) = Add DB2User to this "right"
(And)
Create global objects (SeCreateGlobalPrivilege)
Per: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0023391.html
Unless you have done this step, I believe this will solve or get us closer.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
OK, I believe the extended security is installed. Â From what I have read, I should be able to add a network AD group to the server group DB2USERS and then the members of that AD group should have ...
DB2USERS
Read and Execute access for all Db2 objects located in the installation and instance directories, but no access to objects under the database system directory and limited access to IPC resources.
This is not working. Â Members of the AD group can connect to DB2 via Toad or DBeaver but they don't have any permissions to tables. Â If they run SELECT * FROM SCHEMA1.TABLE1 they get a messages saying ...
SQL Error [42501]: The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Â Authorization ID: "Bob". Â Operation: "SELECT". Object: "SCHEMA1.TABLE1".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.19.66
So, if "Bob" can connect via Toad when his AD group is part of the local DB2USERS group, why can't he select from the table?
Am I misunderstanding what is granted by DB2USERS group?
Jim
DB2USERS
Read and Execute access for all Db2 objects located in the installation and instance directories, but no access to objects under the database system directory and limited access to IPC resources.
This is not working. Â Members of the AD group can connect to DB2 via Toad or DBeaver but they don't have any permissions to tables. Â If they run SELECT * FROM SCHEMA1.TABLE1 they get a messages saying ...
SQL Error [42501]: The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Â Authorization ID: "Bob". Â Operation: "SELECT". Object: "SCHEMA1.TABLE1".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.19.66
So, if "Bob" can connect via Toad when his AD group is part of the local DB2USERS group, why can't he select from the table?
Am I misunderstanding what is granted by DB2USERS group?
Jim
Not a misunderstanding, we are simply not finished. Â We'll get there. Â I'll respond momentarily. Â I've been through this documentation prior but not recently so I need some time to narrow down the permission issue.
I appreciate the help greatly. Â Thank you.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
No problem. Â
I actually overlooked this step, rule 1 - never assume, did you grant the appropriate permissions in DB2 to the DB2USERS?
http://www-01.ibm.com/support/docview.wss?uid=swg21224422
AND here, under "Authorization"
https://www.ibm.com/developerworks/data/library/techarticle/dm-0508wasserman/index.html
You can also use db2 grant <>Â as in this example but replace with correct permission per prior article.
https://www-01.ibm.com/support/docview.wss?uid=swg21989698
And
https://dba.stackexchange.com/questions/43795/how-to-grant-all-privileges-on-all-tables-in-a-schema-to-a-user-in-ibm-db2
To refine the GRANT permissions we can use the diagnostic log located in the vicinity of:
C:\Program Files\IBM\SQLLIB\DB2\db2di
I actually overlooked this step, rule 1 - never assume, did you grant the appropriate permissions in DB2 to the DB2USERS?
http://www-01.ibm.com/support/docview.wss?uid=swg21224422
AND here, under "Authorization"
https://www.ibm.com/developerworks/data/library/techarticle/dm-0508wasserman/index.html
You can also use db2 grant <>Â as in this example but replace with correct permission per prior article.
https://www-01.ibm.com/support/docview.wss?uid=swg21989698
And
https://dba.stackexchange.com/questions/43795/how-to-grant-all-privileges-on-all-tables-in-a-schema-to-a-user-in-ibm-db2
To refine the GRANT permissions we can use the diagnostic log located in the vicinity of:
C:\Program Files\IBM\SQLLIB\DB2\db2di
So while working on this, I have found that I do not understand how DB2 is authorizing users. Â I have two users, User1 and User2. Â User1 has been granted full system admin rights to the windows server (in the ADMINISTRATOR and DBADMN groups) and full rights in DB2 ( DBADM,CREATETAB,BINDADD,CO
User2 had rights similar to User1 but now has been completely removed from the windows server (is in no windows group) and removed from DB2 (all privileges revoked).
I can log into DB2 with User1 with TOAD and do everything as expected.
I would expect to not be able to log in with User2, but I still can and I can still select from tables. Â
WHY? Â It makes no sense to me.
Any thoughts?
User2 had rights similar to User1 but now has been completely removed from the windows server (is in no windows group) and removed from DB2 (all privileges revoked).
I can log into DB2 with User1 with TOAD and do everything as expected.
I would expect to not be able to log in with User2, but I still can and I can still select from tables. Â
WHY? Â It makes no sense to me.
Any thoughts?
Hi Jim,
In your experimenting, did you happen to create that user locally? Â It may be that you're expecting authentication back to AD, but since the user exists locally DB2 is authenticating to the local account.
In your experimenting, did you happen to create that user locally? Â It may be that you're expecting authentication back to AD, but since the user exists locally DB2 is authenticating to the local account.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
no, it is an AD account and I have triple checked it. Â It should not have any access the the windows server or DB2. Â I have not felt this stupid in a very long time.
Sounds like in your troubleshooting might have granted permission to the AD user with is independent of the group. Â You can grant granular permissions to DB2 without using groups. Â Best practice is to use groups. Â This would explain why that user can still access the database but without being in specific group memberships whether AD or DB2. Â You can grant permissions to a "remote" user in this case (versus DB2 user) and after turning on extended security model of DB2.
OK, I kind of understand what you are saying, but if User2 is not in a group or role and not listed as a user under security and not in any windows group, how the heck is he being authorized? Â Where can I see that from?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Yep, User2 is in there a few times and it looks like PUBLIC has been granted all kinds of permissions. Â OMG.
Thank you. Â I will try to clean that all up and retest.
Thank you. Â I will try to clean that all up and retest.
My pleasure. Let me know if anything else comes up.
My pleasure. Let me know if anything else comes up.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
In simple terms, can someone tell me what the difference is between a DB2 group and a DB2 role? Â I have read the IBM docs and I don't get it. Â My DB2 for Windows for Dummies book does not cover it either.
This should not be this hard.
Jim
This should not be this hard.
Jim
Hi Jim,
Obviously, they're related. Â :)
It's largely management style (or needs). Â Multiple users could be assigned to a group, and multiple roles could be assigned to a user or group, but both (or either) can be used to manage user permissions.
IBM cautions that there are inconveniences of inheritance due to group permissions being handled more restrictively than role permissions. Â That translates to "roles are more flexible and behave as you'd expect". Â If your shop uses groups as the primary control of user privileges you'll probably still have to assign roles, but if you use roles you may have no need for groups.
Kent
Obviously, they're related. Â :)
It's largely management style (or needs). Â Multiple users could be assigned to a group, and multiple roles could be assigned to a user or group, but both (or either) can be used to manage user permissions.
IBM cautions that there are inconveniences of inheritance due to group permissions being handled more restrictively than role permissions. Â That translates to "roles are more flexible and behave as you'd expect". Â If your shop uses groups as the primary control of user privileges you'll probably still have to assign roles, but if you use roles you may have no need for groups.
Kent
Windows OS
--
Questions
--
Followers
Top Experts
This topic area includes legacy versions of Windows prior to Windows 2000: Windows 3/3.1, Windows 95 and Windows 98, plus any other Windows-related versions including Windows Mobile.