validate a password within PL/SQL? 3

[CatherineDevlin]

Hi! I'm looking for a way to validate a password within PL/SQL. I want to write

CREATE PROCEDURE change_password(old_password IN VARCHAR2)
IS
BEGIN
-- check if old_password is correct... but how?

I can get the hashed value of the password from DBA_USERS, of course, but is there a way to hash old_password to see if it matches? (I wouldn't be surprised if Oracle doesn't supply access to its one-way password hashing algorithm... too useful for a password cracker...)

I can't actually try a CONNECT statement from within PL/SQL, right? And even if I could, that would kill my current connection, right? That's no good...

Of course, because the user logged in successfully, they obviously had the correct password at one point. But what if they logged in, left their desk, and now somebody else is trying to change their password? Limiting idle_time in the user's profile reduces the risk of this, but it's also really annoying, especially if the time is short enough to protect every stroll to the coffeepot.

The PASSWORD command in SQL*Plus prompts for old password, but I'm trying to put this in a procedure that can be called from a GUI.

OK, here's an idea! I can create a dummy user identified by the supplied old_password, then SELECT PASSWORD FROM DBA_USERS to see if the hashed password of the dummy user matches the hashed password of the application user... nope, didn't work! Apparently the algorithm doesn't have a simple 1 clear-text-password: 1 hashed-password mapping; each username/password combination gets a different result.

As you can see, I'm running out of ideas. Can anyone help?

Thanks very much!
- Catherine

 

[sem]

What kind of front-end application do you use? In most cases this issue is resolve by storing password on the client side. E.g. in Forms is can be accessed from GET_APPLICATION_PROPERTY built-in.
As for generic server-side solution, I think that seting idle time is the only way, though without return

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Catherine,

Following is a procedure, "CHANGE_PASSWORD", that I wrote to simulate your situation. I infer from the name, "CHANGE_PASSWORD", that you want to change passwords for a user, but I wasn't sure. Also, I was not sure "who" was changing the password. In any case, I wrote the procedure to validate a password without disconnecting and without getting into "idle time" issues.

My procedure relies upon creating a DATABASE LINK to validate the user's password. Since I didn't know the name of the "host string" that you use in your tnsnames.ora, I couldn't hard code it, but you can, if you wish, thus removing "host_service" as a procedure argument. If you are creating this procedure for use for the changing user's own password, then you can also remove procedure-argument #2, "usernm", from the code. If you the procedure is for creating a new password (as I presume it is), then you will want to add an argument for "new_password". Also, I placed "screen display stubs" in the procedure code to represent "success" or "failure" of the procedure; you'll probably want to remove the stubs, replacing them with what you actually want the procedure to do.

As my Tek-Tipster buddy, Carp, pointed out to me, for this code to work, it is not good enough for the compiling user to be a (member of the role) DBA; you must log in as SYS and explicitly &quot;GRANT CREATE DATABASE LINK TO <compiling_username>;&quot; (...seems like an Oracle bug to me, but that is another issue).

In any case, here is the code, along with proof-of-concept executions:

CREATE or replace PROCEDURE change_password
	(host_service in varchar2
	,usernm varchar2
	,old_password IN VARCHAR2) 
IS
	drop_db_link	boolean;
	bad_uname_pw	boolean;
	hold_username	varchar2(100);
	bad_username_pw	exception;
	pragma exception_init(bad_username_pw,-1017);
BEGIN
	drop_db_link	:= false;
	declare
		dummy	char(1);
	begin
		select 'x' into dummy
			from user_db_links
			where db_link like '%TESTPW%';
		drop_db_link	:= true;
	exception
		when no_data_found then
			drop_db_link := false;
	end;
	if drop_db_link then
		execute immediate 'drop database link testpw';
	end if;
	execute immediate 'create database link testpw connect to '||
		usernm||' identified by '||old_password||' using '''||
		host_service||'''';
	execute immediate
		'select username from user_users@testpw' into hold_username;
	dbms_output.put_line('Username/password combination validated. '||
				'Do what you need to do.');
	exception
		when bad_username_pw then
			bad_uname_pw	:= true;
			dbms_output.put_line('Error: Bad Username or password.');
end;
/

Procedure created.

SQL> exec change_Password('dhunt','test','test')
Username/password combination validated. Do what you need to do.

PL/SQL procedure successfully completed.

SQL> exec change_Password('dhunt','test','yada')
Error: Bad Username or password.

PL/SQL procedure successfully completed.

Let me know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:22 (15Dec03) GMT, 12:22 (15Dec03) Mountain Time)

 

[sem]

Great! Another way (plagiarized from Dave's, of course ) is to create Java Stored Procedure that accepts username/password and tries to open connection. Drawback - java involved, advantage - no need in DDL.

Regards, Dima

http://seminihin.narod.ru

 

[CatherineDevlin]

Wow! Musafa, I am amazed! Thank you!

Belive it or not, I had actually thought about this technique, too. But apparently I did something wrong on my own attempt, and because the approach seemed far-fetched, I lost courage when I got error messages and figured it was just too weird to work. But you showed me it isn't.

The one thing I'd add is that I was sometimes getting &quot;ORA-02018: database link of same name has an open connection&quot; when I used the procedure repeatedly. Adding a COMMIT at the end of the procedure stopped that.

 

[SantaMufasa]

Catherine,

I am really pleased that it worked for you!

Another way to deal with the &quot;ORA-02018: database link of same name has an open connection&quot; error is to issue the following command:

ALTER SESSION CLOSE DATABASE LINK yada;

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:46 (17Dec03) GMT, 12:46 (17Dec03) Mountain Time)