I'm running DB2 v7.2.9..... not my doing - we have an extended warranty so please, no need to tell me to upgrade. ($$$)

Anyway, DB2 is installed on a server running Windows Server 2000. We use Active Directory to manage Users/Groups which is on another server. If I try to add a Group or a User from within DB2, I can only add those that are stored on the database server where DB2 resides.... Example: On the Database Server, db2test is a User and aspgrp is a Group... I can see both within DB2 so I can add both. If I remove this user and group from the Database Server and have them in Active Directory (only) with db2test added to the group (aspgrp), DB2 does not list (see) either of these so I cannot add them to DB2. I need to be able to assign limited rights to some users/groups but if they are not listed on the Database Server I can't assign any rights.

This seems to defeat the entire purpose of Active Directory so I assume I am missing something here.

Can anyone identify what I'm missing? Maybe point me to some article that explains proper configuration?

I'm assuming you use Control Center to "add users"? Did you try just typing user/group names instead of selecting them from the drop-down list?

Yes, I did this. I can add the Group to DB2 in this manner but still have problems. After adding this Group, I allow Connect to the group along with one table and the Select right for that table. I then stop/start the database then go to a DB2 prompt and can connect to the database as that user but cannot perform a Select statement unless I add the User also. The user is the only user in the Group so this seems redundant.

What's the output of "db2set -all"?

C:\PROGRA~1\SQLLIB\BIN>db2 attach to test

Instance Attachment Information

Instance server = DB2/NT 7.2.9
Authorization ID = EDRECZK
Local instance alias = TEST


C:\PROGRA~1\SQLLIB\BIN>db2set -all
[e] DB2PATH=C:\Program Files\SQLLIB
[i] DB2SATELLITEID=administrator
[i] DB2_ENABLE_LDAP=YES
[i] DB2NTNOCACHE=ON
[i] DB2NBADAPTERS=0
[i] DB2INSTPROF=C:\PROGRAM FILES\SQLLIB
[i] DB2COMM=NETBIOS,TCPIP,NPIPE
[g] DB2_GRP_LOOKUP=DOMAIN
[g] DB2SYSTEM=RSSO2D1
[g] DB2PATH=C:\Program Files\SQLLIB
[g] DB2INSTDEF=DB2
[g] DB2COMM=NETBIOS,TCPIP,NPIPE
[g] DB2ADMINSERVER=DB2DAS00

C:\PROGRA~1\SQLLIB\BIN>


I tried setting DB2_GRP_LOOKUP=Local - but got the same results.

Ok, let's check these:
"net user test /domain"
"db2 select * from syscat.tabauth where tabname='YOURTABLE'"

for net user db2test /domain:

C:\PROGRA~1\SQLLIB\BIN>net user db2test /domain
The request will be processed at a domain controller for domain
rsso.ibmsears.com.

User name db2test
Full Name db2test
Comment IBM sytem account
User's comment
Country code 000 (System Default)
Account active Yes
Account expires Never

Password last set 8/10/2007 4:22 PM
Password expires Never
Password changeable 8/10/2007 4:22 PM
Password required Yes
User may change password No

Workstations allowed All
Logon script
User profile
Home directory
Last logon Never

Logon hours allowed All

Local Group Memberships
Global Group memberships *db2asp *Domain Users
The command completed successfully.


C:\PROGRA~1\SQLLIB\BIN>


For the db2 select statement:

C:\PROGRA~1\SQLLIB\BIN>db2 select * from syscat.tabauth where tabname='edreczk.org'

GRANTOR
GRANTEE
GRANTEETYPE TABSCHEMA
TABNAME
CONTROLAUTH ALTERAUTH DELETEAUTH INDEXAUTH INSE
RTAUTH SELECTAUTH REFAUTH UPDATEAUTH
-------------------------------------------------------------------------------------------------------------------
------------- -----------------------------------------------------------------------------------------------------
--------------------------- ----------- ---------------------------------------------------------------------------
----------------------------------------------------- -------------------------------------------------------------
------------------------------------------------------------------- ----------- --------- ---------- --------- ----
------ ---------- ------- ----------

