Preventive Service Planning
Abstract
In DB2 Version 9.7 for Linux, UNIX, and Windows, users with DBADM authority wrongfully have implicit USAGE privilege on sequences. In DB2 Version 10.1 for Linux, UNIX, and Windows, this behavior has been changed. Instead, users with DATAACCESS authority implicitly have the USAGE privilege on sequences. If there is any package that depends on a sequence, does not have explicit USAGE privilege on the sequence, and the binder of the package is DBADM but not DATAACCESS, rebinding the package in V10.1, after upgrading to V10.1, fails with SQLCODE -551. This is because the binder of the package does not have USAGE privilege on the sequence any more. The solution to this is to grant the binder implicit or explicit USAGE privilege on the sequence. This can be done either before or after the upgrade process. The alternative is to drop the package and recreate it by a user who has the necessary privileges.This page shows users who are preparing the upgrade how to manually identify whether they have such packages.
Content
The db2ckupgrade command can be used to identify these packages. Running the db2ckupgrade command before the upgrade will identify these packages automatically.
You do not need to perform the steps discussed on this page if either of following actions apply to you:
- You ran the db2ckupgrade manually before the upgrade.
- You upgraded from V9.7 to V10.1 by issuing the db2iupgrade command (which invokes the db2ckupgrade command automatically).
If you do a backup restore upgrade, you should manually run db2ckupgrade before the backup. Otherwise, you need to follow the instructions below after you have upgraded to V10.1.
Step 1. Find out all valid user packages that have dependency on sequences. Here is an example:
select char(a.pkgschema, 10)pkgschema, char(a.pkgname, 20)pkgname, char(boundby,10)boundby, valid, char(bschema,10)bschema, char(bname,10)bname from syscat.packages a, syscat.packagedep b where a.pkgschema=b.pkgschema and a.pkgname=b.pkgname and b.btype='Q' and bschema not like 'SYS%' order by boundby
PKGSCHEMA PKGNAME BOUNDBY VALID BSCHEMA BNAME
---------- -------------------- ---------- ----- ---------- ----------
NEWTON P1273097164 NEWTON Y TESTING MYSEQ
PLATO P1107247365 PLATO Y TESTING MYSEQ4
USER2 P413002034 USER2 Y TESTING MYSEQ3
DYUPING SEQPACK2 ZURBIE Y TESTING MYSEQ
ZURBIE P1131750305 ZURBIE Y TESTING MYSEQ
ZURBIE P1996658867 ZURBIE Y TESTING MYSEQ2
6 record(s) selected.
There are three packages that depend on sequence TESTING.MYSEQ, one package that depends on sequence TESTING.MYSEQ2, one that depends on TESTING.MYSEQ3, and one that depends on TESTING.MYSEQ4.
Step 2. Go through each BOUNDBY column for the packages (also called the binder) and verify whether it has explicit USAGE privilege on the sequence. For example:
SELECT SUBSTR(AUTHID,1,16) as AUTHID, AUTHIDTYPE, PRIVILEGE, SUBSTR(OBJECTNAME,1,10) as OBJECTNAME, SUBSTR(OBJECTSCHEMA,1,10) as OBJECTSCHEMA, substr(OBJECTTYPE,1,10) as objecttype FROM SYSIBMADM.PRIVILEGES where objecttype='SEQUENCE' and privilege='USAGE' order by authid
AUTHID AUTHIDTYPE PRIVILEGE OBJECTNAME OBJECTSCHEMA OBJECTTYPE
------------ ---------- ----------- ---------- ------------ ----------
NEWTON U USAGE MYSEQ TESTING SEQUENCE
NEWTON U USAGE MYSEQ2 TESTING SEQUENCE
NEWTON U USAGE MYSEQ3 TESTING SEQUENCE
NEWTON U USAGE MYSEQ4 TESTING SEQUENCE
PUBLIC G USAGE MYSEQ4 TESTING SEQUENCE
R1 R USAGE MYSEQ2 TESTING SEQUENCE
R1_2 R USAGE MYSEQ3 TESTING SEQUENCE
7 record(s) selected.
2.a. BOUNDBY=NEWTON
NEWTON has explicit USAGE privilege directly on the sequence TESTING.MYSEQ, therefore rebinding the package NEWTON.P1273097164 bound by NEWTON after upgrading to V10.1 will not fail with SQLCODE -551 and can be disregarded.
PKGSCHEMA PKGNAME BOUNDBY VALID BSCHEMA BNAME
---------- -------------------- ---------- ----- ---------- ----------
NEWTON P1273097164 NEWTON Y TESTING MYSEQ
PLATO P1107247365 PLATO Y TESTING MYSEQ4
USER2 P413002034 USER2 Y TESTING MYSEQ3
DYUPING SEQPACK2 ZURBIE Y TESTING MYSEQ
ZURBIE P1131750305 ZURBIE Y TESTING MYSEQ
ZURBIE P1996658867 ZURBIE Y TESTING MYSEQ2
2.b BOUNDBY=PLATO
PLATO has explicit USAGE privilege on TESTING.MYSEQ4 indirectly via PUBLIC, therefore rebinding the package PLATO.P1107247365 bound by PLATO after upgrading to V10.1 will not fail with SQLCODE -551 and can also be disregarded.
PKGSCHEMA PKGNAME BOUNDBY VALID BSCHEMA BNAME
---------- -------------------- ---------- ----- ---------- ----------
NEWTON P1273097164 NEWTON Y TESTING MYSEQ
PLATO P1107247365 PLATO Y TESTING MYSEQ4
USER2 P413002034 USER2 Y TESTING MYSEQ3
DYUPING SEQPACK2 ZURBIE Y TESTING MYSEQ
ZURBIE P1131750305 ZURBIE Y TESTING MYSEQ
ZURBIE P1996658867 ZURBIE Y TESTING MYSEQ2
2.c BOUNDBY=USER2 and BOUNDBY=ZURBIE
USER2 has no USAGE privilege on TESTING.MYSEQ3 directly, and ZURBIE does not have USAGE privilege on TESTING.MYSEQ and TESTING.MYSET2 directly either. However USER2 and ZURBIE could gain USAGE privileges via role hierarchy, so you need to continue performing the following steps:
Step 3. Continue to go though each remaining BOUNDBY column, verify whether it has explicit USAGE privilege on the sequence indirectly (via role hierarchy).
3.a BOUNDBY=USER2
select substr(grantor,1,10) as grantor, grantortype, substr(grantee,1,10) as grantee, granteetype, substr(rolename,1,10) as rolename from table (sysproc.auth_list_roles_for_authid('USER2','U')) as T where rolename not like 'SYS%'
GRANTOR GRANTORTYPE GRANTEE GRANTEETYPE ROLENAME
---------- ----------- ---------- ----------- ----------
NEWTON U R2_2 R R1_2
NEWTON U R3_2 R R2_2
NEWTON U PUBLIC G R3_2
3 record(s) selected.
The above example shows the following role hierarchy:
R1_2 -> R2_2 -> R3_2 -> PUBLIC
R1_2 has USAGE privilege on TESTING.MYSEQ3, and USER2 has USAGE privilege on TESTING.MYSEQ3 via the above hierarchy, therefore rebinding the package USER2.P413002034 bound by USER2 after upgrading to V10.1 will not fail with SQLCODE -551. Thus package USER2.P413002034 can be disregarded.
PKGSCHEMA PKGNAME BOUNDBY VALID BSCHEMA BNAME
---------- -------------------- ---------- ----- ---------- ----------
NEWTON P1273097164 NEWTON Y TESTING MYSEQ
PLATO P1107247365 PLATO Y TESTING MYSEQ4
USER2 P413002034 USER2 Y TESTING MYSEQ3
DYUPING SEQPACK2 ZURBIE Y TESTING MYSEQ
ZURBIE P1131750305 ZURBIE Y TESTING MYSEQ
ZURBIE P1996658867 ZURBIE Y TESTING MYSEQ2
3.b BOUNDBY=ZURBIE
select substr(grantor,1,10) as grantor, grantortype, substr(grantee,1,10) as grantee, granteetype, substr(rolename,1,10) as rolename from table (sysproc.auth_list_roles_for_authid('ZURBIE','U')) as T where rolename not like 'SYS%'
GRANTOR GRANTORTYPE GRANTEE GRANTEETYPE ROLENAME
---------- ----------- ---------- ----------- ----------
NEWTON U R2 R R1
NEWTON U R3 R R2
NEWTON U ZURBIE U R3
NEWTON U R2_2 R R1_2
NEWTON U R3_2 R R2_2
NEWTON U PUBLIC G R3_2
6 record(s) selected.
The above example shows the following role hierarchy:
R1 -> R2 -> R3 -> ZURBIE
R1 has USAGE privilege on TESTING.MYSEQ2, and ZURBIE has USAGE privilege on TESTING.MYSEQ2 via the above role hierarchy, therefore rebinding the package ZURBIE.P1996658867 bound by ZURBIE after upgrading to V10.1 will not fail with SQLCODE -551. Thus package ZURBIE.P1996658867 can be disregarded.
PKGSCHEMA PKGNAME BOUNDBY VALID BSCHEMA BNAME
---------- -------------------- ---------- ----- ---------- ----------
NEWTON P1273097164 NEWTON Y TESTING MYSEQ
PLATO P1107247365 PLATO Y TESTING MYSEQ4
USER2 P413002034 USER2 Y TESTING MYSEQ3
DYUPING SEQPACK2 ZURBIE Y TESTING MYSEQ
ZURBIE P1131750305 ZURBIE Y TESTING MYSEQ
ZURBIE P1996658867 ZURBIE Y TESTING MYSEQ2
There are two more packages to consider: ZURBIE.P1131750305 and DYUPING.SEQPACK2 bounded by ZURBIE.
Step 4. Verify whether the BOUNDBY column of the package (also called binder) has implicit USAGE on sequences (that is BOUNDBY is DBADM but not DATAACCESS):
SELECT substr(AUTHORITY,1,10), D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('ZURBIE', 'U') ) AS T ORDER BY AUTHORITY
1 D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC
---------- ------ ------- -------- --------- ---------- -----------
ACCESSCTRL N N N N N N
BINDADD N N Y N N N
CONNECT N N Y N N N
CREATETAB N N Y N N N
CREATE_EXT N N N N N N
CREATE_NOT N N N N N N
DATAACCESS N N N N N N
DBADM Y N N N N N
EXPLAIN N N N N N N
IMPLICIT_S N N Y N N N
LOAD N N N N N N
QUIESCE_CO N N N N N N
SECADM N N N N N N
SQLADM N N N N N N
SYSADM * N * * * *
SYSCTRL * N * * * *
SYSMAINT * N * * * *
SYSMON * N * * * *
WLMADM N N N N N N
19 record(s) selected.
ZURBIE is a user with DBADM authority but without DATAACCESS authority, therefore rebinding packages ZURBIE.P1131750305 and DYUPING.SEQPACK2 bound by ZURBIE after upgrading to V10.1 will fail with SQLCODE -551.
PKGSCHEMA PKGNAME BOUNDBY VALID BSCHEMA BNAME
---------- -------------------- ---------- ----- ---------- ----------
DYUPING SEQPACK2 ZURBIE Y TESTING MYSEQ
ZURBIE P1131750305 ZURBIE Y TESTING MYSEQ
Note that in V9.7 group membership (D_GROUP or ROLE_GROUP) is considered when determining whether a DBADM authority has implicit usage on a sequence or not; while in V10.1, group membership is not considered when determining whether a DATAACCESS authority has implicit usage on sequence. For example, if ZURBIE has DBADM authority and also DATAACCESS authority through group membership (that is. D_GROUP or ROLE _GROUP is Y), the package will still have problem because ZURBIE (having DATAACCESS authority through group membership) will not have USAGE privilege on the sequence after upgrading to V10.1.
Step 5. Users need to decide what to do with these two packages. For example, to avoid the problem, users can grant USAGE privilege on the sequence to the BOUNDBY or users can grant DATAACCESS authority to the BOUNDBY.
The following example shows how to grant USAGE privilege:
grant usage on sequence testing.myseq to user zurbie
DB20000I The SQL command completed successfully.
If users do not do anything at step 5 for the packages identified by step 1 - 4, after upgrading to V10.1 they will see SQLCODE -551 when they rebind the packages after the upgrade process is complete. The following examples show how rebinding such packages fail when you issue the db2rbind command:
rebind dyuping.SEQPACK2
SQL0551N "ZURBIE" does not have the required authorization or privilege to
perform operation "USAGE" on object "TESTING.MYSEQ". SQLSTATE=42501
rebind ZURBIE.P1131750305
SQL0551N "ZURBIE" does not have the required authorization or privilege to
perform operation "USAGE" on object "TESTING.MYSEQ". SQLSTATE=42501
db2rbind test97 -l db2rbind.log.new.bad
Errors detected !!
Check log file 'db2rbind.log.new.bad' for details
Package 'NEWTON.P1273097164' of database 'TEST97' succeeded to rebind. UniqueID = 76417558514D4463
...
Package 'DYUPING.SEQPACK2' of database 'TEST97' did not succeed to rebind. UniqueID = 70417258514D4463
sqlcode = -551; sqlerrmc = ZURBIEÿU; sqlrrp = SQLRA030
SQL0551N "ZURBIE" does not have the required authorization or privilege to perform
operation "US
...
Package 'PLATO.P1107247365' of database 'TEST97' succeeded to rebind. UniqueID = 69423358514D4463
...
Package 'USER2.P413002034' of database 'TEST97' succeeded to rebind. UniqueID = 62413158514D4463
...
Package 'ZURBIE.P1131750305' of database 'TEST97' did not succeed to rebind. UniqueID = 70417858514D4463
sqlcode = -551; sqlerrmc = ZURBIEÿU; sqlrrp = SQLRA030
SQL0551N "ZURBIE" does not have the required authorization or privilege to perform
operation "US
...
Package 'ZURBIE.P1996658867' of database 'TEST97' succeeded to rebind. UniqueID = 34417858514D4463
...
In this case, users can still grant USAGE privilege on the sequence to the binder as described in step 5 to resolve the problem.
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - Bind","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21588835