Revoke Cascade SQL

Francis Leblanc

Revoke Cascade SQL
Happy Holidays!

I was wondering if anyone has attempted to code some SQL that would help determine the cascade effects of a revoke statement. I'm guessing that this could be done with recursive SQL, but before I tried, I thought I'd see if anyone else had already done so. If the wheel has already been invented, I'd just as soon use the wheel and move on to inventing fire. :-)

Thanks!


Fritz Leblanc
Manager, Mainframe Database
Sallie Mae, Inc
11100 USA Parkway
Fishers, IN 46037
Tel: 317-806-0375
Cell: 317-607-2657
Fax: 317-595-1494
email: [login to unmask email]

Avram Friedman

Re: Revoke Cascade SQL
(in response to Francis Leblanc)
The practice I am most familer with to avoid cascading revokes is to make the
target of the revoke install SYSADM and then do the revoke. This avoides the
cascading delete problem.

Two alternatives for avoiding the creation of the cascading delete problem in
the future are:
A) Always make the granter a group ID and never go deeper that 2 levels
Grantor->Grantee never Grantor->Grantee->Grantee
B) Switch to external security

Avram Frieman

On Thu, 20 Dec 2007 11:17:24 -0500, FRITZ LEBLANC
<[login to unmask email]> wrote:

>SGFwcHkgSG9saWRheXMhDQoNCkkgd2FzIHdvbmRlcmluZyBpZiBhbnlvbmUgaGFz
IGF0dGVtcHRl
>ZCB0byBjb2RlIHNvbWUgU1FMIHRoYXQgd291bGQgaGVscCBkZXRlcm1pbmUgdGhl
IGNhc2NhZGUg
>ZWZmZWN0cyBvZiBhIHJldm9rZSBzdGF0ZW1lbnQuICBJJ20gZ3Vlc3NpbmcgdGhh
dCB0aGlzIGNv
>dWxkIGJlIGRvbmUgd2l0aCByZWN1cnNpdmUgU1FMLCBidXQgYmVmb3JlIEkgdHJp
ZWQsIEkgdGhv
>dWdodCBJJ2Qgc2VlIGlmIGFueW9uZSBlbHNlIGhhZCBhbHJlYWR5IGRvbmUgc28uIC
BJZiB0aGUg
>d2hlZWwgaGFzIGFscmVhZHkgYmVlbiBpbnZlbnRlZCwgSSdkIGp1c3QgYXMgc29v
biB1c2UgdGhl
>IHdoZWVsIGFuZCBtb3ZlIG9uIHRvIGludmVudGluZyBmaXJlLiAgOi0pDQoNClRoYW5
rcyENCg0K
>DQpGcml0eiBMZWJsYW5jDQpNYW5hZ2VyLCBNYWluZnJhbWUgRGF0YWJhc2UNC
lNhbGxpZSBNYWUs
>IEluYw0KMTExMDAgVVNBIFBhcmt3YXkNCkZpc2hlcnMsIElOIDQ2MDM3DQpUZW
w6IDMxNy04MDYt
>MDM3NQ0KQ2VsbDogMzE3LTYwNy0yNjU3DQpGYXg6IDMxNy01OTUtMTQ5NA0K
ZW1haWw6IGZyaXR6
>LmxlYmxhbmNAc2FsbGllbWFlLmNvbQ==
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Francis Leblanc

Re: Revoke Cascade SQL
(in response to Avram Friedman)
Hi Avram!

I'm familiar with the Install SYSADM route also. The problem is that we normally only shutdown our DB2 subsystems for maintenance activities, and only if absolutely necessary. I understand that changing the install sysadm requires an outage. For us, that my not occur for several months. I certainly understand the desirability of refraining from going too deep with grant levels, and we should be able to do that in the future. But that does not help us clean up what we already have in place.

Thanks!

Fritz

>>> Avram Friedman <[login to unmask email]> 12/20/07 11:34 AM >>>

The practice I am most familer with to avoid cascading revokes is to make the
target of the revoke install SYSADM and then do the revoke. This avoides the
cascading delete problem.

Two alternatives for avoiding the creation of the cascading delete problem in
the future are:
A) Always make the granter a group ID and never go deeper that 2 levels
Grantor->Grantee never Grantor->Grantee->Grantee
B) Switch to external security

Avram Frieman

