DB2 External security and EXPLAIN statement behaviour

Mahmood Wadee

DB2 External security and EXPLAIN statement behaviour

Hello all

Hoping you can provide some insight into a small issue we facing.

DB2 was upgraded to V10.

We using RACF EXTERNAL security

Customer issues an EXPLAIN statement with the keyword ALL and received the following error BUT the statement completes successfully
Error/warning received
ICH408I USER(M98B695 ) GROUP(FOMFB   ) NAME(NANINE (N.G.J.) OVER)       
DI08.EXPLAIN CL(MDSNSM  )                                             
INSUFFICIENT ACCESS AUTHORITY                                         
FROM DI%%.** (G)                                                     
ACCESS INTENT(READ   )  ACCESS ALLOWED(NONE   )                       
ICH408I USER(M98B695 ) GROUP(FOMFB   ) NAME(NANINE (N.G.J.) OVER)       
DI08.EXPLAIN CL(MDSNSM  )                                             
INSUFFICIENT ACCESS AUTHORITY                                         
FROM DI%%.** (G)                                                     
ACCESS INTENT(READ   )  ACCESS ALLOWED(NONE   ) 

This is what I know
-------------------------
This works in V9 because the checking for the explain statement is ONLY done on the specified SQL . So if the EXPLAIN "SELECT" was done , authorization will be verified on the select statement via the external racf profile for that table.

In V10 the authorization checking extends a little bit further. 
According to the manual it does the following

To issue the EXPLAIN statement with the PLAN and ALL keywords, the privilege
set that is defined below must include at least one of the following:
1) EXPLAIN
2) SQLADM
3) System DBADM
4) The authorization rules that are defined for the SQL statement specified in the
EXPLAIN statement. For example, the authorization rules that apply when a 
DELETE statement is explained are the authorization rules for the DELETE
statement.

I need to know why does RACF issue a warning message when the access is granted in point 4 .

We have created a RACF profile to prevent the message but the customer is not happy. They believe we are just masking the fact that RACF is erroneously issuing the warning message because eventually in the chain of access it is being authorized to execute the statement.



 



 

Edited By:
Mahmood Wadee[Organization Members] @ Oct 11, 2017 - 03:35 AM (America/Eastern)

Charles Brown

DB2 External security and EXPLAIN statement behaviour
(in response to Mahmood Wadee)
Hello Mahmood,
... then, based on your assumption, what error messages would the customer get if he perform an EXPLAIN on a simple SELECT statement? Hmmm! So the customer is right? Sounds familiar?

Here is the problem description:
There exists a generic profile called "DI%%.** ". This profile governs the resource (DI08.EXPLAIN) that Nannie, your customer was attempting to access.

Here is the problem cause:
Nannie attempted to READ the resource. -- ACCESS INTENT(READ )
But RACF was instructed to allow no one from his group access. -- ACCESS ALLOWED(NONE)

Resolution:
Send a friendly email to your RACF person -- he'll hook you up. While waiting, you may also want to check your plan_tables if they're granted PUBLIC. They should be granted PUBLIC ...

Hope this helps

Chas/b
NZ DBA

Sent from my iPad

