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

3562141

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?

Joerg.Sobottka

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

Timo Hahn

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

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

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

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

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

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

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

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