External Table Authentication - Encrypted Password

I am trying to blog on this from long time now i have some time to write a blog :) how to store the encrypted passwords in database for External Database Authentication

Step1 : Create a function in Database by executing below code.

create or replace FUNCTION obiee_pass(p_login IN VARCHAR2,p_password IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN LTrim(To_Char(Dbms_Utility.get_hash_value(Upper(p_login)|| '/' || p_password, 10000,Power(2,30)),RPad('X',29,'X') || 'X')); END;

Step2 : create a table with columns Login,Password,Loglevel,Group

we have to insert the values to the above table obiee_users

with the following statement
insert into OBIEE_USERS values('User3',obiee_pass('User3','User3'),'Group2',2);

Step3: In rpd we need to configure Click at step7 i..e. Initialization Block we need to change with following SQL CODE

SELECT USER_NAME,LOGLEVEL,GROUP_NAME FROM OBIEE_USERS where
USER_NAME=':USER' NQS_PASSWORD_CLAUSE(and password= obiee_pass(':USER',':PASSWORD'))NQS_PASSWORD_CLAUSE

Step4: Login with users you will able to login to dashboards normally as before and apply the security as usual