> On Oct 11, 2017, at 2:33 AM, Mahmood Wadee <[login to unmask email]> wrote:
>
> Hello all
>
> Hoping you can provide some insight into a small issue we facing.
>
> DB2 was upgraded to V10.
>
> We using RACF EXTERNAL security
>
> Customer issues an EXPLAIN statement with the keyword ALL and received the following error BUT the statement completes successfully
> Error/warning received
> ICH408I USER(M98B695 ) GROUP(FOMFB ) NAME(NANINE (N.G.J.) OVER)
> DI08.EXPLAIN CL(MDSNSM )
> INSUFFICIENT ACCESS AUTHORITY
> FROM DI%%.** (G)
> ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )
> ICH408I USER(M98B695 ) GROUP(FOMFB ) NAME(NANINE (N.G.J.) OVER)
> DI08.EXPLAIN CL(MDSNSM )
> INSUFFICIENT ACCESS AUTHORITY
> FROM DI%%.** (G)
> ACCESS INTENT(REA ) ACCESS ALLOWED(NONE )
>
>
> This is what I know
> -------------------------
> This works in V9 because the checking for the explain statement is ONLY done on the specified SQL . So if the EXPLAIN "SELECT" was done , authorization will be verified on the select statement via the external racf profile for that table.
>
> In V10 the authorization checking extends a little bit further.
> According to the manual it does the following
>
> To issue the EXPLAIN statement with the PLAN and ALL keywords, the privilege
> set that is defined below must include at least one of the following:
> 1) EXPLAIN
> 2) SQLADM
> 3) System DBADM
> 4) The authorization rules that are defined for the SQL statement specified in the
> EXPLAIN statement. For example, the authorization rules that apply when a
> DELETE statement is explained are the authorization rules for the DELETE
> statement.
>
> I need to know why does RACF issue a warning message when the access is granted in point 3 .
>
> We have created a RACF profile to prevent the message but the customer is not happy. They believe we are just masking the fact that RACF is erroneously issuing the warning message because eventually in the chain of access it is being authorized to execute the statement.
>
>
>
>
>
>
>
>
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Mahmood Wadee

RE: DB2 External security and EXPLAIN statement behaviour
(in response to Mahmood Wadee)

Chris ,

You have disregarded my question

I never said the problem was the RACF error message.

I stated that we have already created a RACF profile to fix the warning message.

The problem is as I have stated .. .The EXPLAIN statement works even when we DO NOT have a RACF profile defined. The customer is not happy with us creating the profile in RACF .. they want to know why the error is issued when the statement executes successfully.

My question is

I need to know why does RACF issue a warning message when the access is granted in point 4 which is

4) The authorization rules that are defined for the SQL statement specified in the
EXPLAIN statement. For example, the authorization rules that apply when a 
DELETE statement is explained are the authorization rules for the DELETE
statement.

Lizette Koehler

DB2 External security and EXPLAIN statement behaviour
(in response to Mahmood Wadee)
There is a RACF list that might be helpful. If you have not joined, you can go to this URL RACF http://www.listserv.uga.edu/archives/racf-l.html





However I will attempt to reconcile your concern.



1. If the RACF class MDSNSM is in WARING the word WARNIG would be in the ICH480I. Since it is not, it is not in warning mode. How was it determined it was in warning mode?
2. RACF does not FAIL a request. It merely tells the requestor RC0 (allowed) or RC not 0 – disallowed (I think it is an RC4)
3. Please show how you corrected the Facility class for the user

a. UACC READ
b. ACL List includes the ID to run Explain?
c. Created a new entity for Class MDSNSM for DI%%.EXPLAIN?



So in the first event where the ICH408I says INTENT(READ) ALLOWED(NONE) that says RACF returned an RC04 to the utility saying the request was not allowed. An ICH408I is displayed so we will know what happened.



The product being used does a RACCHECK (Or something similar) – RACF Verifies the access and responds to the request to the utility. It either says RC0 or RC4 (I am thinking it is a 4). The utility will then have code that says IF RC0 then proceed, If not RC0 fail the request. RACF produces a message to show what it knew about the request.







Once you change the Class MDSNSM – did your security team need to REFRESH the class? If so, was that done?



Once the change was made, what message did the user receive when the EXPLAIN was run?



it is hard to know what happened without knowing all the steps done.



Lizette





From: Mahmood Wadee [mailto:[login to unmask email]
Sent: Wednesday, October 11, 2017 12:34 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 External security and EXPLAIN statement behaviour



Hello all

Hoping you can provide some insight into a small issue we facing.

DB2 was upgraded to V10.

We using RACF EXTERNAL security

Customer issues an EXPLAIN statement with the keyword ALL and received the following error BUT the statement completes successfully
Error/warning received
ICH408I USER(M98B695 ) GROUP(FOMFB ) NAME(NANINE (N.G.J.) OVER)
DI08.EXPLAIN CL(MDSNSM )
INSUFFICIENT ACCESS AUTHORITY
FROM DI%%.** (G)
ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )
ICH408I USER(M98B695 ) GROUP(FOMFB ) NAME(NANINE (N.G.J.) OVER)
DI08.EXPLAIN CL(MDSNSM )
INSUFFICIENT ACCESS AUTHORITY
FROM DI%%.** (G)
ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )

