I would steer clear of statements in the form
SELECT INTO name ....
because this is a CREATE RECORD construct under Postgres.
I recommend using a statement in the form
SELECT taccessflag FROM name WHERE (name.tusername = username
AND name.tpassword = password)
This will return a record only if your user name and password match.
After that you can determine if variable "taccessflag" contains the
required accesscode to grant access.
Please be aware that access control of this type must be maintained by
you for as long as your application is alive. Alternatively, PAM & LDAP
offer general-purpose access control mechanisms that exist outside
applications, yet can be customized to provide specific access rights
for each application.
HTH, Hendrik schaink
Cirez Communications, inc. wrote:
> I am trying to write a series of functions in PL/PGSQL to handle among
> other things user login. The way the application is written right now,
> when the user provides a username/password pair, the program retrieves
> the user_name, password, access_flag, etc... If the user/pass combo
> supplied matches the one on the table, then the login process goes
> through. If on the other hand only the user name is valid, a message
> is displayed to let the user know the login failed as well as
> initializing a login attempt counter (invisible to the user). If booth
> user/pass are invalid, then a simple error messahe is displayed. I was
> thinking it would make more sense to write a function to return only
> the columns that would be used in any instance, rather than returning
> everything and having the program do the checking--to be honest, I am
> not sure which way is more cost effective. Anyways, here is the
> pseudo-code for one of these functions...
>
> CREATE OR REPLACE FUNCTION userlogin(username varchar, password
> varchar) RETURNS TYPE_TYPE AS $RETURN_TYPE$
> DECLARE
> name TYPE_TYPE;
> BEGIN
> SELECT INTO name (tusername, tpassword, taccessflag) FROM table_name
> WHERE (tusername=username);
> IF ((name.tusername == username) && (name.tpassword == password)) THEN
> RETURN name;
> ELSE IF ((name.tusername == username) && (name.tpassword != password)) THEN
> RETURN (name.tusername, name.taccessflag);
> ELSE
> RETURN NULL;
> END IF;
> END;
> $RETURN_TYPE$ language plpgsql;
--
Hendrik M. Schaink
Chief Consultant
"Integrated Business Solutions & Dependable Service"
InfoVision Consulting
Calgary, Alberta, Canada
Phone: (403) 239-0099
"The Vision: We are the partners of choice for companies and
organizations that share our commitment to creating a world
that is truly wise, courageous, prosperous, innovative,
inclusive, sustainable and humane." --Ruben Nelson
GPG Fingerprint: 1371 0927 8C3C 831F A838 C312 68BC F5DB 010D F3D7
_______________________________________________
clug-talk mailing list
[email protected]
http://clug.ca/mailman/listinfo/clug-talk_clug.ca
Mailing List Guidelines (http://clug.ca/ml_guidelines.php)
**Please remove these lines when replying