On Thu, 20 Dec 2007 11:17:24 -0500, FRITZ LEBLANC
<[login to unmask email]> wrote:

>SGFwcHkgSG9saWRheXMhDQoNCkkgd2FzIHdvbmRlcmluZyBpZiBhbnlvbmUgaGFz
IGF0dGVtcHRl
>ZCB0byBjb2RlIHNvbWUgU1FMIHRoYXQgd291bGQgaGVscCBkZXRlcm1pbmUgdGhl
IGNhc2NhZGUg
>ZWZmZWN0cyBvZiBhIHJldm9rZSBzdGF0ZW1lbnQuICBJJ20gZ3Vlc3NpbmcgdGhh
dCB0aGlzIGNv
>dWxkIGJlIGRvbmUgd2l0aCByZWN1cnNpdmUgU1FMLCBidXQgYmVmb3JlIEkgdHJp
ZWQsIEkgdGhv
>dWdodCBJJ2Qgc2VlIGlmIGFueW9uZSBlbHNlIGhhZCBhbHJlYWR5IGRvbmUgc28uIC
BJZiB0aGUg
>d2hlZWwgaGFzIGFscmVhZHkgYmVlbiBpbnZlbnRlZCwgSSdkIGp1c3QgYXMgc29v
biB1c2UgdGhl
>IHdoZWVsIGFuZCBtb3ZlIG9uIHRvIGludmVudGluZyBmaXJlLiAgOi0pDQoNClRoYW5
rcyENCg0K
>DQpGcml0eiBMZWJsYW5jDQpNYW5hZ2VyLCBNYWluZnJhbWUgRGF0YWJhc2UNC
lNhbGxpZSBNYWUs
>IEluYw0KMTExMDAgVVNBIFBhcmt3YXkNCkZpc2hlcnMsIElOIDQ2MDM3DQpUZW
w6IDMxNy04MDYt
>MDM3NQ0KQ2VsbDogMzE3LTYwNy0yNjU3DQpGYXg6IDMxNy01OTUtMTQ5NA0K
ZW1haWw6IGZyaXR6
>LmxlYmxhbmNAc2FsbGllbWFlLmNvbQ==
>



This E-Mail has been scanned for viruses.

Mike Turner

Re: Revoke Cascade SQL
(in response to Francis Leblanc)
Hi Fritz

If your on V8 or later, Install Sysadm can be changed dynamically.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565 873702
web: www.michael-turner.ltd.uk
----------------------------------------------------------------------------------------
Michael Turner Ltd. Registered in England and Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT Reg. No. GB 338 4159 44.
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]On Behalf Of FRITZ LEBLANC
Sent: Thursday, 20 December 2007 17:12
To: [login to unmask email]
Subject: Re: [DB2-L] Revoke Cascade SQL


Hi Avram!

I'm familiar with the Install SYSADM route also. The problem is that we normally only shutdown our DB2 subsystems for maintenance activities, and only if absolutely necessary. I understand that changing the install sysadm requires an outage. For us, that my not occur for several months. I certainly understand the desirability of refraining from going too deep with grant levels, and we should be able to do that in the future. But that does not help us clean up what we already have in place.

Thanks!

Fritz

>>> Avram Friedman <[login to unmask email]> 12/20/07 11:34 AM >>>

The practice I am most familer with to avoid cascading revokes is to make the
target of the revoke install SYSADM and then do the revoke. This avoides the
cascading delete problem.

Two alternatives for avoiding the creation of the cascading delete problem in
the future are:
A) Always make the granter a group ID and never go deeper that 2 levels
Grantor->Grantee never Grantor->Grantee->Grantee
B) Switch to external security

Avram Frieman

On Thu, 20 Dec 2007 11:17:24 -0500, FRITZ LEBLANC
<[login to unmask email]> wrote:

