Wednesday, March 02, 2005

CFLOGIN synchronization with SQL logins

I haven't quite decided if this creates a better security situation or a worse security situation. Maybe you can help.

I've created a bit of code that lets you use the login credentials stored in MSSql server as the supplier of your cflogin data. Doing this gives you a couple of nice benefits including:
  1. All of your querries can identify who is making them. You use the username and password attributes of cfquery and cfstoredproc throughout your app.
  2. Since you have a named user for each connection, you can build triggers that support detailed logging in the db.
  3. Removal of the user from the db removes them from access to your app.
  4. Users can manage their passwords in sql from within your app.
  5. SQL admins don't need to know user passwords.
  1. You might create a new attack vector directly to your db through your app. Would this be any different than having cfide on the same machine?
  2. Definitely not portable to other databases.
  3. ?
Here's the bulk of the code that makes it happen:

application.cfc (partial)
<cffunction name="OnRequestStart">
<cfargument name="request" required="true"/>
<cfif IsDefined("form.logout")>
<cfif NOT IsDefined("cflogin")>
<cfinclude template="loginform.cfm">
<cfif is "" or cflogin.password IS "">
<h2>You must enter text in both the User Name and Password fields.</h2>
<cfinclude template="loginform.cfm">
<cfstoredproc procedure="sp_helpuser" datasource="acs" username="" password="#cflogin.password#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="">
<cfprocresult name="sqlUser">
<cfloginuser name="" password="#cflogin.password#" roles="#valueList(sqlUser.GroupName)#">
<cfcatch type="database">
<h2>Login failed.</h2>
<cfinclude template="loginform.cfm">

<form name="loginFrm" action="" method="post">
<label for="j_username">Username:</label>
<input type="text" name="j_username" />
<br />
<label for="j_password">Password:</label>
<input type="password" name="j_password" />
<input type="submit" value="submit" />

<h1>You Made it.</h1>
<!--- SQL Role names --->
ACSAdmin: #IsUserInRole("ACSAdmin")#<br />
ACSManager: #IsUserInRole("ACSManager")#<br />
ACSUser: #IsUserInRole("ACSUser")#<br />

Does this make any sense, or is this a stupid thing to do?


  1. This is how the DBAs that I worked with in the government wanted to do it. They were used to having a db user account for each application user. If you provide a web-based administrative interface to manage the users without the assistance of a DBA, then go for it! Great work!

  2. Thanks david,

    The whole idea still makes me a little itchy from a security perspective, but it sure looks workable.

    I hadn't planned on making a web based user admin that would manage logins, but that sure looks possible with tools like sp_addlogin and sp_droplogin.