This is what I know
-------------------------
This works in V9 because the checking for the explain statement is ONLY done on the specified SQL . So if the EXPLAIN "SELECT" was done , authorization will be verified on the select statement via the external racf profile for that table.

In V10 the authorization checking extends a little bit further.
According to the manual it does the following

To issue the EXPLAIN statement with the PLAN and ALL keywords, the privilege
set that is defined below must include at least one of the following:
1) EXPLAIN
2) SQLADM
3) System DBADM
4) The authorization rules that are defined for the SQL statement specified in the
EXPLAIN statement. For example, the authorization rules that apply when a
DELETE statement is explained are the authorization rules for the DELETE
statement.

I need to know why does RACF issue a warning message when the access is granted in point 3 .

We have created a RACF profile to prevent the message but the customer is not happy. They believe we are just masking the fact that RACF is erroneously issuing the warning message because eventually in the chain of access it is being authorized to execute the statement.











-----End Original Message-----

Phil Grainger

DB2 External security and EXPLAIN statement behaviour
(in response to Mahmood Wadee)
I have no personal experience, but it could be that Db2 is stepping through the different auth checks to see if you do indeed have the authorization to do the explain

It checks in RACF and discovers that you DON’T (and RACF issues an error message), then Db2 checks further and finds that you DO have the authority, so the EXPLAIN works as planned

Or it could be something else entirely
________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Mahmood Wadee [mailto:[login to unmask email]
Sent: 11 October 2017 11:19
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 External security and EXPLAIN statement behaviour


Chris ,

You have disregarded my question

I never said the problem was the RACF error message.

I stated that we have already created a RACF profile to fix the warning message.

The problem is as I have stated .. .The EXPLAIN statement works even when we DO NOT have a RACF profile defined. The customer is not happy with us creating the profile in RACF .. they want to know why the error is issued when the statement executes successfully.

My question is

I need to know why does RACF issue a warning message when the access is granted in point 4 which is

4) The authorization rules that are defined for the SQL statement specified in the
EXPLAIN statement. For example, the authorization rules that apply when a
DELETE statement is explained are the authorization rules for the DELETE
statement.

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

Mahmood Wadee

RE: DB2 External security and EXPLAIN statement behaviour
(in response to Lizette Koehler)

hello Lizette ,

thanks for this

The RACF profile is setup without warning.

If RACF issues a RC=4 it should not issue a ICH408I according to most of the authorization scenarios that are presented in the manual . Normally a message gets issued when RC = 8 .

The profile was defined as DI%%.EXPLAIN 

Once the profile was defined the user gets no message as expected.

Mahmood Wadee

RE: DB2 External security and EXPLAIN statement behaviour
(in response to Phil Grainger)

Thanks Phil

This is exactly how I explained it to the customer . They not happy with the explanation though :-)

Chad Walmer

DB2 External security and EXPLAIN statement behaviour
(in response to Mahmood Wadee)
Phil is correct in that the DB2 RACF exit walks through the various authorities that would allow a given access. However, it is supposed to issue a RACROUTE request with the LOG=NOFAIL parameter while determining if the user has the appropriate access. The NOFAIL parameter will prevent an ICH408I message from being issued. Once it is determined the user does not have the authority, the exit is then supposed to re-issue the RACROUTE request with the LOG=ASIS parameter on the most specific (lowest level) access that was checked so that an ICH408I message is issued.

It’s possible the exit is not working correctly, or as Lizette mentioned, I have seen these messages when the RACF profile is in WARN mode and I believe with certain RACF audit settings on the profile as well (but can’t remember the specifics at this time.) You can monitor what the DB2 RACF exit is doing by starting an IFCID 314 trace.

Chad Walmer


From: Mahmood Wadee [mailto:[login to unmask email]
Sent: Thursday, October 12, 2017 8:53 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 External security and EXPLAIN statement behaviour


Thanks Phil

This is exactly how I explained it to the customer . They not happy with the explanation though :-)

-----End Original Message-----