Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Unanswered: Sysadm_group - primary group?

    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  2. #2
    Join Date
    Apr 2008
    Posts
    51
    SYSADM group does not have to be primary - it is sufficient to have it in the list of groups for the user
    db2topgun.com

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sathyaram_s

    But in the above setup, adding the user to the sysadm_group does not grant syadm authority.
    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?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2008
    Posts
    51
    Pls include db directory entry for mydb
    db2topgun.com

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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".
    Last edited by Cougar8000; 09-23-09 at 14:06.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Bella, your solution will not work for us.

    Someone has opened a PMR. Will advise when and if it will get resolved.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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

    2009-10-06-21.10.05.739885-300 I249A242 LEVEL: Severe
    PID : 10584160 TID : 1
    FUNCTION: DB2 Common, Security, Users and Groups, secLogMessage, probe:20
    DATA #1 : String, 37 bytes
    osplugin_get_groups rc = -2029059908
    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

    SQL30060N "<username>" does not have the privilege to perform operation "0x2203". SQLSTATE=08004
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •