Can I check username/password in PL/SQL? 2

[ChrisHunt]

I'm looking for a way to programatically authenticate whether a password is valid for a particular oracle account, without actually logging on as that user (or, maybe, by logging in as the user within an autonomous transaction). Basically something like this:

FUNCTION valid_user (username IN VARCHAR2,
                     password IN VARCHAR2) RETURN BOOLEAN;

It's going to be done inside a package owned by a highly privileged user, so it can involve whatever commands you like.

And no, I'm not gonna use it to crack passwords...

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ChrisHunt]

Answering my own question, I did a search of this forum and came up (eventually) with thread759-728921 . It uses a pretty devious method, which I've tweaked and adapted as follows:

   FUNCTION valid_user (pstrUsername IN VARCHAR2,
                        pstrPassword IN VARCHAR2,
                        pstrDatabase IN VARCHAR2 := 'XXX.WORLD') RETURN BOOLEAN IS
      lstrLink   VARCHAR2(15);
      lnumDummy  NUMBER;
      lblnAnswer BOOLEAN;
      xBadUserPw EXCEPTION;
      PRAGMA EXCEPTION_INIT(xBadUserPw,-1017);
   BEGIN
      -- Generate a random name for the DB Link
      lstrLink := 'TEMP_'||dbms_random.string('x',10);

      -- Create the database link
      EXECUTE IMMEDIATE 'CREATE DATABASE LINK '||lstrLink||
                       ' CONNECT TO '||pstrUsername||
                       ' IDENTIFIED BY '||pstrPassword||
                       ' USING '''||pstrDatabase||'''';

      BEGIN
         -- Try using the link...
         EXECUTE IMMEDIATE 'SELECT 99 FROM user_users@'||lstrLink
                           INTO lnumDummy;
         lblnAnswer := TRUE;     -- ... it worked!
      EXCEPTION
         WHEN xBadUserPw THEN
            lblnAnswer := FALSE; -- ... it didn't
      END;

      -- Drop the link
      EXECUTE IMMEDIATE 'DROP DATABASE LINK '||lstrLink;

      RETURN lblnAnswer;
   END;

I need to get "CREATE DATABASE LINK" granted to the package owner before I can test this, but I think (hope) it should work.

I'm open to other ideas on how to do this though...

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

Chris,

Here is an alternate method to check password validity. [And like your code, above, even if someone wants to hack passwords, this code does not tell you what the password is (unless one already knows the password)...it simply confirms what the password isn't.]

First (as you also mentioned earlier), this function depends upon use by a "highly privileged user", specifically one that has, at least, the "ALTER USER" privilege. (In your coded example, the use must have the "CREATE DATABASE LINK" privilege for success.) And, (as our brilliant Tek-Tips colleague, Sem (Dima), reminded me within the last hour):

...roles are disabled in stored procedures (and functions)

Therefore, the using user must have (in your case, the "CREATE DATABASE LINK" privilege and) in my case, the "ALTER USER" privilege from an explicit GRANT. This means that despite the using user being a member of the DBA role, the user must also have explicit use of the "ALTER USER" privilege. To make this happen, you can:

SQL> connect system/<password>@<tns alias>
SQL> grant ALTER USER to <using user>;
SQL> -- or, in your earlier case: [i]grant CREATE DATABASE LINK to <using user>;[/i]

Then, connect as the user that you want to own the user-defined function that checks for password validity, and compile the password-checking function:

****************************************************************************************
create or replace function check_password
    (user_in varchar2, password_in varchar2)
    return varchar2
is
    hold_existing_pw_value	varchar2(30);
    hold_check_pw_value         varchar2(30);
    sql_stm                     varchar2(500);
    pragma autonomous_transaction;
begin
    begin
        select password 
            into hold_existing_pw_value
            from dba_users
            where username=upper(user_in);
    exception
        when no_data_found then
            return 'User ['||user_in||'] does not exist.';
    end;
    sql_stm := 'alter user '||user_in||' identified by '||password_in;
    execute immediate sql_stm;
    select password 
        into hold_check_pw_value
        from dba_users
        where username=upper(user_in);
    sql_stm := 'alter user '||user_in||' identified by values '||hold_existing_pw_value;
    if hold_existing_pw_value = hold_check_pw_value then
        return '['||password_in||'] is a valid password for user ['||user_in||'].';
    else
        return '['||password_in||'] is an invalid password for user ['||user_in||'].';
    end if;
end;
/

Function created.

You may also want to create a PUBLIC synonym for any function that multiple users will access so that they do not need to qualify the function name with a schema name:

conn system/<password>@<tns alias>

create public synonym check_password for <owning_schema>.check_password;

Synonym created.

Then, we can test the function.

Test 1 -- Use by non-privileged user:

SQL> conn fred/fred
Connected.
SQL> select check_password('test','test') from dual;
select check_password('test','test') from dual
       *
ERROR at line 1:
ORA-01031: insufficient privileges

Test 2 -- Use by privileged user for valid username/password:

conn dhunt/<password>

col x heading "User/Password Check" format a50
select check_password('test','test') x from dual;

User/Password Check
-------------------------------------------
[test] is a valid password for user [test].

Test 3 -- ...Valid user, bad password:

select check_password('test','blah') x from dual;

User/Password Check
----------------------------------------------
[blah] is an invalid password for user [test].

Test 4 -- ...Invalid user:

select check_password('blah','blah') x from dual;

User/Password Check
---------------------------
User [blah] does not exist.

Let us know if this is an interesting alternative.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Dave, I find that procedure to be quite dangerous: what if it fails after the first statement? Sure, the probability is quite low, but the consequences... IMHO creating db_link is more safe.

Regards, Dima

http://seminihin.narod.ru

 

[sem]

To be more precise in matter, not in quote, you should add viwes along with functions.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

C'mon, Dima, aren't you a risk taker? Don't you enjoy living on the edge? And anyway...If the function fails (as you pointed out), then this is a multi-purpose function: it automatically creates new passwords for people...

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Regards, Dima

http://seminihin.narod.ru

 

[ChrisHunt]

Well, the DB Link approach isn't working for me - it doesn't seem to like creating a link from a database to itself. Mind you, I can't see the tnsnames.ora file on the database server, so I'm not 100% sure that I'm using the database name used in there.

So, here's my version of Dave's second approach:

FUNCTION valid_user (pstrUsername IN VARCHAR2,
                     pstrPassword IN VARCHAR2) RETURN BOOLEAN IS
   CURSOR curPass IS
      SELECT password
      FROM   dba_users
      WHERE  username = UPPER(pstrUsername);

   lstrCurrent   dba_users.password%TYPE;
   lstrCompare   dba_users.password%TYPE;
   lblnValid     BOOLEAN;
BEGIN
   OPEN curPass;
   FETCH curPass INTO lstrCurrent;
   lblnValid := curPass%FOUND;
   CLOSE curPass;
      
   IF lblnValid THEN
      EXECUTE IMMEDIATE 'ALTER USER '||pstrUsername||
                       ' IDENTIFIED BY '||pstrPassword;
                          
      OPEN curPass;
      FETCH curPass INTO lstrCompare;
      CLOSE curPass;

      EXECUTE IMMEDIATE 'ALTER USER '||pstrUsername||
                       ' IDENTIFIED BY VALUES '''||lstrCurrent||'''';

      lblnValid := lstrCurrent = lstrCompare;
   END IF;

   RETURN lblnValid;
END;

It doesn't seem to like selecting from [tt]dba_users[/tt] inside a function (the same user can select from it in a simple SELECT statement) - I suppose I'll need to explicitly grant access to that view (as well as ALTER USER privilege) for the user in question.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

That is correct, Chris. You must explicitly "GRANT ALTER USER TO <user_running_the_check_password_function>;", else you will receive an "insufficient privileges" error.

Let us know your outcomes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ChrisHunt]

I knew about granting ALTER USER, but a bit surprised about having to GRANT SELECT ON SYS.DBA_USERS. I can't log in as SYS, so that's causing me some trouble.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

Chris,

Under "normal" circumstances, if a DBA grants to the using user this privilege:

grant select_catalog_role to <using_user>;

...then that should be plenty to access any data dictionary view. I don't know, however, whether your implementation is subject to the "role disabling" that occurs in a stored procedure/function. Try it and see, then let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

What's wrong with creating loopback db_link? I'm sure that you use incorrect USING clause (tns alias) or your local (to database) sqlnet.ora/tnsnames.ora are wrong. Where did you look for tnsnames.ora? Did you check TNSADMIN variable (it points to that location to override defaults)? What OS do you use?

Regards, Dima

http://seminihin.narod.ru

 

[ChrisHunt]

What's wrong with creating loopback db_link?

I can't get it to work.

I'm sure that you use incorrect USING clause (tns alias) or your local (to database) sqlnet.ora/tnsnames.ora are wrong.

You're probably right.

Where did you look for tnsnames.ora?

I don't have a sign-on for the database server (I'm using TOAD on a PC), so I can't look for it.

Did you check TNSADMIN variable (it points to that location to override defaults)?

No, but I don't thnk it will do me any good.

What OS do you use?

It's on a unix box.

Oh, and select_catalog_role doesn't help either.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[sem]

Try to use the result of SELECT INSTANCE_NAME FROM V$INSTANCE as a value for host_service in Dave's procedure. If it fails you may create a full tns connect string, assuming your instance name is the same and the listener is on standard port.

Regards, Dima

http://seminihin.narod.ru

 

[ChrisHunt]

That did it! It seems I tried every possibility for the instance name apart from the right one. I've encapsulated the working code (with a couple of further refinements) into a package...

CREATE OR REPLACE PACKAGE auth AS

  /*
   * VALID_USER
   *
   * Authenticates an oracle username and password
   *
   * Input values : pstrUsername - user to authenticate
   *                pstrPassword - user's (supposed) password
   *                pstrInstance - Name of the instance (from tnsnames.ora)
   *                               if omitted, the current instance will be used
   *
   * Return value : TRUE if a valid username & password, FALSE otherwise
   */

   FUNCTION valid_user (pstrUsername IN VARCHAR2,
                        pstrPassword IN VARCHAR2,
                        pstrInstance IN VARCHAR2 := NULL) RETURN BOOLEAN;
  
END auth;
/
CREATE OR REPLACE PACKAGE BODY auth AS

  /*
   * Globals and Constants
   */

   -- Location of database
   gstrInstance  VARCHAR2(30);

  /*
   * Local Procedures
   */

   PROCEDURE initialise IS
      CURSOR curInstance IS
         SELECT instance_name
         FROM   v$instance;
   BEGIN
      OPEN curInstance;
      FETCH curInstance INTO gstrInstance;
      CLOSE curInstance;
   END;

  /*
   * Public Procedures
   */
   
   FUNCTION valid_user (pstrUsername IN VARCHAR2,
                        pstrPassword IN VARCHAR2,
                        pstrInstance IN VARCHAR2 := NULL) RETURN BOOLEAN IS
      lstrLink   VARCHAR2(100);
      lnumDummy  NUMBER;
      lblnAnswer BOOLEAN;
      xBadUserPw EXCEPTION;
      PRAGMA EXCEPTION_INIT(xBadUserPw,-1017);
      xInvUser   EXCEPTION;
      PRAGMA EXCEPTION_INIT(xInvUser,-987);
   BEGIN
      IF pstrUsername IS NULL
      OR pstrPassword IS NULL THEN
         RETURN FALSE;
      END IF;

      -- Generate a random name for the DB Link
      lstrLink := 'TEMP_'||dbms_random.string('x',10);

      -- Create the database link
      EXECUTE IMMEDIATE 'CREATE DATABASE LINK '||lstrLink||
                       ' CONNECT TO '||pstrUsername||
                       ' IDENTIFIED BY '||pstrPassword||
                       ' USING '''||NVL(pstrInstance,gstrInstance)||'''';

      BEGIN
         -- Try using the link...
         EXECUTE IMMEDIATE 'SELECT 99 FROM user_users@'||lstrLink
                           INTO lnumDummy;
         lblnAnswer := TRUE;     -- ... it worked!
      EXCEPTION
         WHEN xBadUserPw THEN
            lblnAnswer := FALSE; -- ... it didn't
      END;

      -- Drop the link
      EXECUTE IMMEDIATE 'DROP DATABASE LINK '||lstrLink;

      RETURN lblnAnswer;
   EXCEPTION
      WHEN xInvUser THEN  -- Creation of link failed
         RETURN FALSE;
   END; 

BEGIN
   initialise;   
END auth;
/

The owner of this package must be explicitly granted CREATE DATABASE LINK privilege, and SELECT on v$instance. If (like me) you can't get access to the V$ view, you'll have to hard-code the value of cstrInstance, or determine it in some other way specific to your own circumstances.

It can be executed by any user that you grant execute permission, but be careful lest it gets used for dictionary-based password guessing attacks...

Stars all round guys, thanks for your help.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Thargy]

Gents,

I defer to all of your previous posts, but I think you've wandered away from the requirement, which was to authenticate a user, without logging on.

I read with some horror the various possible modes of failure of highly privileged accounts etc. I have achieved secure authentication in the following way:-

I have an account with known hard-coded password and user name, e.g. tharg/tharg. This account has no access to the database, and can only execute an authentication function in another secure and restricted account. The application logs in (via tharg/tharg), and when the user enters name and password, calls the authentication routine. The authentication is against the hash value of the username and password. This either returns a single-finger salute or matches the stored hash and generates a pseudo-random string. The privileged account then sets the user's account password to that string.

The application then logs in using the just-received guaranteed good pseudo-random password.

This fulfils the original requirement. Effectively, the password is valid for that session, and that session only.
Also, the user never knows their own password, preventing tinkering via SQL Plus or Access etc. Further niceties are possible, such as automatically setting the password to a different random string when the user logs off.
Note that even if the happy hacker learns about the tharg/tharg account it does them no good.

I hope this helps.

Regards

Tharg

 

[sem]

Tharg, the technique you suggest is good (and in fact is used) for web applications to avoid opening/closing real database connections, that may hit performance, but IMHO has nothing to do with the original task to check Oracle password

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Dima,

I stand corrected. You are quite right, it's to authenticate the Oracle password, not the user.

Much egg on face.

Regards Tharg