DB2 zOS v11 - Grant SELECT only access to all tables?

Michelle Witt

DB2 zOS v11 - Grant SELECT only access to all tables?

Is there a way to grant SELECT only access to all tables without granting them individually to tables? We're looking for something like DATAACCESS, but we need to limit the access to SELECT only and not include INSERT, UPDATE, DELETE. Want it at the system level so we don't have to manage granting the access to new tables that are created. We looked in the manuals but not finding anything, just want to make sure we didn't miss something. A coworker said they have the ability to grant administrator privilege on SQL server and then can DENY specific authority they don't want included, but I don't know of anything similar for zOS. We won't be going to v12 until next year, but I didn't see anything like that in v12 either.

Thanks!

John Bucaria

DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Michelle Witt)
I’m not aware of any fastpath for this. We have the CA RC/Secure product which generates the GRANT statements for you.

From: Michelle Witt <[login to unmask email]>
Sent: Wednesday, October 02, 2019 4:10 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 zOS v11 - Grant SELECT only access to all tables?


Is there a way to grant SELECT only access to all tables without granting them individually to tables? We're looking for something like DATAACCESS, but we need to limit the access to SELECT only and not include INSERT, UPDATE, DELETE. Want it at the system level so we don't have to manage granting the access to new tables that are created. We looked in the manuals but not finding anything, just want to make sure we didn't miss something. A coworker said they have the ability to grant administrator privilege on SQL server and then can DENY specific authority they don't want included, but I don't know of anything similar for zOS. We won't be going to v12 until next year, but I didn't see anything like that in v12 either.

Thanks!

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

Anguraj Rathinasamy

DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to John Bucaria)

You can construct the grant sql stmt from systables.

> On Oct 2, 2019, at 4:22 PM, Bucaria, John <[login to unmask email]> wrote:
>
> 
> I’m not aware of any fastpath for this. We have the CA RC/Secure product which generates the GRANT statements for you.
>
> From: Michelle Witt <[login to unmask email]>
> Sent: Wednesday, October 02, 2019 4:10 PM
> To: [login to unmask email]
> Subject: [DB2-L] - DB2 zOS v11 - Grant SELECT only access to all tables?
>
> Is there a way to grant SELECT only access to all tables without granting them individually to tables? We're looking for something like DATAACCESS, but we need to limit the access to SELECT only and not include INSERT, UPDATE, DELETE. Want it at the system level so we don't have to manage granting the access to new tables that are created. We looked in the manuals but not finding anything, just want to make sure we didn't miss something. A coworker said they have the ability to grant administrator privilege on SQL server and then can DENY specific authority they don't want included, but I don't know of anything similar for zOS. We won't be going to v12 until next year, but I didn't see anything like that in v12 either.
>
> Thanks!
>
>
> -----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]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> 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
>

John Bucaria

DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Anguraj Rathinasamy)
Yes. A simple program that creates the GRANT statements by reading SYSTABLES for tables in a database.

From: Anguraj Rathinasamy <[login to unmask email]>
Sent: Wednesday, October 02, 2019 4:35 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 zOS v11 - Grant SELECT only access to all tables?


You can construct the grant sql stmt from systables.


On Oct 2, 2019, at 4:22 PM, Bucaria, John <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I’m not aware of any fastpath for this. We have the CA RC/Secure product which generates the GRANT statements for you.

From: Michelle Witt <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Wednesday, October 02, 2019 4:10 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 zOS v11 - Grant SELECT only access to all tables?


Is there a way to grant SELECT only access to all tables without granting them individually to tables? We're looking for something like DATAACCESS, but we need to limit the access to SELECT only and not include INSERT, UPDATE, DELETE. Want it at the system level so we don't have to manage granting the access to new tables that are created. We looked in the manuals but not finding anything, just want to make sure we didn't miss something. A coworker said they have the ability to grant administrator privilege on SQL server and then can DENY specific authority they don't want included, but I don't know of anything similar for zOS. We won't be going to v12 until next year, but I didn't see anything like that in v12 either.

Thanks!

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

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

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

James Campbell

DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Michelle Witt)
>Is there a way to grant SELECT only access to all tables without granting them individually
to tables?

As the other replies haven't said it, "No".

James Campbell



On 2 Oct 2019 at 13:10, Michelle Witt wrote:

> Is there a way to grant SELECT only access to all tables without granting them individually to tables? We're looking for something like DATAACCESS, but we need to limit the access to SELECT only and not include INSERT, UPDATE, DELETE. Want it at the system level so we don't have to manage granting the access to new tables that are created. We looked in the manuals but not finding anything, just want to make sure we didn't miss something. A coworker said they have the ability to grant administrator privilege on SQL server and then can DENY specific authority they don't want included, but I don't know of anything similar for zOS. We won't be going to v12 until next year, but I didn't see anything like that in v12 either.
> Thanks!
>
>

--
This email has been checked for viruses by AVG.
https://www.avg.com

Colin Raybould

RE: DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to James Campbell)

Hi,

As this question is z/OS specific, the answer is YES, but you will have to use a SAF product (RACF, ACF/2....) for this.

Both RACF and ACF/2 can allow generic select only access to tables.

See the DB2 Manual “RACF Access Control Module Guide” for how to set this up for RACF.

Regards, 

Colin Raybould.


In Reply to James Campbell:

>Is there a way to grant SELECT only access to all tables without granting them individually
to tables?

As the other replies haven't said it, "No".

James Campbell



On 2 Oct 2019 at 13:10, Michelle Witt wrote:

