Help with setting user preferences for logging in...

We are switching to ColdFusion for all of our websites for medical studies where I work. I am in the process of learning ColdFusion, but have not had a chance to take the class on the more advanced topics as of yet, so I appreciate any help that you can give.

I am trying to create a login system where the user can only see records based on their CenterID. For example, user MCV can only see records where the CenterID = 4001. I know (or hope. ) that the SQL would be something like...

SELECT *
FROM table
WHERE CenterID = '4001'

The database consists of many tables (around eighty so far) that contain data for many patient forms that exist. I want to be able to have the user go from form to form and only see their own patients. I just don't know what or how to do this yet, do I use session variables, do I use <cflogin>, do I pull my hair out and run screaming?

I have four other centers besides MCV that I have to be able to do the above things for. If I need to put this in the application.cfm file, how should I go about coding something like this?

how do you know which rows in a table a user is allowed to see?

or are you saying that there's a different table for each user?

clearly, the user-data relationship has to be recorded in the database itself somehow

you would require the user to log in, and then you would use the session variable for userid in the query

Yes, I'd look into CFLOGIN to handle the login functionality. There is an example in the docs: http://livedocs.macromedia.com/cold...cs/appsec27.htm

And yes, you'd keep up with the user's centerID as a session variable. In the login code just add a query and set the centerID like <cfset session.centerID = myQuery.centerID />. Session variables are also covered in the docs.

And both of these are covered extensively in Ben Forta's CF books.

We are in the process of trying to correct some bad database design, but at the moment each table contains the centerID for each center. I also have ben forta's web app construction kit...but it's currently farmed out to another friend of mine who I think I convinced to switch to CF at his job as well. If it's ok with you, can I post my code in here to be checked out? I know there will probably be a lot of errors to start, but I want to make sure that it is right...and I figure this might be the best way to learn more.

In general I'm happy to look at small pieces of code if you're having a specific problem. Unfortunately, I probably won't have time to read through and test larger blocks of code. Others may have more time to look at it, but if you post a bunch of code don't be too surprised if folks are too busy to go through it all and respond.

I completely understand, people have lives and they have better things to do than review the code for my entire website. No worries. My main problem will be the session variable part of the application page, so that is really the only thing I am concerned with. I may post the whole page of code, but I just want to make sure that I am coding the session variables correctly. I appreciate any help that you can give.

Pardon any errors below. What I am trying to do is set sessions and their preferences for each of the centers and the investigators. There are five centers that are restricted to their own patients' data, and then two investigators that can see everything. Could you look at the code between the <cflock> tags and let me know if I am even REMOTELY on the right track with this?

<cfapplication name="febseauth" sessionmanagement="Yes" loginStorage="Session">

<cflock scope="Session"
timeout="10" type ="Exclusive">
<cfif not IsDefined("session.MCV")>
<cfset session.MCV = "">
SELECT *
FROM LoginInfo
WHERE CenterID = '4001'
</cfif>
<cfif not IsDefined("session.MONTE")>
<cfset session.MONTE = "">
SELECT *
FROM LoginInfo
WHERE CenterID = '4020'
</cfif>
<cfif not IsDefined("session.DUKE")>
<cfset session.DUKE = "">
SELECT *
FROM LoginInfo
WHERE CenterID = '4001'
</cfif>
<cfif not IsDefined("session.CHIL")>
<cfset session.CHIL = "">
SELECT *
FROM LoginInfo
WHERE CenterID = '4004'
</cfif>
<cfif not IsDefined("session.EVMS")>
<cfset session.EVMS = "">
SELECT *
FROM LoginInfo
WHERE CenterID = '4007'
</cfif>
<cfif not IsDefined("session.MCV")>
<cfset session.MCV = "">
SELECT *
FROM LoginInfo
WHERE CenterID = '4001'
</cfif>
<cfif not IsDefined("session.SHINNAR")>
<cfset session.SHINNAR = "">
SELECT *
FROM LoginInfo
ORDER BY CenterID
</cfif>
<cfif not IsDefined("session.IEC")>
<cfset session.IEC = "">
SELECT *
FROM LoginInfo
ORDER BY CenterID
</cfif>
</cflock>

<cfif IsDefined("Form.logout")>
<cflogout>
</cfif>

<cflogin>
<cfif NOT IsDefined("cflogin")>
<cfinclude template="loginform.cfm">
<cfabort>
<cfelse>
<cfif cflogin.name IS "" OR cflogin.password IS "">
<cfoutput>
<H2>You must enter text in both the User Name and Password fields</H2>
</cfoutput>
<cfinclude template="loginform.cfm">
<cfabort>
<cfelse>
<cfquery name="loginQuery" dataSource="febse">
SELECT UserID, Roles
FROM LoginInfo
WHERE
UserID = '#cflogin.name#'
AND Password = '#cflogin.password#'
</cfquery>
<cfif loginQuery.Roles NEQ "">
<cfloginuser name="#cflogin.name#" Password = "#cflogin.password#"
roles="#loginQuery.Roles#">
<cfelse>
<cfoutput>
<H2>Your login information is not valid.<br>
Please Try again</H2>
</cfoutput>
<cfinclude template="loginform.cfm">
<cfabort>
</cfif>
</cfif>
</cfif>
</cflogin>


<cfif GetAuthUser() NEQ "">
<cfoutput>
<form action="MyApp/index.cfm" method="Post">
<input type="submit" Name="Logout" value="Logout">
</form>
</cfoutput>
</cfif>