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
        ENDAS Permissions
  FROM Permissions
  WHERE AccountID =<cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#">/* Dynamic variable here */
)
SELECTDISTINCT AccountID, AccessType, AccessLevel
  , CASEWHEN AccessLevel ='S'THEN Permissions 
      ELSELEFT(ca.pl, COALESCE(LEN(ca.pl),0)-1)
    ENDAS 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

<cfqueryname="qryUserPerm"datasource="#Application.dsn#">
    SELECT AccessType, AccessLevel, PermissionList
    FROM myPermissionsView
    WHERE AccountID = <cfqueryparamcfsqltype="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 CityWHEN'B' THEN BuildingWHEN'S' THEN StateENDAS AccessValue
 FROM   Permissions
 WHERE  AccountID = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.AccountID#">

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

<cfsetlocal.permissionType = qryUserPerm.AccessType><cfsetlocal.permissionLevel = qryUserPerm.AccessLevel><cfsetlocal.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.AccessValueFROM ( SELECTCASE AccessLevel 
                                  WHEN'C' THEN City WHEN'B' THEN Building WHEN'S' THEN State ENDAS AccessValue
                         FROM   Permissions l
                         WHERE  l.AccountID = p.AccountID 
                     ) l
                  GROUPBY l.AccessValue
                  FOR XML PATH('')
                ),1,1,'') AS PermissionsListFROM   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.

<cfsetlocal.permissionType = qryUserPerm.AccessType><cfsetlocal.permissionLevel = qryUserPerm.AccessLevel><cfsetlocal.permissionList = qryUserPerm.PermissionsList>

Solution 3:

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

<cfsetlocal.permissionType = ""><cfsetlocal.permissionLevel = ""><cfsetlocal.permissionList = ""><cfifqryUserPerm.AccessLevelEQ "S"><cfsetlocal.permissionType = qryUserPerm.AccessType><cfsetlocal.permissionLevel = qryUserPerm.AccessLevel><cfsetlocal.permissionList = qryUserPerm.State><cfelseifqryUserPerm.AccessLevelEQ "C"><cfsetlocal.permissionType = qryUserPerm.AccessType><cfsetlocal.permissionLevel = qryUserPerm.AccessLevel><cfsetlocal.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.City))><cfelseifqryUserPerm.AccessLevelEQ "B"><cfsetlocal.permissionType = qryUserPerm.AccessType><cfsetlocal.permissionLevel = qryUserPerm.AccessLevel><cfsetlocal.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;
}
elseif(qryUserPerm.AccessLevel EQ "C"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.City));
}
elseif(qryUserPerm.AccessLevel EQ "B"){
   local.permissionType = qryUserPerm.AccessType;
   local.permissionLevel = qryUserPerm.AccessLevel;
   local.permissionList = ListRemoveDuplicates(ValueList(permissionList,qryUserPerm.Building));
}

</cfscript>

Share