Best Way To Store Permissions For The User Account?

I have permission records that are tied to each account in my application. Each account can have one or multiple permission records based on account type. Here is example:

Solution 1:

This can be done in SQL itself, which may be more performant than manipulating the data in code.

One issue with the data is that the State, City and Building columns need to be unpivoted to then be turned into a comma-delimited list.

Since you are using SQL 2008, you have access to the functionality you need.

The query is: http://sqlfiddle.com/#!18/0f4f7/1

; WITH cte AS (
  SELECT
      AccountID, AccessType, AccessLevel
      , CASE AccessLevel
          WHEN 'S' THEN State
          WHEN 'C' THEN City
          WHEN 'B' THEN Building
        END AS Permissions
  FROM Permissions
  WHERE AccountID = 
    <cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#"> 
    /* Dynamic variable here */
)
SELECT DISTINCT AccountID, AccessType, AccessLevel
  , CASE 
      WHEN AccessLevel = 'S' THEN Permissions 
      ELSE LEFT(ca.pl, COALESCE(LEN(ca.pl),0)-1)
    END AS PermissionList
FROM cte
CROSS APPLY (
  SELECT p.Permissions + ', '
  FROM cte p
  WHERE p.AccountID = cte.AccountID
    AND p.AccessType = cte.AccessType
    AND p.AccessLevel = cte.AccessLevel
  FOR XML PATH('')
) ca (pl)     ;

I start with a CTE to build out the "unpivoted" list of Permissions based on the AccessLevel. If this can be put in a SQL View, you can just leave out the WHERE statement here and just call it when you call the View. A View would be my preference, if you can get it into your database.

After I have the CTE, I just select the base columns (AccountID, AccessType and AccessLevel, and then I CROSS APPLY a comma-delimited list of the Permissions. I use FOR XML PATH('') to build that comma-delimited list.

If this is able to be converted to a View, it would be a simple

<cfquery name="qryUserPerm" datasource="#Application.dsn#">
    SELECT AccessType, AccessLevel, PermissionList
    FROM myPermissionsView
    WHERE AccountID = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(session.AccountID)#">
</cfquery>

If not, you'll have to try running the above full query inside the cfquery tag.

This should give you back a dataset like:

| AccessType | AccessLevel |         PermissionList |
|------------|-------------|------------------------|
|          F |           B | 8010, 5412, 6103, 3106 |

You only have one result to work with and won't have to loop.

======================================================================

If you want to go the in-code route, I'd still recommend trying to use cfscript to build out the structs. But, if you can have more than one AccessLevel, your results may not be what you think they should be. You'll have to doublecheck your data.

  local.permissionType = q2.AccessType ;
  local.permissionLevel = q2.AccessLevel ;

  switch( q2.AccessLevel ) {
    case "S" :  local.permissionList = q2.State ;
      break ;
    case "C" :  local.permissionList = ListRemoveDuplicates(ValueList(q2.City)) ;
      break ;
    case "B" :  local.permissionList = ListRemoveDuplicates(ValueList(q2.Building)) ;
      break ;
  }

https://trycf.com/gist/e811ec86f0d5a52fd9ce703f897cb5aa/acf2016?theme=monokai

Solution 2:

You could trim down the code by using CASE to merge everything into a single column, based on the Access Level.

SELECT AccessType
        , AccessLevel
        , CASE AccessLevel
            WHEN 'C' THEN City
            WHEN 'B' THEN Building
            WHEN 'S' THEN State
          END AS AccessValue
 FROM   Permissions
 WHERE  AccountID = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#">

Then build your list from that column. No cfif's needed.

 <cfset local.permissionType = qryUserPerm.AccessType>
 <cfset local.permissionLevel = qryUserPerm.AccessLevel>
 <cfset local.permissionList = valueList(qryUserPerm.AccessValue)>

You could also build the CSV list in SQL only, but not sure it's worth it in this scenario, since it's just as easy to build in CF.

SELECT TOP 1 AccessType
        , AccessLevel
        , STUFF(( SELECT ','+ l.AccessValue
                  FROM ( SELECT CASE AccessLevel 
                                  WHEN 'C' THEN City 
                                  WHEN 'B' THEN Building 
                                  WHEN 'S' THEN State 
                                END AS AccessValue
                         FROM   Permissions l
                         WHERE  l.AccountID = p.AccountID 
                     ) l
                  GROUP BY l.AccessValue
                  FOR XML PATH('')
                ),1,1,'') AS PermissionsList
 FROM   Permissions p            
 WHERE  AccountID = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#">       

Anyway, using the above the query will return everything you need in a single row: AccessType, AccessLevel and csv list of permissions.

 <cfset local.permissionType = qryUserPerm.AccessType>
 <cfset local.permissionLevel = qryUserPerm.AccessLevel>
 <cfset local.permissionList = qryUserPerm.PermissionsList>

Solution 3:

I would be tempted to remove the loop. I am thinking that this may make things a little simpler.

<cfset local.permissionType = "">
<cfset local.permissionLevel = "">
<cfset local.permissionList = "">


<cfif qryUserPerm.AccessLevel EQ "S">
     <cfset local.permissionType = qryUserPerm.AccessType>
     <cfset local.permissionLevel = qryUserPerm.AccessLevel>
     <cfset local.permissionList = qryUserPerm.State>
<cfelseif qryUserPerm.AccessLevel EQ "C">
     <cfset local.permissionType = qryUserPerm.AccessType>
     <cfset local.permissionLevel = qryUserPerm.AccessLevel>
     <cfset local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.City))>
<cfelseif qryUserPerm.AccessLevel EQ "B">
     <cfset local.permissionType = qryUserPerm.AccessType>
     <cfset local.permissionLevel = qryUserPerm.AccessLevel>
     <cfset local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.Building))>
</cfif>

And, if you want to compare the lists in future for equality, you may want to use:

<cfset local.permissionList = ListSort(local.permissionList,"textnocase","asc")>

UPDATE:

<cfscript>

qryUserPerm = queryExecute("
  SELECT AccessType, AccessLevel, State, City, Building 
  FROM Permissions
  WHERE AccountID = :AccountID 
",
{
  AccountID = {value = Trim(session.AccountID), cfsqltype = "cf_sql_integer"}
},
{
  datasource = Application.dsn 
});

local.permissionType = "";
local.permissionLevel = "";
local.permissionList = "";

if(qryUserPerm.AccessLevel EQ "S"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = qryUserPerm.State;
}
else if(qryUserPerm.AccessLevel EQ "C"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.City));
}
else if(qryUserPerm.AccessLevel EQ "B"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.Building));
}

</cfscript>

Share