>SGFwcHkgSG9saWRheXMhDQoNCkkgd2FzIHdvbmRlcmluZyBpZiBhbnlvbmUgaGFz
IGF0dGVtcHRl
>ZCB0byBjb2RlIHNvbWUgU1FMIHRoYXQgd291bGQgaGVscCBkZXRlcm1pbmUgdGhl
IGNhc2NhZGUg
>ZWZmZWN0cyBvZiBhIHJldm9rZSBzdGF0ZW1lbnQuICBJJ20gZ3Vlc3NpbmcgdGhh
dCB0aGlzIGNv
>dWxkIGJlIGRvbmUgd2l0aCByZWN1cnNpdmUgU1FMLCBidXQgYmVmb3JlIEkgdHJp
ZWQsIEkgdGhv
>dWdodCBJJ2Qgc2VlIGlmIGFueW9uZSBlbHNlIGhhZCBhbHJlYWR5IGRvbmUgc28uIC
BJZiB0aGUg
>d2hlZWwgaGFzIGFscmVhZHkgYmVlbiBpbnZlbnRlZCwgSSdkIGp1c3QgYXMgc29v
biB1c2UgdGhl
>IHdoZWVsIGFuZCBtb3ZlIG9uIHRvIGludmVudGluZyBmaXJlLiAgOi0pDQoNClRoYW5
rcyENCg0K
>DQpGcml0eiBMZWJsYW5jDQpNYW5hZ2VyLCBNYWluZnJhbWUgRGF0YWJhc2UNC
lNhbGxpZSBNYWUs
>IEluYw0KMTExMDAgVVNBIFBhcmt3YXkNCkZpc2hlcnMsIElOIDQ2MDM3DQpUZW
w6IDMxNy04MDYt
>MDM3NQ0KQ2VsbDogMzE3LTYwNy0yNjU3DQpGYXg6IDMxNy01OTUtMTQ5NA0K
ZW1haWw6IGZyaXR6
>LmxlYmxhbmNAc2FsbGllbWFlLmNvbQ==
>



This E-Mail has been scanned for viruses.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Francis Leblanc

Re: Revoke Cascade SQL
(in response to Mike Turner)
Excellent! I must have missed that in the documentation.

So, we should be able to change the install sysadm, issue the revoke, and change the install sysadm back to it's original value in a single process? Wow. I'm a happy man!

Thanks! What a Christmas present! And what a terrific resource this is!

>>> Mike Turner <[login to unmask email]> 12/20/07 12:39 PM >>>

Hi Fritz

If your on V8 or later, Install Sysadm can be changed dynamically.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565 873702
web: www.michael-turner.ltd.uk
----------------------------------------------------------------------------------------
Michael Turner Ltd. Registered in England and Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT Reg. No. GB 338 4159 44.
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]On Behalf Of FRITZ LEBLANC
Sent: Thursday, 20 December 2007 17:12
To: [login to unmask email]
Subject: Re: [DB2-L] Revoke Cascade SQL


Hi Avram!

I'm familiar with the Install SYSADM route also. The problem is that we normally only shutdown our DB2 subsystems for maintenance activities, and only if absolutely necessary. I understand that changing the install sysadm requires an outage. For us, that my not occur for several months. I certainly understand the desirability of refraining from going too deep with grant levels, and we should be able to do that in the future. But that does not help us clean up what we already have in place.

Thanks!

Fritz

>>> Avram Friedman <[login to unmask email]> 12/20/07 11:34 AM >>>

The practice I am most familer with to avoid cascading revokes is to make the
target of the revoke install SYSADM and then do the revoke. This avoides the
cascading delete problem.

Two alternatives for avoiding the creation of the cascading delete problem in
the future are:
A) Always make the granter a group ID and never go deeper that 2 levels
Grantor->Grantee never Grantor->Grantee->Grantee
B) Switch to external security

Avram Frieman

On Thu, 20 Dec 2007 11:17:24 -0500, FRITZ LEBLANC
<[login to unmask email]> wrote:

