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 **********************************************************************
