Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Question Unanswered: Group access issue

    Hi All - DB2 8.1.5 on AIX 5.2.0.0

    I'm having trouble with assigning select privileges to
    groups in my enviroment.

    Its no problem creating the groups, assigning the privileges and
    puttinng users into the groups

    But it seems like the database doesn't scan for privileges
    assigned to a persons group, only those assigned individually

    I know it sounds strange, but I've tested it a dozen times.

    Has anyone experienced anything similar ?
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    group

    we are using this for a long time
    creating groups at os level / attach user to this group
    only grants are given to groups..
    grant select on xx.yy to group zzz
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  3. #3
    Join Date
    Oct 2005
    Posts
    109
    Did you
    - revoke all authorities from PUBLIC (what public can do, everybody can do)
    - set the authentication to server (and not to client ?)
    Juliane

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow I think so

    My settings on the DBM:

    Database manager authentication (AUTHENTICATION) = SERVER
    Cataloging allowed without authority (CATALOG_NOAUTH) = NO
    Trust all clients (TRUST_ALLCLNTS) = YES
    Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
    Bypass federated authentication (FED_NOAUTH) = NO

    And all public priviliges are on objects not related to this user
    Last edited by Tank; 11-25-05 at 05:02. Reason: Error
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I know it sounds crazy and you most likely already checked on it, but you might want to double check that those users are in those groups? We have never incountered such a problem. We DO NOT give access to PUBLIC nor to individual id's, only group access.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Confusing - isn't it

    I have checked, look at the attached files:

    in 'dwlprk gruppen' you can see that user 'sta' belongs to the group 'dwlprk'

    in 'dwlprk privileges' you can see the group 'dwlprk' having select
    privileges on the view 'lpr.v_adm1977'

    But then 'sta refused' speaks for itself !!
    Attached Thumbnails Attached Thumbnails dwlprk gruppen.bmp   dwlprk privileges.bmp   sta refused.bmp  
    Last edited by Tank; 11-25-05 at 10:43. Reason: typos
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Somehow I don't trust those fancy GUI things.

    Can you check that "grep dwlprk /etc/group" indeed shows you "sta" as a member?

    What does "select * from syscat.tabauth where tabschema='LPR' and tabname='V_ADM1977'" give you?

  8. #8
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Unhappy The Plot thickens

    I've checked both (before) but here are the results:

    db2inst2@viggo:/home/db2inst2> lsgroup DWLPRK
    DWLPRK id=285 admin=false users=sta adms=jej,db2inst2 registry=files

    and:

    DB2INST2 | DWLPRK | G | LPR | V_ADM1977 | N N N N N Y N N

    where the Y is yes to selectauth.

    I'm thoroughly mystified - I think I'll call IBM, thanks for trying guys.
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Are these errors in Static SQL ? eg. Stored Procedures ? or are these in dynamic SQL eg. Command window. ?

    Cheers
    Sathyaram

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The documentation says :

    GROUP privileges are not checked for static INSERT statements.
    GROUP privileges are not checked for static SELECT INTO statements.

    But notes, these SIUD privleges for tables referred in static statements need to be given to users who bind the packages (or create SPs). Once the packages are created, a group can be grant EXECUTE permission on the package and that will not be a problem.

    I guess you will face this issue primarily in Dev environment ..

    HTH

    Cheers
    Sathyaram

  11. #11
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Exclamation

    Hi Sathyaram

    No it works fine in static (SP's)

    It doesn't work dynamically
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  12. #12
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Exclamation Solution is at hand

    If you guys are interested

    It turns out its a reverse uppercase problem.

    If you assign privileges on windows to a group these
    are upcased - no problem, I knew that and thus had created my
    group on the Backend AIX with capitals.

    But DB2 doesn't seem to grasp this.

    If you create the groups with lowercase names on the backend -
    the whole thing works. Even though the privileges and the group checks
    are automatically upcased ???!!!

    I'm pretty sure it's a bug

    But at least it works now.
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    It is always a little guy problem

    Glad you figured it out.
    --
    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
  •