> Is there a way to grant SELECT only access to all tables without granting them individually to tables? We're looking for something like DATAACCESS, but we need to limit the access to SELECT only and not include INSERT, UPDATE, DELETE. Want it at the system level so we don't have to manage granting the access to new tables that are created. We looked in the manuals but not finding anything, just want to make sure we didn't miss something. A coworker said they have the ability to grant administrator privilege on SQL server and then can DENY specific authority they don't want included, but I don't know of anything similar for zOS. We won't be going to v12 until next year, but I didn't see anything like that in v12 either.
> Thanks!
>
>

--
This email has been checked for viruses by AVG.
https://www.avg.com

Russell Peters

RE: DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Michelle Witt)

We don't use native db2 authorization, we use RACF for all authorizations. In RACF you can grant select access generically, something like this:

MDSNTB.SSID.*.*.SELECT or permit by schema/creator like this:

MDSNTB.SSID.ABC.*.SELECT where ABC is the schema name.

We create racf rules for SELECT, UPDATE, INSERT, DELETE and either permit or deny the access.

Raymond Bell

DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Russell Peters)
Hmmm… with RACF-controlled access, the MDSNTB.SSID.*.*.SELECT profile will only give you Select if there’s not already a more specific profile for the given object. If there is, that one will take precedence. So you’d need to have READ on every SELECT MDSNTB profile there is.

Or… if you really want to.. Maybe create a DSNADM profile called, say, SSID.*.DATAACCESS (if there is such a thing) and grant READ on that to PUBLIC. No idea if that would work, but it sounds like what you ‘want’.

If that’s not enough rope, try GRANTing SYSADM TO PUBLIC. That’s usually good for a laugh.

Cheers,


Raymond
PS: laugh = public execution.

From: Russell Peters <[login to unmask email]>
Sent: 03 October 2019 13:45
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 zOS v11 - Grant SELECT only access to all tables?


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************

We don't use native db2 authorization, we use RACF for all authorizations. In RACF you can grant select access generically, something like this:

MDSNTB.SSID.*.*.SELECT or permit by schema/creator like this:

MDSNTB.SSID.ABC.*.SELECT where ABC is the schema name.

We create racf rules for SELECT, UPDATE, INSERT, DELETE and either permit or deny the access.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Colin Raybould

RE: DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Raymond Bell)

Hi Raymond,

Why the ‘*’ what would DATAACCESS be qualified by?  

Surely it should be: ssid.DATAACCESS

Not: SSID.*.DATAACCESS

Regards,

Colin Raybould.

In Reply to Raymond Bell:

Hmmm… with RACF-controlled access, the MDSNTB.SSID.*.*.SELECT profile will only give you Select if there’s not already a more specific profile for the given object. If there is, that one will take precedence. So you’d need to have READ on every SELECT MDSNTB profile there is.

Or… if you really want to.. Maybe create a DSNADM profile called, say, SSID.*.DATAACCESS (if there is such a thing) and grant READ on that to PUBLIC. No idea if that would work, but it sounds like what you ‘want’.

If that’s not enough rope, try GRANTing SYSADM TO PUBLIC. That’s usually good for a laugh.

Cheers,


Raymond
PS: laugh = public execution.

From: Russell Peters <[login to unmask email]>
Sent: 03 October 2019 13:45
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 zOS v11 - Grant SELECT only access to all tables?


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************

We don't use native db2 authorization, we use RACF for all authorizations. In RACF you can grant select access generically, something like this:

MDSNTB.SSID.*.*.SELECT or permit by schema/creator like this:

MDSNTB.SSID.ABC.*.SELECT where ABC is the schema name.

We create racf rules for SELECT, UPDATE, INSERT, DELETE and either permit or deny the access.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Raymond Bell

DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Colin Raybould)
Hey Colin,

You’re right, of course; no additional qualification. I saw a ssid.*.DBADM and didn’t join the dots because, of course, we’re not daft enough to have a DATAACCESS DSNADM class active.

So yes: ssid.DATAACCESS would be what’s ‘required’. :o)

Cheers,


Raymond
PS: How’s the Land of the Long White Roundabout? :o)

From: Colin Raybould <[login to unmask email]>
Sent: 03 October 2019 16:09
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 zOS v11 - Grant SELECT only access to all tables?


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************

Hi Raymond,

Why the ‘*’ what would DATAACCESS be qualified by?

Surely it should be: ssid.DATAACCESS

Not: SSID.*.DATAACCESS

Regards,

Colin Raybould.

In Reply to Raymond Bell:
Hmmm… with RACF-controlled access, the MDSNTB.SSID.*.*.SELECT profile will only give you Select if there’s not already a more specific profile for the given object. If there is, that one will take precedence. So you’d need to have READ on every SELECT MDSNTB profile there is.

Or… if you really want to.. Maybe create a DSNADM profile called, say, SSID.*.DATAACCESS (if there is such a thing) and grant READ on that to PUBLIC. No idea if that would work, but it sounds like what you ‘want’.

If that’s not enough rope, try GRANTing SYSADM TO PUBLIC. That’s usually good for a laugh.

Cheers,


Raymond
PS: laugh = public execution.

From: Russell Peters
Sent: 03 October 2019 13:45
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: DB2 zOS v11 - Grant SELECT only access to all tables?


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************

We don't use native db2 authorization, we use RACF for all authorizations. In RACF you can grant select access generically, something like this:

MDSNTB.SSID.*.*.SELECT or permit by schema/creator like this:

MDSNTB.SSID.ABC.*.SELECT where ABC is the schema name.

We create racf rules for SELECT, UPDATE, INSERT, DELETE and either permit or deny the access.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com http://www.rbs.com

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

James Campbell

RE: DB2 zOS v11 - Grant SELECT only access to all tables?
(in response to Colin Raybould)

But a SAF product (RACF, ACF/2....) doesn't use "SELECT" (OP's word).

</quibble>

James Campbell