Change database creator

Eric Pearson

Change database creator
We have a requirement to make
catalog references to a given ID as
a CREATOR or GRANTOR go away.
Unfortunately, the ID has created a large database in
'DB2 UDB for OS/390 and z/OS V7'. This means the
problem ID has DBADM against the database.
This DBMS does not have an 'ALTER DATABASE .. CREATOR'.

SO what we are considering (will definitely try this in a 'sandbox' first):

1) From a generic ID, redo all grants ever done against the
database and its objects by the problem ID including any REVOKE cascade
exposures.
2) Recycle the subsystem bringing it up in MAINT mode.
3) COPY the tablespaces in the database.
4) COPY the catalog and directory.
5) STOP the database.
6) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
to change the CREATOR in SYSIBM.SYSDATABASE.
7) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
to change the GRANTOR and GRANTEE for this ID in SYSIBM.SYSDBAUTH.
8) START the database and verify authorizations are all still in
place and that no PLAN or PACKAGE has been invalidated.

Anyone done this before? Or found another way of
changing the DATABASE CREATOR without a DROP/CREATE of
the database?

Anyone done similar with TABLESPACE creator?


Regards,
Eric Pearson



Eric Pearson

Change database creator
(in response to Eric Pearson)
Oh...
I did leave out a step.
Rebuild indexes DSNADH01 and DSNADX01.

Regards,
Eric Pearson
NS ITO DB2 support



Tina Hilton

Re: Change database creator
(in response to Eric Pearson)
Why do you want to change the creator? If you want to be able to revoke the
id, just set it to install sysadm in the zparms, bounce DB2, do the revoke,
set zparms back to the way it was, bounce DB2 and you're done.

Tina Hilton
arvato systems

-----Original Message-----
From: Pearson, Eric L, [mailto:[login to unmask email]
Sent: December 19, 2002 4:14 PM
To: [login to unmask email]
Subject: Change database creator


We have a requirement to make
catalog references to a given ID as
a CREATOR or GRANTOR go away.
Unfortunately, the ID has created a large database in
'DB2 UDB for OS/390 and z/OS V7'. This means the
problem ID has DBADM against the database.
This DBMS does not have an 'ALTER DATABASE .. CREATOR'.

SO what we are considering (will definitely try this in a 'sandbox' first):

1) From a generic ID, redo all grants ever done against the
database and its objects by the problem ID including any REVOKE cascade
exposures.
2) Recycle the subsystem bringing it up in MAINT mode.
3) COPY the tablespaces in the database.
4) COPY the catalog and directory.
5) STOP the database.
6) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
to change the CREATOR in SYSIBM.SYSDATABASE.
7) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
to change the GRANTOR and GRANTEE for this ID in SYSIBM.SYSDBAUTH.
8) START the database and verify authorizations are all still in
place and that no PLAN or PACKAGE has been invalidated.

Anyone done this before? Or found another way of
changing the DATABASE CREATOR without a DROP/CREATE of
the database?

Anyone done similar with TABLESPACE creator?


Regards,
Eric Pearson








Eric Pearson

Re: Change database creator
(in response to Tina Hilton)
When you are the creator of a database,
you get DBADM on the database. The auditors do not
want defunct RACF IDs to have DBADM as these IDs may
some day get reused.

Regards,
Eric Pearson



-----Original Message-----
From: Hilton, Tina, arvato systems - NMI -PM
[mailto:[login to unmask email]
Sent: Friday, December 20, 2002 7:20 AM
To: [login to unmask email]
Subject: Re: Change database creator


Why do you want to change the creator? If you want to be able to revoke the
id, just set it to install sysadm in the zparms, bounce DB2, do the revoke,
set zparms back to the way it was, bounce DB2 and you're done.

Tina Hilton
arvato systems

-----Original Message-----
From: Pearson, Eric L, [mailto:[login to unmask email]
Sent: December 19, 2002 4:14 PM
To: [login to unmask email]
Subject: Change database creator


We have a requirement to make
catalog references to a given ID as
a CREATOR or GRANTOR go away.
Unfortunately, the ID has created a large database in
'DB2 UDB for OS/390 and z/OS V7'. This means the
problem ID has DBADM against the database.
This DBMS does not have an 'ALTER DATABASE .. CREATOR'.

SO what we are considering (will definitely try this in a 'sandbox' first):

1) From a generic ID, redo all grants ever done against the
database and its objects by the problem ID including any REVOKE cascade
exposures.
2) Recycle the subsystem bringing it up in MAINT mode.
3) COPY the tablespaces in the database.
4) COPY the catalog and directory.
5) STOP the database.
6) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
to change the CREATOR in SYSIBM.SYSDATABASE.
7) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
to change the GRANTOR and GRANTEE for this ID in SYSIBM.SYSDBAUTH.
8) START the database and verify authorizations are all still in
place and that no PLAN or PACKAGE has been invalidated.

