?
Solved

Oracle username and password validation

Posted on 2005-02-28
9
Medium Priority
?
1,699 Views
Last Modified: 2012-05-05
Hi

I am trying to develop an ASP.net app which runs against an Oracle database. I have a package which has all of my procedures in it and I'm trying to write a procedure which can check a given username/password against the Oracle ones. The proc takes arguments of username and password and I'm trying to validate them. I suspect I will need to use DBA_USERS table to do this, but when I try and code SELECT statments in the proc I get "table or view does not exist" for dba_users. I tried giving my user SELECT_CATALOG_ROLE (briefly) and it made no difference.

Does anybody else do anything like this? Can anyone give an guidance please?

Thanks for your help
0
Comment
Question by:maran_software
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:neo9414
ID: 13421078
Well with select_catalog_role you should be able to access DBA_USERS table. Not sure why this is happening. Anyways even if you do get access to this table how will you verify the password??? The password is stored in encrypted form which cannot be decoded.
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13421105
I think the way to do this will be
1. check if the user exists in DBA_USERS (or ALL_USERS) table.
2. if yes, then try to make connection to Oracle database with the given user and password. If connection succeeds then the password is verified else incorrect password.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13421108
the bad news:  The DBA level roles are reserved for DBA accounts.

Now the really bad news:  I don't believe there is a way to validate a text string against an oracle encrypted password.

I suggest you create your own users table and validate against that (you can easily encrypt the password scring using SHA1 or MD5).  Then the .Net app always connects to the DB using it's own username.

You might also look into windows based authentication for this app.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Expert Comment

by:neo9414
ID: 13421173
Thats lot of bad news... :-)
as slightwv mentions create your own users table and store the encrypted password
You can use DBMS_OBFUSCATION package provided by oracle to do teh encryption/decryption.
0
 

Author Comment

by:maran_software
ID: 13421331
I still have the problem of validating existing users. We do have a USERS table that has a password column (empty at the moment), if I wanted to use that I would still need to get the Oracle password wouldn't I?
0
 
LVL 9

Accepted Solution

by:
neo9414 earned 300 total points
ID: 13421365
yeah you would. To get around this you can create an oracle user with readonly permission your user table. Let your application connect through this user and check for the username and password supplied at the log on page. If they match then drop the existing connection and create another connection with the supplied username/password.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 75 total points
ID: 13421379
Not really.  If you want to use neo's approach, then you can create another DB connection and check the return code.

You only need an Oracle username/password to connect to the database.  If the app is set up to connect as a specific user and you want the app to perform the authentication using something like forms-base authentication, then the app controls encryption of the password.

Check out:
      FormsAuthentication.HashPasswordForStoringInConfigFile
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13429486
You can not check Oracle passwords because they are not written in the database.
The string you see as password is not the password.
It is the string '12345678' encrypted using the password as an encryption key.
Only the user knows the key, i.e. the password.
To read DBA_USERS the sys user should grant you select on this particular view.
0
 

Author Comment

by:maran_software
ID: 13481761
Thanks for your input, I understand authentication a bit better now. I used neo's suggestion in the end but also slightvw suggestion of FormsAuthentication.HashPasswordForStoringInConfigFile.

Cheers
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question