Decode bits in integer used to store permissions-CodePudding

An existing SQL Server 2014 database stores permissions for users in a 32 bit integer.

bit 0 = Read files
bit 1 = Move files
bit 2 = Delete files
bit 3 = Save files
etc.

If the bit is 1 the permission is set, if the bit = 0 the permission is not set.

I need a query to show all permissions set for a given user. I have a table that has a list of all 32 permissions and am not sure of how to join the user table with the permissions table to get a list.

For example, if User Joe has permission INT = 3 then the query should return 2 rows:

Read Files
Move Files

If user Peter has permission INT = 8 then the query should return only 1 row:

Save Files

If user Dave has permission INT = 7 then the query should return 3 rows:

Read files
Move Files
Delete files

The permissions table has 3 columns: bit#, value, description

0   0   Read Files
1   2   Move Files
2   4   Delete Files
3   8   Save Files
etc.

I can easily change the design of the permissions table if different or additional columns would be helpful.

CodePudding user response：

One way is to store the flags in a table (maybe you already have one):

CREATE TABLE dbo.PermissionDefinitions
  (i int, Permission varchar(32));

INSERT dbo.PermissionDefinitions(i, Permission) 
  VALUES 
  (1, 'Read files'),
  (2, 'Move files'),
  (4, 'Delete files'),
  (8, 'Save files'); -- ... more ...

And you have a Users table with a column containing their permission set:

CREATE TABLE dbo.Users(Username sysname, PermissionSet int);

INSERT dbo.Users VALUES(N'Joe', 3), (N'Suzy', 15);

Then you can just cross join all those with bitwise and:

SELECT u.Username, ps.Permission 
  FROM dbo.Users AS u
  CROSS JOIN dbo.PermissionDefinitions AS ps
  WHERE u.PermissionSet & ps.i = ps.i;

Example fiddle.