tag:blogger.com,1999:blog-4516533711330247058.post5945094896906205992..comments2024-03-29T05:15:24.778-07:00Comments on Robert's Db2 blog: Db2 for z/OS and RACF, Part 2: Db2-Internal SecurityRoberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-4516533711330247058.post-85923720993937348312020-12-22T16:23:43.260-08:002020-12-22T16:23:43.260-08:00You are welcome. Good to know that the issue has b...You are welcome. Good to know that the issue has been resolved.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-34398785094079627002020-12-22T05:29:13.172-08:002020-12-22T05:29:13.172-08:00Robert,
I tested a job by replacing the asterisk i...Robert,<br />I tested a job by replacing the asterisk in the PKLIST with actual location name and I could see that GETPAGES against SYSIBM auth tables disappeared.Thank you so much for your help!!<br />Ravikumar Srinivasanhttps://www.blogger.com/profile/17932661354568770827noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-70863327980482317072020-12-14T17:58:15.575-08:002020-12-14T17:58:15.575-08:00Hard to say. At least some of the Db2 catalog acce...Hard to say. At least some of the Db2 catalog accesses could be related to whatever data federation software is used to makes the remote Oracle database server appear to the Db2 for z/OS system as a DRDA server. It could also relate to the entries in the Db2 for z/OS system's communications database that pertain to the remote Oracle database server - for example, it may be that outbound ID translation is occurring.<br /><br />Accesses to Db2 catalog authorization tables can often be reduced by removing asterisks in location name positions in a plan's PKLIST. You have *ORAPROD.*. If there is an ORAPROD collection at the local Db2 system, remove the asterisk before ORAPROD so that the location name will default to the local system. If there is also an ORAPROD collection at the remote server, list ORAPROD again in the PKLIST, prefixed by the location name for the remote Oracle server.<br /><br />Additionally, the CACHEPAC and CACHERAC parameters in ZPARM should both have a value of 10M (the fix for APAR PH28280 removes both of these parameters from ZPARM and sets them both internally to 10M).<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-59443976586540104262020-12-13T22:06:20.933-08:002020-12-13T22:06:20.933-08:00Robert,
Thank you so much for your reply!!
A99S018...Robert,<br />Thank you so much for your reply!!<br />A99S0187 is a COBOL package that issues a SELECT SQL against a remote ORCLE table about 189,549 times and it was bind statically BIND to ORACLE and BIND Plan has PKLIST ( *.ORAPROD.*). For this SELECT SQL, I see the below statistics from CA detector. <br /><br />947,745 Getpage against DSNAUH01 index of SYSUSERAUTH <br />947,745 Getpage against tablespace of SYSUSERAUTH <br />1,137,294 Getpage against DSNAGH01 index of SYSRESAUTH<br />1,705,941 Getpage against DSNKAX02 index of SYSPACKAUTH<br />947,745 Getpage against DSNKKX02 index of SYSPACKAGE<br /><br />Do you think the above GETPAGEs are normal for a remote package ?. <br />Ravikumar Srinivasanhttps://www.blogger.com/profile/17932661354568770827noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-15996495643239037562020-12-12T10:34:17.371-08:002020-12-12T10:34:17.371-08:00VALIDATE(BIND), from an authorization standpoint, ...VALIDATE(BIND), from an authorization standpoint, relates only to privileges required to create the package (i.e., to successfully bind the package). When there is a request to execute the package, that will drive authorization checks related to package execution, versus package creation.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-59097178609691729622020-12-12T04:28:42.820-08:002020-12-12T04:28:42.820-08:00Below screen shows the SQL issued from LOCAL syste...Below screen shows the SQL issued from LOCAL system<br /> <br /> 20.0 > ------------- DETECTOR Plan SQL Display ------------ 12/11/20 05:11<br /> Command ==> Scroll ==> CSR <br /> LINE 4 OF 4<br /> DB2 SSID ==> DSN Planname ==> A99P910U Program ==> A99S0068 <br /> Type ==> PACKAGE Collid ==> PROD <br /> Version ==> 2020-11-03-17.12.44.217010 <br /> Total/Avg => T <br /> <br /> Interval Date => 11/30/20 Interval Time => 09:00:00 Elapsed Time => 03:00 <br /> -------------------------------------------------------------------------------<br /> <br /> Q -View SQL text, T -View Tables/Indexes, E -Explain, D -View Detail <br /> <br /> SQL_CALL SQL CPUPCT INDB2_TIME INDB2_CPU GETPAGE GETPFAIL <br /> -------- ---------- ------- ------------ ------------ ---------- ---------- <br /> _ SELECT 241723 10.13% 00:05.138987 00:03.691959 559854 0 <br /> ******************************* BOTTOM OF DATA ********************************<br /> <br /> Below screen shows the GETPAGEs for the above query. <br /> <br /><br /> 20.0 > -------- Detector SQL Table Activity Display -------- 12/11/20 05:11<br /> COMMAND ===> Scroll ==> CSR <br /> LINE 1 OF 7<br /> Total/Avg => T DB2 SSID ==> DSN <br /> Program ==> A99S0068 Type ==> PKGE Statement ==> 0005760 <br /> Ctoken ==> 1B188E82167AAC88 Section ==> 00003 <br /> Collid ==> PROD SQL Call Type ==> SELECT <br /> Version ==> 2020-11-03-17.12.44.217010 <br /> <br /> Interval Date => 11/30/20 Time => 09:00:00 Elaps Time => 03:00 Samp => 100%<br /> -------------------------------------------------------------------------------<br /> <br /> TABLE INDEX TB_SEQ_GP TB_IDX_GP TB_LNK_GP IS_GETP <br /> ------------------ -------- ---------- ---------- ---------- ---------- <br /> SYSUSERAUTH DSNAUH01 1265 <br /> SYSUSERAUTH 0 1265 0 <br /> SYSRESAUTH DSNAGH01 1518 <br /> SYSPACKAUTH DSNKAX02 2277 <br /> SYSPACKAGE DSNKKX02 1265 <br /> FULFILMNT_MNT RBODFFK0 241532 <br /> FULFILMNT_MNT 0 311462 0 <br /> ******************************* BOTTOM OF DATA ********************************<br />Ravikumar Srinivasanhttps://www.blogger.com/profile/17932661354568770827noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-81781011782317395322020-12-12T04:25:32.420-08:002020-12-12T04:25:32.420-08:00Hi Robert,
Thank you so much for your reply!. Belo...Hi Robert,<br />Thank you so much for your reply!. Below report was taken from CA detector. A99S0187 package is a COBOL program and it issues SELECT to a remote ORACLE table. This A99S0187 package was BIND with VALIDATE(BIND). <br />I would like to understand why DB2 is doing GETPAGEs against SYSIBM Authorization tables though this package was BIND with VALIDATE(BIND) parameter.<br /><br /> 20.0 > ------------- DETECTOR Plan SQL Display ------------ 12/11/20 05:10<br /> Command ==> Scroll ==> CSR <br /> LINE 1 OF 2<br /> DB2 SSID ==> DSN Planname ==> A99P910U Program ==> A99S0187 <br /> Type ==> RMPK Collid ==> ORAPROD <br /> Version ==> <br /> Total/Avg => T <br /> <br /> Interval Date => 11/30/20 Interval Time => 09:00:00 Elapsed Time => 03:00 <br /> -------------------------------------------------------------------------------<br /> <br /> Q -View SQL text, T -View Tables/Indexes, E -Explain, D -View Detail <br /> <br /> SQL_CALL SQL CPUPCT INDB2_TIME INDB2_CPU GETPAGE GETPFAIL <br /> -------- ---------- ------- ------------ ------------ ---------- ---------- <br /> _ SELECT 189549 50.19% 02:26:00.973 00:32.886688 4928274 0 <br /><br />Below screen shows the GETPAGEs for the above the qurery <br /> <br /> 20.0 > -------- Detector SQL Table Activity Display -------- 12/11/20 05:10<br /> COMMAND ===> Scroll ==> CSR <br /> LINE 1 OF 5<br /> Total/Avg => T DB2 SSID ==> DSN <br /> Program ==> A99S0187 Type ==> RMPK Statement ==> 0001543 <br /> Ctoken ==> 1B188EE818425BD0 Section ==> 00001 <br /> Collid ==> ORAPROD SQL Call Type ==> SELECT <br /> Version ==> <br /> <br /> Interval Date => 11/30/20 Time => 09:00:00 Elaps Time => 03:00 Samp => 100%<br /> -------------------------------------------------------------------------------<br /> <br /> TABLE INDEX TB_SEQ_GP TB_IDX_GP TB_LNK_GP IS_GETP <br /> ------------------ -------- ---------- ---------- ---------- ---------- <br /> SYSUSERAUTH DSNAUH01 947745 <br /> SYSUSERAUTH 0 947745 0 <br /> SYSRESAUTH DSNAGH01 1137294 <br /> SYSPACKAUTH DSNKAX02 1705941 <br /> SYSPACKAGE DSNKKX02 947745 Ravikumar Srinivasanhttps://www.blogger.com/profile/17932661354568770827noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-67437877505923556822020-12-11T10:28:45.685-08:002020-12-11T10:28:45.685-08:00First, of course, SYSPACKAGE is not an authorizati...First, of course, SYSPACKAGE is not an authorization table.<br /><br />As for the other tables, you would expect SYSRESAUTH to be accessed as Db2 checks privileges related to the use of things such as collections, buffer pools and table spaces. Similarly, you would expect accesses to SYSUSERAUTH as Db2 checks system privileges of users, such as SYSADM, SYSTEM DBADM and BINDAGENT, as well as authorization to do such things as start and stop traces, issue -DISPLAY commands, and issue BIND PACKAGE commands with the ADD option.<br /><br />With regard to SYSPACKAUTH, you would expect Db2 to check for the EXECUTE authority on packages when there are requests to execute the packages. A lot of those package authorization checks should be satisfied via the in-memory package authorization cache, but some checks will still likely require access to SYSPACKAUTH. Sometimes, an overly high level of package authorization checks are the result of PKLIST specifications for "local to Db2" plans (i.e., plans other than DISTSERV, which is not a plan in the traditional sense) that have an asterisk (i.e., *) for the location of listed collections - removing those asterisks can resolve that issue (absent an *, location defaults to the local Db2 system). If in fact packages at a remote server need to be executed, explicitly identify that remote location for the relevant collection in the plan's PKLIST.<br /><br />Sometimes, elevated levels of package authorization checks can be related to stored procedure calls, when the OWNER of the stored procedure has not been explicitly granted EXECUTE on the stored procedure's package (this is not an issue when the stored procedure OWNER has an authority, such as SYSADM, that already has the ability to execute packages).<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-5091439350557761742020-12-11T04:03:06.162-08:002020-12-11T04:03:06.162-08:00Hi Robert
Thank you so much for your post!!
We are...Hi Robert<br />Thank you so much for your post!!<br />We are seeing get pages against SYSIBM auth tables such as SYSUSERAUTH,SYSRESAUTH,SYSPACKAUTH and SYSPACKAGE. The packages are BIND with VALIDATE(BIND). I would like to understand why auth tables are accessed before executing the SQL queries. Thank you so much for your help in Advance !!<br />Below is the ZPARM setting <br />Authorization option to enable/disable DB2 auth. YES AUTH <br />Default authorization cache size. 4096 AUTHCACH <br />Authorization exit cache refresh. NONE AUTHEXIT_CACHERE><br />Authorization exit check. PRIMARY AUTHEXIT_CHECK <br />Cache store limit. 0 CACHE_DEP_TRACK_><br />Dynamic SQL statement cache support option. YES CACHEDYN <br />Free local cached statements to relieve storage. 0 CACHEDYN_FREELOC><br />Max storage for package authorization cache. 10485760 CACHEPAC <br />Max storage for routine authorization cache. 10485760 CACHERAC <br />Ravikumar Srinivasanhttps://www.blogger.com/profile/17932661354568770827noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-35640300507899465062020-02-16T23:05:29.872-08:002020-02-16T23:05:29.872-08:00That ZPARM idea could be a good enhancement reques...That ZPARM idea could be a good enhancement request. You could enter that request (or any other Db2 for z/OS-related enhancement request) at this URL: https://ibm-data-and-ai.ideas.aha.io<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-78230069183504711712020-02-07T13:34:40.589-08:002020-02-07T13:34:40.589-08:00Thanks Robert.
We went to RACF security some time...Thanks Robert.<br /><br />We went to RACF security some time ago.<br /><br />Pros: as our databases and tables are mostly defined with a well known naming standard, we can use generic RACF profiles. We went from thousands of Grant to a bunch of profiles. And they are ready before object creation.<br /><br />Contras: Db2 continues to create implicit auths. We would like a zparm to deactivate internal Db2 security at all.<br /><br />Extra: we need a role similar to DATAACCESS but for readonly access. A update to the exit was the solution. Unknownhttps://www.blogger.com/profile/07789823511157207638noreply@blogger.com