Re: Validating alternative username/password on Oracle DB

I did this - and it works well. Oracle will not tell you how the encrytion works - but there is a sneaky way around the encryption algorithm. It does, however, rely on the fact that all your application (Oracle) user names are UPPER CASE. This is how it works:

Take the username and password (e.g. FRED/ABC123). Create a lower-case or mixed-case version of the username (e.g. I would use 'FreD'). Use dynamic SQL to create a user with this username and password (e.g. FreD/ABC123). Read the encrypted password from dba_users. Drop the user. Now you have the encrypted password!

This works across Oracle 7 and 8. The password is not case-sensitive, so ABC123 is the same as Abc123. The username is case-sensitive, so Fred is not the same as fred is not the same as fReD - all can exist at the same time.

It sounds like there is a large overhead on this, but we have used it very successfuly with hundreds of people logging on and using the WEB server. I have enabled per-procedure validation and check the username and password each time a procedure is called. (You can cheat and write a one-session cookie to the user's browser so show they have logged on - then you don't need to check the password again if the cookie is there - but ensure the cookie isn't used as a means to switch between different systems by logging onto one then changing the URL to get onto another).

I hope this helps.

JJ

Mike Jones <Mike_at_mikejones.fsnet.co.uk> wrote in message news:01bf2179$66d72b40$ae95883e_at_mikejones.fsnet.co.uk...
> I'm currently woking on a project to connect an Oracle DB to an Intranet.
>
> The intranet sever connects in via an ODBC DSN and so has a username and
> password specified for it. However any one accessing the page should have
a
> normal Oracle log in to the DB and I need to validate this so that the web
> page can decide what Info to give the user.
>
> Due to the newness of all this (I've never done web based stuff before) we
> are trying to limit the web page to only issuing SELECT statements.
>
> As such I was going to create a stored function that took in a
> username/password and returned true or fale depending on wether that was
> allowed. This seems sensible to me.
>
> However, although I can get the encryted password from DBA_USERS I don't
> know how I can encrypt the password given to check against the one in
> DBA_USERS. I though I might be able to create a new user with the same
> password given and then check it that way, but oracle seems to use the
> usrename in the encryption algorithm and so I'm stuffed there too.
>
> Can anyone help me?
>
> mijones_at_hof.co.uk
Received on Mon Nov 01 1999 - 23:26:05 CET