Basis and others: Note 50088 - Creating OPS$ users on Windows NT/Oracle

Key Point is to execute @ORADBUSR.SQL with a following parameter for example if you're sidadm is Doman1/ATCADM then you should follow the following command, details can be obtained from the usefull sap note below.

Steps summary from the note if you are running on Oracle 10g
stop sap system

F:\>sqlplus /nolog @oradbusr.sql SAPDAT NT DOMAINA ATC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Summary

Symptom
Creating OPS$ users on Windows NT/Oracle
Other terms
SQL*NetV2
SQL*NetV1
OPS$
ops$
Password change
Changing the password
OPS$ user
Reason and Prerequisites
OPS$ users should be created on the ORACLE database for R/3 users SAPService<SAPSID> and <SAPSID>ADM.
Solution
You can use the ORACLE OPS$ user mechanism to assign a password other than 'sap' to the user 'sapr3' in the SAP System. This prevents unauthorized access to the database, as only the operating system users of the SAP System (normally SAPService <SAPSID> and <SAPSID>ADM) have access to the password of user 'sapr3' in the database.

Furthermore, the OPS$ mechanism is required for backup planning using transaction DB13 in CCMS.

In Windows NT SQL*Net V1, this mechanism only works in central systems.
With the ORACLE network protocol SQL*Net V2, you can use the OPS$ user mechanism in both central and distributed SAP Systems. However, if you use the OPS$ in distributed systems, the following parameter must be set in the file init<SAPSID>.ora.
"remote_os_authent=TRUE" (not Oracle 8.*)

To set up SQL*Net V2, refer to Note 48736.

Contents
Ia  Creating OPS$ users (up to Release 4.0B)
Ib  Creating OPS$ users (as of Release 4.0B)
Ic  Creating OPS$ users (as of Oracle 8.1.6)
Id  Creating OPS$ users in mixed environments (as of 8.1.6)
IIa Changing the password for user 'sapr3' (< Oracle 8.1.6)
IIb Changing the password for user 'sapr3' (Oracle 8.1.6)
IIc Changing the password for user 'SAP<SID>'(> Oracle 8.1.6)

Ia  Creating OPS$ users (up to Release 4.0B)

The OPS$ user has to be created for the following users:

1. A user who logs on to the database outside of the SAP system. If you call programs which make a connection to the database via another user (for example, program "tp"), then you must create an OPS$ user for this user. In general, this is user <SAPSID>ADM.

2. A user under which the SAP System is started. You can find this specified user under "Control Panel" -> "Administrative Tools" -> "Services".

a) Select the service SAP<SAPSID>_<Instance_ID> from the service list.

b) Choose "Startup". The specified user, under which the SAP system is started, is shown in the field "Log on As".

In general, the SAP System is started under user SAPService<SAPSID>.
Note that you may need to adapt the commands given below (How to create user <SAPSID>ADM) to the user that you are using. This might be the case, for example, if you installed the latest R/3 Version by repeated upgrades starting from a very old R/3 Release.

Please proceed as follows to create both OPS$ users (sample commands for user <SAPSID>ADM and SAPSERVICE<SAPSID>):
How to create user <SAPSID>ADM

1. Stop the SAP System.

3. Start sqldba72 (or svrmgr23, svrmgr30) and log on to the database with 'connect internal'.

4. Execute the following commands in the database one after the other. Replace <SAPSID> with the corresponding SAP system ID for your system:
'create user OPS$<SAPSID>ADM default tablespace psapuser1d
temporary tablespace psaptemp identified externally;'

'grant connect, resource to OPS$<SAPSID>ADM;'

'connect /'

'create table SAPUSER
( USERID VARCHAR2(256), PASSWD VARCHAR2 (256));'

'insert into SAPUSER values ('SAPR3', '<password>');'

'connect internal'

'alter user sapr3 identified by <password>;'
How to create user SAPSERVICE<SAPSID>

1. Log on as user <SAPSID>ADM to the host on which the R/3 database for the SAP System is running.

2. Start sqldba72 (or svrmgr23, svrmgr30) and log on to the database with 'connect internal'.

3. Execute the following commands in the database one after the other: Replace <SAPSID> with the corresponding SAP system ID for your system:
'create user OPS$SAPSERVICE<SAPSID> default tablespace psapuser1d
temporary tablespace psaptemp identified externally;'

'grant connect, resource to OPS$SAPSERVICE<SAPSID>;'

'create public synonym sapuser for OPS$<SAPSID>ADM.SAPUSER;'

'connect /'

'grant select on sapuser to OPS$SAPSERVICE<SAPSID>;'

The R/3 processes can now log on with the new password after reading the password from table SAPUSER in the database.
Ib  Creating OPS$ users (as of Release 4.0B)
For the creation and change of OPS$ users SQL scripts are available as of SAP R/3 Version 4.0B under Oracle 8.0.6.
Oracle 8.0.6:  Open a DOS BOX, and change to the directory \orant\orainst.
Oracle 8.1.5: the scripts are no longer automatically copied to Orainst during the Oracle installation and must therefore be copied manually from the RDMBS CD (directory \SAP) to a writeable working directory.

Execute the following command under user <SID>ADM:

    plus80 internal @sapuser.sql        (Oracle 8.0.6)
    sqlplus internal @sapuser.sql       (Oracle 8.1.5)
