ColdFusion with Access and Active Directory

We have a ColdFusion page that updates an Access database. I was asked to make provisions to provide the user name to each record that was updated. Maybe a user name field that automatically fills in, when a record is updated?

Can I do this using my existing log in method? (IIS with Active Directory)
Who is Participating?
IF GetAuthUser() persists, then you don't need a session variable.  Just remember, GetAuthUser() may be blank if anonymous users can access the app. So you might want to handle things differently.

BUT I wouldn't make those values form fields because you don't want anyone to be able to tamper with them, like spoofing the user id or changing the timestamp. As long as  GetAuthUser() persists, just use it directly in your query:

 UPDATEBY =  <cfqueryparam value="#GetAuthUser()#" cfsqltype="cf_sql_varchar">
,  UPDATEDT =  <cfqueryparam value="#now()#" cfsqltype="CF_SQL_TIMESTAMP">cfsqltype="CF_SQL_TIMESTAMP">
You can't do it from Access side, but you can try to write CGI.AUTH_USER (full user name with domain)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

(no points...)

         provide the user name to each record that was updated

Storing an 'ID" would be better - that is IF your current login method provides one. Reason being if the username changes, you won't be able to associate all of the changes for that user.  

With db table based login systems I create a [user] table:

         UserID | UserName | FirstName | LastName
         1  | jsmith | John | Smith

All key tables have "UpdatedBy" fields that store the "ID" and timestamp of who last updated the records:

               MyTableID | SomeColumn  | UpdatedBy | UpdatedDate
               15 | xxxx data xxxxx |  1 | {date and timestamp  }

To find out who last updated a record OR which records a particular user updated I use a JOIN

             SELECT   t.MyTableID, t.SomeColumn, ...other columns
                            , u.FirstName +' '+ u.LastName AS UpdatedByUser
                            , u.UpdatedDate
             FROM     MyTable t INNER JOIN user u ON u.userID = t.UpdatedBy
             WHERE  .... conditions ....
DJPr0Author Commented:
Does anyone have a  code sample to obtain the user name and put it in the table with a time stamp?

Should I only use ColdFusion for the authentication or AD?
Which is more secure?
I haven't used AD in ages, so I can't advise on which is better. I think they offer different benefits.  Whichever one you use, the validated user info would be stored in a variable - probably "session" variable. From there all you need to do is a) add the "updatedBy" columns to all tables you want to track and b) use the variable in your SQL updates.   Change the cfsqltypes to match your column data types.

      UPDATE  TableName
      SET        SomeColumn = <cfqueryparam value="#form.someColumn#"
                , UpdatedBy     = <cfqueryparam value="#session.userID#"
                  , UpdatedDate = <cfqueryparam value="#now()#"

      WHERE  RecordID =  <cfqueryparam value="#form.someRecordID#"
DJPr0Author Commented:
IIS 7 is currently managing log in's with AD.
I.E. there isn't any C.F. code for logging in.

Can I obtain the user identity with out using ColdFusion log in code?
@als315 suggested how to do that with CGI variables above.  I'm really rusty w/AD so I'll leave the AD advice to others :)
(Posting comment here, so as not to discourage somebody else from answering your other question...)

I saw an EE thread that mentioned GetAuthUser() can come up blank when anonymous access is enabled. Might be your problem.
DJPr0Author Commented:
I have the user name and date with time inserting into the table successfully with adding the following code. I'm not using a session variable and need advice if I should alter my code to accommodate one.

Please advise.

<cfset userid =  #GetAuthUser()#>
<cfset updatedt = #now()#>

<cfinput name="updatedt_#currentRow#" type="hidden" value="#now()#" size="15">
<cfinput name="userid_#currentRow#" type="hidden" value="#updateby#" size="15">

<cfset variables.updatedt   = FORM["updatedt_"& counter]>
<cfset variables.updateby   = FORM["userid_"& counter]>

 UPDATEBY =  <cfqueryparam value="#variables.userID#" cfsqltype="cf_sql_varchar">,
 UPDATEDT =  <cfqueryparam value="#variables.updateDT#" cfsqltype="CF_SQL_TIMESTAMP">cfsqltype="CF_SQL_TIMESTAMP">
DJPr0Author Commented:
Thanks _agx_!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.