IBM Support

IC71354: PUBLIC PRIVILEGE TAKE PRECEDENCE OVER GROUP PRIVILEGE WHEN CHOOSING THE DEFAULT TABLE SPACE WHEN CREATING NEW TABLE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When choosing a table space for a new table, DB2 is using the
    following set of rules.
    
    If no IBMDEFAULTGROUP table space exists, and two or more table
    spaces with the same page size are available, according to
    documentation, DB2 would check for USE privileges in the
    following order to determine if particular table space should be
    selected:
    
    1. The authorization ID
    2. A group to which the authorization ID belongs
    3. PUBLIC
    
    However, the actual behavior switches the ordering to:
    
    1. The authorization ID
    2. A role to which the authorization ID is granted to
    3. PUBLIC
    4. A role to which PUBLIC is granted to
    5. A group to which the authorization ID belongs
    6. A role to which a group the authorization ID belongs is
    granted to
    
    Thus, table space for which user has USE privilege on via PUBLIC
    will get chosen before table space in which USE privilege has
    been granted to a group that the user belongs to. Also, roles
    are missing from the documented list.
    
    Example:
    
    PUBLIC has USE privilege on USERSPACE1.
    Group A has USE privilege on TBSP1 (same page size as
    USERSPACE1).
    
    Suppose the user is a member of group A, when it issues
    
    CREATE TABLE T1 (C1 INT)
    
    The table will get created in USERSPACE1 instead of TBSP1 which
    is inconsistent from the documented behaviour described above.
    

Local fix

  • As a workaround, grant USE privilege to a role to which the user
    is granted to.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users running DB2 v9.7 FP3 and earlier.                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When choosing a table space for a new table, DB2 is using    *
    * the                                                          *
    * following set of rules.                                      *
    *                                                              *
    *                                                              *
    *                                                              *
    * If no IBMDEFAULTGROUP table space exists, and two or more    *
    * table                                                        *
    * spaces with the same page size are available, according to   *
    *                                                              *
    * documentation, DB2 would check for USE privileges in the     *
    *                                                              *
    * following order to determine if particular table space       *
    * should be                                                    *
    * selected:                                                    *
    *                                                              *
    *                                                              *
    *                                                              *
    * 1. The authorization ID                                      *
    *                                                              *
    * 2. A group to which the authorization ID belongs             *
    *                                                              *
    * 3. PUBLIC                                                    *
    *                                                              *
    *                                                              *
    *                                                              *
    * However, the actual behavior switches the ordering to:       *
    *                                                              *
    *                                                              *
    *                                                              *
    * 1. The authorization ID                                      *
    *                                                              *
    * 2. A role to which the authorization ID is granted to        *
    *                                                              *
    * 3. PUBLIC                                                    *
    *                                                              *
    * 4. A role to which PUBLIC is granted to                      *
    *                                                              *
    * 5. A group to which the authorization ID belongs             *
    *                                                              *
    * 6. A role to which a group the authorization ID belongs is   *
    *                                                              *
    * granted to                                                   *
    *                                                              *
    *                                                              *
    *                                                              *
    * Thus, table space for which user has USE privilege on via    *
    * PUBLIC                                                       *
    * will get chosen before table space in which USE privilege    *
    * has                                                          *
    * been granted to a group that the user belongs to. Also,      *
    * roles                                                        *
    * are missing from the documented list.                        *
    *                                                              *
    *                                                              *
    *                                                              *
    * Example:                                                     *
    *                                                              *
    *                                                              *
    *                                                              *
    * PUBLIC has USE privilege on USERSPACE1.                      *
    *                                                              *
    * Group A has USE privilege on TBSP1 (same page size as        *
    *                                                              *
    * USERSPACE1).                                                 *
    *                                                              *
    *                                                              *
    *                                                              *
    * Suppose the user is a member of group A, when it issues      *
    *                                                              *
    *                                                              *
    *                                                              *
    * CREATE TABLE T1 (C1 INT)                                     *
    *                                                              *
    *                                                              *
    *                                                              *
    * The table will get created in USERSPACE1 instead of TBSP1    *
    * which                                                        *
    * is inconsistent from the documented behavior described       *
    * above.                                                       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v9.7 FP4.                                     *
    ****************************************************************
    

Problem conclusion

  • Problem was fixed in DB2 v9.7 FP4.
    

Temporary fix

  • As a workaround, grant USE privilege to a role to which the
    
    user
    is granted to.
    

Comments

APAR Information

  • APAR number

    IC71354

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-09-21

  • Closed date

    2011-05-09

  • Last modified date

    2011-05-09

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
09 May 2011