存储用户帐户权限的最佳方法? [英] 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:

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

上面的查询将为一个帐户生成这样的数据:

Query above will produce data like this for one of the accounts:

RecID   AccountID   AccessType  AccessLevel State     City    Building
70      285A637D82B9    F            B        NY    New York    8010
71      285A637D82B9    F            B        NY    New York    5412
73      285A637D82B9    F            B        NY    New York    6103
74      285A637D82B9    F            B        NY    New York    3106

正如您在上方看到的，该帐户已分配了4条记录.访问类型可以是完全F或仅查看V.访问级别可以是州"S"，城市"C"或建筑物"B".用户一次只能分配一个访问级别，因此，例如，不会出现用户可以分配城市和州级别的情况.我的问题是，针对特定访问级别从查询组织数据的最佳方法是什么?在这种情况下，我必须合并列表或数组中的4条记录.州级别只能分配一个权限记录，城市和建筑物可以具有多个记录.这是我所拥有的示例:

As you can see above this account have 4 records assigned to them. Access Type can be Full F or View Only V. Access Level can be State 'S', City 'C' or Building 'B'. User can have only one access level assigned to them at the time, so for example there is no situation where user can have assigned City and State level. My question is what would be the best way to organize the data from the query for specific access level? In this case I have to merge 4 records in list or array. State level only can have one permission record assigned, City and Building can have multiple records. Here is example of what I have:

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

<cfloop query="qryUserPerm">
    <cfif qryUserPerm.AccessLevel EQ "S">
         <cfset local.permissionType = qryUserPerm.AccessType>
         <cfset local.permissionLevel = qryUserPerm.AccessLevel>
         <cfset local.permissionList = listAppend(permissionList, "", ",")>
    <cfelseif qryUserPerm.AccessLevel EQ "C">
         <cfset local.permissionType = qryUserPerm.AccessType>
         <cfset local.permissionLevel = qryUserPerm.AccessLevel>
         <cfset local.permissionList = listAppend(permissionList, qryUserPerm.City, ",")>
    <cfelseif qryUserPerm.AccessLevel EQ "B">
         <cfset local.permissionType = qryUserPerm.AccessType>
         <cfset local.permissionLevel = qryUserPerm.AccessLevel>
         <cfset local.permissionList = listAppend(permissionList, qryUserPerm.Building, ",")>
    <cfelse>
         <cfset local.permissionType = "">
         <cfset local.permissionLevel = "">
         <cfset local.permissionList = listAppend(permissionList, "", ",")>
    </cfif>
</cfloop>

将permissionType和permissionLevel保留在循环中似乎是多余的，但是我目前不知道避免这种情况的更好方法.同样，这使得处理非常困难，以防万一我必须比较权限列表.如果当前登录的用户更改了权限，我将必须运行相同的过程并构建列表，以便将其与Session.premissionList进行比较.有什么办法可以将这些记录与SQL合并?还是这种方法是更好的选择?

It seems redundant to keep permissionType and permissionLevel inside of the loop but I do not know better way currently to avoid that. Also this makes process very dificult in case when I have to compare permission list. I would have to run this same process and build the list in order to compare that with Session.premissionList in case where currently logged user change their permissions. Is there any way to merge these records with SQL? Or this approach is better option?

推荐答案

这可以在SQL本身中完成，它可能比处理代码中的数据更有效.

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

数据的一个问题是，State，City和Building列需要取消旋转，然后才能变成逗号分隔的列表.

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.

由于使用的是SQL 2008，因此可以访问所需的功能.

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

查询为: 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)     ;

我从CTE开始，根据AccessLevel建立Permissions的无支点"列表.如果可以将其放在SQL视图中，则可以只在此处省略WHERE语句，并在调用视图时调用它.如果可以将View放入数据库，则将是我的首选.

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.

获得CTE之后，我只需选择基本列(AccountID，AccessType和AccessLevel，然后选择CROSS APPLY以逗号分隔的Permissions列表.我使用FOR XML PATH('')来建立以逗号分隔的清单.

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>

如果没有，则必须尝试在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.

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

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

如果您想执行代码内编码，我仍然建议您尝试使用cfscript来构建结构.但是，如果您可以有多个AccessLevel，则结果可能与您认为的不一样.您必须仔细检查数据.

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/eistnec86f0d5a52fd9ce703f897cbtheaa==

这篇关于存储用户帐户权限的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！