[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1716
  • Last Modified:

Oracle username and password validation

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
maran_software
Asked:
maran_software
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
neo9414Commented:
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
 
neo9414Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
neo9414Commented:
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
 
maran_softwareAuthor Commented:
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
 
neo9414Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
schwertnerCommented:
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
 
maran_softwareAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now