Grant Access to all tables in same schema

Mohamed Esmael

Grant Access to all tables in same schema

Hi ALL,

I want to give access to the tables created with particular schema, to Particular team 
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help 

Walter Janißen

AW: Grant Access to all tables in same schema
(in response to Mohamed Esmael)
Hi

The only way to do that is: You can generate these 400 grant-statements using SQL. This is no big deal. There is no statement, which does what you want with one statement. Or: you can create e.g. a RACF-group with the same name as the schema-name and connect all these users with that group, but in this case these users are owner of these tables.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

  • image001.png (2.6k)

Ruediger Kurtz

AW: Grant Access to all tables in same schema
(in response to Mohamed Esmael)
Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

Philip Sevetson

Grant Access to all tables in same schema
(in response to Ruediger Kurtz)
Mohammed Esmael,

Essentially, Rüdiger is correct. However - if you’re using SPUFI or DSNTEP2 or DSNTIAUL to run the statement, you’ll need a bit of trim, thus:

SELECT CHAR(
'GRANT SELECT ON TABLE '
CONCAT RTRIM(CREATOR) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' TO [userID]' CONCAT X'5E'
,80)
FROM SYSIBM.SYSTABLES
WHERE CREATOR = '[schema]'
;

The resulting output is executable SQL, which must be run through a SQL processor against your system.

Note 1: If your table name plus schema adds up to more than 36 characters, either use a GUI processor like Data Studio, or ask me about breaking this up into a multi-line statement. It can be done, it’s just a fair bit longer code.

Note 2: The X’5E’ is the EBCDIC code for a semicolon, and represents paranoia on my part about what terminal or CCSID you’re using to enter the generating SQL query.

--Phil

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:51 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Larry Jardine

Grant Access to all tables in same schema
(in response to Philip Sevetson)
If you are using IBM’s DB2 ADMIN Tool, you can bring up a list of tables with the specified schema and use primary command GRANT to generate the statement(s).

Other tools may have similar features.

Larry Jardine
Database Advisor, Aetna

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 6:50 AM
To: '[login to unmask email]'
Subject: [EXTERNAL] [DB2-L] - RE: Grant Access to all tables in same schema

**** External Email - Use Caution ****
Mohammed Esmael,

Essentially, Rüdiger is correct. However - if you’re using SPUFI or DSNTEP2 or DSNTIAUL to run the statement, you’ll need a bit of trim, thus:

SELECT CHAR(
'GRANT SELECT ON TABLE '
CONCAT RTRIM(CREATOR) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' TO [userID]' CONCAT X'5E'
,80)
FROM SYSIBM.SYSTABLES
WHERE CREATOR = '[schema]'
;

The resulting output is executable SQL, which must be run through a SQL processor against your system.

Note 1: If your table name plus schema adds up to more than 36 characters, either use a GUI processor like Data Studio, or ask me about breaking this up into a multi-line statement. It can be done, it’s just a fair bit longer code.

Note 2: The X’5E’ is the EBCDIC code for a semicolon, and represents paranoia on my part about what terminal or CCSID you’re using to enter the generating SQL query.

--Phil

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:51 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.huk.de&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=cnkD4XxnoZKF21TMJd9rP-aT6ORD6rxYsKW1xgwEJac&s=MUGHCdUO_YL63DNXejwsT4fwVUQqwo7cKMQtgSlPqM4&e=

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

Ruediger Kurtz

AW: Grant Access to all tables in same schema
(in response to Philip Sevetson)
Mohamed,

and remember, you must decide who your grantee shall be. Since we are using RACF-groups in our shop we simply establish a group, add the relevant users to it and run the grants. If you want the grantee to be list of users the task may become slightly cumbersome – or the resulting SQL may become pretty large, thus Phil’s idea of breaking it up into multi-line statements becomes increasingly important.

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 12:50
An: '[login to unmask email]' <[login to unmask email]>
Betreff: [DB2-L] - RE: Grant Access to all tables in same schema

Mohammed Esmael,

Essentially, Rüdiger is correct. However - if you’re using SPUFI or DSNTEP2 or DSNTIAUL to run the statement, you’ll need a bit of trim, thus:

SELECT CHAR(
'GRANT SELECT ON TABLE '
CONCAT RTRIM(CREATOR) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' TO [userID]' CONCAT X'5E'
,80)
FROM SYSIBM.SYSTABLES
WHERE CREATOR = '[schema]'
;