Anyone done this before? Or found another way of
changing the DATABASE CREATOR without a DROP/CREATE of
the database?

Anyone done similar with TABLESPACE creator?


Regards,
Eric Pearson













michael bell

Re: Change database creator
(in response to Eric Pearson)
I suppose this isn't a good time for a rant but
DB2 has had support for secondary auth id's for a decade now. Do not give
create dba auth to a personal id! EVER! I recommend using 8 char secondary
id because tso is limited to 7. All the administration products support
secondary id's, it only takes a set current sqlid to make spufi and stuff
work.

If you use the procedure below, it will remove the dbadm auth and you don't
care about the created by since it is always the personal id not the
secondary (that means it is commentary and audit trail not authorization).

Mike Bell
HLS Technologies
----- Original Message -----
From: "Pearson, Eric L," <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Friday, December 20, 2002 8:00 AM
Subject: Re: Change database creator


> When you are the creator of a database,
> you get DBADM on the database. The auditors do not
> want defunct RACF IDs to have DBADM as these IDs may
> some day get reused.
>
> Regards,
> Eric Pearson
>
>
>
> -----Original Message-----
> From: Hilton, Tina, arvato systems - NMI -PM
> [mailto:[login to unmask email]
> Sent: Friday, December 20, 2002 7:20 AM
> To: [login to unmask email]
> Subject: Re: Change database creator
>
>
> Why do you want to change the creator? If you want to be able to revoke
the
> id, just set it to install sysadm in the zparms, bounce DB2, do the
revoke,
> set zparms back to the way it was, bounce DB2 and you're done.
>
> Tina Hilton
> arvato systems
>
> -----Original Message-----
> From: Pearson, Eric L, [mailto:[login to unmask email]
> Sent: December 19, 2002 4:14 PM
> To: [login to unmask email]
> Subject: Change database creator
>
>
> We have a requirement to make
> catalog references to a given ID as
> a CREATOR or GRANTOR go away.
> Unfortunately, the ID has created a large database in
> 'DB2 UDB for OS/390 and z/OS V7'. This means the
> problem ID has DBADM against the database.
> This DBMS does not have an 'ALTER DATABASE .. CREATOR'.
>
> SO what we are considering (will definitely try this in a 'sandbox'
first):
>
> 1) From a generic ID, redo all grants ever done against the
> database and its objects by the problem ID including any REVOKE cascade
> exposures.
> 2) Recycle the subsystem bringing it up in MAINT mode.
> 3) COPY the tablespaces in the database.
> 4) COPY the catalog and directory.
> 5) STOP the database.
> 6) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
> to change the CREATOR in SYSIBM.SYSDATABASE.
> 7) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
> to change the GRANTOR and GRANTEE for this ID in SYSIBM.SYSDBAUTH.
> 8) START the database and verify authorizations are all still in
> place and that no PLAN or PACKAGE has been invalidated.
>
> Anyone done this before? Or found another way of
> changing the DATABASE CREATOR without a DROP/CREATE of
> the database?
>
> Anyone done similar with TABLESPACE creator?
>
>
> Regards,
> Eric Pearson
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
>





Eric Pearson

Re: Change database creator
(in response to michael bell)
We have some 'legacy' implicit authorizations
from before anyone in the shop had much DB2
training or experience. We now use *only* secondary
authids for object creation and grants and binds
(at least in QA and prod). I did the secondary auth
rant my first week as a contractor a few years ago.
and yes, it is *always* a good time for that
particular rant!

Regards,
Eric Pearson



