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.

Business model is not necessarily a star

is true that the business model must be only in the shape of stars - can have the shape of flakes. The advantage of the model in the form of flakes is the automatic construction of hierarchies for dimensions including all levels and keys. The disadvantage is the complexity / complexity of the Business Model that contains multiple tables, columns, and links. How to do it 1 / structures will import data from database to the physical layer. Fill in the missing keys and links. Create a business model in which the physical layer-copy. 2 / When you mark a table that has a direct link with the facts from the table menu, select "Create Dimension ' 3 / result is an automatically generated hierarchy, including all levels of Erik Eckhardt.