0 record(s) selected.


The database I am testing this under is the sample database provided with DB2 - db2sampl.exe.
I am running my tests against one table - ORG. This table contains 8 records.

In that case the query should read "... where tabname = 'ORG'".

Did that... same results as I sent you.

Logged on as admin and ran same command... same results.

Sorry, that does not make sense. If you have granted permissions on the table to either the group or the user, they should show up in that select.

As one fine gentleman commented recently, "those who live by GUI, die by GUI". Try this:
"db2 grant select on table whateverschema.org to group db2asp"

Then run this again: "db2 select * from syscat.tabauth where tabname='ORG'"

Then connect as the user db2test and run a query against the ORG table. If it gives you an error, paste your query and the error.

PS. You realize that 'org' and 'ORG' are different tables, don't you?

Sorry... 'ORG' makes a huge difference.

C:\PROGRA~1\SQLLIB\BIN>db2 select * from syscat.tabauth where tabname = 'ORG'

GRANTOR
GRANTEE
GRANTEETYPE TABSCHEMA
TABNAME
CONTROLAUTH ALTERAUTH DELETEAUTH INDEXAUTH INSE
RTAUTH SELECTAUTH REFAUTH UPDATEAUTH
-------------------------------------------------------------------------------------------------------------------
------------- -----------------------------------------------------------------------------------------------------
--------------------------- ----------- ---------------------------------------------------------------------------
----------------------------------------------------- -------------------------------------------------------------
------------------------------------------------------------------- ----------- --------- ---------- --------- ----
------ ---------- ------- ----------
SYSIBM
EDRECZK
U EDRECZK
ORG
Y G G G G
G G G
DB2ADMIN
DB2ASP
G EDRECZK
ORG
N N N N N
Y N N

2 record(s) selected.




We can discuss the use of GUI at a later date. Maybe if DB2 documentation were less cryptic people wouldn't use it as much.

What happens if log in to Windows using db2test and do this:
"db2 connect to <your database>"
"db2 select <whatever you are selecting"

and this:
"db2 connect to <your database> user db2test"
"db2 select ..."

Please paste complete sessions, including command output and error messages, if any.

First example is where I logged on to the Windows Server (as me) and connected to the SAMPLE database as db2test.
The second example is where I logged on to the Windows Server as db2test.

No errors.


Both have db2test (user) defined via DB2 GUI


Database Connection Information

Database server = DB2/NT 7.2.9
SQL authorization ID = DB2TEST
Local database alias = SAMPLE


C:\PROGRA~1\SQLLIB\BIN>db2 select * from edreczk.org

DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION
-------- -------------- ------- ---------- -------------
10 Head Office 160 Corporate New York
15 New England 50 Eastern Boston
20 Mid Atlantic 10 Eastern Washington
38 South Atlantic 30 Eastern Atlanta
42 Great Lakes 100 Midwest Chicago
51 Plains 140 Midwest Dallas
66 Pacific 270 Western San Francisco
84 Mountain 290 Western Denver

8 record(s) selected.


C:\PROGRA~1\SQLLIB\BIN>


Database Connection Information

Database server = DB2/NT 7.2.9
SQL authorization ID = DB2TEST
Local database alias = SAMPLE


C:\PROGRA~1\SQLLIB\BIN>db2 select * from edreczk.ORG

DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION
-------- -------------- ------- ---------- -------------
10 Head Office 160 Corporate New York
15 New England 50 Eastern Boston
20 Mid Atlantic 10 Eastern Washington
38 South Atlantic 30 Eastern Atlanta
42 Great Lakes 100 Midwest Chicago
51 Plains 140 Midwest Dallas
66 Pacific 270 Western San Francisco
84 Mountain 290 Western Denver

8 record(s) selected.

PS.... you may notice that one table is lower case, the other upper case... same table, same results. Only did this so that I could ensure my cut & paste was working correctly.

One other item:

I had to add db2test to local admin in order to be able to log onto Windows Server. Only admin have this ability... not sure if this changes things but, wanted you to be aware.