Dynamic switch DB or Schema on OBIEE User Login

We have a requirement to switch between the DB’s based on the user login. We have two identical/similar databases with different connection string and different DB user/Schema name and password corresponding to that schema . But based on the users who logged in the report, the connection should switch. Also one more objective we need to fulfill here is that the password of the database schema that will be stored should be encrypted so that nobody can tamper this and obviously due to reason of security violation and it should be decrypted by the OBIEE before connect to that database schema .

To cater this reqr. I have followed the below steps :

1) Create a table “DB_DETAILS” in any db with the following details:

Column Name Data Type
=======================
LOGIN_NAME VARCHAR2 (20 Byte) 
DBNAME  VARCHAR2 (30 Byte) 
DBLOGIN  VARCHAR2 (30 Byte) 
DBPASSWORD VARCHAR2 (256 Byte) 
DBPASSWORD_ENCRYPT VARCHAR2 (256 Byte) 

2) Here I use a package called : ENCRYPTION to encrypt and decrypt the password in DB_DETAILS table

CREATE OR REPLACE PACKAGE encryption AS

  FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2;

END;
/

 CREATE OR REPLACE PACKAGE BODY         encryption AS

   – Declare the values for the key.
   — The package will be wrapped to hide the encryption key.
  
   key_string          VARCHAR2(1000)  := ’12345678′;
   encrypted_string    VARCHAR2(2048);
   decrypted_string    VARCHAR2(2048);

   – FUNCTION ENCRYPT encrypts encrypted string
      FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      encrypted_string    := null;
      dbms_obfuscation_toolkit.DESEncrypt(input_string => input_string,
                                            key_string => key_string,
                                             encrypted_string => encrypted_string );
       RETURN  encrypted_string;
   END;

   – FUNCTION DECRYPT decrypts encrypted string
      FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN

      decrypted_string    := null;
      encrypted_string    := null;
      dbms_obfuscation_toolkit.DESDecrypt( input_string    => input_string,
                                            key_string       =>key_string,
                                            decrypted_string => decrypted_string );

      RETURN  decrypted_string;
   END;

END;
/

2) Add the ‘DB_DETAILS’ table against ‘Proxy_Conn’ .

3) Add the relevant data against ‘DB_DETAILS’ table .

4) Make sure you have two database configured properly with above credentials .Update the DBPASSWORD_ENCRYPT field by running . Note that the DBPASSWORD column would have no use for this POC . This is for reference only .

UPDATE  DB_DETAILS SET  DBPASSWORD_ENCRYPT = ENCRYPTION. Encrypt(DBPASSWORD)  ;
COMMIT;

5) Create the below Session Var Initialization Block . Here I am decrypting the password and assign it to a variable called ‘dbpassword’ . 

6) Now for the connection pool for which we need dynamic switching as per the logged in users, set the below properties in connection pool of that database in physical layer:
Data Source Name: VALUEOF(NQ_SESSION.dbname)
User Name: VALUEOF(NQ_SESSION.dbuser)
Password:VALUEOF(NQ_SESSION.dbpassword)

 

7) Create the Subject Area ‘EMP_DEPT_DB’ depending on the database ‘ORBIT’, Connection pool ‘Conn_Pool’ . Assign the Physical join , Logical join properly in the conventional method.

8) Now create two users in repository as ‘deba’ and ‘rosy’ . According to that setup when you entered thorough ‘rosy ‘ you should be able to see data for ‘EMP’ and ‘DEPT’ table from database : ‘ORBIT’ while user ‘deba’ will be able to see the data from database ‘OREIPR’.Note that in both cases only one Subject area has been used but the data differs depending on logged in Users from different databases.