db6util -rtvt authorization error

[former_member542926]

Hi all,

We just performed a system copy and as part of post activities when we try to run the below command in the target system (DB2/linux). We face authorization error

db6util -rtvt <sourcesid>#% <targetsid>#%

This is the DB6 (DB2 UDB) utility program for SAP kernel 721.

(I) Option "-rtvt NV1#% NV3#%" .

Renaming data tablespace NV1#STABD in virtual Table /1CN/CMFSAPH0FOR . Renaming index tablespace NV1#STABI in virtual Table /1CN/CMFSAPH0FOR . Renaming long tablespace NV1#STABD in virtual Table /1CN/CMFSAPH0FOR . (I) Starttime: 20180204195040 (I) Connecting to NV3 as user sapnv3. (I) Successfully connected to database NV3 . (I) Setting current schema to SAPNV1 . (I) Setting current path to SYSTEM PATH, 'SAPTOOLS' , 'SAPNV1' .

(E) An unexpected error occured while accessing table /1CN/CMFSAPH0FOR [IBM][CLI Driver][DB2/LINUXX8664] SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "SAPNV3". Operation: "REPLACE VIEW". Object: "SAPNQ1./1CN/CMFSAPH0FOR". SQLSTATE=42501 (I) Disconnected from database. (I) Disconnected from database.

the user SAPNV3 has DBADM, SAP is running fine.

Can you please let us know how to overcome this error?

[Frank-Martin]

Hi,

This is not a db6util problem but a problem with the ownership with some of your database objects. Even if your SAP system is currently running fine, you may encounter problems later when the SAP ABAP dictionary tries to touch a database object. SAP ABAP dictionary assumes that all database objects are owned by the ABAP connect user.

Did you use SWPM for your system copy?

As part of the system copy procedure SWPM transfers the ownership of all database objects that were owned by the old connect user to the new connect user. From the SQL error message above I conclude that your old connect user has been
"SAPNQ1" and your new connect user is "SAPNV3". If you have been using SWPM and transferring the ownership did not work, this may be a SWPM bug and you should open an SAP incident . In this case SAP support will need your installation log files to check if the correct parameters have been entered in the dialog phase of SWPM.

If you did not use SWPM for your system copy and you just performed a plain BACKUP/RESTORE of your database you are on your own. Officially this is not supported. To resolve this problem now the ownership of all database objects that were owned by the old connect user need to be transfered to the new connect user using DB2 means. This can be pretty complex depending on your SAP basis version.

For example you can check if there are still VIEWS owned by the old connect user using the following DB2 CLP command:

db2 " select count(*) from syscat.tables where owner = 'SAPNQ1' and type = 'V' "

If this returns a non zero number, you can use the following SQL statement to create CLP commands that transfer the ownership of the views.

db2 -x " select 'db2 ' || chr(34) || ' TRANSFER OWNERSHIP OF VIEW SAPNQ1.' || chr(34) || tabname || chr(34) || ' TO USER SAPNV3 PRESERVE PRIVILEGES ' || chr(34) from syscat.tables where owner = 'SAPPB3' and type = 'V' "

If this is correct you may use this output to create a DB2 CLP script or even pipe the output into a shell.

db2 -x " select 'db2 ' || chr(34) || ' TRANSFER OWNERSHIP OF VIEW SAPNQ1.' || chr(34) || tabname || chr(34) || ' TO USER SAPNV3 PRESERVE PRIVILEGES ' || chr(34) from syscat.tables where owner = 'SAPPB3' and type = 'V' " | sh

In SAP basis versions lower than 7.40 similar steps have to be performed for all TABLES, INDEXES and SQL VARIABLES owned by the old connect user. In SAP basis versions 7.40 and higher there are even more database objects created by the SAP ABAP stack ( SCALAR FUNCTIONS, TABLE FUNCTIONS, GLOBAL TEMPORARY TABLES, MODULES ) where the ownership needs to be transfered to the new connect user. To add to the picture there is no TRANSFER OWNERSHIP command for MODULES. Those need to be recreated with the new owner preserving the old definition.

Sounds complex ? It is!!! This is why you should make use of SWPM for your system copy with BACKUP/RESTORE ( note 713524 ).

Only if the connect user stays the same on source and target system, the task becomes easier. However most customers reject this setup for security reasons.

Regards
Frank

[Frank-Martin]

to add some background ...

The db6util problem is caused by CREATE OR REPLACE (VIEW) commands. To execute a CREATE OR REPLACE (DBOBJECT) statement you need to be owner of this statement. DBADM authority is not sufficient.

I also forgot TRIGGERs as database objects. Those may or may not exist in the BACKUP.

Regards

Frank