 | |
#11
 
| |||
| |||
|
Re: DB2 Version 9.7 Security -
10-20-2011
, 12:38 PM
|
MarkB <mark.barinst... (AT) gmail (DOT) com> wrote innews:e1613960-a944-4d76-94f1-6f69deabb19b (AT) a25g2000yqi (DOT) googlegroups.com: On Oct 12, 5:19 pm, Chris Briel <cbr... (AT) gmail (DOT) com> wrote: On Oct 12, 2:07 am, MarkB <mark.barinst... (AT) gmail (DOT) com> wrote: The instance owner in production is db2tgms and the instance owner in dev is itgmsv9. db2tgms has the privileges, bu I do not have an instance in dev with that name. It is itgmsv9. Hi Chris, It changes nothing. You don't have to have the same instance name in dev to restore the database from a backup taken on prod with different instance name. Once again: If you set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES variable as described above, you will have SECADM and DBADM for user itgmsv9 (if you run RESTORE under this user) in your restored database. Hi Mark, The itgmsv9 user did not exist in the sysdbauth table after I did the restore. See below; db2 "select * from sysibm.sysdbauth" GRANTOR GRA NTEE DBADMA UTH CREATETABAUTH BINDADDAUTH CONNECTAUTH NOFENCEAUTH GRANTEETYPE IMPLSCHEMAAUTH LOADAUTH EXTERNALROUTINEAUTH QUIESCECONNECTAUTH SEC URITYADMAUTH LIBRARYADMAUTH GRANTORTYPE GRANTEEROLEID GRANTORROLEID SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH ACCESSCTRLAUTH ---------------------------------------------------------------------->> --- -- ----------------------------------------------------- --- ---------------------------------------------------------------------- --- -- -------------------------------------------------- ------ --- ------------- ----------- ----------- ----------- ----------- -------------- -------- ------------------- ------------------ --- ------------ -------------- ----------- ------------- ------------- ---------- ---------- ----------- -------------- -------------- SYSIBM DB2 TGMS Y Y Y Y * Y U Y Y Y Y Y N S * - - N N N Y * Y SYSIBM PUB LIC N Y Y Y * N G Y N N N N N S * - - N N N N * N DB2TGMS UTG MSAPP N N N Y * N U N N N N N N U * - - N N N N * N SYSIBM GTG MSDB Y N N N * N G N N N N N N S * - - N N N Y * Y 4 record(s) selected. Hi Chris, I was not able to reproduce this. For example, for this query: select dbadmauth, securityadmauth, substr(grantee, 1, 10) grantee from sysibm.sysdbauth I have: in the database under instance db2inst1: DBADMAUTH SECURITYADMAUTH GRANTEE --------- --------------- ---------- Y * * * * Y * * * * * * * DB2INST1 N * * * * N * * * * * * * PUBLIC when I restore this database under instance db2inst2 from the instance owner of this instance I have: DBADMAUTH SECURITYADMAUTH GRANTEE --------- --------------- ---------- Y * * * * Y * * * * * * * DB2INST1 N * * * * N * * * * * * * PUBLIC Y * * * * Y * * * * * * * DB2INST2 so, it works as designed. The reason why you don't have the same in you env can be only if you didn't set the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable for your dev instance. Otherwise you should open the PMR. Regards, Mark B. The registry variable you mention was introduced in 9.7 FP2, so may be the OP hasn't applied that fixpack yet? Cheers! -- Jeroen |
#12
| |||
| |||
|
|
MarkB <mark.barinst... (AT) gmail (DOT) com> wrote innews:e1613960-a944-4d76-94f1-6f69deabb19b (AT) a25g2000yqi (DOT) googlegroups.com: On Oct 12, 5:19 pm, Chris Briel <cbr... (AT) gmail (DOT) com> wrote: On Oct 12, 2:07 am, MarkB <mark.barinst... (AT) gmail (DOT) com> wrote: The instance owner in production is db2tgms and the instance owner in dev is itgmsv9. db2tgms has the privileges, bu I do not have an instance in dev with that name. It is itgmsv9. Hi Chris, It changes nothing. You don't have to have the same instance name in dev to restore the database from a backup taken on prod with different instance name. Once again: If you set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES variable as described above, you will have SECADM and DBADM for user itgmsv9 (if you run RESTORE under this user) in your restored database. Hi Mark, The itgmsv9 user did not exist in the sysdbauth table after I did the restore. See below; db2 "select * from sysibm.sysdbauth" GRANTOR GRA NTEE DBADMA UTH CREATETABAUTH BINDADDAUTH CONNECTAUTH NOFENCEAUTH GRANTEETYPE IMPLSCHEMAAUTH LOADAUTH EXTERNALROUTINEAUTH QUIESCECONNECTAUTH SEC URITYADMAUTH LIBRARYADMAUTH GRANTORTYPE GRANTEEROLEID GRANTORROLEID SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH ACCESSCTRLAUTH ---------------------------------------------------------------------->> --- -- ----------------------------------------------------- --- ---------------------------------------------------------------------- --- -- -------------------------------------------------- ------ --- ------------- ----------- ----------- ----------- ----------- -------------- -------- ------------------- ------------------ --- ------------ -------------- ----------- ------------- ------------- ---------- ---------- ----------- -------------- -------------- SYSIBM DB2 TGMS Y Y Y Y * Y U Y Y Y Y Y N S * - - N N N Y * Y SYSIBM PUB LIC N Y Y Y * N G Y N N N N N S * - - N N N N * N DB2TGMS UTG MSAPP N N N Y * N U N N N N N N U * - - N N N N * N SYSIBM GTG MSDB Y N N N * N G N N N N N N S * - - N N N Y * Y 4 record(s) selected. Hi Chris, I was not able to reproduce this. For example, for this query: select dbadmauth, securityadmauth, substr(grantee, 1, 10) grantee from sysibm.sysdbauth I have: in the database under instance db2inst1: DBADMAUTH SECURITYADMAUTH GRANTEE --------- --------------- ---------- Y * * * * Y * * * * * * * DB2INST1 N * * * * N * * * * * * * PUBLIC when I restore this database under instance db2inst2 from the instance owner of this instance I have: DBADMAUTH SECURITYADMAUTH GRANTEE --------- --------------- ---------- Y * * * * Y * * * * * * * DB2INST1 N * * * * N * * * * * * * PUBLIC Y * * * * Y * * * * * * * DB2INST2 so, it works as designed. The reason why you don't have the same in you env can be only if you didn't set the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable for your dev instance. Otherwise you should open the PMR. Regards, Mark B. The registry variable you mention was introduced in 9.7 FP2, so may be the OP hasn't applied that fixpack yet? Cheers! -- Jeroen |
We are running DB2 Version 9.7 FP3a.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.