Optimizing OEBS Security

Terminology & Background:-

Encryption​

• Encrypting is a two way function. A key is used to Encrypt the data and same key or combination is used to decrypt the mangled string.​
• It’s reversible, you can decrypt the mangled string to get original string if you have the key.​

Hashing ​

• Hashing is a one way function. A secure hash algorithm is applied to get a unique hash value for a string.​
• It’s irreversible and even if someone knows the algorithm they cannot  get the original string back.​
• The most someone can do is to generate “a collision”, that is, finding a different string that provides the same hash.​

– Hashing is the industry wide standard for storing passwords.​

Vulnerability & Impact​:-

Glaring Security Hole ​

• The fundamental issue is that the Oracle Application’s application account passwords are stored in the database encrypted, using the APPS database password as the encryption key, rather than using a strong, one-way hash algorithm.​

Impact​

• This means that all user password in OEBS are up for grabs.​
• Any person with DB access can know and thus login as any user in system.​
• Even read-only users can find APPS schema password, thus making them super users.​
• This means there is no security at all whatsoever. As any user can login as any user.​

Most Oracle Applications 11i and R12 implementations are vulnerable to this security weakness because of the encryption of passwords.​

A user with DB access may be able to circumvent all application controls by accessing any application account or obtain the APPS database account password.​

Issue is big with the convergence of ​

• An inherent architectural weakness in the application. ​
• Generally accepted insecure operational procedures for ad-hoc query access and cloning.​
• Multiple examples of effective, easy to execute exploit code for decrypting application passwords.

Businesses have the duty to protect users’ passwords, mainly because most users use the same password over and over again, thus exposing them to a greater risk by leaking their passwords.​

Exploiting Security Vulnerability​:-

Oracle Applications 11i  and R12 stores passwords in two tables: ​

1. FND_USER​ – The FND_USER table stores application user account passwords​.

The APPLSYS.FND_USER table contains all the application accounts. There are two password columns in this table: ENCRYPTED_FOUNDATION_PASSWORD and ENCRYPTED_USER_PASSWORD.​

Column​	Value​	Encryption Key​
ENCRYPTED_FOUNDATION_PASSWORD	APPS Password​	Username/password​
ENCRYPTED_USER_PASSWORD​	User password​	APPS Password​

These two columns provide for a two-way encryption of the passwords ​

• If you know a username and password, you can get the APPS password = ENCRYPTED_FOUNDATION_PASSWORD ​
• If you know the APPS password, you can get any user’s password = ENCRYPTED_USER_PASSWORD ​

2. FND_ORACLE_USERID -The FND_ORACLE_USERID table stores internal Oracle Applications database account passwords.​

The APPLSYS.FND_ORACLE_USERID table contains all the Oracle Applications related database accounts – there is one database account for each Oracle Applications module (i.e., GL = General Ledger). ​

The application needs access to these database schemas to perform various functions, thus it must have access to the database account password. All the passwords in the FND_ORACLE_USERID table are encrypted using the APPS password as the key.​

Column	Value​	Encryption Key​
ENCRYPTED_ORACLE_PASSWORD	Database account password ​	APPS password ​

Both tables use the same encryption algorithm to protect the passwords. ​

ENCRYPTION ALGORITHM​

Most references to the encryption algorithm point to either:​

• Java class “oracle.apps.fnd.security.WebSessionManagerProc”.​
• PL/SQL package APPS.FND_WEB_SEC​.
  • This internally calls the Java code​

For decryption and encryption, the following calls are made – ​

• APPS.FND_WEB_SEC  ​=>
  • oracle.apps.fnd.security.WebSessionManagerProc ​=>
    • oracle.apps.fnd.security.AolSecurity  ​=>
      • oracle.apps.fnd.security.AolSecurityPrivate ​

• The actual encryption and decryption routines are in the “oracle.apps.fnd.security.AolSecurityPrivate” Java class.​

Steps for Exploiting Security Vulnerability​

Step 1: Get encrypted password for any known user:

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from fnd_user where USER_NAME=’GUEST’;  

ENCRYPTED_FOUNDATION_PASSWORD  ​

—————————————————————————  ​

ZHE************BE6194CC ​

Step 2: Decrypt APPS password:

String foundationPwd = WebSessionManagerProc.decrypt(guestPwd, encFoundPwdGuest);​

String foundationPwd = WebSessionManagerProc.decrypt(“GUEST/GUEST”, “ZHE****BE6194CC”);​

–This gives APPS password in plain text.​

Step 3: Get encrypted password for any user​:

