Re: Oracle database validating password

<martha123456_at_hotmail.co.uk> wrote in message news:1147624523.244430.47820_at_i40g2000cwc.googlegroups.com...
> Hi, this is my first post to this group.
>
> I have a webservice that receives a username and password for an Oracle
> database over https (eg. scott/tiger). I communicate with the database
> using a different username and password from my webservice code (eg.
> webservice001/ijHHGuy762) and there is only one connection. I want to
> validate the scott/tiger pair passed in the webservice with Oracle
> using the connection I made with webservice001/ijHHGuy762 and see
> whether it is valid before returning table information. I want to do it
> like this as I do not want to have a separate connection for every user
> and connecting as a new user each time is very slow.
>
> Without connecting to Oracle as scott/tiger, is there a
> package/procedure in Oracle I can call connected as
> webservice001/ijHHGuy762 to verify scott/tiger is valid.
>
> I am running Windows 2003 Web Edition and Oracle XE.
>
> Thank you
> Martha Smythe-Harrop
>

Create your own authentication mechanism. Something like this:

create table myusers(
userid number check(userid > 0),
username varchar100,
password varchar 100);

function is_authenticated_user(username myusers.username%type, password myusers.username%type)
return myusers.userid%type
is
v_userid myusers.userid%type;
begin
/* Authenticate user and return the userid, else, return 0 */ /* ... */
return v_userid;
end;
/

You should also have a pool of connections ready to service requests, and have that pool grow or shrink as necessary. Having only one connection to the database when there are, say, 100 users accessing your application server can get really slooooow.

I take it you're using .NET? Create a class called ConnectionPool, for example, and use that class to meter out already established connections. Also, the ConnectionPool class should handle increasing or decreasing connections as necessary.

-- 

Andreas Sheriff
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"

DO NOT REPLY TO THIS EMAIL
Reply only to the group.