Oracle User Security and Connectivity options #sapadmin
For many SAP on Oracle administrators, the steps involved in checking or modifying the OP$-users to ensure your SAP systems connect to your database have become almost automatic, even for the people like me who have only just stopped thinking of of BR*Tools as some new fangled tool of the devil. But I’ve been ‘seeing other databases’ for the last few years, and while I was away, there have been a few changes and simplifications in how both Oracle and SAP handle Oracle users, passwords and the connection between SAP and Oracle.
The simplest way to change an Oracle Password….
If you need to set or reset a password (i.e. to solve an ORA-2800x issue), you could use BRCONNECT,
brconnect -u / -f chpass -o <SAP schema user>
but this may only provide you a temporary fix (if it helps at all)….
With Oracle 11g, passwords of database users with the DEFAULT user profile expire after 180 days (PASSWORD_LIFE_TIME=180). Every database user has a user profile that is assigned when the user is created or altered. The lifetime of the user password (PASSWORD_LIFE_TIME) is one property of a user profile. Additionally, as of Oracle 10g, the limit for FAILED_LOGIN_ATTEMPTS for the DEFAULT user profile is 10, whereas in prior releases, the default was UNLIMITED. As a consequence, the SAP application account can get automatically locked after 10 failed login attempts. Apart from simple typos by yours truly, these failures have causes as simple as a scheduled job trying to log into the SAP database with an invalid password, or as dangerous as someone guessing passwords. Whatever the cause, once the account is locked, the SAP Work Processes can no longer log on to the database.
Obviously, if the database user profile is not configured correctly (i.e. SAP schema users SAPSR3 and / or SAPSR3DB have user profiles, with PASSWORD_LIFE_TIME=180), then this can also cause password lockout (after too many attempts on day 181).
You can check which user profile is assigned via the following SQL
select username, profile from dba_users where username in ('SAPSR3', 'SAPSR3DB');
Read the latest version of SAP note 1519872 – SAP Database User Profile SAPUPROF for details on how to create your own Oracle user profile, and instructions on Manual Configuration of User Profile SAPUPROF and some Security Recommendations.
And more change to come (unless you are already doing them, in which case, share your experiences in the comments)
I said “it’s complicated”…
Don’t forget that as of Release 11g, Oracle have announced that remote connect by OPS$ (using the TNS alias name) will no longer be supported by future Oracle versions. SAP’s reaction, with the implementation of Kernel 7.20 (11/2011) as a downward-compatible kernel, is “Secure Storage in File System” (SSFS), where an encrypted password for the SAP database user is no longer stored in the database, but in the file system. This is available in all 7.x systems (as of SAP 7.00). For what it’s worth, this feature is also available on SAP NW systems installed on SYBASE ASE.
For backwards compatibility, the historical connect method will be supported up to Version 11.2 for all SAP systems that have Oracle. On the other hand, as of Kernel 7.20, all SAP systems that use Oracle versions after 11g, will uprate se the new new method only;
SAP note 1639578 – SSFS as password storage for primary database connect describes the general steps that are required to use the “Secure Storage in File System” for AS ABAP, and
SAP note 1622837 – New connect method of AS ABAP to Oracle via SSFS goes into the specifics for Oracle.
Home work ? HOMEWORK ????
Given my absence from Oracle Administration, I’m sure that I have missed out some important implications, glossed over something else, and completely ignored a show stopper or two. If that’s the case, please use the comments to tell me.
You have will be doing me a HUGE favor !!!