Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Finding privileges

    Hello Experts,

    I could not find the difference in any way between two users, but userA can query sysibmadm.snapdb while userB cannot.

    I have checked all, including db2look output with -x

    group memberships are same, no entries for userA in dbauth or tabahth views.

    Please help me with any idea.

    DB2 v9.7 Enterprise partioned db envoirenment on AIX 6.1

    Regards

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks, but the output for both users is exactly the same.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by DBFinder View Post
    Hello Experts,

    I could not find the difference in any way between two users, but userA can query sysibmadm.snapdb while userB cannot.

    I have checked all, including db2look output with -x

    group memberships are same, no entries for userA in dbauth or tabahth views.

    Please help me with any idea.

    DB2 v9.7 Enterprise partioned db envoirenment on AIX 6.1

    Regards
    Basic question, what is the error message you get ???

    Also use the get authorizations command to confirm the authorizations are same. ..

    db2 get authorizations

    Administrative Authorizations for Current User

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

    Indirect SYSADM authority = YES
    Indirect SYSCTRL authority = NO
    Indirect SYSMAINT authority = NO
    Indirect DBADM authority = NO
    Indirect CREATETAB authority = NO
    Indirect BINDADD authority = NO
    Indirect CONNECT authority = NO
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Code:
    fapfdwsdb24:userB:/home/userB>db2 select 1 from sysibmadm.snapdb
    
    1
    -----------
    SQL1092N  "USERB" does not have the authority to perform the requested
    command or operation.

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Code:
     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                  = YES

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Code:
    SELECT substr(GRANTEE,1,20), GRANTEETYPE,SELECTAUTH  FROM SYSCAT.TABAUTH  WHERE TABSCHEMA = 'SYSIBMADM' AND TABNAME = 'SNAPDB'
    
    1                    GRANTEETYPE SELECTAUTH
    -------------------- ----------- ----------
    PUBLIC               G           Y
    UMW9725              U           Y
    USWLAP4              U           Y
    ROOT                 U           Y
    USERB                U           Y
    
      5 record(s) selected.

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Any one needs more info, please help !!

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Can you provide "db2 get authorizations" for userA and
    "id userA"
    "id userB"

    Any errors logged in the db2diag.log?

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    No errors are logged

    OS groups are same.

    Code:
     Administrative Authorizations for Current User        Administrative Authorizations for Current User
                                                          
     Direct SYSADM authority                    = NO       Direct SYSADM authority                    = NO
     Direct SYSCTRL authority                   = NO       Direct SYSCTRL authority                   = NO
     Direct SYSMAINT authority                  = NO       Direct SYSMAINT authority                  = NO
     Direct DBADM authority                     = NO       Direct DBADM authority                     = NO
     Direct CREATETAB authority                 = NO       Direct CREATETAB authority                 = NO
     Direct BINDADD authority                   = NO       Direct BINDADD authority                   = NO
     Direct CONNECT authority                   = NO       Direct CONNECT authority                   = NO
     Direct CREATE_NOT_FENC authority           = NO       Direct CREATE_NOT_FENC authority           = NO
     Direct IMPLICIT_SCHEMA authority           = NO       Direct IMPLICIT_SCHEMA authority           = NO
     Direct LOAD authority                      = NO       Direct LOAD authority                      = NO
     Direct QUIESCE_CONNECT authority           = NO       Direct QUIESCE_CONNECT authority           = NO
     Direct CREATE_EXTERNAL_ROUTINE authority   = NO       Direct CREATE_EXTERNAL_ROUTINE authority   = NO
     Direct SYSMON authority                    = NO       Direct SYSMON authority                    = NO
                                                          
     Indirect SYSADM authority                  = NO       Indirect SYSADM authority                  = NO
     Indirect SYSCTRL authority                 = NO       Indirect SYSCTRL authority                 = NO
     Indirect SYSMAINT authority                = NO       Indirect SYSMAINT authority                = NO
     Indirect DBADM authority                   = NO       Indirect DBADM authority                   = NO
     Indirect CREATETAB authority               = NO       Indirect CREATETAB authority               = NO
     Indirect BINDADD authority                 = NO       Indirect BINDADD authority                 = NO
     Indirect CONNECT authority                 = YES      Indirect CONNECT authority                 = YES
     Indirect CREATE_NOT_FENC authority         = NO       Indirect CREATE_NOT_FENC authority         = NO
     Indirect IMPLICIT_SCHEMA authority         = NO       Indirect IMPLICIT_SCHEMA authority         = NO
     Indirect LOAD authority                    = NO       Indirect LOAD authority                    = NO
     Indirect QUIESCE_CONNECT authority         = NO       Indirect QUIESCE_CONNECT authority         = NO
     Indirect CREATE_EXTERNAL_ROUTINE authority = NO       Indirect CREATE_EXTERNAL_ROUTINE authority = NO
     Indirect SYSMON authority                  = YES      Indirect SYSMON authority                  = YES

  11. #11
    Join Date
    Jul 2011
    Location
    USA
    Posts
    80
    please share details :
    lsuser userA
    lsuser userB

    and
    db2 get dbm cfg | grep -i group

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Code:
    $ db2 get dbm cfg | grep -i group                                                                   
      SYSADM group name                        (SYSADM_GROUP) = BCUIGRP                                  
      SYSCTRL group name                      (SYSCTRL_GROUP) = BCUIGRP                                  
      SYSMAINT group name                    (SYSMAINT_GROUP) =                                          
      SYSMON group name                        (SYSMON_GROUP) = BCUMGRP                                  
      Group Plugin                             (GROUP_PLUGIN) =                                          
                                                                                                         
    
     $ lsuser userA                                                                                     
     userA id=963 pgrp=bcumgrp groups=bcumgrp,staff home=/home/userA shell=/usr/bin/ksh                
    
     $ lsuser userB                                                                                    
     userB id=19 pgrp=bcumgrp groups=bcumgrp,staff home=/home/userB shell=/usr/bin/ksh gecos=NOC user

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    bcumgrp -> is this BCU / DPF env? If so, does userB exist on all partitions?

    Please verify that it meets the following requirement:
    Code:
    In addition, one of the following privileges or authorities is also required:
    EXECUTE privilege on the SNAP_GET_DB_V95 table function
    DATAACCESS authority

    Also, if you can temporary increase diaglevel to 4, perform:

    db2diag -A
    db2 update dbm cfg using diaglevel 4
    execute query
    db2 update dbm cfg using diaglevel 3
    attach new db2diag.log here (from all partitions, if different)

  14. #14
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Bela,

    I could not figure out, but AIX admin has fixed the issue was with gid. I think user existed with different gids across lpars.

    Regards

Posting Permissions

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