-----Original Message-----
From: michael bell [mailto:[login to unmask email]
Sent: Friday, December 20, 2002 10:08 AM
To: [login to unmask email]
Subject: Re: Change database creator


I suppose this isn't a good time for a rant but
DB2 has had support for secondary auth id's for a decade now. Do not give
create dba auth to a personal id! EVER! I recommend using 8 char secondary
id because tso is limited to 7. All the administration products support
secondary id's, it only takes a set current sqlid to make spufi and stuff
work.

If you use the procedure below, it will remove the dbadm auth and you don't
care about the created by since it is always the personal id not the
secondary (that means it is commentary and audit trail not authorization).

Mike Bell
HLS Technologies
----- Original Message -----
From: "Pearson, Eric L," <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Friday, December 20, 2002 8:00 AM
Subject: Re: Change database creator


> When you are the creator of a database,
> you get DBADM on the database. The auditors do not
> want defunct RACF IDs to have DBADM as these IDs may
> some day get reused.
>
> Regards,
> Eric Pearson
>
>
>
> -----Original Message-----
> From: Hilton, Tina, arvato systems - NMI -PM
> [mailto:[login to unmask email]
> Sent: Friday, December 20, 2002 7:20 AM
> To: [login to unmask email]
> Subject: Re: Change database creator
>
>
> Why do you want to change the creator? If you want to be able to revoke
the
> id, just set it to install sysadm in the zparms, bounce DB2, do the
revoke,
> set zparms back to the way it was, bounce DB2 and you're done.
>
> Tina Hilton
> arvato systems
>
> -----Original Message-----
> From: Pearson, Eric L, [mailto:[login to unmask email]
> Sent: December 19, 2002 4:14 PM
> To: [login to unmask email]
> Subject: Change database creator
>
>
> We have a requirement to make
> catalog references to a given ID as
> a CREATOR or GRANTOR go away.
> Unfortunately, the ID has created a large database in
> 'DB2 UDB for OS/390 and z/OS V7'. This means the
> problem ID has DBADM against the database.
> This DBMS does not have an 'ALTER DATABASE .. CREATOR'.
>
> SO what we are considering (will definitely try this in a 'sandbox'
first):
>
> 1) From a generic ID, redo all grants ever done against the
> database and its objects by the problem ID including any REVOKE cascade
> exposures.
> 2) Recycle the subsystem bringing it up in MAINT mode.
> 3) COPY the tablespaces in the database.
> 4) COPY the catalog and directory.
> 5) STOP the database.
> 6) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
> to change the CREATOR in SYSIBM.SYSDATABASE.
> 7) Use REPAIR ... LOCATE ... VERIFY ... REPLACE
> to change the GRANTOR and GRANTEE for this ID in SYSIBM.SYSDBAUTH.
> 8) START the database and verify authorizations are all still in
> place and that no PLAN or PACKAGE has been invalidated.
>
> Anyone done this before? Or found another way of
> changing the DATABASE CREATOR without a DROP/CREATE of
> the database?
>
> Anyone done similar with TABLESPACE creator?
>
>
> Regards,
> Eric Pearson
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
>










Phil Grainger

Re: Change database creator
(in response to Eric Pearson)
I started thinking about this

It seems a shame to have to drop the database just to change the creator -
just think how much stuff is going to have to be recreated! (doesn't bear
thinking about)

Then I wondered if you could just use REPAIR to change the creator ID in
SYSDATABASE and SYSDBAUTH (and any affected indexes)....... (followed by a
judicious running of REPAIR REBUILD DBD)

I'd have to try it to see, and you wouldn't even be asking the question if
this wasn't a PRODUCTION subsystem we were talking about, so.... Perhaps
it's too near Christmas to risk being 'downsized'!

Otherwise, I don't think there IS a solution

