Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

CatherineDevlin (Programmer) (OP)
12 Dec 03 17:29
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 (Programmer)
15 Dec 03 4:07
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

Helpful Member!(3)  SantaMufasa (TechnicalUser)
15 Dec 03 14:26
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 "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)
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 (Programmer)
16 Dec 03 2:15
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 (Programmer) (OP)
17 Dec 03 14:27
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.
SantaMufasa (TechnicalUser)
17 Dec 03 14:49
Catherine,

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:

ALTER SESSION CLOSE DATABASE LINK yada;

Cheers,

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

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!

Back To Forum

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