Re: validate password within PL/SQL?

arktikturtle_at_correct_the_spelling.yahoo.com wrote:

> 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
> http://profiles.yahoo.com/arcticturtle
>
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net

There may be no need for it; any user is allowed to change his own password with "alter user <current_user> identified by <new_password>"

SQL> create user demo identified by demo default tablespace users; User created.

SQL> grant create session to demo;
Grant succeeded.

SQL> connect demo/demo_at_o920
Connected.

SQL> alter user demo identified by nemo; User altered.

SQL> connect demo/nemo_at_o920
Connected.

So, create your procedure with invoker's rights and change the password - as you mention, the user is logged on, so has to know his/her password.

The obvious risk is someone else is actually changing the password, while the user strolled off, leaving the application open.
I'll leave it to you to shoot those endusers ;-)

-- 
Regards, Frank van Bortel