DB02 privileges on SYSIBM etc.

[Former Member]

Dear communitiy,

I've a problem with transaction DB02 after and upgrade from basis 6.20 to 7.00:

SQL0551N "SAPPSM" does not have the privilege to

perform operation "SELECT" on object

"SYSIBM.SYSDUMMY1". SQLSTATE=42501

sappsm is the connect user which is in the newly created group dbpsmmnt and in also in group sapsys.

db2 get dbm cfg:

SYSADM group name (SYSADM_GROUP) = DBPSMADM

SYSCTRL group name (SYSCTRL_GROUP) = DBPSMCTL

SYSMAINT group name (SYSMAINT_GROUP) = BPSMMNT

db2 version is 8.1 fixpak 11

also saproot.sh was executed successfully...

I don't know what more to do. I cross checked all permissions with a 6.40 system and everything I can think of is the same.

Regards,

Pascal

[Former Member]

We have the same problem. Some ideas ?

Looks like rights are missing for sapr3:

Direct DBADM authority = NO

Direct CREATE_NOT_FENC authority = NO

Direct LOAD authority = NO

Direct QUIESCE_CONNECT authority = NO

Direct CREATE_EXTERNAL_ROUTINE authority = NO

Kind regards,

Uta

[Former Member]

We have found the solution:

sf601:db2ez1 10% db2 grant dbadm on database to user sapr3

DB20000I The SQL command completed successfully.

Rights are o.k. now and db6cokpit is working correctly.

sf601:db2ez1 12% db2 connect to ez1 user sapr3 using <passwordt>

sf601:db2ez1 13% 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 = YES

Direct BINDADD authority = YES

Direct CONNECT authority = YES

Direct CREATE_NOT_FENC authority = NO

Direct IMPLICIT_SCHEMA authority = YES

Direct LOAD authority = NO

Direct QUIESCE_CONNECT authority = NO

Direct CREATE_EXTERNAL_ROUTINE authority = NO

Direct SYSMON authority = NO

Indirect SYSADM authority = NO

Indirect SYSCTRL authority = NO

Indirect SYSMAINT authority = YES

Indirect DBADM authority = NO

Indirect CREATETAB authority = YES

Indirect BINDADD authority = YES

Indirect CONNECT authority = YES

Indirect CREATE_NOT_FENC authority = NO

Indirect IMPLICIT_SCHEMA authority = YES

Indirect LOAD authority = NO

Indirect QUIESCE_CONNECT authority = NO

Indirect CREATE_EXTERNAL_ROUTINE authority = NO

Indirect SYSMON authority = NO

#####################################################

Kind regards

Uta

[Former Member]

Hi pascal

pls issue

grant execute on Procedure SYSPROC.GET_DB_CONFIG to SAP<sapsid>

(or to PUBLIC )

Best regards

dirk

[Former Member]

Hi Dirk,

also this command solves the problem, thanks.

But I'm still wondering where these missing permissions come from...usually you grant use for tablespace to public for SAp tablespaces, but this is not possible for SYSCATSPACE...

[Frank-Martin]

Hi Pascal,

yes, I also wonder where the missing permissions come from. If you want to have this problem invesigated further, I suggest to open a SAP service call. We can then forward your data to IBM in a PMR.

Regards

Frank

[Former Member]

Hi Frank,

I opened a customer message about this topic, I keep you updated...

Regards,

Pascal

[Former Member]

Hi Pascal

pls issue

select * from syscat.routineauth where schema='SYSPROC' and specificname='GET_DB_CONFIG'

Best regards

dirk

[Former Member]

Hi Dirk,

SYSIBM DB2PSM U SYSPROC GET_DB_CONFIG - - P G 2005-06-06-12.29.33.418085

Regards,

Pascal

[Former Member]

Hi Pascal

the standard is select authority for public on SYSIBM.SYSDUMMY1 which was somehow revoked on your System .

Only with SYSADM you gain access to this table without proper permissions.

