Hide / Encrypt username and password while calling Oracle Reports

If you have not configured reports with Oracle Single Sign-on, Oracle report server explicitly requires username and password when report is called using Web.Show_document(). Username and password is required in report calling URL, for example following method calls a reports using Web.Show_Documnet().

Web.Show_Document('http://domainname.com:8090/reports/rwservlet?userid=username/password@db& server=ReportsServer_1&desformat=PDF&destype=cache&report=report.rdf&paramform=yes','_blank');

In above call username and password are visible in URL, causing security problem. Oracle has provided several methods to resolve this problem, one of these solution is to define key-mapping in CGICMD.DAT file. In Reports services 11g this file is located at following location

$DOMAIN_HOME/servers/WLS_REPORTS/stage/reports/reports/configuration/cgicmd.dat

In Oracle Reports services 10g this file can be located in reports conf directory.

To define a key mapping, append following line at the end of the file

userlogin: userid=username/password@db %*

Restart reports server/Managed server, now you can call your report using following URL

Web.Show_Document('http://domainname.com:8090/reports/rwservlet?userlogin&server=ReportsServer_1&desformat=PDF&destype=cache&report=report.rdf&paramform=yes','_blank');

You can define key mapping for as many parameter as you need using following syntax,

userlogin: userid=username/password@db server=ReportsServer_1 desformat=PDF destype=cache %*

If reports have been configured with SSO, simply pass ssoconn=config parameter in reports calling URL, here config is the Resource Access Descriptor defined in OID. This parameter will automatically get login information from Oracle Internet Directory.

Another scenario can be built by through PL/SQL Code:

Create a procedure in Program Unit of Form Builder for encryption of database User ID and Password with below mentioned code:

function encrypt_logon_data return varchar2 is
l_user varchar2(200);
l_user_encrypt varchar2(300);
begin
l_user := lower ( GET_APPLICATION_PROPERTY( username ) || '/' ||
GET_APPLICATION_PROPERTY( password ) || '@' ||
GET_APPLICATION_PROPERTY( connect_string ) );
for i in 1..length( l_user ) loop
l_user_encrypt := l_user_encrypt|| '%' || LTRIM (TO_CHAR (ASCII (SUBSTR (l_user, i, 1)), 'XX'));
end loop;
return ( l_user_encrypt );
end;

Now use above procedure while calling reports. 

(For Example):

DECLARE
vrepid report_object;
vrep VARCHAR2 (100);
rep_url varchar2(2000);
BEGIN
rep_url:='LOCALHOST/reports/rwservlet?erver=Report_server&report=D:\SampleReport'
||'&desformat=pdf&destype=cache&userid='

|| encrypt_logon_data
|| '&paramform=No'
|| '&maximize=Yes' ;
web.show_document (rep_url,'_blank');
END;