[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 749
  • Last Modified:

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)
0
DJPr0
Asked:
DJPr0
2 Solutions
 
als315Commented:
You can't do it from Access side, but you can try to write CGI.AUTH_USER (full user name with domain)
0
 
_agx_Commented:
(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 ....
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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?
0
 
_agx_Commented:
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#"
                                                                 cfsqltype="cf_sql_varchar">
                , UpdatedBy     = <cfqueryparam value="#session.userID#"
                                                                 cfsqltype="cf_sql_integer">
                  , UpdatedDate = <cfqueryparam value="#now()#"
                                                           cfsqltype="cf_sql_timestamp">


      WHERE  RecordID =  <cfqueryparam value="#form.someRecordID#"
                                                      cfsqltype="cf_sql_integer">
0
 
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?
0
 
_agx_Commented:
@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 :)
0
 
_agx_Commented:
(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.
0
 
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">
0
 
_agx_Commented:
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">
0
 
DJPr0Author Commented:
Thanks _agx_!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now