You will be prompted for a new password for the database user sapr3. After successful completion of sapuser.sql the following line is displayed:

    | Script sapuser.sql successfully finished |

If this line is not displayed, check the file sapuser.log in the same directory for possible error causes and consult your SAP Support center, if necessary.
You can also use this procedure to change the password for the database user sapr3.
Ic  Creating OPS$ users (as of Oracle 8.1.6)
On Oracle 8.1.6, security for OPS$ access has been enhanced by including the domain name in the OPS$ user names.
Creating the OPS$ user is done using script oradbusr.sql.
By mistake, there are two different scripts that share the same name but with a different number of parameters. With the older script, two parameters are used. With the new script, three parameters ('schemaowner' added) are used.
The old version with two parameters is contained on the RDBMS CD, on earlier Kernel CDs, and in the installation directory. A current version has also been appended to this note.
The new version with three parameters is contained in the new installation kits. The new version must be used as of Version 6.10 and in MCOD systems as of 4.6.
As of Oracle Version 10 use the script contained in archive oradbusr10.zip.
You can find out whether you have the new script or the old script installed as follows:
Open script 'oradbusr.sql' using a text editor.
If 'oradbusrSD.sql' is listed under 'Usage', you have the new version.
If 'oradbusr.sql' is listed, you have the old version.
Execute the following command to set up the OPS$ user:

New script:
   sqlplus /NOLOG @oradbusr.sql <schemaowner> NT <userdomain>
Old script:
   sqlplus /NOLOG @oradbusr.sql NT <userdomain>

Set the domain name of the domain for <userdomain> in which the user <SID>ADM was created. This usually corresponds to the value of the environment variable USERDOMAIN if you have logged on as <SID>ADM. You can find out what the current value of the environment variable is
      set USER
in a command prompt.
For <schemaowner>, you may need to set a different value if you use the new oradbusr.sql script:
Up to Version 4.6D, use 'SAPR3' for 'schemaowner'.
As of Version 6.10, use 'SAP<SID>' for 'schemaowner'.
You can also use the environment variable to check which value you need to set for 'schemaowner':
If the environment variable was set to DBS_ORA_SCHEMA, use the value specified there (SAPR3 or SAP<SID>).
If DBS_ORA_SCHEMA was not set, use 'SAPR3'.

Now you need to set the password for user sapr3 acc. to IIb or IIc.
Id Creating OPS$ users in mixed environments (as of 8.1.6)
This section describes the creation of the OPS$ access in mixed environments, i.e. the Oracle client (R/3 Application Server) and the Oracle database are operated on different platforms (Unix / Windows).

1. Log on to the database server as user <SID>adm.

If the database server runs on a Unix computer:
    sqlplus> create user "OPS$<DOMAIN>\SAPSERVICE<SID>" identified
            externally;
    sqlplus> grant connect, resource to
            "OPS$<DOMAIN>\SAPSERVICE<SID>";
    sqlplus> connect /
    sqlplus> grant select, insert, update on sapuser to
            "OPS$<DOMAIN>\SAPSERVICE<SID>"
    sqlplus> exit

If the database server runs on a Windows computer:
    sqlplus> create user OPS$<SID>ADM identified externally;
    sqlplus> grant connect, resource to OPS$<SID>ADM;
    sqlplus> connect /
    sqlplus> grant select, insert, update on sapuser to
             OPS$<SID>ADM;

Note:
As a general rule, the user name and the domain of the Windows user, under which the SAP service was started on the NT computer, must be used. If the service is started with a local user, use the host name of the Application Server as the domain name.
Some Unix derivatives can only manage users with a user name length of 8 characters. In this case, unlike with the SAP standard, user <SID>adm was entered as service user. Likewise, user "OPS$<DOMAIN>\<SID>ADM" must be registered in the Oracle database.
The OPS$ user names for the Windows users must be delimited by double quotes, since the character string contains the "\" character. In this case, the user name must be written in uppercase letters as the case is observed when inserting character strings that are limited by quotes!
IIa Changing the password for the user 'sapr3'
If the OPS$ user has already been created as described above, the password for the 'sapr3' user can be changed at any time. As user <SAPSID>ADM, you must follow two steps (replace <new password> with the new password for the user 'sapr3'):

1. Stop the SAP System.

3. Change the entry for the user 'sapr3' in table SAPUSER.

a) Start sqldba72 (or svrmgr23, svrmgr30) and log on to the database with 'connect internal'.

4. Change the password for the 'sapr3' user the database with the following commands:
'connect internal'

'alter user sapr3 identified by <new password>;'
IIb Changing the password for user 'sapr3' (Oracle 8.1.6)

To do this, call BRCONNECT as follows:

  brconnect -u system/<syst_pwd> -f chpass -o sapr3 -p <new_sap_pwd>

You do not need to carry out the steps under IIa.
IIc Changing the password for user 'schemaowner>' (as of Oracle 8.1.7)
Applies to MCOD systems or installations with kernel version > 4.6D

  brconnect -u system/<syst_pwd> -f chpass -o sap<sid> -p <new_sap_pwd>

You do not need to carry out the steps under IIa.

Note 50088 - Creating OPS$ users on Windows NT/Oracle

No comments: