Database: pass logged in user variable to retrieve record

[Rich Palarea]

I've managed to create a "secure" (what is secure these days!?)
application - nothing more than an Access database of user IDs, passwords
and some profile data for each user.

I can login with a form that I've created. It uses the following script to
ensure a match of username and password:

' This function checks for a username/password combination.
Function ComparePassword(UID,PWD)
' Define your variables.
Dim strSQL, objCN, objRS
' Set up your SQL string.
strSQL = "SELECT * FROM " & USERS_TABLE & " WHERE (UID='" & UID & "' AND
PWD='" & PWD & "');"
' Create a database connection object.
Set objCN = Server.CreateObject("ADODB.Connection")
' Open the database connection object.
objCN.Open "driver={Microsoft Access Driver (*.mdb)}; dbq=" &
Server.MapPath(MDB_URL) & "; uid=admin; pwd="
' Run the database query.
Set objRS = objCN.Execute(strSQL)
' Set the status to true/false for the database lookup.
ComparePassword = Not(objRS.EOF)
' Close your database objects.
Set objRS = Nothing
Set objCN = Nothing
End Function

But the main part of the app is using the Database Interface Wizard. I have
the edit.asp page that the DIW creates and I want to pass in the UID for
this session so that the record that comes back for editing belongs to the
logged in user.

How is this done (or is there an easier way than how I'm doing it)? The code
referenced above is contained in an include file that I can place in the
header of a page to redirect if not logged in with a valid account.

Thanks,
Rich

 

[MD WebsUnlimited.com]

Hi Rich,

Once the UID is validated you'd set either a cookie or a session var
indicating that the user has successfully logged in. The cookie or session
var is then checked in subsequent pages to see if the user has been
authenticated.

Rich the following is not meant to be demeaning but to enlighten and broaden
the learning experience

You mentioned that you have this function in an include that could be used
in other pages to authenticate them. But please don't do that. It would be a
large burden on the server to have to access the database each and very time
a page is accessed and if it is a shared server it would impact all websites
sharing that server. Not to mention that the visitors will be treated to
delays while the database access is being accomplished.

Also the SQL statement maybe burdensome. In your opening statement you
mentioned that you created the database with a userid, password and profile
information fields which to me implies that the database was not normalized.
It should have at least two tables the logon authenication table and the
profile table. Why? So that when you do statements like "Select * from" you
only get the needed information accessed. It also increases the overall
speed of the database as each table could be accessed by index via a primary
key of UID. Okay back to the function ComparePassword.

First problem, the select statement may require that all records in the
database be accessed and compared when the logic only requires that a single
record existence is proof of authentication "Select Top 1 UID from" meets
that requirement without accessing the entire table. Second problem, all the
fields of the table are pulled in, i.e. Select * from versus Select UID
from. If the other information is not required why move it from the database
record to the recordset buffer. While the function serves its purpose by my
why of thinking a function should be as flexible as possible. The whole
purpose of function is so that then can be reused not only from within this
application but in subsequent ones also. e.g. you have it in an include. It
could tell me if the UID does not exist, that the UID exists but the
password is not correct, or that the UID and password match. Why would this
be important? I can think of several examples: 1. That if the correct UID is
enter but not the password then after three attempts I can send them to a
page that will email them their password. 2. They have entered an invalid
UID three times so I can send them to a page that will authenticate them via
a memory jogger, eg. pet's name, maiden name, etc. then email them the UID.

Rich, again the above not personal you've done a great job at getting this
started.

To answer the second part of your question how do you interface this with
the DIW edit.asp page. First each page in the DIW uses it own static form of
authentication so you'll need to change that to utilize the session or
cookie var that you're setting in your authentication. Next, you need to
change the following statement fp_sQry select statement to use the session
or cookie stored UID and the profile table. If you need additional help let
us know.

For those that don't have the time or the desire to take on writing an
authentication system take a look at our Page Protector Pro product.
http://www.websunlimited.com/order/Product/PagePro/pagepro.htm

 

[Rich Palarea]

That is all great info -- thanks!

I used the login application that was from a MSDN article where they gave
you sample code and some steps to create an authentication page and a
user's database. Beyond that (and using data access wizards from within FP),
I have no idea what I'm doing or how to do what I want to do! So, with that
said, maybe I can start over.

The objective of my app is to create a sales automation (lite) app with the
following features:
1. Each salesperson has an account (create & edit profile)
2. Each salesperson can enter and update their own leads
3. All salespeople can see leads in the system and various notes and
status, preventing disagreements, territory disputes, etc. The system will
also allow salespeople to leverage other leads with complimentary info
(dashboard view so when they are on a call they can query data and use
phrases like "We just did this project with Company X").

The application will be the foundation for a larger extranet, since my
salesforce are all independent contractors working around the nation.

I'm very comfortable poking around in code and figuring out what different
pieces are doing. Comfortable with Access, although I'm not sure how to
optimize it for the web world. I've been building websites for about 9
years. I own JBots and other WU products.

What would you suggest I do to get this project jumpstarted and built. I run
the company, so I would rather not spend a huge amount of time re-inventing
wheels that already exist...but I don't want to pay for stuff that I can do
relatively easily.

Thanks!
Rich