DB2 z/OS V8 PK09118

Roland Schiradin

DB2 z/OS V8 PK09118
Does anybody have a smart REXX or SPUFI to recreate the grants after the needed FREE/BIND?
I'm thinking of such one as of so may DB2 subsystem with the same issue.


Roland Schiradin
ALTE LEIPZIGER Lebensversicherung auf Gegenseitigkeit
IT Betrieb - DB/DC
Tel. (06171) 66-4095, Fax (06171) 66-7500-4095
mailto:[login to unmask email]
http://www.Alte-Leipziger.de

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David S. Waugh

Re: DB2 z/OS V8 PK09118
(in response to Roland Schiradin)
So, they leave it up to you to clean up the mess in SYSPLANAUTH by (a)
freeing the plans with 2 or more rows, (b) BINDing then plans again, and
then (c) re-granting the authorities that people had before, huh? Swell...

This isn't what I would call terribly "smart" (actually it's probably much
cruder than what you really need), but it should help:

-- GENERATE RE-GRANTS OF PLAN AUTHORITIES AFTER PK09118
SELECT
'GRANT',
CASE WHEN BINDAUTH ¬= ' ' THEN 'BIND,' ELSE ' ' END,
CASE WHEN EXECUTEAUTH ¬= ' ' THEN 'EXECUTE' ELSE ' ' END,
'ON PLAN', STRIP(NAME),
'TO', STRIP(GRANTEE) CONCAT X'5E'
-- , '-- BOUND BY', GRANTOR, 'ON:', GRANTEDTS
FROM SYSIBM.SYSPLANAUTH
WHERE NAME IN ('AAAAAAAA',
'BBBBBBBB',
'CCCCCCCC',
'DDDDDDDD' )
-- AND GRANTEE IN ('XXXXXXXX',
-- 'YYYYYYYY',
-- 'ZZZZZZZZ' )
AND (BINDAUTH ¬= ' '
OR EXECUTEAUTH ¬= ' ')
GROUP BY GRANTOR, BINDAUTH, EXECUTEAUTH,
GRANTEE, NAME, GRANTEDTS
ORDER BY GRANTOR, GRANTEE, NAME
FOR FETCH ONLY WITH UR;

Obviously, you'll have to supply the names of all the plans affected, the
grantees you want re-instated (optional), and then edit the output to get
rid of extraneous commas, grants for authids that have been gotten rid of,
and such. And you'll have to come up with some way of identifying the
affected plan names in the first place before you can plug them into the
WHERE NAME IN clause (that's going to be the hardest part, I think)...

Good luck! Hope there aren't too many you have to fix like this.
.
Thanks,
.
David Waugh
DSW Consulting & Services




"Schiradin,Roland HG-Dir itb-db/dc" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/16/2005 05:28 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] DB2 z/OS V8 PK09118






Does anybody have a smart REXX or SPUFI to recreate the grants after the
needed FREE/BIND?
I'm thinking of such one as of so may DB2 subsystem with the same issue.


Roland Schiradin
ALTE LEIPZIGER Lebensversicherung auf Gegenseitigkeit
IT Betrieb - DB/DC
Tel. (06171) 66-4095, Fax (06171) 66-7500-4095
mailto:[login to unmask email]
http://www.Alte-Leipziger.de

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm