DB2 8.2 FP15/16 AIX 5.3 . OS relies on LDAP for security (user/group) management.

A user has, say, staff as primary group and db2adm (sysadm_group) as secondary group. I remember to have used this method in the past to grant sysadm authority to the user.

But in the above setup, adding the user to the sysadm_group does not grant syadm authority. DB2 Commands that need authority(eg. get snapshot) fail with SQL1092N. Am I missing anything obvious here ?

Thanks in advance

Sathyaram

SYSADM group does not have to be primary - it is sufficient to have it in the list of groups for the user

I'm going to verify something obvious: the sysadm_group is defined _in the LDAP directory_, as well as the user, and the user is the member of that group there, yes?

Here's the information about the users and groups :

------------------------------------------------
lsuser udbmaint
udbmaint id=322700
pgrp=udbadmin
groups=udbadmin,udbmaint
home=/home/udbmaint
shell=/usr/bin/ksh
gecos=xxxxxx
registry=LDAP

lsuser udbmon
udbmon id=954100
pgrp=udbmon
groups=udbmon,udbadmin
home=/home/udbmon
shell=/usr/bin/ksh
gecos=xxxxxxx
User registry=LDAP
----------------
db2 get dbm cfg | grep -i sys
SYSADM group name (SYSADM_GROUP) = UDBADMIN
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =

------------------
db2 connect to mydb user udbmon using xxxxxx
SQL1060N User "UDBMON " does not have the CONNECT privilege. SQLSTATE=08004

------------------------
db2 connect to mydb user udbmaint using xxxxxx
Database Connection Information
Database server = DB2/AIX64 8.2.8
SQL authorization ID = UDBMAINT
Local database alias = MYDB
db2 get authorizations | grep -i sysadm
Direct SYSADM authority = NO
Indirect SYSADM authority = YES
--------------------------


While making this list, I found the group udbadmin in /etc/group too. Not sure if this is the problem. The gid is the same in LDAP and local, if that makes a difference.

The Unix team is on the case to remove local entries.

Thanks

Sathyaram

Pls include db directory entry for mydb

I don't think the local group matters. Check that the group lookup configuration in IBMLDAPSecurity.ini matches your LDAP server implementation. There are two ways to associate users and groups: either list group members in the group attributes, or list user's groups in the user attribute. Read comments in the .ini file around GROUP_LOOKUP_METHOD

Nick, Gene and others as well

I have just encountered similar issue. User has access to a DB; one of the groups has SELECT access on a table. However, when trying to select from it user gets

SQL0551N "Uxx3x40" does not have the privilege to perform operation "SELECT"
on object "FxxxxxxxT.GxxxxxxxxxxxxxxHT". SQLSTATE=42501

Have verified and UNIX group that this user belongs to DOES have access on this table. Here is the info on the user and db

Group that has access on the table above is the second group "bxxxxrg"

User has no problem accessing tables that are granted to the Primary group. When we flip flop group order. User still only able tables which have grants only to primary group.

lsuser uxx3x40
uxx3x40 id=29669 pgrp=pxxxxsr groups=pxxxxsr,bxxxxrg home=

db2 get dbm cfg | grep -i sys
Federated Database System Support (FEDERATED) = YES
SYSADM group name (SYSADM_GROUP) = aaaaa
SYSCTRL group name (SYSCTRL_GROUP) = bbbb
SYSMAINT group name (SYSMAINT_GROUP) = ccccc
SYSMON group name (SYSMON_GROUP) = ddddd
Priority of agents (AGENTPRI) = SYSTEM

db2 get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = NO
Direct BINDADD authority = NO
Direct CONNECT authority = NO
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO
Direct QUIESCE_CONNECT authority = NO
Direct CREATE_EXTERNAL_ROUTINE authority = NO
Direct SYSMON authority = NO

Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = NO
Indirect BINDADD authority = NO
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = NO
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO


Database alias = FxxxxxP
Database name = FxxxxxP
Local database directory = /dbxxx/xxxxxx84
Database release level = b.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

Thank you for your help.

DB21085I Instance "uszpsb84" uses "64" bits and DB2 code release "SQL09012"
with level identifier "01030107".
Informational tokens are "DB2 v9.1.0.2", "s070210", "U810940", and Fix Pack
"2".
Product is installed at "/opt/IBM/db2/V9.1".

Alex, I don't know the reason why it's not working with the secondary group but try setting the following registry variable if you're using LDAP:

DB2_ALTERNATE_GROUP_LOOKUP=GETGRSET
restart instance

Bella, your solution will not work for us.

Someone has opened a PMR. Will advise when and if it will get resolved.

Well, IBM has recommended that we DO implement the solution that Bella has posted. It was implemented and it did solve the problem. But like with anything else there was a big BUT.

It created a new problem. Right after the bounce dbdiaglog started to get this error message

It was flooding the log at the speed of light. yes, the original problem was resolved but now we had a new fight on our hands. Another user, who accidentally is in the same primary unix group as the original user, now could not connect and was getting

The only difference between those two ids as far as I can tell is one(original) is a user account. And the later one is an application account.

I have found a solution by IBM for this problem and tried to implemented it, but no cigar.

IBM - SQl30060N error during rebind.

We had to remove DB2_ALTERNATE_GROUP_LOOKUP=GETGRSETparm from the system. After the bounce no more errors.

Back to talking with IBM.