DB2 table access issue

Sankar Sathyamoorthy

DB2 table access issue

We were working on a security issue wherein a user was trying to select some values from a table using DSNTIAUL. Later we found a RACF group (group1) could not access the table (even though grant select was issued to group1 to which the user is connected). We resolved by giving select access to another RACF group group2 to which the user is connected. However I am interested in finding out why group1 got -551 even though group1 had select access. We gave group1 select access on other tables for testing purpose but user still got -551 on other tables as well.  

Up on reading I found unwanted secondary AUTH IDS (RACF group) access to DB2 is controlled by connection exit routine.  

Is there a way to get a list of Secondary Auth ids that is allowed to access DB2? Does anyone have a sample job to get this info?

Env: DB2 11.1 CM.

z/Os 2.1

Thanks,

Sankar

Lizette Koehler

DB2 table access issue
(in response to Sankar Sathyamoorthy)
If you have not done so, you may wish to join the RACF list to ask this question.



They may be better able to help you list RACF elements



To join, if you have not done so, use this URL



RACF http://www.listserv.uga.edu/archives/racf-l.html





Lizette



From: Sankar Sathyamoorthy [mailto:[login to unmask email]
Sent: Thursday, January 25, 2018 1:49 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 table access issue



We were working on a security issue wherein a user was trying to select some values from a table using DSNTIAUL. Later we found a RACF group (group1) could not access the table (even though grant select was issued to group1 to which the user is connected). We resolved by giving select access to another RACF group group2 to which the user is connected. However I am interested in finding out why group1 got -551 even though group1 had select access. We gave group1 select access on other tables for testing purpose but user still got -551 on other tables as well.

Up on reading I found unwanted secondary AUTH IDS (RACF group) access to DB2 is controlled by connection exit routine.

Is there a way to get a list of Secondary Auth ids that is allowed to access DB2? Does anyone have a sample job to get this info?

Env: DB2 11.1 CM.

z/Os 2.1

Thanks,

Sankar



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

Sankar Sathyamoorthy

RE: DB2 table access issue
(in response to Lizette Koehler)

I thought this was more of a DB2 question than a RACF question that is why I posted it here. 

Thank you for the link. I enrolled myself in that list a few weeks ago and requested access but no response from them yet.

I will try again.

Thanks,

Sankar

Robert Plata

DB2 table access issue
(in response to Sankar Sathyamoorthy)
Hi Sankar,
Do you have a tool that assists with DBA functions – like CA RC/Query, BMC Catalog Manager, or even IBM Data Studio? All of these can give you list of what RACF groups are granted privileges on a particular table. In my experience, I have found these tools very useful in working through -551s. Otherwise, you will have to query SYSIBM.SYSTABAUTH directly.

Or, are you asking about RACF resource classes? Or, are you asking about the DB2 authorization exit?

I’m just looking for a little clarification.

Robert Plata

From: Sankar Sathyamoorthy [mailto:[login to unmask email]
Sent: Thursday, January 25, 2018 1:14 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 table access issue


I thought this was more of a DB2 question than a RACF question that is why I posted it here.

Thank you for the link. I enrolled myself in that list a few weeks ago and requested access but no response from them yet.

I will try again.

Thanks,

Sankar

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

Sankar Sathyamoorthy

RE: DB2 table access issue
(in response to Robert Plata)

Thanks Robert for your time in trying to help me out. 

We do have RC/Query that is where I confirmed that the table had access to RACF group group1.

We provide access on DB2 objects directly to the RACF groups through GRANT SQL statements, we do n't provide access through DB2 RACF class or Permit command.

I am asking about DB2 security exit. I think somewhere in DB2 Authorization exits DB2 was instructed to not to allow the RACF group Group1. 

Thanks,

Sankar

Edited By:
Sankar Sathyamoorthy[Organization Members] @ Jan 25, 2018 - 05:36 PM (America/Central)

Robert Plata

DB2 table access issue
(in response to Sankar Sathyamoorthy)
Sorry that I misunderstood.

Your system programmer would know where the authorization exit source resides…[login to unmask email]

From: Sankar Sathyamoorthy [mailto:[login to unmask email]
Sent: Thursday, January 25, 2018 3:33 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 table access issue


Thanks Robert for your time in trying to help me out.

We do have RC/Query that is where I confirmed that the table had access to RACF group group1.

We provide access on DB2 objects directly to the RACF groups through GRANT SQL statements, we do n't provide access through DB2 RACF class or Permit command.

I am asking about DB2 security exit. I think somewhere in DB2 security exits DB2 was instructed to not to allow the RACF group Group1.

Thanks,

Sankar

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

James Campbell

DB2 table access issue
(in response to Sankar Sathyamoorthy)
IFCID 83 records the secondary auth-ids that are available at sign-on. (87 for CICS and IMS
attachements)

Or you can write code to extract IFCID 234, but that might be more work. If you have QMF, it
provides a user table functions that reads this and return the results.

James Campbell



On 25 Jan 2018 at 13:48, Sankar Sathyamoorthy wrote:

>
> We were working on a security issue wherein a user was trying to select some values from a table
> using DSNTIAUL. Later we found a RACF group (group1) could not access the table (even
> though grant select was issued to group1 to which the user is connected). We resolved by giving
> select access to another RACF group group2 to which the user is connected. However I am
> interested in finding out why group1 got -551 even though group1 had select access. We gave
> group1 select access on other tables for testing purpose but user still got -551 on other tables as
> well.  
> Up on reading I found unwanted secondary AUTH IDS (RACF group) access to DB2 is controlled
> by connection exit routine.  
> Is there a way to get a list of Secondary Auth ids that is allowed to access DB2? Does anyone
> have a sample job to get this info?
> Env: DB2 11.1 CM.
> z/Os 2.1
> Thanks,
> Sankar
>

Sankar Sathyamoorthy

RE: DB2 table access issue
(in response to James Campbell)

Thank you James for your suggestion.

I tried to activate SECIDTR (which uses trace class 7 and IFCID 55, 83, 87 and 319) for secondary ID utilization in CA Platinum. I tried to execute the select from SPUFI and Batch job. I did not get any trace record for that id. Then I tried to explicity code SET CURRENT SQLID=group1 and  SET CURRENT SQLID=group2 in two separate executions. I got -553 for group1 but no record yet in SECIDTR. However I got a record punched with successful status in SECIDTR for group2.

Hi Robert

I will ask my DB2 SYSADM about what RACF groups are being allowed to access DB2 tables, may be that is the easier way to go.

 

Thanks,

Sankar

Sankar Sathyamoorthy

RE: DB2 table access issue
(in response to Sankar Sathyamoorthy)

Hi James

Forgot to mention that we do have QMF but not sure how to get the details. Could you point me to the user table that will have the details?

Thanks,

Sankar

James Campbell

DB2 table access issue
(in response to Sankar Sathyamoorthy)
https://www.ibm.com/support/knowledgecenter/SS9UMF_10.1.0/igm/tpc/dsq_user_defined_f
unc.html

Definitions are in SDSQSAPE(DSQ0UDF)

James Campbell

On 29 Jan 2018 at 15:54, Sankar Sathyamoorthy wrote:

>
> Hi James
> Forgot to mention that we do have QMF but not sure how to get the details. Could you point me to
> the user table that will have the details?
> Thanks,
> Sankar
>

Anguraj Rathinasamy

DB2 table access issue
(in response to Robert Plata)
Sankar, table has granted with two group.. however is there any could you verify user has attached to both group - group1 and group2 ?



Sent from my iPhone

> On Jan 25, 2018, at 5:46 PM, PLATA Robert M <[login to unmask email]> wrote:
>
> Hi Sankar,
> Do you have a tool that assists with DBA functions – like CA RC/Query, BMC Catalog Manager, or even IBM Data Studio? All of these can give you list of what RACF groups are granted privileges on a particular table. In my experience, I have found these tools very useful in working through -551s. Otherwise, you will have to query SYSIBM.SYSTABAUTH directly.
>
> Or, are you asking about RACF resource classes? Or, are you asking about the DB2 authorization exit?
>
> I’m just looking for a little clarification.
>
> Robert Plata
>
> From: Sankar Sathyamoorthy [mailto:[login to unmask email]
> Sent: Thursday, January 25, 2018 1:14 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: DB2 table access issue
>
> I thought this was more of a DB2 question than a RACF question that is why I posted it here.
>
> Thank you for the link. I enrolled myself in that list a few weeks ago and requested access but no response from them yet.
>
> I will try again.
>
> Thanks,
>
> Sankar
>
>
> -----End Original Message-----
>
> 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]
> ** ** ** Attend the 2018 IDUG Tech Conference North America ** ** **
> ---> Philadelphia, Pennsylvania, April 29 - May 03, 2018 <---
> http://www.idug.org/na2018
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Sankar Sathyamoorthy

RE: DB2 table access issue
(in response to Anguraj Rathinasamy)

Thank you James for the link. I will check that. 

Yes Anguraj, I myself verified that the user is present in both the groups.