Forum Stats

  • 3,826,224 Users
  • 2,260,608 Discussions
  • 7,896,835 Comments

Discussions

Built in procedure of function to check correct username and password on oracle Db 10g

3562141
3562141 Member Posts: 4
edited Feb 16, 2018 9:45AM in PHP

Is there any procedure or function in Oracle 10g which can help to check username and password from application code (php or .net), not like LOGON procedure?

Best Answer

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Feb 15, 2018 9:49AM Answer ✓
    3562141 wrote:Joerg,Thank you, but with this approach I have to grant all users select and alter command to DBA_USERS ?? Anything more feasible.

    Hi,
    I don't understand why you think you need to grant all users select on DBA_USERS?

    EITHER you connect from php or .net with the credentials you have for this user (and if you connect, the password is ok),

    OR you connect with a user (I call him AppOwner) and this AppOwner User needs the rights to alter other users and selects from DBA_USERS.

    You must have been logged on to the database to execute any function to prove the password...

    Please provide more details about your configuration and what you want to do at which time (workflow of the part where you want to check for the password).

    Regards

    Joerg

Answers

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,457 Red Diamond
    edited Feb 14, 2018 8:46AM

    User, your question is not clear. Are you using APEX and want to know this regarding a requirement you want to solve in APEX?

    Or do you talk about a totally different product?

    Provide more information about the use case, please.

    We are then able to determine the right space for your question.

    Timo

  • 3562141
    3562141 Member Posts: 4
    edited Feb 14, 2018 10:54AM

    Timo,

    I want to use current users and password which are part of oracle database. In Oracle Forms there is LOGON built in procedure where you pass the arguments (;user, pass) and procedure return value if they are correct. What I want is smth like LOGON procedure to use in PHP code to check if entered username and password are correct by checking in Oracle DB users. I am using Oracle DB 10g to build an application based on PHP. I want to use same credentials to authenticate users in this php applicaiton, credentials are user database and passwords from dba_users table. I hope I was clear.

    ***Moderator action (Timo): moved from to ***

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Feb 14, 2018 10:51AM

    Why do you need a database procedure for that? Use the credentials to connect to the database (from PHP). If that succeeds you don't need a procedure/function to check the credentials because you already know they are valid.

  • 3562141
    3562141 Member Posts: 4
    edited Feb 15, 2018 2:02AM

    I need because I want to use existing users and their credentials to authenticate in application. I do not want to make another table of users. Thnx

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Feb 15, 2018 3:07AM

    There is no build in, you need to create your own function, like mentionend in (Can I check username/password in PL/SQL? - Oracle: Oracle release - 9i and earlier - Tek-Tips ) this should work also in 10g (not in 11g/12c as there is another and larger column to select in dba_users.

    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;

    /

  • 3562141
    3562141 Member Posts: 4
    edited Feb 15, 2018 4:49AM

    Joerg,

    Thank you, but with this approach I have to grant all users select and alter command to DBA_USERS ?? Anything more feasible.

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Feb 15, 2018 5:00AM

    Who needs another table of users????

    You are not connected to the database because you are running a PHP program.

    And than you want to connect to the database using a "admin/dba/some other user" to verify that some credentials you have are a belonging to database user using some database procedure? Right, or do I not understand your problem?

    Why not simply use those credentials to connect to the database. If you can connect the are valid, no need for others functions or checks. If you can't connect they are not valid. Simple, no need for others tables, no need for functions, just try to connect to the database.

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Feb 15, 2018 9:49AM Answer ✓
    3562141 wrote:Joerg,Thank you, but with this approach I have to grant all users select and alter command to DBA_USERS ?? Anything more feasible.

    Hi,
    I don't understand why you think you need to grant all users select on DBA_USERS?

    EITHER you connect from php or .net with the credentials you have for this user (and if you connect, the password is ok),

    OR you connect with a user (I call him AppOwner) and this AppOwner User needs the rights to alter other users and selects from DBA_USERS.

    You must have been logged on to the database to execute any function to prove the password...

    Please provide more details about your configuration and what you want to do at which time (workflow of the part where you want to check for the password).

    Regards

    Joerg

This discussion has been closed.