Home » SQL & PL/SQL » SQL & PL/SQL » password value
password value [message #197187] Tue, 10 October 2006 07:49 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
does DBMS_OBFUSCATION_TOOLKIT value changes when we export the database and import?


I have a stored proc which stores password in a table by DBMS_OBFUSCATION_TOOLKIT.

We have a validation check thru front end.If that password value matches it logs in.


But when i exported the db ,the password field has same hash value,but could not login?



Re: password value [message #197192 is a reply to message #197187] Tue, 10 October 2006 08:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did you by any chance import into a database with a different characterset to the one that you exported from.

You can check this by looking at the top of the log file for the import.
Re: password value [message #197197 is a reply to message #197192] Tue, 10 October 2006 08:16 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
I called command "NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG" to set the NLS LANG correctly

I did backup with: exp system/ipdc@ipdc_db1
owner=(IPDC,IPDC_APPL,IPDC_PROC) buffer=1000000 compress=n log=export.log file=bsm.dmp

while importing


imp system/ipdc@ipdc_db1 file=bsm.dmp log=import.log commit=y fromuser=ipdc,ipdc_appl,ipdc_proc touser=ipdc,ipdc_appl,ipdc_proc

Re: password value [message #197199 is a reply to message #197197] Tue, 10 October 2006 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Was there a warning at the top of the log file about Language conversion?

Also, what datatype have you stored this password in?

And, have you encrypted it or done a MD5 hash on it?
Re: password value [message #197201 is a reply to message #197199] Tue, 10 October 2006 08:20 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
i did MD5 HASH on the input string using dbms_obfuscation_toolkit
Re: password value [message #197202 is a reply to message #197187] Tue, 10 October 2006 08:23 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
procedure verifyLogin(i_accountTypeEnum in varchar2, i_accountLogin in varchar2, i_password in varchar2, o_rowCount out integer, o_ModifDate out date, o_NofLoginFailures out integer, o_accountStatusEnum out varchar2, o_AccountStatusInfo out varchar2, o_RegistrationDate out date, o_ExpiryDate out date, o_IsDeleted out integer, o_ModifUser out varchar2)
is
passwordHashVal varchar2(64) := null;
begin
passwordHashVal := ipdc_common.getHash(i_password);
select count(*) into o_rowCount from Account where AccountTypeEnum= i_accountTypeEnum and
PasswordHashValue=passwordHashVal and AccountLogin=i_accountLogin and isDeleted=0;
if(o_rowCount > 0) then
select ModifDate,NofLoginFailures, AccountStatusEnum, AccountStatusInfo, RegistrationDate, ExpiryDate, IsDeleted ,ModifUser into
o_ModifDate, o_NofLoginFailures, o_accountStatusEnum,o_AccountStatusInfo,o_RegistrationDate, o_ExpiryDate,o_isDeleted, o_ModifUser
from Account where AccountTypeEnum= i_accountTypeEnum and
PasswordHashValue=passwordHashVal and AccountLogin=i_accountLogin and isDeleted=0;
end if;
end verifyLogin;



function ipdc_common.getHash(p_password IN VARCHAR2)
return VARCHAR2 AS
begin
return DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => (p_password));
end;


Re: password value [message #197208 is a reply to message #197187] Tue, 10 October 2006 08:39 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
The same works before in source db,when i import it is not working.

Table has passwordhashvalue as same in source and after export

Re: password value [message #197215 is a reply to message #197208] Tue, 10 October 2006 09:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So when you manually do a MD5 hash on the import database, does it return the same value as on the old database
eg, does
SELECT ipdc_common.getHash('Fishflaps') from dual;
return the same value on both databases?
Re: password value [message #197219 is a reply to message #197187] Tue, 10 October 2006 10:06 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Yes,it returns the same value .

Passwordhashvalue is a varchar2 column
Re: password value [message #197221 is a reply to message #197219] Tue, 10 October 2006 10:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So you're saying that the function that creates the hash values returns identical values on the two databases, AND that the stored hash values in the two databases are the same, but somehow when you compare the generated hash with the stored hash on the Import database, it doesn't work.

Is that the gist of it?
Re: password value [message #197222 is a reply to message #197187] Tue, 10 October 2006 10:18 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
yes
Re: password value [message #197293 is a reply to message #197187] Tue, 10 October 2006 22:09 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
the passwordhashvalue in the table account for user ipdc is


oWs5m. before export and also the same after import(shows same value after import also).But i could not login through frontend.


passwordhashvalue is varchar2(64) .

When i manually do this ,i am able to log in

update account set passwordhashvalue=(select BSM_3_1_5_ipdc_common.getHash('ipdc') from dual) where accountlogin='ipdc';

Why is this happening?Eventhough the value is same it could not validate and login?How should i resolve this?


Re: password value [message #197344 is a reply to message #197293] Wed, 11 October 2006 00:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Add some debug code to your login code and see exactly where it is failing.
2) If you can create a little stand alone example that shows the failure, we can see if we can reproduce it.

Were the export and import databases on the same version of Oracle?
Previous Topic: Can we create index on view?
Next Topic: Please Help Me
Goto Forum:
  


Current Time: Wed Dec 07 02:49:17 CST 2016

Total time taken to generate the page: 0.24356 seconds