SQL> select ENCRYPTED_USER_PASSWORD from fnd_user where USER_NAME=‘SYSADMIN’;  ​

ENCRYPTED_USER_PASSWORD  ​

—————————————————————————  ​

ZH4************D3E938​

Step 4: Decrypt user password:​

String foundationPwd = WebSessionManagerProc.decrypt(foundationPwd, encUserPwd);​

String foundationPwd = WebSessionManagerProc.decrypt(“APPS_PWD”, “ZH4****D3E938”);​

–This gives user password in plain text.​

Additionally:​

• GUEST user’s password can be obtained from:​
• fnd_vault.get(‘FND’, ‘GUEST_USER_PWD’)​
• It is not needed to have GUEST password, APPS password can be decrypted with any known user password.​
• WebSessionManagerProc.decrypt(userName+”/”+userPwd, encUserFoundPwd)​
• Where encUserFoundPwd is the value of ENCRYPTED_FOUNDATION_PASSWORD column from FND_USER for the user row.​

Securing OEBS Logins​

• To fix this security hole Oracle has released below patch to move passwords onto a hashing scheme which is irreversible and thus passwords can never be recovered.​
  • Oracle note – “R12: New Feature: Enhance Security With Non-Reversible Hash Password (Doc ID 457166.1)” can used to implement the new feature.​
  • Main steps​
    • Apply necessary patches​
    • Use AFPASSWD to move into hashing scheme​

Common Mistakes​

CLONED DATABASES​-

In almost all implementations, the production database is cloned to test, development, and/or training databases on a periodic basis.​

• Data is not scrambled between Prod and Test/Dev.​
  • This means that developers have access to all data in Prod.​
  • Prod is just as secure as the weakest link, i.e. Dev instance.​

• User account passwords are not changed.​
  • When the APPS password is known, it is trivial to decrypt all the account passwords. And If the passwords have not been changed during the cloning process, then a developer is able to obtain all users’ passwords for the production instance.​
• Database schema passwords are not changed​
  • Using FND_ORACLE_USERID table a developer will be able to obtain these passwords for the production database.​

SQL ACCESS TO PRODUCTION DATABASE​-

• It is common for query access to the production database to be provided to a set of users or IT staff for troubleshooting and ad-hoc reporting.​
• Due to the complexity of the Oracle Applications data model and DBA time constraints, usually access is granted with SELECT ANY TABLE privilege rather than a narrow set of individual table grants.​

Securing OEBS​

General​

• CHANGE GUEST ACCOUNT PASSWORD ​
• CHANGE PASSWORDS FOR ALL SEEDED ORACLE APPLICATIONS ACCOUNTS ​
• CHANGE PASSWORDS FOR ALL DATABASE ACCOUNTS [CRITICAL] ​
• CREATE ALL NEW APPLICATION ACCOUNTS WITH STRONG PASSWORDS​

SQL Access​

• LIMIT ACCESS TO FND_USER AND FND_ORACLE_USERID ​

Cloned Databases​

• CHANGE ALL APPLICATION ACCOUNT PASSWORDS DURING CLONING [CRITICAL] ​
• CHANGE THE GUEST ACCOUNT PASSWORD DURING CLONING ​
• CHANGE ALL DATABASE ACCOUNT PASSWORDS DURING CLONING [CRITICAL] ​
• SCRAMBLE DATA DURING CLONNING​

Steps for – Improve granularity of data security​

Step 1: Create a function to evaluate criteria:

Ex.: Create a function to allow only APPS, APPLSYS, APPLSYSPUB users.​

CREATE OR REPLACE FUNCTION XX_EVAL(v_schema IN VARCHAR2,  v_objname IN VARCHAR2)  ​

  RETURN VARCHAR2 AS​

  con VARCHAR2(200);  ​

BEGIN  ​

  IF SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) NOT IN ( ‘APPS’,’APPLSYS’,’APPLSYSPUB’) THEN  ​

    con := ‘USER_NAME IS  NULL’;​

  END IF;​

  RETURN con;​

END XX_EVAL;​

Step 2: Add the policy​

BEGIN  ​

  DBMS_RLS.ADD_POLICY (object_schema         => ‘APPLSYS’,  ​

                    object_name           => ‘fnd_user’,  ​

                    policy_name           => ‘fnd_user_xx_eval’,  ​

                    function_schema       => ‘XX_APPS’,  ​

                    policy_function       => ‘xx_eval’,  ​

                    sec_relevant_cols     => ‘encrypted_foundation_password,encrypted_user_password’,​

                    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);  ​

END;