Sorry, guys -- wrong list! Respectfully,
Adam Phillip Churvis Member of Team Macromedia http://www.ProductivityEnhancement.com Download Plum and other cool development tools, and get advanced intensive Master-level training: * C# & ASP.NET for ColdFusion Developers * ColdFusion MX Master Class * Advanced Development with CFMX and SQL Server 2000 ----- Original Message ----- From: "Adam Churvis" <[EMAIL PROTECTED]> To: "Plum Discussion List" <[email protected]> Sent: Friday, April 15, 2005 9:32 PM Subject: [plum] Database Schema and Methods for a Role-Based Authorization Model > Database Schema and Methods for a Role-Based Authorization Model > > > Schema > ------ > > Before I go much further, perhaps I should discuss the simple logical data > model that supports our role-based authorization model. Here's the LDM > (Logical Data Model): > > http://www.productivityenhancement.com/images/AuthModelSchema.gif > > The objects that are relevant to this discussion are the PlumRole and > PlumUserRole entities and the UserID, Email, and Password attributes of the > PlumUser entity. There are other attributes that are significant to the > Plum Framework, but they have nothing to do with the core discussion of > role-based authorization. > > As you can see, there is an alternate key on PlumUser.Email so a user's > profile may be retrieved by his email address alone. > > > Methods > ------- > > Authentication and authorization are performed in two separate, highly > optimized database queries. > > Authentication is performed via a single-table query on a simple key > (PlumUser.Email), which engages a non-clustered index to facilitate a > bookmark lookup of the row in the PlumUser table. (NOTE: I'm showing just > the significant portion of what's inside the component methods for the sake > of brevity.) > > > <cfquery name="userQuery" > datasource="#Application.dbDSN#" > username="#Application.dbUsername#" > password="#Application.dbPassword#"> > SELECT > UserID, > FirstName, > LastName, > Email, > Password, > LoginID, > Status > FROM > PlumUser > WHERE > Email = <cfqueryparam value="#Trim(Arguments.email)#" > cfsqltype="CF_SQL_VARCHAR"> > </cfquery> > > > Using this method not only minimizes both database load and potential > blocking (and correspondingly maximizes performance), it also enables the > password match to be performed using ColdFusion string functions, so the > developer can decide whether or not to use case-sensitive (Compare()) or > case-insensitive (CompareNoCase()) matching. This also enables the > application to determine whether or not the unsuccessful login is from a new > user or an existing user who entered an incorrect password; this information > can be used to either assist the new user in creating a new profile, or > assist the existing user in retrieving his password via email, as the case > warrants. > > > <cfif CompareNoCase(userQuery.Password, Arguments.password) NEQ 0> > <cfthrow type="Authenticate.PasswordDoesNotMatch" > message="You are in our system, but your password does not match. Please > try again." > errorcode="66030"> > </cfif> > > > Authorization is performed via a single-table query on a single column > within a compound key, which engages a clustered index to facilitate a range > lookup of the rows in the PlumUserRole table. The query calls database > string functions to correct roles for stray space characters (you can also > correct case at this point, if you wish), so these operations are optimized. > > > <cfquery name="plumUserRoleQuery" > datasource="#Application.dbDSN#" > username="#Application.dbUsername#" > password="#Application.dbPassword#"> > SELECT > RTRIM(RoleCode) AS TrimmedRoleCode > FROM > PlumUserRole > WHERE > UserID = <cfqueryparam value="#Val(Arguments.userID)#" > cfsqltype="CF_SQL_INTEGER"> > </cfquery> > > > Using this method minimizes both database load and potential blocking (and > correspondingly maximizes performance). The recordset object returned to > ColdFusion is very compact, and the resulting ColdFusion query object can be > used to directly produce a comma-delimited list of roles that may be passed > to the roles attribute of the CFLOGINUSER tag. > > > <cfloginuser name="#userQuery.UserID#" > password="#userQuery.Password#" > roles="#ValueList(plumUserRoleQuery.TrimmedRoleCode)#"> > > > Next I'll cover applying authorization restrictions to code. After that, > I'll discuss how to authorize based on transitory data, such as things like > subscriptions and other data that isn't related to the roles a user plays in > a system. > > Respectfully, > > Adam Phillip Churvis > Member of Team Macromedia > http://www.ProductivityEnhancement.com > > Download Plum and other cool development tools, > and get advanced intensive Master-level training: > > * C# & ASP.NET for ColdFusion Developers > * ColdFusion MX Master Class > * Advanced Development with CFMX and SQL Server 2000 > > > ********************************************************************** > You can subscribe to and unsubscribe from lists, and you can change > your subscriptions between normal and digest modes here: > > http://www.productivityenhancement.com/support/DiscussionListsForm.cfm > ********************************************************************** > ********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm **********************************************************************