To restore the Privileges on SYSIBM.SYSDUMMY1 issue

GRANT SELECT ON SYSIBM.SYSDUMMY1 TO PUBLIC

So you could perform the Select on this table without being SYSADM

>>>snip<<<

but it's the same result on a system where it works...

>>>unsnip<<<

Maybe there is no select on that table in DB02 on that System .

You could check , if this system would also be affected , when you connect to the mentioned System as sap<sapsid> as above .

Check that you have only SYSMAINT Authority .

Issue

Select * from sysibm.sysdummy1

Without the Grant, this should give you the same results as above .

db2 'select * from sysibm.sysdummy1'

SQL0551N "SAPxxx" does not have the privilege to perform operation "SELECT"

on object "SYSIBM.SYSDUMMY1". SQLSTATE=42501

Hth

Best regards

Dirk

[Former Member]

HI Dirk,

thanks, issuing the grant solved the problem. How did you know that the standard is select for public allowed on that table?

The other system was basis 6.40 not 7.0 so you could be right that DB02 doesn't need sysdummy1 there.

[Former Member]

Hi Pascal

Create a new database on an development/test system

and issue

select * from syscat.tabauth where tabname='SYSDUMMY1' and TABSCHEMA='SYSIBM'

Btw , nothing else makes sense to me for that table.

Best regards

dirk

[Former Member]

Hi Dirk,

well, when calling DB13 I receive a new error:

SQL0551N "SAPPSM" does not have the privilege to

perform operation "EXECUTE" on object

"SYSPROC.GET_DB_CONFIG". SQLSTATE=42501

Did I miss anything during the upgrade to nw2004s?

Regards,

Pascal

(PS I want to give you points but it always says rewarding the message fails, sorry but I keep trying)

[Former Member]

Hi Pascal

what gives

select GRANTOR,GRANTEE,SELECTAUTH from syscat.tabauth where tabname='SYSDUMMY1' and TABSCHEMA='SYSIBM'

?

Best regards

dirk

[Former Member]

Hi Dirk,

the result is:

0 record(s) selected.

but it's the same result on a system where it works...

Regards,

Pascal

[Former Member]

Hi Pascal

Please perform the following steps for Diagnosis

1. Logon to the Database Server as user db2<dbsid>

2. Connect to your Database as user sap<sapsid>

issue

db2 connect to psm user sappsm

then issue

db2 get authorizations

3. Please verify , that you have

>>>snip<<<

Indirect SYSMAINT authority = YES

>>>unsnip<<<

Could you please provide the results

Best regards

dirk

[Former Member]

Hi Dirk, this is the result:

db2psm> db2 connect to psm user sappsm

Enter current password for sappsm:

Database Connection Information

Database server = DB2/LINUX 8.2.4

SQL authorization ID = SAPPSM

Local database alias = PSM

db2psm> 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 = YES

Direct BINDADD authority = YES

Direct CONNECT authority = YES

Direct CREATE_NOT_FENC authority = NO

Direct IMPLICIT_SCHEMA authority = YES

Direct LOAD authority = NO

Direct QUIESCE_CONNECT authority = NO

Direct CREATE_EXTERNAL_ROUTINE authority = NO

Direct SYSMON authority = NO

Indirect SYSADM authority = NO

Indirect SYSCTRL authority = NO

Indirect SYSMAINT authority = YES

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 = YES

Indirect SYSMON authority = NO

So it should be correct, by the way I added user sappsm to group dbpsmadm and the DB02 problems are gone. But this isn't a real solution...

Regards,

Pascal

[Frank-Martin]

Hi Pascal,

could this be a type in the group name in dbm cfg?

You wrote on OS level user sappsm is in group dbpsmmnt which does not match the configured SYSMAINT_GROUP .

SYSMAINT group name (SYSMAINT_GROUP) = BPSMMNT

Regards

Frank

[Former Member]

Hi Frank,

no, this was only because I reformatted the copy&pasted text here, sorry, it's:

DBPSMMNT

Regards,

Pascal