Thinking "outside the box" why not get the ID reallocated immediately but
NOT allocated to anyone. Then it won't be reused accidentally in the future

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Pearson, Eric L, [mailto:[login to unmask email]
Sent: 20 December 2002 15:28
To: [login to unmask email]
Subject: Re: [DB2-L] Change database creator


We have some 'legacy' implicit authorizations
from before anyone in the shop had much DB2
training or experience. We now use *only* secondary
authids for object creation and grants and binds
(at least in QA and prod). I did the secondary auth
rant my first week as a contractor a few years ago.
and yes, it is *always* a good time for that
particular rant!

Regards,
Eric Pearson

Marcus Davage

Re: Change database creator
(in response to Eric Pearson)
Eric,

I'd be interested to see if this works. In fact, I think we've a database I
think we might be able to try it out on!

Marcus


-----------------------------------------------------------------------------------------------------------------------
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.

evolvebank.com is a division of Lloyds TSB Bank plc.
Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS. Registered in
England, number 2065. Telephone No: 020 7626 1500
Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone
No: 0131 225 4555

Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
Financial Services Authority and represent only the Scottish Widows
and Lloyds TSB Marketing Group for life assurance, pensions and
investment business.

Signatories to the Banking Codes.
-----------------------------------------------------------------------------------------------------------------------

Eric Pearson

Re: Change database creator
(in response to Phil Grainger)
We will try this in a sandbox before
in the test, qa, prod environments.
Getting an ID 'reserved' is not so
easy in our environment. Dropping and
recreating the databases is not an
option as the change must be accomplished
in a very tiny window and one of the
databases has about 400 million rows and
some others are near that range.


Regards,
Eric Pearson


-----Original Message-----
From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, December 20, 2002 11:05 AM
To: [login to unmask email]
Subject: Re: Change database creator



I started thinking about this

It seems a shame to have to drop the database just to change the creator -
just think how much stuff is going to have to be recreated! (doesn't bear
thinking about)

Then I wondered if you could just use REPAIR to change the creator ID in
SYSDATABASE and SYSDBAUTH (and any affected indexes)....... (followed by a
judicious running of REPAIR REBUILD DBD)

I'd have to try it to see, and you wouldn't even be asking the question if
this wasn't a PRODUCTION subsystem we were talking about, so.... Perhaps
it's too near Christmas to risk being 'downsized'!

Otherwise, I don't think there IS a solution

Thinking "outside the box" why not get the ID reallocated immediately but
NOT allocated to anyone. Then it won't be reused accidentally in the future

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Pearson, Eric L, [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: 20 December 2002 15:28
To: [login to unmask email]
Subject: Re: [DB2-L] Change database creator


We have some 'legacy' implicit authorizations
from before anyone in the shop had much DB2
training or experience. We now use *only* secondary
authids for object creation and grants and binds
(at least in QA and prod). I did the secondary auth
rant my first week as a contractor a few years ago.
and yes, it is *always* a good time for that
particular rant!

Regards,
Eric Pearson

Eric Pearson

Re: Change database creator
(in response to Marcus Davage)
I will probably try this in a sandbox next week.
I will report the success or failure and any
difficulties encountered.


Regards,
Eric Pearson
NS ITO DB2 support

-----Original Message-----
From: Davage, Marcus [mailto:[login to unmask email]
Sent: Friday, December 20, 2002 11:06 AM
To: [login to unmask email]
Subject: Re: Change database creator



Eric,

I'd be interested to see if this works. In fact, I think we've a database I
think we might be able to try it out on!

Marcus



----------------------------------------------------------------------------
-------------------------------------------
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.

evolvebank.com is a division of Lloyds TSB Bank plc.
Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS. Registered in
England, number 2065. Telephone No: 020 7626 1500
Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone
No: 0131 225 4555

Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
Financial Services Authority and represent only the Scottish Widows
and Lloyds TSB Marketing Group for life assurance, pensions and
investment business.

Signatories to the Banking Codes.
----------------------------------------------------------------------------
-------------------------------------------


Eric Pearson

Re: Change database creator
(in response to Eric Pearson)
For those interested,
the process which worked was:

1) Run REPAIR ... LOCATE ... DUMP against DSNDB06.SYSDBAUT to get the
page numbers and displacements of the SYSDATABASE and SYSDBAUTH entries for
the database.

2) Run REPAIR ... LOCATE ... VERIFY to make sure you understand the
pages and displacements correctly.

FOLLOWING STEPS SHOULD BE DONE WITH SUBSYSTEM IN ACC(MAINT):



3) Back up DSNDB01 and DSNDB06.SYSDBAUT. You could do this before the
recycle to MAINT mode, but extra caution is often of value.

4) Run REPAIR ... LOCATE ... VERIFY ... REPLACE against the
GRANTOR and GRANTEE fields in SYSDBAUTH.

5) Run REPAIR ... LOCATE ... VERIFY ... REPLACE against the
CREATOR field in SYSDATABASE.

6) REBUILD INDEX(SYSIBM.DSNADX01)

7) REBUILD INDEX(SYSIBM.DSNADH01) - order of 6) and 7) does not matter.

8) Start the database in ACC(UT).

9) Run REPAIR DBD REBUILD to regenerate the DBD entry in the directory from
the database entries in the catalog.

10) Restart the database in ACC(RW).



No objects or authorizations were lost or damaged.



Regards,
Eric Pearson
NS ITO DB2 support

-----Original Message-----
From: Davage, Marcus [mailto:[login to unmask email]
Sent: Friday, December 20, 2002 11:06 AM
To: [login to unmask email]
Subject: Re: Change database creator



Eric,

I'd be interested to see if this works. In fact, I think we've a database I
think we might be able to try it out on!

Marcus



----------------------------------------------------------------------------
-------------------------------------------
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.

evolvebank.com is a division of Lloyds TSB Bank plc.
Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS. Registered in
England, number 2065. Telephone No: 020 7626 1500
Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone
No: 0131 225 4555

Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
Financial Services Authority and represent only the Scottish Widows
and Lloyds TSB Marketing Group for life assurance, pensions and
investment business.

Signatories to the Banking Codes.
----------------------------------------------------------------------------
-------------------------------------------