Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

validate a password within PL/SQL?

validate a password within PL/SQL?

validate a password within PL/SQL?

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)
  -- 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

RE: validate a password within PL/SQL?

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

RE: validate a password within PL/SQL?


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 "GRANT CREATE DATABASE LINK TO <compiling_username>;" (...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)
    drop_db_link    boolean;
    bad_uname_pw    boolean;
    hold_username    varchar2(100);
    bad_username_pw    exception;
    pragma exception_init(bad_username_pw,-1017);
    drop_db_link    := false;
        dummy    char(1);
        select 'x' into dummy
            from user_db_links
            where db_link like '%TESTPW%';
        drop_db_link    := true;
        when no_data_found then
            drop_db_link := false;
    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 '''||
    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.');
        when bad_username_pw then
            bad_uname_pw    := true;
            dbms_output.put_line('Error: Bad Username or password.');

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.

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

RE: validate a password within PL/SQL?

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

RE: validate a password within PL/SQL?

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 "ORA-02018: database link of same name has an open connection" when I used the procedure repeatedly.  Adding a COMMIT at the end of the procedure stopped that.

RE: validate a password within PL/SQL?


I am really pleased that it worked for you!

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



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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close