|
Problem IC66032
|
Status: Closed |
AN INCORRECT AUTHORIZATION ID WAS REPORTED IN SQLCODE -20402 WITH LBAC SECURITY ENABLED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
In a LBAC security setup when a user does not have the proper
authority to the operations SQL20402n error is reported with the
previous user id rather than the actual user id.
Here is a test case.
Setup:
user instuser: instance owner
user appuser: simple user
user secadm: setup as secadm
CREATE SECURITY LABEL COMPONENT TESTSECLEVEL ARRAY ['TOP
SECRET', 'SECRET', 'CONFIDENTIAL', 'RESTRICTED', 'PUBLIC'];
CREATE SECURITY POLICY SECPOLICYTEST COMPONENTS TESTSECLEVEL
WITH DB2LBACRULES;
CREATE SECURITY LABEL SECPOLICYTEST.PUBLIC COMPONENT
TESTSECLEVEL 'PUBLIC';
GRANT SECURITY LABEL SECPOLICYTEST.PUBLIC TO USER appuser;
CREATE TABLE TEST.TMP_TABLE (COL1 varchar(20) ,COL2
varchar(20),COL_ROWSECLABEL DB2SECURITYLABEL)SECURITY POLICY
SECPOLICYTEST;
GRANT ALL ON TEST.TMP_TABLE TO PUBLIC;
As appuser, insert into temp table worked:
appuser @ chiana : /home/appuser
$ db2 "INSERT INTO TEST.TMP_TABLE (col1, col2) VALUES ('33',
'33')"
DB20000I The SQL command completed successfully.
As instance insert a row and receive the error about not having
authorization
instuser @ chiana : /home/instuser
$ db2 "INSERT INTO TEST.TMP_TABLE (col1, col2) VALUES ('33',
'33')"
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20402N Authorization ID "INSTUSER" does not have the LBAC
credentials to
perform the "INSERT" operation on table "TEST.TMP_TABLE".
SQLSTATE=42519
Then when tried as SECADM, but instead of it reporting SECADM
not having auth, gets the instance owner again:
secadm @ chiana : /home/secadm
$ db2 connect to P16506
Database Connection Information
Database server = DB2/LINUX 9.1.5
SQL authorization ID = SECADM
Local database alias = P16506
secadm @ chiana : /home/secadm
$ db2 "INSERT INTO TEST.TMP_TABLE (col1, col2) VALUES ('33',
'33')"
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20402N Authorization ID "INSTUSER" does not have the LBAC
credentials to
perform the "INSERT" operation on table "TEST.TMP_TABLE".
SQLSTATE=42519 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users on V9.7 GA and FP1 *
****************************************************************
* PROBLEM DESCRIPTION: *
* In a LBAC security setup when a user does not have *
* theproperauthority to the operations SQL20402n error is *
* reported withtheprevious user id rather than the actual user *
* id.Here is a test case.Setup:user instuser: instance *
* owneruser appuser: simple useruser secadm: setup as *
* secadmCREATE SECURITY LABEL COMPONENT TESTSECLEVEL ARRAY *
* ['TOPSECRET', 'SECRET', 'CONFIDENTIAL', 'RESTRICTED', *
* 'PUBLIC'];CREATE SECURITY POLICY SECPOLICYTEST COMPONENTS *
* TESTSECLEVELWITH DB2LBACRULES;CREATE SECURITY LABEL *
* SECPOLICYTEST.PUBLIC COMPONENTTESTSECLEVEL 'PUBLIC';GRANT *
* SECURITY LABEL SECPOLICYTEST.PUBLIC TO USER appuser;CREATE *
* TABLE TEST.TMP_TABLE (COL1 varchar(20) *
* ,COL2varchar(20),COL_ROWSECLABEL DB2SECURITYLABEL)SECURITY *
* POLICYSECPOLICYTEST;GRANT ALL ON TEST.TMP_TABLE TO PUBLIC;As *
* appuser, insert into temp table worked:appuser @ chiana : *
* /home/appuser$ db2 "INSERT INTO TEST.TMP_TABLE (col1, col2) *
* VALUES ('33','33')"DB20000I The SQL command completed *
* successfully.As instance insert a row and receive the error *
* about nothavingauthorizationinstuser @ chiana : *
* /home/instuser$ db2 "INSERT INTO TEST.TMP_TABLE (col1, col2) *
* VALUES ('33','33')"DB21034E The command was processed as an *
* SQL statementbecauseit was not avalid Command Line Processor *
* command. During SQL processingitreturned:SQL20402N *
* Authorization ID "INSTUSER" does not have the *
* LBACcredentials toperform the "INSERT" operation on table *
* "TEST.TMP_TABLE".SQLSTATE=42519Then when tried as SECADM, *
* but instead of it reportingSECADMnot having auth, gets the *
* instance owner again:secadm @ chiana : /home/secadm$ db2 *
* connect to P16506Database Connection InformationDatabase *
* server = DB2/LINUX 9.1.5SQL authorization ID = *
* SECADMLocal database alias = P16506secadm @ chiana : *
* /home/secadm$ db2 "INSERT INTO TEST.TMP_TABLE (col1, col2) *
* VALUES ('33','33')"DB21034E The command was processed as an *
* SQL statementbecauseit was not avalid Command Line Processor *
* command. During SQL processingitreturned:SQL20402N *
* Authorization ID "INSTUSER" does not have the *
* LBACcredentials toperform the "INSERT" operation on table *
* "TEST.TMP_TABLE".SQLSTATE=42519 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to FixPack 2 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
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 9a 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 10 for Linux, UNIX, and Windows
|
| Solution |
First Fixed in V9.7 Fix Pack 2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.02.2010
29.07.2010
29.07.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
