SQL Server 2014：基于32位权限整数查询用户权限的方法

场景说明 #

SQL Server 2014数据库中，用户权限采用32位整数存储，每个二进制位对应一项具体权限：

• bit 0 = 读取文件
• bit 1 = 移动文件
• bit 2 = 删除文件
• bit 3 = 保存文件
• ...

当对应二进制位为1时，表示用户拥有该权限；为0则无此权限。

需求 #

编写SQL查询语句，关联用户表与权限表，展示指定用户的所有已启用权限，示例效果如下：

• 用户Joe的权限整数为3（二进制0011），返回「读取文件」「移动文件」两行；
• 用户Peter的权限整数为8（二进制1000），仅返回「保存文件」一行；
• 用户Dave的权限整数为7（二进制0111），返回「读取文件」「移动文件」「删除文件」三行。

权限表结构调整建议 #

原权限表的value列存在逻辑错误（bit 0对应的权限值应为2^0=1而非0，否则无法通过位运算判断权限），调整后的权限表结构如下：

其中value列的值为2^bit#，对应每个二进制位的权重。

SQL查询语句 #

假设用户表名为Users，包含UserId、UserName、PermissionValue（存储权限整数）字段；权限表名为Permissions，包含bit#、value、description字段。

查询指定用户的权限 #

以查询用户Joe为例：

SELECT p.description AS 已启用权限
FROM Users u
INNER JOIN Permissions p 
    ON (u.PermissionValue & p.value) = p.value
WHERE u.UserName = 'Joe';

查询所有用户的权限 #

SELECT u.UserName, p.description AS 已启用权限
FROM Users u
INNER JOIN Permissions p 
    ON (u.PermissionValue & p.value) = p.value
ORDER BY u.UserName;

原理说明 #

利用SQL的位与运算符&：当用户的权限整数与权限项的value做位与运算时，若结果等于该value，说明对应二进制位为1，即用户拥有该权限。

内容的提问来源于stack exchange，提问作者JPoole