Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Built in procedure of function to check correct username and password on oracle Db 10g
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
-
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 Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,457 Red Diamond
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
-
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.
-
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.
-
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
-
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;
/
-
Joerg,
Thank you, but with this approach I have to grant all users select and alter command to DBA_USERS ?? Anything more feasible.
-
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.
-
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