>SGFwcHkgSG9saWRheXMhDQoNCkkgd2FzIHdvbmRlcmluZyBpZiBhbnlvbmUgaGFz
IGF0dGVtcHRl
>ZCB0byBjb2RlIHNvbWUgU1FMIHRoYXQgd291bGQgaGVscCBkZXRlcm1pbmUgdGhl
IGNhc2NhZGUg
>ZWZmZWN0cyBvZiBhIHJldm9rZSBzdGF0ZW1lbnQuICBJJ20gZ3Vlc3NpbmcgdGhh
dCB0aGlzIGNv
>dWxkIGJlIGRvbmUgd2l0aCByZWN1cnNpdmUgU1FMLCBidXQgYmVmb3JlIEkgdHJp
ZWQsIEkgdGhv
>dWdodCBJJ2Qgc2VlIGlmIGFueW9uZSBlbHNlIGhhZCBhbHJlYWR5IGRvbmUgc28uIC
BJZiB0aGUg
>d2hlZWwgaGFzIGFscmVhZHkgYmVlbiBpbnZlbnRlZCwgSSdkIGp1c3QgYXMgc29v
biB1c2UgdGhl
>IHdoZWVsIGFuZCBtb3ZlIG9uIHRvIGludmVudGluZyBmaXJlLiAgOi0pDQoNClRoYW5
rcyENCg0K
>DQpGcml0eiBMZWJsYW5jDQpNYW5hZ2VyLCBNYWluZnJhbWUgRGF0YWJhc2UNC
lNhbGxpZSBNYWUs
>IEluYw0KMTExMDAgVVNBIFBhcmt3YXkNCkZpc2hlcnMsIElOIDQ2MDM3DQpUZW
w6IDMxNy04MDYt
>MDM3NQ0KQ2VsbDogMzE3LTYwNy0yNjU3DQpGYXg6IDMxNy01OTUtMTQ5NA0K
ZW1haWw6IGZyaXR6
>LmxlYmxhbmNAc2FsbGllbWFlLmNvbQ==
>



This E-Mail has been scanned for viruses.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

This E-Mail has been scanned for viruses.

Douwe van Sluis

Re: Revoke Cascade SQL
(in response to Francis Leblanc)
Hi Fritz,

I did the same thing a couple of months ago. It's easy to do, but also
easy to mess up.
I used the following approach:
1-List the GRANTEE's that need to be revoked, give them a number.
2-Assemble a new Zparm with the GRANTEE as INSTALL SYSADM, pre-/postfix
the zparm with the number. Do this for each GRANTEE that needs to be
REVOKEd.
3-SET SYSPARM to load the zparm.
4-Run DSNWZP and check if the GRANTEE you want to REVOKE is in the
INSTALL SYSADM.
5-Run the REVOKE.
6-Goto step 3.

Have fun and keep it save...

Vriendelijke groet,
Douwe van Sluis
+31 (0) 6 11 18 59 58

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
FRITZ LEBLANC
Verzonden: donderdag 20 december 2007 18:50
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] Revoke Cascade SQL


Excellent! I must have missed that in the documentation.

So, we should be able to change the install sysadm, issue the revoke,
and change the install sysadm back to it's original value in a single
process? Wow. I'm a happy man!

Thanks! What a Christmas present! And what a terrific resource this
is!

>>> Mike Turner <[login to unmask email]> 12/20/07 12:39 PM >>>

Hi Fritz

If your on V8 or later, Install Sysadm can be changed dynamically.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565 873702
web: www.michael-turner.ltd.uk
------------------------------------------------------------------------
----------------
Michael Turner Ltd. Registered in England and Wales No. 1433722.
Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT
Reg. No. GB 338 4159 44.
-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]On Behalf Of FRITZ LEBLANC
Sent: Thursday, 20 December 2007 17:12
To: [login to unmask email]
Subject: Re: [DB2-L] Revoke Cascade SQL


Hi Avram!

I'm familiar with the Install SYSADM route also. The problem is that we
normally only shutdown our DB2 subsystems for maintenance activities,
and only if absolutely necessary. I understand that changing the
install sysadm requires an outage. For us, that my not occur for
several months. I certainly understand the desirability of refraining
from going too deep with grant levels, and we should be able to do that
in the future. But that does not help us clean up what we already have
in place.

Thanks!

Fritz

>>> Avram Friedman <[login to unmask email]> 12/20/07 11:34 AM >>>

The practice I am most familer with to avoid cascading revokes is to
make the
target of the revoke install SYSADM and then do the revoke. This
avoides the
cascading delete problem.

Two alternatives for avoiding the creation of the cascading delete
problem in
the future are:
A) Always make the granter a group ID and never go deeper that 2 levels
Grantor->Grantee never Grantor->Grantee->Grantee
B) Switch to external security

Avram Frieman

On Thu, 20 Dec 2007 11:17:24 -0500, FRITZ LEBLANC
<[login to unmask email]> wrote:

>SGFwcHkgSG9saWRheXMhDQoNCkkgd2FzIHdvbmRlcmluZyBpZiBhbnlvbmUgaGFz
IGF0dGVtcHRl
>ZCB0byBjb2RlIHNvbWUgU1FMIHRoYXQgd291bGQgaGVscCBkZXRlcm1pbmUgdGhl
IGNhc2NhZGUg
>ZWZmZWN0cyBvZiBhIHJldm9rZSBzdGF0ZW1lbnQuICBJJ20gZ3Vlc3NpbmcgdGhh
dCB0aGlzIGNv
>dWxkIGJlIGRvbmUgd2l0aCByZWN1cnNpdmUgU1FMLCBidXQgYmVmb3JlIEkgdHJp
ZWQsIEkgdGhv
>dWdodCBJJ2Qgc2VlIGlmIGFueW9uZSBlbHNlIGhhZCBhbHJlYWR5IGRvbmUgc28uIC
BJZiB0aGUg
>d2hlZWwgaGFzIGFscmVhZHkgYmVlbiBpbnZlbnRlZCwgSSdkIGp1c3QgYXMgc29v
biB1c2UgdGhl
>IHdoZWVsIGFuZCBtb3ZlIG9uIHRvIGludmVudGluZyBmaXJlLiAgOi0pDQoNClRoYW5
rcyENCg0K
>DQpGcml0eiBMZWJsYW5jDQpNYW5hZ2VyLCBNYWluZnJhbWUgRGF0YWJhc2UNC
lNhbGxpZSBNYWUs
>IEluYw0KMTExMDAgVVNBIFBhcmt3YXkNCkZpc2hlcnMsIElOIDQ2MDM3DQpUZW
w6IDMxNy04MDYt
>MDM3NQ0KQ2VsbDogMzE3LTYwNy0yNjU3DQpGYXg6IDMxNy01OTUtMTQ5NA0K
ZW1haWw6IGZyaXR6
>LmxlYmxhbmNAc2FsbGllbWFlLmNvbQ==
>



This E-Mail has been scanned for viruses.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

This E-Mail has been scanned for viruses.



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Robert Tilkes

Re: Revoke Cascade SQL
(in response to Douwe van Sluis)
Also be aware that if your environment is LLA managed and your DB2 EXIT
libraries are managed by LLA you will need to have LLA update after you
reassembled your ZPARM prior to the reloading of the sysparm.

Bob

IBM Certified Database Administrator DB2 9 for z/OS
IBM Certified Database Associate DB2 9 Fundamentals

IBM Certified Database Administrator DB2 UDB V8.1 for z/OS
IBM Certified Database Associate DB2 UDB V8.1 Family



Douwe van Sluis <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>




12/20/2007 12:05 PM
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

From
Douwe van Sluis <[login to unmask email]>
To
[login to unmask email]
cc

Subject
Re: [DB2-L] Revoke Cascade SQL






Hi Fritz,

I did the same thing a couple of months ago. It's easy to do, but also
easy to mess up.
I used the following approach:
1-List the GRANTEE's that need to be revoked, give them a number.
2-Assemble a new Zparm with the GRANTEE as INSTALL SYSADM, pre-/postfix
the zparm with the number. Do this for each GRANTEE that needs to be
REVOKEd.
3-SET SYSPARM to load the zparm.
4-Run DSNWZP and check if the GRANTEE you want to REVOKE is in the INSTALL
SYSADM.
5-Run the REVOKE.
6-Goto step 3.

Have fun and keep it save...
Vriendelijke groet,
Douwe van Sluis
+31 (0) 6 11 18 59 58
-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
FRITZ LEBLANC
Verzonden: donderdag 20 december 2007 18:50
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] Revoke Cascade SQL

Excellent! I must have missed that in the documentation.

So, we should be able to change the install sysadm, issue the revoke, and
change the install sysadm back to it's original value in a single process?
Wow. I'm a happy man!

Thanks! What a Christmas present! And what a terrific resource this is!

>>> Mike Turner <[login to unmask email]> 12/20/07 12:39 PM >>>
Hi Fritz

