*****SPAM***** Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage

James Campbell

*****SPAM***** Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage
Yes. But exactly what will be locked - causing a wait (and wait)? Or do you think I use RR
on the SQL?

BTW, I forgot to include synonym and MQT names as something to capture and compare.
Which I noticed because, as it happens, tomorrow I have to run my little set of SQL.

James Campbell

On 8 Feb 2010 at 11:49, Phil Grainger wrote:

> BUT
>
> What happens if someone tries to access an object between the start of step
> 2 and the end of step 4?
>
> Because the REVOKE is not yet committed nor rolled back, DB2 will make them
> wait (and wait)
>
> Phil Grainger
> Grainger Database Solutions Ltd
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of James Campbell
> Sent: 07 February 2010 08:27
> To: [login to unmask email]
> Subject: Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage
>
> ROLLBACK is your friend.
>
> 1) Capture current authorities, *invalid* plans/packages, view names
> 2) REVOKE
> 3) Capture post-revoke authorities, *invalid* plans/packages, view names
> 4) ROLLBACK
>
> Use (the ISPF utility) SUPERC to compare 1 and 3.
>
> (I capture invalid plans and packages because there are fewer of them than
> valid ones.)
>
> James Campbell
>
> On 5 Feb 2010 at 18:19, Larry wrote:
>
> > I figure that someone has already prepared a query like the one I want,
> so, I
> > am hoping not to have to reinvent the wheel.
> >
> > I am about to revoke PUBLIC access from a table (actually, a view). I
> will be
> > checking out my dynamic SQL users by way of auditing the table. However,
> I
> > would like to find out which packages will be invalidated before I run the
>
> > REVOKE. (Of course, I could always run the revoke and find out which
> > packages were invalidated, but I think it would be better to do my
> homework
> > ahead of time).
> >
> > Has anyone out there coded this query and have it available to share with
> the
> > community? Of course, I would also like to have respectable performance
> for
> > this query, so I may need to know of any user indexes on the catalog that
> are
> > required to support this performance.
> >
> > Larry Kirkpatrick
> > Principal Database Architect
> > Mutual of Omaha
> >
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> * * If you are going to attend only one conference this year, this
> is it!
> _____________________________________________________________________
> b
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> ** Your only source for independent, unbiased, and trusted DB2 information.
>
> _____________________________________________________________________
> http://www.IDUG.org/mentor
> How can you expand your staff or do succession planning in this economy?
> Mentoring is a proven, economical, way to train the next generation of DB2 Users!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: *****SPAM***** Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage
(in response to James Campbell)
Well, I imagine you will be locking pages in the xxxAUTH tables of the
catalog, potentially preventing DB2 doing other authorisation checks until
you do your ROLLBACK

Phil Grainger
Grainger Database Solutions Ltd

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of James Campbell
Sent: 09 February 2010 11:26
To: [login to unmask email]
Subject: Re: [DB2-L] *****SPAM***** Re: [DB2-L] Revocation of PUBLIC - how
to predetermine the damage

Yes. But exactly what will be locked - causing a wait (and wait)? Or do
you think I use RR
on the SQL?

BTW, I forgot to include synonym and MQT names as something to capture and
compare.
Which I noticed because, as it happens, tomorrow I have to run my little set
of SQL.

James Campbell

On 8 Feb 2010 at 11:49, Phil Grainger wrote:

> BUT
>
> What happens if someone tries to access an object between the start of
step
> 2 and the end of step 4?
>
> Because the REVOKE is not yet committed nor rolled back, DB2 will make
them
> wait (and wait)
>
> Phil Grainger
> Grainger Database Solutions Ltd
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of James Campbell
> Sent: 07 February 2010 08:27
> To: [login to unmask email]
> Subject: Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage
>
> ROLLBACK is your friend.
>
> 1) Capture current authorities, *invalid* plans/packages, view names
> 2) REVOKE
> 3) Capture post-revoke authorities, *invalid* plans/packages, view names
> 4) ROLLBACK
>
> Use (the ISPF utility) SUPERC to compare 1 and 3.
>
> (I capture invalid plans and packages because there are fewer of them than
> valid ones.)
>
> James Campbell
>
> On 5 Feb 2010 at 18:19, Larry wrote:
>
> > I figure that someone has already prepared a query like the one I want,
> so, I
> > am hoping not to have to reinvent the wheel.
> >
> > I am about to revoke PUBLIC access from a table (actually, a view). I
> will be
> > checking out my dynamic SQL users by way of auditing the table.
However,
> I
> > would like to find out which packages will be invalidated before I run
the
>
> > REVOKE. (Of course, I could always run the revoke and find out which
> > packages were invalidated, but I think it would be better to do my
> homework
> > ahead of time).
> >
> > Has anyone out there coded this query and have it available to share
with
> the
> > community? Of course, I would also like to have respectable performance
> for
> > this query, so I may need to know of any user indexes on the catalog
that
> are
> > required to support this performance.
> >
> > Larry Kirkpatrick
> > Principal Database Architect
> > Mutual of Omaha
> >
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> * * If you are going to attend only one conference this year, this
> is it!
> _____________________________________________________________________
> b
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
> ** Your only source for independent, unbiased, and trusted DB2
information.
>
> _____________________________________________________________________
> http://www.IDUG.org/mentor
> How can you expand your staff or do succession planning in this economy?
> Mentoring is a proven, economical, way to train the next generation of DB2
Users!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L