Access to MAXDB SAP Schema tables

[Former Member]

HI Guys ,

I am trying to reset SAP* password in my MAXDB DB 7.6. I know that is a very simple operation and I did it many times in other DBs , but here I have stuck during whole day .

I have connected to SQL Studio as SUPERDBA .

My SAP Schema is "SAPIDS" .

To access usr02 table I run the following SQL :

select MANDT, BNAME, UFLAG from SAPIDS.USR02 where MANDT='000' and BNAME='SAP*'

, but I got the error thet the table does not exist .

I also tried:

select MANDT, BNAME, UFLAG from "SAPIDS"."USR02" where MANDT='000' and BNAME='SAP* ,

select * from SAPIDS.USR02 ; select * from "SAPIDS"."USR02" ;

I switch to Oracle mode also , but Nothing is working !

Also important to say that sql :

select * from tables where owner='SAPIDS'and tablename='USR02'

shows the USR02 table as well .

Any ideas ?

thanks in advance,

Evgeny

[lbreddemann · Accepted solution]

> Any ideas ?

Sure

> Evgeny

In MaxDB the DBA user (SUPERDBA) owns the SAP schema owner SAP<SID>.

But that does not imply that SUPERDBA also has access to all of SAP<SID>s objects.

In fact - and this is unlike Oracle - SUPERDBA cannot and should not access the objects in the SAP<SID> schema, unless somebody explicitly grants the respective permissions to SUPERDBA.

If you want to access the SAP schema object on DB level (something that you never should want for a billion reasons) then you've to logon as SAP<SID> or grant the permissions to the user you use for logon.

regards,

Lars

[Former Member]

Hi Lars ,

Tnanks a lot for your explanation , it is really clear now .

Can you tell me also is it any rules to be applied when I change the SAP Schema password ?

As I understand I should change it in two places : at SAP Level in DBACockpit and at MAXDB level in SQL Studio .

Is that right ?

Cheers ,

Evgeny

[lbreddemann]

> Can you tell me also is it any rules to be applied when I change the SAP Schema password ?

No, there aren't specific rules in place for that.

It's just a standard SQL password with the documented limitations.

> As I understand I should change it in two places : at SAP Level in DBACockpit and at MAXDB level in SQL Studio .

>

> Is that right ?

When you change the SAP<SID> password you'll need to adapt:

1. the XUSER logon entries (at least the DEFAULT key entry)

Be aware that this has to be done on all application servers and for all OS users from where SAP workj processes should connect to the database.

2. the logon data stored for connecttion in DBACockpit/DB59/DB50

regards,

Lars

[Former Member]

So ,based on your recommendations and notes 25591 and 39439, the actions should be in the following order :

1)Reset SAP<SAPSID> password in SQL Studio (note 25591 section 4):

1.1 Log onto the database as user SYSDBA in the SQLStudio.

2.1 Execute the following SQL command:

alter password <DBA user> <new pw>

2) Update the XUSER : (note 39439 , section 2,b)

2.1 Connect to the OS as sid<adm> (Windows)

2.2 open the console

2.3 run " xuser -U DEFAULT -u SAP<SID>,<password> -d <database_name> -n <database_server> -S SAPR3 -t 0 -I 0 set "

3) Update the Logon Data in SAP :

Transaction DBACOCKPIT/sm59/sm50

My doubts are that I am not so sure in regards of order of the point 2 and 3 .

I assume that if I update firstly the XUSER , the work processes should lose the connection to DB and be cancelled , so as I will not be able to connect to the SAP to change connection data .

In other hand , if I reset firstly the connection data from SAP , I will be able to update the XUSER after .

Regards,

Evgeny

[former_member188065]

Evgeny, it's not a bad idea to start from OS level, at least I do it like this. So my best practise is to set xuser list first. Any mechanism relying on xuser should work as designed then.