If your on V8 or later, Install Sysadm can be changed dynamically.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565 873702
web: www.michael-turner.ltd.uk
----------------------------------------------------------------------------------------
Michael Turner Ltd. Registered in England and Wales No. 1433722.
Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT
Reg. No. GB 338 4159 44.
-----Original Message-----
From: [login to unmask email] [
mailto:[login to unmask email]On Behalf Of FRITZ LEBLANC
Sent: Thursday, 20 December 2007 17:12
To: [login to unmask email]
Subject: Re: [DB2-L] Revoke Cascade SQL


Hi Avram!

I'm familiar with the Install SYSADM route also. The problem is that we
normally only shutdown our DB2 subsystems for maintenance activities, and
only if absolutely necessary. I understand that changing the install
sysadm requires an outage. For us, that my not occur for several months.
I certainly understand the desirability of refraining from going too deep
with grant levels, and we should be able to do that in the future. But
that does not help us clean up what we already have in place.

Thanks!

Fritz

>>> Avram Friedman <[login to unmask email]> 12/20/07 11:34 AM >>>

The practice I am most familer with to avoid cascading revokes is to make
the
target of the revoke install SYSADM and then do the revoke. This avoides
the
cascading delete problem.

Two alternatives for avoiding the creation of the cascading delete problem
in
the future are:
A) Always make the granter a group ID and never go deeper that 2 levels
Grantor->Grantee never Grantor->Grantee->Grantee
B) Switch to external security

Avram Frieman

On Thu, 20 Dec 2007 11:17:24 -0500, FRITZ LEBLANC
<[login to unmask email]> wrote:

>SGFwcHkgSG9saWRheXMhDQoNCkkgd2FzIHdvbmRlcmluZyBpZiBhbnlvbmUgaGFz
IGF0dGVtcHRl
>ZCB0byBjb2RlIHNvbWUgU1FMIHRoYXQgd291bGQgaGVscCBkZXRlcm1pbmUgdGhl
IGNhc2NhZGUg
>ZWZmZWN0cyBvZiBhIHJldm9rZSBzdGF0ZW1lbnQuICBJJ20gZ3Vlc3NpbmcgdGhh
dCB0aGlzIGNv
>dWxkIGJlIGRvbmUgd2l0aCByZWN1cnNpdmUgU1FMLCBidXQgYmVmb3JlIEkgdHJp
ZWQsIEkgdGhv
>dWdodCBJJ2Qgc2VlIGlmIGFueW9uZSBlbHNlIGhhZCBhbHJlYWR5IGRvbmUgc28uIC
BJZiB0aGUg
>d2hlZWwgaGFzIGFscmVhZHkgYmVlbiBpbnZlbnRlZCwgSSdkIGp1c3QgYXMgc29v
biB1c2UgdGhl
>IHdoZWVsIGFuZCBtb3ZlIG9uIHRvIGludmVudGluZyBmaXJlLiAgOi0pDQoNClRoYW5
rcyENCg0K
>DQpGcml0eiBMZWJsYW5jDQpNYW5hZ2VyLCBNYWluZnJhbWUgRGF0YWJhc2UNC
lNhbGxpZSBNYWUs
>IEluYw0KMTExMDAgVVNBIFBhcmt3YXkNCkZpc2hlcnMsIElOIDQ2MDM3DQpUZW
w6IDMxNy04MDYt
>MDM3NQ0KQ2VsbDogMzE3LTYwNy0yNjU3DQpGYXg6IDMxNy01OTUtMTQ5NA0K
ZW1haWw6IGZyaXR6
>LmxlYmxhbmNAc2FsbGllbWFlLmNvbQ==
>



This E-Mail has been scanned for viruses.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information, and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

This E-Mail has been scanned for viruses.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Revoke Cascade SQL
(in response to Robert Tilkes)
and if you have ANY CA DB2 tool, then you can do this from the Value Pack, at no charge, on any release of DB2

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Mike Turner
Sent: Thu 20/12/2007 17:39
To: [login to unmask email]
Subject: Re: [DB2-L] Revoke Cascade SQL



Hi Fritz

If your on V8 or later, Install Sysadm can be changed dynamically.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565 873702
web: www.michael-turner.ltd.uk
----------------------------------------------------------------------------------------
Michael Turner Ltd. Registered in England and Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT Reg. No. GB 338 4159 44.
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]On Behalf Of FRITZ LEBLANC
Sent: Thursday, 20 December 2007 17:12
To: [login to unmask email]
Subject: Re: [DB2-L] Revoke Cascade SQL


Hi Avram!

I'm familiar with the Install SYSADM route also. The problem is that we normally only shutdown our DB2 subsystems for maintenance activities, and only if absolutely necessary. I understand that changing the install sysadm requires an outage. For us, that my not occur for several months. I certainly understand the desirability of refraining from going too deep with grant levels, and we should be able to do that in the future. But that does not help us clean up what we already have in place.

