|
Problem IC71354
|
Status: Closed |
PUBLIC PRIVILEGE TAKE PRECEDENCE OVER GROUP PRIVILEGE WHEN CHOOSING THE DEFAULT TABLE SPACE WHEN CREATING NEW TABLE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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 behaviour described above. |
| 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. *
**************************************************************** |
| Local Fix: |
As a workaround, grant USE privilege to a role to which the user
is granted to. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was fixed in DB2 v9.7 FP4. |
| Workaround |
As a workaround, grant USE privilege to a role to which the
user
is granted to. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.09.2010
09.05.2011
09.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
