Skip to Content

DB connect mechanisms in Oracle and SAP – Part III

The so called OPS$ mechanism is the key the connection of SAP workprocesses to the DB in the ABAP stack. When looking into a workprocess trace file (dev_wXX), we will usually see the following:

M Wed Nov  7 20:18:03 2007
M  ***LOG Q01=>

tskh_init, WPStart (Workproc 0 1 10083) -rw-r––  1 oranw2 dba    19524 2007-11-07 20:20 nw2_ora_10100.trc<br />-rw-r–  1 oranw2 dba   148624 2007-11-07 20:20 nw2_ora_10102.trc<br />-rw-r–  1 oranw2 dba   887227 2007-11-07 20:21 nw2_ora_10090.trc<br />-rw-r–  1 oranw2 dba   200894 2007-11-07 20:21 nw2_ora_10106.trc<br />-rw-r–  1 oranw2 dba 12524597 2007-11-07 20:24 nw2_ora_10092.trc<br />-rw-r–  1 oranw2 dba  1852994 2007-11-07 20:25 nw2_ora_10094.trc<br />drwxr-xr-x  2 oranw2 dba     4096 2007-11-07 20:25 .<br />-rw-r–  1 oranw2 dba    10488 2007-11-07 20:25 nw2_ora_10211.trc</p><p>Please note that the pid that is included into the name of the trace file corresponds to the pid of the Oracle Server processes and not to the pid of the workprocess. </p><h5 style=”margin: auto 0cm”>(1) Logon on as OPS$ user</h5><p>Prerequisite: os_authent_prefix=OPS$<br />                   remote_os_authent=TRUE<br />This kind of authorization is called external authorization. This means that we rely on authentication by the operating system and a DB user with the name  OPS$<osusername> has to exist on the DB. Since a password was already entered during logon on OS level, there is no password necessary for the connection to the DB as long as the connection is done as <osusername>. Please note that this particular user must have been created with the command<br />  CREATE USER <username> IDENTIFIED EXTERNALLY;<br />In SAP environments, this user is created by the oradbusr script when the SAP system is installed.<br />When this mechanism is enabled, the workprocess can perform the following(the workprocess does not use sqlplus, but the principle is the same):</p><p>sqlplus /@NW2<br />SQL> show user<br />USER is “OPS$NW2ADM”Therefore, after these linesC  Logon as OPS$-user to get SAPNW2’s password<br />C  Connecting as /@NW2 on connection 0 (nls_hdl 0) …<br />C  Nls Characterset                 Natcharacterset              C      EnvHp      ErrHp ErrHpBatch<br />C    0                                                           1  0x38d3530  0x38e5f80  0x38e5918<br />C  Attaching to DB Server NW2 (con_hdl=0,svchp=0x38e5848,svrhp=0x38e7d28)C Wed Nov  7 20:18:05 2007<br />C  Starting user session (con_hdl=0,svchp=0x38e5848,srvhp=0x38e7d28,usrhp=0x38f0750)<br />C  Now ‘/@NW2’ is connected (con_hdl 0, nls_hdl 0).the workprocess is connected as OPS$NW2ADM </p><h5 style=”margin: auto 0cm”>(2) Get password of SAP owner from SAPUSER table</h5><p>The SAPUSER table is the only table owned by the OPS user:</p><p>SQL> select owner, table_name from dba_tables where owner LIKE ‘OPS%’;OWNER                          TABLE_NAME<br />

OPS$NW2ADM                     SAPUSER

The SAPUSER table has the following structure:SQL> desc “OPS$NW2ADM”.”SAPUSER”
 Name                                      Null?    Type


<br /> USERID                                             VARCHAR2(255)<br /> PASSWD                                             VARCHAR2(255)</p><p>Basically, the SAPUSER table only contains one entry, the password of the SAPR3 or SAP<SID> user.<br />In the SQL Trace we can now see what exactly is done by the workprocess here:</p><p>PARSING IN CURSOR #1 len=61 dep=0 uid=26 oct=3 lid=26 tim=1166499498219827 hv=932047195 ad=’681694b0′<br />SELECT USERID, PASSWD FROM SAPUSER WHERE USERID IN (:A0, :A1)<br />END OF STMT<br />PARSE #1:c=1000,e=605,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1166499498219819<br />BINDS #1:<br />kkscoacd<br /> Bind#0<br />  oacdty=01 mxl=2000(384) mxlc=00 mal=00 scl=00 pre=00<br />  oacflg=00 fl2=1000010 frm=01 csi=871 siz=4000 off=0<br />  kxsbbbfp=2a979b2270  bln=2000  avl=06  flg=05<br />  value=”SAPNW2″<br /> Bind#1<br />  oacdty=01 mxl=2000(384) mxlc=00 mal=00 scl=00 pre=00<br />  oacflg=00 fl2=1000010 frm=01 csi=871 siz=0 off=2000<br />  kxsbbbfp=2a979b2a40  bln=2000  avl=12  flg=01<br />  value=”SAPNW2-CRYPT”</p><p>I will not go into details here, it means that either the user SAPNW2 or SAPNW2-CRYPT have to be found in the SAPUSER table. Why ‘-CRYPT’? In case you use the brtools for maintaining the SAPUSER table (which is highly recommended), the password will be stored encrypted in the SAPUSER table. The relevant brconnect call is<br />   brconnect -u / -o SAPNW2 -p xyz<br />This will create the entry ‘SAPNW2-CRYPT’ in the SAPUSER table and stores xyz as encrypted password in the DB. </p><h5 style=”margin: auto 0cm”>(3) Logon as SAP owner</h5><p>C  Got SAPNW2’s password from OPS$-user<br />C  Disconnecting from connection 0 …<br />C  Closing user session (con_hdl=0,svchp=0x38e5848,usrhp=0x38f0750)<br />C  Now I’m disconnected from ORACLE<br />C  Connecting as SAPNW2/<pwd>@NW2 on connection 0 (nls_hdl 0) …</p><p>Since the password for the SAPNW2 user was retrieved from the DB, the OPS$ user can disconnect from the DB and the SAPNW2 user can logon.<br />At this point, you should be aware of the fact that manual changes to the SAPUSER table without changing the password of the SAPNW2 user with ‘alter user … identified by <password>’ would cause serious problems: The wrong password would be retrieved from the SAPUSR table and the workprocess connection attempts will fail. This is also the reason why only brconnect should be used for maintaining the SAPUSER table. </p>

Be the first to leave a comment
You must be Logged on to comment or reply to a post.