On our development node the instance owner is 'itgmsv9'.
On our production node the instance owner is 'db2tgms'.

When I restored a production backup to a database on our development
node under the 'itgmsv9' instance, I was unable to re-grant the
permissions on the tables to the development team using this instance.

This has to do with the security model changes in 9.7.

The instance owner does not have SECADM authority by default. The
SYSADM has the ability to grant other users the SECADM authority.
However, the SYSADM cannot grant herself the SECADM authority. Any
member of the SYSADM_GROUP has SYSADM authority and thus can grant
SECADM authority to any user.

In this case, we found that 'db2tgms' was the only user that had
SECADM authority.

db2 "SELECT GRANTEE FROM SYSCAT.DBAUTH WHERE SECURITYADMAUTH = 'Y'"


Below is the writeup from the documentation;

A user who holds SYSADM authority no longer has implicit DBADM
authority, so has limited capabilities compared to those available in
Version 9.5.
A user who holds SYSADM authority is no longer able to grant any
authorities or privileges, except to grant table space privileges.

For a user holding SYSADM authority to obtain the same capabilities as
in Version 9.5 (other than the ability to grant SECADM authority), the
security administrator must explicitly grant them DBADM authority.
Note that when the security administrator grants DBADM authority, the
new DATAACCESS and ACCESSCTRL authorities are included by default.
This will give the user equivalent Version 9.5 capability. For this
user to also be able to grant SECADM authority, they must be granted
SECADM authority as well. Note, however, that holding SECADM authority
will allow this user to perform more actions than they could as a
Version 9.5 system administrator. For example, they will be able to
create objects such as roles, trusted contexts and audit policies.


This is what we had to do to get around the problem:

1) Create a 'db2tgms' account on our development node.

2) Login to the 'db2tgms' account on the development node and execute
the following commands;

db2 connect to <development db>

db2 grant dbadm on database to user itgmsv9

3) Login to the 'itgmsv9' on the development node and run my grant
script for the developers.


Is there an easier way to achieve this????

Thanks in advance,

On Sep 16, 7:37*pm, Chris Briel <cbr... (AT) gmail (DOT) com> wrote:
Hi Chris,

use the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable to have
all main administrative authorities in your restored database:
http://publib.boulder.ibm.com/infoce.../r0005658.html

Regards,
Mark B.

On Sep 24, 1:12*pm, MarkB <mark.barinst... (AT) gmail (DOT) com> wrote:
Hi Mark,

I tried this and it did not work.
I am able to take a production backup under instance A and restore it
in a development environment under instance B with no issues.
The issue I am having is when I go to run my 'grant' script under
instance B, he does not have permission to grant access to objects.

Below is the error message;

grant select, insert, update, delete on table T_USER_EVENTS to user
tgmsteam
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0551N "ITGMSV9" does not have the required authorization or
privilege to
perform operation "GRANT" on object "DB2TGMS.T_USER_EVENTS".
SQLSTATE=42501

To get around the issue I had to create an instance A user on the
development box as he was the only user that had SECURITYADMAUTH
authority.

Then I was able to use this account to grant DBADM to instance B.

Once this was done I could logon to the development box with instance
B and run my grant script.

Is there an easier way to do this?

This is something new in 9.7.

On Sep 27, 11:22*pm, Chris Briel <cbr... (AT) gmail (DOT) com> wrote:
Chris,

If you have set this variable for your development instance itgmsv9:
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
then restarted the instance (you have to do this until APAR IC76330
will be resolved):
db2stop
db2start
then restored your production database under instance itgmsv9 from
user itgmsv9 and didn't get DBADM and SECADM in restored database for
this user:

select dbadmauth, securityadmauth
from sysibm.sysdbauth
where grantee=USER

then it means that it's bug in the db2 code and you should open a PMR
for that.

Regards,
Mark B.

On Sep 28, 3:36*am, MarkB <mark.barinst... (AT) gmail (DOT) com> wrote:
Hi Mark,

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.

On Sep 28, 3:36*am, MarkB <mark.barinst... (AT) gmail (DOT) com> wrote:
Hi Mark,

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.

On Oct 12, 2:07*am, MarkB <mark.barinst... (AT) gmail (DOT) com> wrote:
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.

On Oct 12, 5:19*pm, Chris Briel <cbr... (AT) gmail (DOT) com> wrote:
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.

MarkB <mark.barinstein (AT) gmail (DOT) com> wrote in
news:e1613960-a944-4d76-94f1-6f69deabb19b (AT) a25g2000yqi (DOT) googlegroups.com:

The registry variable you mention was introduced in 9.7 FP2, so may be
the OP hasn't applied that fixpack yet?

Cheers!

--
Jeroen