Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2007
    Posts
    27

    Unanswered: Another User Related Question

    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?

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

  3. #3
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by n_i
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What's the output of "db2set -all"?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by n_i
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Ok, let's check these:
    "net user test /domain"
    "db2 select * from syscat.tabauth where tabname='YOURTABLE'"
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by n_i
    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.

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

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

    ...

    I am running my tests against one table - ORG. This table contains 8 records.
    In that case the query should read "... where tabname = 'ORG'".
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by n_i
    In that case the query should read "... where tabname = 'ORG'".
    Did that... same results as I sent you.

  10. #10
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by n_i
    In that case the query should read "... where tabname = 'ORG'".
    Logged on as admin and ran same command... same results.

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

  12. #12
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by n_i
    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.

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

  14. #14
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by n_i
    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.

  15. #15
    Join Date
    May 2007
    Posts
    27
    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.

Posting Permissions

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