A fix is available
APAR status
Closed as new function.
Error description
new function
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: Users of the Access Control Authorization * * Exit (ACAE) that controls (1) insert, update * * and delete using views, and (2) creation of * * views. * * * * Users of the REFRESH TABLE statement when * * the materialized query table (MQT) * * references a table UDF, irrespective of * * whether ACAE is active or not. * **************************************************************** * PROBLEM DESCRIPTION: ACAE active: * * ------------ * * 1.Insert, update and delete view * * authorization: DB2 does not provide * * the view's base table information * * during insert, update and delete to * * enable view authorization to be tied to * * the base table. * * * * 2.View creation for others: DB2 does * * not identify all implicit databases * * passed to the ACAE for the CRTVUAUT * * check as implicit. * * * * Whether ACAE is active or not: * * ----------------------------- * * ABEND0C4 at DSNXGRFR+0590 on a * * REFRESH TABLE statement when used on * * an MQT that references a table UDF. * **************************************************************** * RECOMMENDATION: * **************************************************************** ACAE is active: --------------- 1.Insert, update and delete view authorization: DB2 provides view information in the XAPL control block to enable an external security system to perform authorization for insert, update and delete using views. However, DB2 does not provide information about the base table on which the view is created for the INSRTAUT(51), UPDTEAUT(53) and DELETAUT(52) view (XAPLTYPE = 'V') checks. Providing this base table information would be useful in linking view authorization to its base table, like DB2 authorization does. 2.View creation for others (only V9): When a view is being created for another user by explicitly qualifying the view, e.g. CREATE VIEW OTHERUSER.RESULTSVIEW..., and the DB2 system parameter DBACRVW is set to YES, DB2 provides the list of databases of the tables on which a view is being created for the CRTVUAUT (108) check. Each database is placed in a XAPLDBS control block. In addition, DB2 identifies if the database passed in the first XAPLDBS block has been implicitly created by turning on the XAPLIMPD flag. If the view is based on tables in multiple databases, any implicit databases after the first one in the list are not identified as such as there is only one implicit database identifier (XAPLIMPD). Whether ACAE is active or not: ------------------------------ ABEND0C4 at DSNXGRFR+0590 may occur when the REFRESH TABLE statement is used to refresh an MQT where the MQT definition references a table UDF. For example: CREATE FUNCTION TUDF1() RETURNS TABLE(C1 INT, C2 INT) LANGUAGE PLI DETERMINISTIC FENCED NO SQL EXTERNAL NAME 'TUDF1' PARAMETER STYLE DB2SQL NO EXTERNAL ACTION; CREATE TABLE MQT1 AS (SELECT C1, C2 FROM TABLE (TUDF1()) AS TUDF) DATA INITIALLY DEFERRED REFRESH DEFERRED DISABLE QUERY OPTIMIZATION; SET CURRENT REFRESH AGE=ANY; SET CURRENT MAINTAINED TYPES=ALL; REFRESH TABLE MQT1; During refresh processing, DB2 didn't handle the table UDF correctly, which caused the 0C4 abend in DSNXGRFR.
Problem conclusion
Temporary fix
Comments
V8 CHANGES: ACAE is active: --------------- 1.Insert, update and delete view authorization: For authorization of DELETE and INSERT, DB2 now passes the schema and name of the base table of the view, along with the view information currently passed. For the UPDATE privilege, DB2 additionally passes the base table column name. Nested views: For INSERT/DELETE/UPDATE on any view in the nested stack, the base table information is passed in addition to the current view information. Intermediate views, between the view being processed and the base table are ignored. In the above cases if the view is not updateable the view information is repeated in the new base table fields (XAPLBxxx). CREATE VIEW: During view creation, DB2 checks whether the view owner has INSERT, UPDATE and DELETE privileges on the underlying table or view for non read-only views. When these checks are on a base view, the base view information will be repeated in the new base table fields (XAPLBxxx) to facilitate the check. The ACAE parameter list (XAPL) has been expanded to include base table information for view authorization as follows: New fields: - XAPLBSCM - ADDRESS TO SCHEMA OF VIEW'S BASE TABLE - XAPLBNAM - ADDRESS TO NAME OF VIEW'S BASE TABLE - XAPLBCOL - ADDRESS TO COLUMN NAME OF VIEW'S BASE TABLE Modified use of existing field for view authorization: - XAPLREL2 - ADDRESS TO DATABASE OF VIEW'S BASE TABLE Macro DSNXAPRV that documents specific fields passed to ACAE is updated as follows: ... DELETAUTV EQU 0052 DELETE V * XAPLOBJN - VIEW NAME * XAPLOWNQ - VIEW NAME QUALIFIER |* XAPLBSCM - VIEW'S BASE TABLE QUALIFIER OR |* REPEATED VIEW QUALIFIER |* XAPLBNAM - VIEW'S BASE TABLE NAME OR |* REPEATED VIEW NAME |* XAPLREL2 - VIEW'S BASE TABLE DATABASE NAME OR |* ZERO IF VIEW HAS NO APPLICABLE BASE TABLE ... INSRTAUTV EQU 0051 INSERT V * XAPLOBJN - VIEW NAME * XAPLOWNQ - VIEW NAME QUALIFIER |* XAPLBSCM - VIEW'S BASE TABLE QUALIFIER OR |* REPEATED VIEW QUALIFIER |* XAPLBNAM - VIEW'S BASE TABLE NAME OR |* REPEATED VIEW NAME |* XAPLREL2 - VIEW'S BASE TABLE DATABASE NAME OR |* ZERO IF VIEW HAS NO APPLICABLE BASE TABLE ... UPDTEAUTV EQU 0053 UPDATE V * XAPLOBJN - VIEW NAME * XAPLOWNQ - VIEW NAME QUALIFIER |* XAPLREL1 - VIEW COLUMN NAME |* XAPLBSCM - VIEW'S BASE TABLE QUALIFIER OR |* REPEATED VIEW QUALIFIER |* XAPLBNAM - VIEW'S BASE TABLE NAME OR |* REPEATED VIEW NAME |* XAPLBCOL - VIEW'S BASE TABLE COLUMN NAME OR |* REPEATED VIEW COLUMN NAME |* XAPLREL2 - VIEW'S BASE TABLE DATABASE NAME OR |* ZERO IF VIEW HAS NO APPLICABLE BASE TABLE ... The IFCID 314 trace is expanded to include offsets for the additional information passed in these new fields as follows: ... QW0314SO DS H OFFSET FROM QW0314 TO VIEWS * BASE TABLE QUALIFIER * (QW0314S) QW0314AO DS H OFFSET FROM QW0314 TO VIEWS * BASE TABLE NAME * (QW0314A) QW0314CO DS H OFFSET FROM QW0314 TO VIEWS * BASE TABLE COLUMN * (QW0314C) ... * QW0314S DSECT QW0314SL DS H LENGTH OF NEXT FIELD QW0314SN DS 0CL128 %U VIEWS BASE TABLE QUALIFIER * QW0314A DSECT QW0314AL DS H LENGTH OF NEXT FIELD QW0314AN DS 0CL128 %U VIEWS BASE TABLE NAME * QW0314C DSECT QW0314CL DS H LENGTH OF NEXT FIELD QW0314CN DS 0CL128 %U VIEWS BASE TABLE COLUMN NAME * ... All of the changes described have been made in the code. Whether ACAE is active or not: ------------------------------ DB2 has been modified to handle the REFRESH TABLE statement correctly for the MQTs described in the above example. V9 CHANGES: ACAE is active: --------------- 1.Insert, update and delete view authorization: For authorization of DELETE and INSERT, DB2 now passes the schema and name of the base table of the view, along with the view information currently passed. For the UPDATE privilege, DB2 additionally passes the base table column name. Instead of Triggers: In this case the base table of the view is not updated using the view. Instead all processing is based on the content of the trigger package. Here the view information is repeated in the base table fields to facilitate the view authorization check. Nested views: For INSERT/DELETE/UPDATE on any view in the nested stack, the base table information is passed in addition to the current view information. Intermediate views, between the view being processed and the base table are ignored. In the above cases if the view is not updateable the view information is repeated in the new base table fields. CREATE VIEW: During view creation, DB2 checks whether the view owner has INSERT, UPDATE and DELETE privileges on the underlying table or view for non read-only views. When these checks are on a base view, the base view information will be repeated in the new base table fields (XAPLBxxx) to facilitate the check. 2.View creation for others: The database information structure (XAPLDBS) has been expanded to include an implicit database identifier (XAPLDBIM) for each database passed to the ACAE. The ACAE parameter list (XAPL) has been expanded to include base table information for view authorization as follows: New fields: - XAPLBSCM - ADDRESS TO SCHEMA OF VIEW'S BASE TABLE - XAPLBNAM - ADDRESS TO NAME OF VIEW'S BASE TABLE - XAPLBCOL - ADDRESS TO COLUMN NAME OF VIEW'S BASE TABLE - XAPLDBIM - IN STRUCTURE XAPLDBS - IMPLICIT DATABASE INDICATOR Modified use of existing field for view authorization: - XAPLREL2 - ADDRESS TO DATABASE OF VIEW'S BASE TABLE - XAPLOOON - ADDRESS TO OWNER OF VIEW'S BASE TABLE - XAPLOOOT - OWNER TYPE OF VIEW'S BASE TABLE - XAPLIMPD - IF BASE TABLE INFORMATION IS PASSED THIS INDICATES THE VIEWS BASE TABLE IS IN AN IMPLICIT DATABASE Field XAPLDBIM has been added to XAPLDBS for V9 to indicate whether the database is implicit or not as follows: XAPLDBS DS 0F .XAPLOK2 ANOP XAPLDBNP DS F PTR TO NEXT DATABASE INFORMATION STRUC XAPLDBNM DS CL8 DATABASE NAME |XAPLDBDA DS CL1 'Y' - IS DBADM - OUTPUT |XAPLDBIM DS CL1 'Y' - IS AN IMPLICIT DATABASE XAPLRSV5 DS CL2 RESERVED - UNUSED Macro DSNXAPRV that documents specific fields passed to ACAE is updated as follows: ... DELETAUTV EQU 0052 DELETE V * XAPLOBJN - VIEW NAME * XAPLOWNQ - VIEW NAME QUALIFIER * XAPLOWNR - VIEW OWNER * XAPLONRT - VIEW OWNER TYPE |* XAPLBSCM - VIEW'S BASE TABLE QUALIFIER OR |* REPEATED VIEW QUALIFIER |* XAPLBNAM - VIEW'S BASE TABLE NAME OR |* REPEATED VIEW NAME |* XAPLOOON - VIEW'S BASE TABLE OWNER OR |* REPEATED VIEW OWNER |* XAPLOOOT - VIEW'S BASE TABLE OWNER TYPE OR |* REPEATED VIEW OWNER TYPE |* XAPLREL2 - VIEW'S BASE TABLE DATABASE NAME OR |* ZERO IF VIEW HAS NO APPLICABLE BASE TABLE |* XAPLIMPD - FLAG IS ON IF VIEWS BASE TABLE |* IS IN AN IMPLICIT DATABASE ... INSRTAUTV EQU 0051 INSERT V * XAPLOBJN - VIEW NAME * XAPLOWNQ - VIEW NAME QUALIFIER * XAPLOWNR - VIEW OWNER * XAPLONRT - VIEW OWNER TYPE |* XAPLBSCM - VIEW'S BASE TABLE QUALIFIER OR |* REPEATED VIEW QUALIFIER |* XAPLBNAM - VIEW'S BASE TABLE NAME OR |* REPEATED VIEW NAME |* XAPLOOON - VIEW'S BASE TABLE OWNER OR |* REPEATED VIEW OWNER |* XAPLOOOT - VIEW'S BASE TABLE OWNER TYPE OR |* REPEATED VIEW OWNER TYPE |* XAPLREL2 - VIEW'S BASE TABLE DATABASE NAME OR |* ZERO IF VIEW HAS NO APPLICABLE BASE TABLE |* XAPLIMPD - FLAG IS ON IF VIEWS BASE TABLE |* IS IN AN IMPLICIT DATABASE ... UPDTEAUTV EQU 0053 UPDATE V * XAPLOBJN - VIEW NAME * XAPLOWNQ - VIEW NAME QUALIFIER * XAPLOWNR - VIEW OWNER * XAPLONRT - VIEW OWNER TYPE |* XAPLREL1 - VIEW COLUMN NAME |* XAPLBSCM - VIEW'S BASE TABLE QUALIFIER OR |* REPEATED VIEW QUALIFIER |* XAPLBNAM - VIEW'S BASE TABLE NAME OR |* REPEATED VIEW NAME |* XAPLBCOL - VIEW'S BASE TABLE COLUMN NAME OR |* REPEATED VIEW COLUMN NAME |* XAPLOOON - VIEW'S BASE TABLE OWNER OR |* REPEATED VIEW OWNER |* XAPLOOOT - VIEW'S BASE TABLE OWNER TYPE OR |* REPEATED VIEW OWNER TYPE |* XAPLREL2 - VIEW'S BASE TABLE DATABASE NAME OR |* ZERO IF VIEW HAS NO APPLICABLE BASE TABLE |* XAPLIMPD - FLAG IS ON IF VIEWS BASE TABLE |* IS IN AN IMPLICIT DATABASE ... CRTVUAUTT EQU 0108 CREATE VIEW T * XAPLIMPD - FLAG IS ON IF ANY TABLE * INVOLVED IN VIEW CREATION IS * IN AN IMPLICIT DATABASE * XAPLREL2 - POINTER TO INFORMATION FOR * DATABASES WHOSE TABLES ARE * INVOLVED IN VIEW CREATION * XAPLDBNP - POINTER TO INFORMATION FOR * THE NEXT DATABASE * XAPLDBNM - DATABASE NAME |* XAPLDBIM IMPLICIT DATABASE INDICATOR The IFCID 314 trace is expanded to include offsets for the additional information passed in these new fields as follows: ... QW0314SO DS H OFFSET FROM QW0314 TO VIEWS * BASE TABLE QUALIFIER * (QW0314S) QW0314AO DS H OFFSET FROM QW0314 TO VIEWS * BASE TABLE NAME * (QW0314A) QW0314CO DS H OFFSET FROM QW0314 TO VIEWS * BASE TABLE COLUMN * (QW0314C) ... * QW0314S DSECT QW0314SL DS H LENGTH OF NEXT FIELD QW0314SN DS 0CL128 %U VIEWS BASE TABLE QUALIFIER * QW0314A DSECT QW0314AL DS H LENGTH OF NEXT FIELD QW0314AN DS 0CL128 %U VIEWS BASE TABLE NAME * QW0314C DSECT QW0314CL DS H LENGTH OF NEXT FIELD QW0314CN DS 0CL128 %U VIEWS BASE TABLE COLUMN NAME * ... QW0314RO DS H OFFSET FROM QW0314 TO OTHER |* OBJECT OWNER OR VIEWS BASE |* TABLE OWNER * (QW0314R) ... QW0314DX DSECT QW0314 DATABASE EXTENSION * AN ARRAY CONTAINING DATABASE * INFORMATION * QW0314DP DS F ADDRESS TO INFORMATION OF NEXT * DATABASE QW0314DN DS CL8 DATABASE NAME QW0314DA DS CL1 RESULTS OF DBADM CHECK |QW0314IM DS CL1 WHETHER DATABASE IS IMPLICIT | DS CL2 RESERVED All of the changes described have been made in the code. Whether ACAE is active or not: ------------------------------ DB2 has been modified to handle the REFRESH TABLE statement correctly for the MQTs described in the above example. Additional keywords: ABEND0C4 SQLMQT SQLREFRESH SQLTABLEUDF HOLD COMMENT FOR RELEASE 810, TYPE ACT: See PK78170 APAR/PTF text in the PTF cover letter for additional information about why this action is necessary. APAR PK78170 enables view's base table information to be used for view authorization by an external security system that performs access control, such as the RACF access control module (DSNXRXAC). After application of this PTF, the external security system must be updated to use the base table information provided for view authorization. The RACF access control module has been updated for this purpose by apar PK75583. If you are using the RACF access control module, apply PK75583 before this view authorization will be effective. HOLD COMMENT FOR RELEASE 910, TYPE ACT: See PK78170 APAR/PTF text in the PTF cover letter for additional information about why this action is necessary. APAR PK78170 enables view's base table information to be used for view authorization by an external security system that performs access control, such as the RACF access control module (DSNXRXAC). After application of this PTF, the external security system must be updated to use the base table information provided for view authorization. The RACF access control module has been updated for this purpose by apar PK75583. If you are using the RACF access control module, apply PK75583 before this view authorization will be effective. ž**** PE09/12/07 FIX IN ERROR. SEE APAR PM02841 FOR DESCRIPTION
APAR Information
APAR number
PK78170
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-01-06
Closed date
2009-08-07
Last modified date
2009-12-21
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK49117 UK49118
Modules/Macros
DSNDACAE DSNDCHK DSNDQW04 DSNDXAPL DSNUGMAP DSNUGPCK DSNUKINE DSNXACAE DSNXAICK DSNXAPRV DSNXARU3 DSNXA02 DSNXA03 DSNXA05 DSNXA07 DSNXA08 DSNXA09 DSNXEDST DSNXIAB1 DSNXIAB2 DSNXIAB5 DSNXIAVW DSNXICLN DSNXICRT DSNXICTR DSNXICVW DSNXIRIX DSNXISB2 DSNXISB4 DSNXISUB DSNXIVRT DSNXI15 DSNXODD2 DSNXODML DSNXOD0Z DSNXOIN DSNXOOP DSNXOST DSNXOTL DSNXTBA
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
21 December 2009