cancel
Showing results for 
Search instead for 
Did you mean: 

Authorizations for user db2<sid> after systemcopy with DB2 V9.7 on AIX

Former Member
0 Kudos
930

Hello,

I made a homogenous systemcopy from the system PRD to ENT with an redirected restore. I had the following system environment:

AIX 5.3 TL10 SP1

DB2 V9.7 (without any fixpack)

After the restore and the recovery were finished, I was able to start the database manager and to activate the database.

I tried to execute a script for cleanup some tables according to the systemcopy guide but I got the following SQL messages:

SQL0551N, SQL0552N for the user db2ent. I checked the authorization for this user and got the following information:

db2 => get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO

Direct SYSCTRL authority = NO

Direct SYSMAINT authority = NO

Direct DBADM authority = NO

Direct CREATETAB authority = NO

Direct BINDADD authority = NO

Direct CONNECT authority = NO

Direct CREATE_NOT_FENC authority = NO

Direct IMPLICIT_SCHEMA authority = NO

Direct LOAD authority = NO

Direct QUIESCE_CONNECT authority = NO

Direct CREATE_EXTERNAL_ROUTINE authority = NO

Direct SYSMON authority = NO

Indirect SYSADM authority = YES

Indirect SYSCTRL authority = NO

Indirect SYSMAINT authority = NO

Indirect DBADM authority = NO

Indirect CREATETAB authority = NO

Indirect BINDADD authority = NO

Indirect CONNECT authority = NO

Indirect CREATE_NOT_FENC authority = NO

Indirect IMPLICIT_SCHEMA authority = NO

Indirect LOAD authority = NO

Indirect QUIESCE_CONNECT authority = NO

Indirect CREATE_EXTERNAL_ROUTINE authority = NO

Indirect SYSMON authority = NO

db2 =>

The user db2ent was/is in the group dbentadm and the group dbentadm is configured as SYSADM:

SYSADM group name (SYSADM_GROUP) = DBENTADM

SYSCTRL group name (SYSCTRL_GROUP) = DBENTCTL

SYSMAINT group name (SYSMAINT_GROUP) = DBENTMNT

The only solution was to grant the authorizations with an other user to db2ent.

For the restore I created an new instance with the following command (as user root):

/db2/ENT/db2_software/instance/db2icrt -a SERVER_ENCRYPT -s ESE -u db2ent db2ent

I set the correct DBM configuration and created an empty database as user db2ent with the following command

db2 create db ENT on /db2/ENT

The restore was executed with db2 -tvf restore_prd.clp as user db2ent.

Is there a bug in the db2 software or is there any other solution? I did not changed the environment for the user db2ent.

The authorization concept has been changed in DB2 V9.7

http://www-01.ibm.com/support/docview.wss?uid=swg21385801

Kind regards,

Christian

Accepted Solutions (0)

Answers (1)

Answers (1)

Frank-Martin
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Christian,

the problem you encountered will be solved in upcoming FPs of V9.7 . V9.7 distinguishes between SYSADM and SECADM authority. As a default the instance owner gets both authorties when the database is created.

After a BACKUP/RESTORE the new instance owner does not get the SECADM and DBADM authority automatically.

You can perform one of the following workarounds:

(1) grant SECADM and DBADM to the target instance owning user before the BACKUP

(2) After the restore create OS user db2<sidold> .

Connect to the database as this user and grant SECADM and DBADM to the target instance owning user.

After this you can remove the user db2<sidold> from OS level.

Regards

Frank

Former Member
0 Kudos

Hi Frank,

the second way was the solution which I took.

Thanks and best Regrads,

Christian

Former Member
0 Kudos

Hello All,

I finished restore using redirect method, but i did not know about this security issue.

Now I tried creating db2<oldsid> user and tried granting dbadm secadm priv.

but i get this error

db2 => GRANT DBADM to USER DB2P60

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0707N The name "DBADM" cannot be used because the specified identifier is

reserved for system use. SQLSTATE=42939

Please help me.

I need a solution at the earliest possible.

Thanks,

Sree

Former Member
0 Kudos

I have used option 2 above and it works. As mentioned above, this is something that changed on DB2 9.7.

You may have te syntax of the grant command wrong. Try:

grant dbadm on database to user <usrid>

former_member196032
Participant
0 Kudos

I also set the dbs_db6_schema in the .dbenv* files to the old db2sid user.