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;
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’;
— FUNCTION ENCRYPT encrypts encrypted string
FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS
encrypted_string := null;
dbms_obfuscation_toolkit.DESEncrypt(input_string => input_string,
key_string => key_string,
encrypted_string => encrypted_string );
— FUNCTION DECRYPT decrypts encrypted string
FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS
decrypted_string := null;
encrypted_string := null;
dbms_obfuscation_toolkit.DESDecrypt( input_string => input_string,
decrypted_string => decrypted_string );
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) ;
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)
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.