Revocation of PUBLIC - how to predetermine the damage

Larry Kirkpatrick

Revocation of PUBLIC - how to predetermine the damage
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 *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Thanikachalam "Billy" Sundarrajan

Re: Revocation of PUBLIC - how to predetermine the damage
(in response to Larry Kirkpatrick)
You can try this query and adjust it as needed.
Also, once you identify the packages, if you GRANT appropriate access to the OWNER of the package, and then do a re-bind, packages will not get invalidated when you subsequently revoke the PUBLIC access.

Look out for access path changes...

Cheers
Billy.

select COLLID, NAME, OWNER, VALIDATE, EXPLAIN from sysibm.syspackage
where
(COLLID, NAME)
IN (
select COLLID, GRANTEE
from sysibm.systabauth
where
GRANTEETYPE = 'P' AND
GRANTOR = 'PUBLIC' AND
TTNAME = ?)
with ur;




________________________________
From: Larry <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, February 5, 2010 1:19:51 PM
Subject: [DB2-L] Revocation of PUBLIC - how to predetermine the damage

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 *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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 *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

James Campbell

Re: Revocation of PUBLIC - how to predetermine the damage
(in response to Thanikachalam "Billy" Sundarrajan)
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!
_____________________________________________________________________
• DB2 certification -> no additional charge
• Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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: Revocation of PUBLIC - how to predetermine the damage
(in response to James Campbell)
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