The resulting output is executable SQL, which must be run through a SQL processor against your system.

Note 1: If your table name plus schema adds up to more than 36 characters, either use a GUI processor like Data Studio, or ask me about breaking this up into a multi-line statement. It can be done, it’s just a fair bit longer code.

Note 2: The X’5E’ is the EBCDIC code for a semicolon, and represents paranoia on my part about what terminal or CCSID you’re using to enter the generating SQL query.

--Phil

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:51 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Ruediger Kurtz

AW: Grant Access to all tables in same schema
(in response to Larry Jardine)
By far the fastest and simplest way ☺




Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Jardine, Lawrence J [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 12:59
An: [login to unmask email]
Betreff: [DB2-L] - RE: Grant Access to all tables in same schema

If you are using IBM’s DB2 ADMIN Tool, you can bring up a list of tables with the specified schema and use primary command GRANT to generate the statement(s).

Other tools may have similar features.

Larry Jardine
Database Advisor, Aetna

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 6:50 AM
To: '[login to unmask email]'
Subject: [EXTERNAL] [DB2-L] - RE: Grant Access to all tables in same schema

**** External Email - Use Caution ****
Mohammed Esmael,

Essentially, Rüdiger is correct. However - if you’re using SPUFI or DSNTEP2 or DSNTIAUL to run the statement, you’ll need a bit of trim, thus:

SELECT CHAR(
'GRANT SELECT ON TABLE '
CONCAT RTRIM(CREATOR) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' TO [userID]' CONCAT X'5E'
,80)
FROM SYSIBM.SYSTABLES
WHERE CREATOR = '[schema]'
;

The resulting output is executable SQL, which must be run through a SQL processor against your system.

Note 1: If your table name plus schema adds up to more than 36 characters, either use a GUI processor like Data Studio, or ask me about breaking this up into a multi-line statement. It can be done, it’s just a fair bit longer code.

Note 2: The X’5E’ is the EBCDIC code for a semicolon, and represents paranoia on my part about what terminal or CCSID you’re using to enter the generating SQL query.

--Phil

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:51 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.huk.de&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=cnkD4XxnoZKF21TMJd9rP-aT6ORD6rxYsKW1xgwEJac&s=MUGHCdUO_YL63DNXejwsT4fwVUQqwo7cKMQtgSlPqM4&e=

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

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

Colin Clayton

Grant Access to all tables in same schema
(in response to Ruediger Kurtz)
IMHO, groups and external security tools (ACF2, RACF…) is the way to go. It centralizes security administration away from the responsibility of the DBAs.

I remember the early days of DB2 when GRANTs were the only solution and even for a small site it was a nightmare handling any kind of change, including DROP impact. Almost a full time job.


From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: 06 February 2018 12:10
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

and remember, you must decide who your grantee shall be. Since we are using RACF-groups in our shop we simply establish a group, add the relevant users to it and run the grants. If you want the grantee to be list of users the task may become slightly cumbersome – or the resulting SQL may become pretty large, thus Phil’s idea of breaking it up into multi-line statements becomes increasingly important.

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.huk.de&d=DwQFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=9UQ9e2nsngkozEhlm3pbCHFJw3YR6OHgYZz0a-TzOZA&m=n8e4ChDu08-Wgxe-9WdbPagl73UgLnR43FRG1ftlPAg&s=bsM8GmiSmlhMSiLvhUPSrAnWQsMEI4Z0SDQ6RNCUfEc&e=

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 12:50
An: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Betreff: [DB2-L] - RE: Grant Access to all tables in same schema

Mohammed Esmael,

Essentially, Rüdiger is correct. However - if you’re using SPUFI or DSNTEP2 or DSNTIAUL to run the statement, you’ll need a bit of trim, thus:

SELECT CHAR(
'GRANT SELECT ON TABLE '
CONCAT RTRIM(CREATOR) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' TO [userID]' CONCAT X'5E'
,80)
FROM SYSIBM.SYSTABLES
WHERE CREATOR = '[schema]'
;

The resulting output is executable SQL, which must be run through a SQL processor against your system.

Note 1: If your table name plus schema adds up to more than 36 characters, either use a GUI processor like Data Studio, or ask me about breaking this up into a multi-line statement. It can be done, it’s just a fair bit longer code.

Note 2: The X’5E’ is the EBCDIC code for a semicolon, and represents paranoia on my part about what terminal or CCSID you’re using to enter the generating SQL query.

--Phil

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:51 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.huk.de&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=9UQ9e2nsngkozEhlm3pbCHFJw3YR6OHgYZz0a-TzOZA&m=n8e4ChDu08-Wgxe-9WdbPagl73UgLnR43FRG1ftlPAg&s=bsM8GmiSmlhMSiLvhUPSrAnWQsMEI4Z0SDQ6RNCUfEc&e=

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

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

Mohamed Esmael

RE: AW: Grant Access to all tables in same schema
(in response to Ruediger Kurtz)

Dear Ruediger 

Thanks a lot for your reply 

I am using trusted context and roles not RACF Group as we want to apply internal DB2 Security 

Steen Rasmussen

Grant Access to all tables in same schema
(in response to Mohamed Esmael)
Hello Mohamed,

Warning up front : stop reading if you don’t want a vendor plug.
If you are looking for NATIVE Db2 security (meaning GRANT’s), CA RC/Secure has a component called SRS where you can create user domain and application domains. You specify all the users who need certain access in the User Domain, you specify the tables (can be generic specification like PROD1.T% for example). Every time you execute the SRS command, we will look in the Db2 catalog and identify all the tables matching the domain and execute the GRANT’s. You can even apply a deadline if needed so the GRANT’s will be revoked once the deadline is met.

Steen Rasmussen

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:16 AM
To: [login to unmask email]
Subject: [DB2-L] - Grant Access to all tables in same schema

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

Philip Sevetson

Grant Access to all tables in same schema
(in response to Philip Sevetson)
Second cut. This is what to do when you need space for longer table names:

WITH TARGET_GRANTS(QUALIFIED_TABLE)
AS (SELECT RTRIM(CREATOR) CONCAT '.' CONCAT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'DB2FDB'
/* YOU MIGHT WANT TO RESTRICT TO TABLES */
/* AND TYPE = 'T' */
)
SELECT FINAL_TEXT
FROM
(SELECT CHAR('GRANT SELECT ON TABLE',80) AS FINAL_TEXT
,1 AS QUERY_ELEMENT
FROM SYSIBM.SYSDUMMY1
UNION
SELECT CHAR(QUALIFIED_TABLE CONCAT ',',80) AS FINAL_TEXT
,2 AS QUERY_ELEMENT
FROM TARGET_GRANTS
WHERE QUALIFIED_TABLE ¬=
(SELECT MAX(QUALIFIED_TABLE)
FROM TARGET_GRANTS
)
UNION
SELECT CHAR(MAX(QUALIFIED_TABLE),80) AS FINAL_TEXT
,3 AS QUERY_ELEMENT
FROM TARGET_GRANTS
WHERE QUALIFIED_TABLE =
(SELECT MAX(QUALIFIED_TABLE)
FROM TARGET_GRANTS
)
UNION
SELECT CHAR('TO SOMEUSER' CONCAT X'5E',80) AS FINAL_TEXT
,4 AS QUERY_ELEMENT
FROM SYSIBM.SYSDUMMY1
) FORMATTED_LINES
ORDER BY QUERY_ELEMENT, FINAL_TEXT
;
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 6:50 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Grant Access to all tables in same schema

Mohammed Esmael,

Essentially, Rüdiger is correct. However - if you’re using SPUFI or DSNTEP2 or DSNTIAUL to run the statement, you’ll need a bit of trim, thus:

SELECT CHAR(
'GRANT SELECT ON TABLE '
CONCAT RTRIM(CREATOR) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' TO [userID]' CONCAT X'5E'
,80)
FROM SYSIBM.SYSTABLES
WHERE CREATOR = '[schema]'
;

The resulting output is executable SQL, which must be run through a SQL processor against your system.

Note 1: If your table name plus schema adds up to more than 36 characters, either use a GUI processor like Data Studio, or ask me about breaking this up into a multi-line statement. It can be done, it’s just a fair bit longer code.

Note 2: The X’5E’ is the EBCDIC code for a semicolon, and represents paranoia on my part about what terminal or CCSID you’re using to enter the generating SQL query.

--Phil

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:51 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

Grant Access to all tables in same schema
(in response to Philip Sevetson)
Whoops. One simple fix, a subselect to delete. Won’t make much difference, but I don’t like being sloppy.

WITH TARGET_GRANTS(QUALIFIED_TABLE)
AS (SELECT RTRIM(CREATOR) CONCAT '.' CONCAT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'DB2FDB'
/* YOU MIGHT WANT TO RESTRICT TO TABLES */
/* AND TYPE = 'T' */
)
SELECT FINAL_TEXT
FROM
(SELECT CHAR('GRANT SELECT ON TABLE',80) AS FINAL_TEXT
,1 AS QUERY_ELEMENT
FROM SYSIBM.SYSDUMMY1
UNION
SELECT CHAR(QUALIFIED_TABLE CONCAT ',',80) AS FINAL_TEXT
,2 AS QUERY_ELEMENT
FROM TARGET_GRANTS
WHERE QUALIFIED_TABLE ¬=
(SELECT MAX(QUALIFIED_TABLE)
FROM TARGET_GRANTS
)
UNION
SELECT CHAR(MAX(QUALIFIED_TABLE),80) AS FINAL_TEXT
,3 AS QUERY_ELEMENT
FROM TARGET_GRANTS
/* subselect deleted here */
UNION
SELECT CHAR('TO SOMEUSER' CONCAT X'5E',80) AS FINAL_TEXT
,4 AS QUERY_ELEMENT
FROM SYSIBM.SYSDUMMY1
) FORMATTED_LINES
ORDER BY QUERY_ELEMENT, FINAL_TEXT
;

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 12:10 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Grant Access to all tables in same schema

Second cut. This is what to do when you need space for longer table names:

WITH TARGET_GRANTS(QUALIFIED_TABLE)
AS (SELECT RTRIM(CREATOR) CONCAT '.' CONCAT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'DB2FDB'
/* YOU MIGHT WANT TO RESTRICT TO TABLES */
/* AND TYPE = 'T' */
)
SELECT FINAL_TEXT
FROM
(SELECT CHAR('GRANT SELECT ON TABLE',80) AS FINAL_TEXT
,1 AS QUERY_ELEMENT
FROM SYSIBM.SYSDUMMY1
UNION
SELECT CHAR(QUALIFIED_TABLE CONCAT ',',80) AS FINAL_TEXT
,2 AS QUERY_ELEMENT
FROM TARGET_GRANTS
WHERE QUALIFIED_TABLE ¬=
(SELECT MAX(QUALIFIED_TABLE)
FROM TARGET_GRANTS
)
UNION
SELECT CHAR(MAX(QUALIFIED_TABLE),80) AS FINAL_TEXT
,3 AS QUERY_ELEMENT
FROM TARGET_GRANTS
WHERE QUALIFIED_TABLE =
(SELECT MAX(QUALIFIED_TABLE)
FROM TARGET_GRANTS
)
UNION
SELECT CHAR('TO SOMEUSER' CONCAT X'5E',80) AS FINAL_TEXT
,4 AS QUERY_ELEMENT
FROM SYSIBM.SYSDUMMY1
) FORMATTED_LINES
ORDER BY QUERY_ELEMENT, FINAL_TEXT
;

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 6:50 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Grant Access to all tables in same schema

Mohammed Esmael,

Essentially, Rüdiger is correct. However - if you’re using SPUFI or DSNTEP2 or DSNTIAUL to run the statement, you’ll need a bit of trim, thus:

SELECT CHAR(
'GRANT SELECT ON TABLE '
CONCAT RTRIM(CREATOR) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' TO [userID]' CONCAT X'5E'
,80)
FROM SYSIBM.SYSTABLES
WHERE CREATOR = '[schema]'
;

The resulting output is executable SQL, which must be run through a SQL processor against your system.

Note 1: If your table name plus schema adds up to more than 36 characters, either use a GUI processor like Data Studio, or ask me about breaking this up into a multi-line statement. It can be done, it’s just a fair bit longer code.

Note 2: The X’5E’ is the EBCDIC code for a semicolon, and represents paranoia on my part about what terminal or CCSID you’re using to enter the generating SQL query.

--Phil

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, February 06, 2018 5:51 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: Grant Access to all tables in same schema

Mohamed,

Something along the lines

select “grant select on table <schema>”
Concat “.”
Concat “<name> to <grantee>;”
From sysibm.systables where creator = <schema>


should do the job, I think.

Hth

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Dienstag, 6. Februar 2018 11:16
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Grant Access to all tables in same schema


Hi ALL,

I want to give access to the tables created with particular schema, to Particular team
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Mohamed Esmael

RE: Grant Access to all tables in same schema
(in response to Philip Sevetson)

Thanks Philip for your reply  

I Actually try what Rüdiger said and I run the executable output , my question is can i have that in one step ( I mean SQL query to generate and run the queries)

 

Thanks in advance  

Mohamed Esmael

RE: Grant Access to all tables in same schema
(in response to Steen Rasmussen)

Thanks Steen for your clarification 

I will use DB2 Native security through Enable SECADM from ZPARM 

Ruediger Kurtz

AW: Grant Access to all tables in same schema
(in response to Mohamed Esmael)
Mohamed,

not in one step, if I’m not mistaken.

You may want the help of REXX, run the query within the REXX and generate an output file containing the grant-statements and in a subsequent step run IKJEFT01 with the output dataset as input.

Not too much work, methinks.

But as others have said before: If you have any catalog visibility tool of any flavor (IBM, BMC, CA …) you should have the functionality of creating the statements and run them online or via batch.

Regards

Rüdiger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Mohamed Esmael [mailto:[login to unmask email]
Gesendet: Donnerstag, 8. Februar 2018 08:15
An: [login to unmask email]
Betreff: [DB2-L] - RE: Grant Access to all tables in same schema


Thanks Philip for your reply

I Actually try what Rüdiger said and I run the executable output , my question is can i have that in one step ( I mean SQL query to generate and run the queries)



Thanks in advance

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

Michael Hannan

RE: Grant Access to all tables in same schema
(in response to Mohamed Esmael)

Mohamed,

TSO is a single job "step", so you can do it in a REXX procedure, as others suggested, that perhaps calls other standard programs instead of using two batch job steps. Not sure why a single step is important. I always used multiple steps, to generate SQLs and to run the resulting SQLs.

The SELECT to generate Grants is DML and can run in SPUFI, DSNTEP4, DSNTIAUL, etc. or through REXX interface. I prefer DSNTIAUL, is output is plain without headings and formatting. You can use others and post process with Utilities to extract the good data from the headings.

The GRANTs themselves are DDL, so can run via DSNTEP2, DSNTIAD, etc., may from REXX direct, I have not checked. Decide based on your requirements for error handling. Stop or keep going?
 
In Reply to Mohamed Esmael:

Thanks Philip for your reply  

I Actually try what Rüdiger said and I run the executable output , my question is can i have that in one step ( I mean SQL query to generate and run the queries)

 

Thanks in advance  

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

Grant Access to all tables in same schema
(in response to Mohamed Esmael)
Mohammed,

As others have said: you can run it in a single batch job with two steps (Using DSNTIAUL, write output as SYSREC; using DSNTEP2, read the SYSREC as input SQL), or a single-step REXX executable. Again: There is no SQL-only solution, because the GRANT statement does not permit the inclusion of a query.

--Phil Sevetson

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, February 08, 2018 2:15 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Grant Access to all tables in same schema


Thanks Philip for your reply

I Actually try what Rüdiger said and I run the executable output , my question is can i have that in one step ( I mean SQL query to generate and run the queries)



Thanks in advance

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Greg Palgrave

RE: Grant Access to all tables in same schema
(in response to Mohamed Esmael)

Mohamed,

You may want to submit a Request For Enhancement (RFE) for this at the RFE community site:

https://www.ibm.com/developerworks/rfe/execute?use_case=changeRequestLanding&BRAND_ID=184&PROD_ID=450&x=17&y=15

 

There is an RFE for this feature for LUW which is under consideration, but I'm not sure if there is currently one for z/OS:

https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=59141

 

Cheers

Greg

In Reply to Mohamed Esmael:

Hi ALL,

I want to give access to the tables created with particular schema, to Particular team 
Is there any SQL command. in DB2 V11 z/OS where i can give SELECT access
to all the tables created with the particular schema, since i have more than 400
tables scattered in more than one database.

Thank you all for your help 

J&#248;rn Thyssen

RE: Grant Access to all tables in same schema
(in response to Mohamed Esmael)

Hi,

If you want it done in one step you could code it as a native stored procedure that takes a schema and user ID as input. 

Open the cursor on SYSTABLES (similar to the one posted earlier in this thread), loop over the tables, and issue GRANT statements. 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal.