This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 26, 2012 11:47 PM by fkukin RSS

Testing username/password within a procedure

fkukin Explorer
Currently Being Moderated
Hello,

Is it possible to test if a username/password are valid within a procedure ?

For example :
- I connect as hr/hr@db1
- call a procedure with as inputs a username and a password and try to connect with the given usr/pwd (like a CONN under sqlplus) ?

Regards
Kukin Frane
  • 1. Re: Testing username/password within a procedure
    ascheffer Expert
    Currently Being Moderated
    And how, from what environment would you call this procedure?
    And what would you do with outcome of this procedure? Login as the the other user?
  • 2. Re: Testing username/password within a procedure
    EdStevens Guru
    Currently Being Moderated
    fkukin wrote:
    Hello,

    Is it possible to test if a username/password are valid within a procedure ?

    For example :
    - I connect as hr/hr@db1
    - call a procedure with as inputs a username and a password and try to connect with the given usr/pwd (like a CONN under sqlplus) ?

    Regards
    Kukin Frane
    Shouldn't be hard to test for yourself .....

    And supposing you got it to work, what would you do with it? Sounds like you are trying to implement an ill-conceived solution to some unknown (to us) problem ...
  • 3. Re: Testing username/password within a procedure
    fkukin Explorer
    Currently Being Moderated
    I have an ADF application that connects with ONE user to a 11g database(hr for example)

    once connected with the hr user, it calls a procedure that will test if the user1/pwd1 are valid within the same db.
    test_login
    (i_user => user1
    ,i_pwd => pwd1)

    the output would be:
    - username not valid
    - password not valid
    - account locked
    - account expired etc....

    most of the output , I can find them with the dba_users/dba_profiles view,
    but how to test if the password is correct ?
  • 4. Re: Testing username/password within a procedure
    ascheffer Expert
    Currently Being Moderated
    If you know the user and the password, the best way is to login as that user, and not as another user.
  • 5. Re: Testing username/password within a procedure
    fkukin Explorer
    Currently Being Moderated
    EdStevens wrote:
    fkukin wrote:
    Hello,

    Is it possible to test if a username/password are valid within a procedure ?

    For example :
    - I connect as hr/hr@db1
    - call a procedure with as inputs a username and a password and try to connect with the given usr/pwd (like a CONN under sqlplus) ?

    Regards
    Kukin Frane
    Shouldn't be hard to test for yourself .....

    And supposing you got it to work, what would you do with it? Sounds like you are trying to implement an ill-conceived solution to some unknown (to us) problem ...
    I though the same => should be no big deal.
    But I cannot find the right syntaxe to test if a user/pwd are valid from a procedure !

    The idea behind that, it's to have one pl sql proc that tests if usr/pwd are valid with the same db
  • 6. Re: Testing username/password within a procedure
    fkukin Explorer
    Currently Being Moderated
    ascheffer wrote:
    If you know the user and the password, the best way is to login as that user, and not as another user.
    That's the solution that I will take, if I cannot get this one to work
  • 7. Re: Testing username/password within a procedure
    padders Pro
    Currently Being Moderated
    The most straightforward way to implement this would be to generate a password hash for the username/password passed in and compare that hash with the stored password hash visible in the DBA_USERS view for that username.

    The password hash is fairly straightforward to generate using the DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT function as seen in the CRACK function in the download at the below link.

    http://www.petefinnigan.com/oracle_password_cracker.htm

    Be aware though that this creates a.) a security risk in exposing the password hashes stored in DBA_USERS and b.) a maintenance risk since the method of encryption used by Oracle changes in 11g to SHA1.
  • 8. Re: Testing username/password within a procedure
    fkukin Explorer
    Currently Being Moderated
    padders wrote:
    The most straightforward way to implement this would be to generate a password hash for the username/password passed in and compare that hash with the stored password hash visible in the DBA_USERS view for that username.

    The password hash is fairly straightforward to generate using the DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT function as seen in the CRACK function in the download at the below link.

    http://www.petefinnigan.com/oracle_password_cracker.htm

    Be aware though that this creates a.) a security risk in exposing the password hashes stored in DBA_USERS and b.) a maintenance risk since the method of encryption used by Oracle changes in 11g to SHA1.
    I had a same idea of concept at the beginning, but as you said some maintenance risk can happen
    when an upgrade of the db is done.
    I slowly think that the test of the usr/pwd must be done outside of a session.
  • 9. Re: Testing username/password within a procedure
    Gaff Journeyer
    Currently Being Moderated
    Use that one application database user that you have but use a context to differentiate application users from each other. Store the application usernames with the password hashes. When the user logs in, hash the password provided and check it against the hash (don't store the actual password anywhere). If the user is valid then set the userid value in an application context. Depending on who the validated user is, you can set any other name/value pairs in the context that you'll need in the application. That way you can enforce constraints etc. via the value of the context attributes.

    See this for more:

    http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adgsec02.htm
  • 10. Re: Testing username/password within a procedure
    rp0428 Guru
    Currently Being Moderated
    >
    Is it possible to test if a username/password are valid within a procedure ?

    For example :
    - I connect as hr/hr@db1
    - call a procedure with as inputs a username and a password and try to connect with the given usr/pwd (like a CONN under sqlplus) ?
    >
    Sure - just write a Java stored procedure that uses the internal JDBC driver to try to connect and have the procedure return the result.

    See the example in Chap 7 'Server-Side Internal Driver' of the JDBC Developer's Guide
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/ssid.htm

    That example shows how to write the Java code but you need to put it in a Java Stored Procedure. For that see the Java Developer's Guide
    http://docs.oracle.com/cd/B28359_01/java.111/b31225/chfive.htm
  • 11. Re: Testing username/password within a procedure
    rp0428 Guru
    Currently Being Moderated
    >
    The most straightforward way to implement this would be to generate a password hash for the username/password passed in and compare that hash with the stored password hash visible in the DBA_USERS view for that username.
    >
    I prefer to just use a Java stored procedure that tries to connect using the internal JDBC driver.
    1. No messing with hashes
    2. No need for access to the DBA_USERS view
    3. No maintenance issue
    4. Still a security risk for brute force attempts to 'guess' the password.
  • 12. Re: Testing username/password within a procedure
    fkukin Explorer
    Currently Being Moderated
    rp0428 wrote:
    >
    The most straightforward way to implement this would be to generate a password hash for the username/password passed in and compare that hash with the stored password hash visible in the DBA_USERS view for that username.
    >
    I prefer to just use a Java stored procedure that tries to connect using the internal JDBC driver.
    1. No messing with hashes
    2. No need for access to the DBA_USERS view
    3. No maintenance issue
    4. Still a security risk for brute force attempts to 'guess' the password.
    Rp0428, so far I end up with the same solution as yours, I have tested it outside
    Of the db, before to implement it as a db java store procedure. Sad that this mechanism is not available
    In a pl sql pkg.
  • 13. Re: Testing username/password within a procedure
    sb92075 Guru
    Currently Being Moderated
    fkukin wrote:
    rp0428 wrote:
    >
    The most straightforward way to implement this would be to generate a password hash for the username/password passed in and compare that hash with the stored password hash visible in the DBA_USERS view for that username.
    >
    I prefer to just use a Java stored procedure that tries to connect using the internal JDBC driver.
    1. No messing with hashes
    2. No need for access to the DBA_USERS view
    3. No maintenance issue
    4. Still a security risk for brute force attempts to 'guess' the password.
    Rp0428, so far I end up with the same solution as yours, I have tested it outside
    Of the db, before to implement it as a db java store procedure. Sad that this mechanism is not available
    In a pl sql pkg.
    SQL is different from PL/SQL which is different from SQL*Plus.

    PL/SQL simply has no concept of "connecting" to the DB since by the time it gets invoked it is already running inside the DB as a validated user.

    logging in using username/password is how client initiates a session in the DB.
  • 14. Re: Testing username/password within a procedure
    fkukin Explorer
    Currently Being Moderated
    sb92075 wrote:
    fkukin wrote:
    rp0428 wrote:
    >
    The most straightforward way to implement this would be to generate a password hash for the username/password passed in and compare that hash with the stored password hash visible in the DBA_USERS view for that username.
    >
    I prefer to just use a Java stored procedure that tries to connect using the internal JDBC driver.
    1. No messing with hashes
    2. No need for access to the DBA_USERS view
    3. No maintenance issue
    4. Still a security risk for brute force attempts to 'guess' the password.
    Rp0428, so far I end up with the same solution as yours, I have tested it outside
    Of the db, before to implement it as a db java store procedure. Sad that this mechanism is not available
    In a pl sql pkg.
    SQL is different from PL/SQL which is different from SQL*Plus.

    PL/SQL simply has no concept of "connecting" to the DB since by the time it gets invoked it is already running inside the DB as a validated user.

    logging in using username/password is how client initiates a session in the DB.
    Yes I absolutely agree with you, but I am surprised that Oracle doesn't provide a function/proc to test a username/pwd validity
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points