Thanks!

Fritz

>>> Avram Friedman <[login to unmask email]> 12/20/07 11:34 AM >>>

The practice I am most familer with to avoid cascading revokes is to make the
target of the revoke install SYSADM and then do the revoke. This avoides the
cascading delete problem.

Two alternatives for avoiding the creation of the cascading delete problem in
the future are:
A) Always make the granter a group ID and never go deeper that 2 levels
Grantor->Grantee never Grantor->Grantee->Grantee
B) Switch to external security

Avram Frieman

On Thu, 20 Dec 2007 11:17:24 -0500, FRITZ LEBLANC
<[login to unmask email]> wrote:

>SGFwcHkgSG9saWRheXMhDQoNCkkgd2FzIHdvbmRlcmluZyBpZiBhbnlvbmUgaGFz
IGF0dGVtcHRl
>ZCB0byBjb2RlIHNvbWUgU1FMIHRoYXQgd291bGQgaGVscCBkZXRlcm1pbmUgdGhl
IGNhc2NhZGUg
>ZWZmZWN0cyBvZiBhIHJldm9rZSBzdGF0ZW1lbnQuICBJJ20gZ3Vlc3NpbmcgdGhh
dCB0aGlzIGNv
>dWxkIGJlIGRvbmUgd2l0aCByZWN1cnNpdmUgU1FMLCBidXQgYmVmb3JlIEkgdHJp
ZWQsIEkgdGhv
>dWdodCBJJ2Qgc2VlIGlmIGFueW9uZSBlbHNlIGhhZCBhbHJlYWR5IGRvbmUgc28uIC
BJZiB0aGUg
>d2hlZWwgaGFzIGFscmVhZHkgYmVlbiBpbnZlbnRlZCwgSSdkIGp1c3QgYXMgc29v
biB1c2UgdGhl
>IHdoZWVsIGFuZCBtb3ZlIG9uIHRvIGludmVudGluZyBmaXJlLiAgOi0pDQoNClRoYW5
rcyENCg0K
>DQpGcml0eiBMZWJsYW5jDQpNYW5hZ2VyLCBNYWluZnJhbWUgRGF0YWJhc2UNC
lNhbGxpZSBNYWUs
>IEluYw0KMTExMDAgVVNBIFBhcmt3YXkNCkZpc2hlcnMsIElOIDQ2MDM3DQpUZW
w6IDMxNy04MDYt
>MDM3NQ0KQ2VsbDogMzE3LTYwNy0yNjU3DQpGYXg6IDMxNy01OTUtMTQ5NA0K
ZW1haWw6IGZyaXR6
>LmxlYmxhbmNAc2FsbGllbWFlLmNvbQ==
>



This E-Mail has been scanned for viruses.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Sysdba_AHE/CORP/TPG

Re: Revoke Cascade SQL
(in response to Phil Grainger)
We tried the trick with changing the Install SYSADM dynamically to prevent
cascading Revokes. It seemed to work fine, the -SET SYSPARM command
returned code 0 ... can you feel a "but" coming?

Subsequently we got complaints about lots of missing GRANTs. When we
checked back there were messages like this in the MSTR log:

DSNZ015I -DSNE DSNZCMD1 YOU MUST HAVE INSTALL SYSADM AUTHORITY TO CHANGE
PARAMETER SYSADM ONLINE. PARAMETER CHANGE IS IGNORED.

DSNZ015I -DSNE DSNZCMD1 YOU MUST HAVE INSTALL SYSADM AUTHORITY TO CHANGE
PARAMETER SYSADM2 ONLINE. PARAMETER CHANGE IS IGNORED.

The person doing the changes was a SYSADM, but not one of the 2 Install
SYSADMs.

So beware - I'd hate for you to have to recover from a similar situation!

Happy Christmas (or whatever you're celebrating) to all listers.

Neil Price
TNT Express ICS, UK

---------------------------------------------------------------------------------------------------------------
This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.
If you are not the intended recipient, please telephone or email the sender and delete this message and any attachment from your system.
If you are not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
Please consider the environmental impact before printing this document and its attachment(s). Print black and white and double-sided where possible.
------------------------------------------------------------------------------

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms