Single-Item Permissions Approach for SQL Table Rows
If you ever wanted to implement single-item permissions for SQL table rows, you will notice that SQL Server itself does not provide a native solution for that.
In contrast to e.g. the SharePoint list approach, where single items can be individually secured, SQL table rows do not have a corresponding solution.
This article describes a quite simple approach how you can achieve something similar to single-row permissions.
The following example goes one step further and implements the permissions using group memberships:
The permissions for the individual rows are restricted to one or more groups, whereas users can be members of one or multiple groups.
Summary
This approach is based on big integer values for users, groups and data rows and bitwise AND operations to compare them.
Each data row is “secured” by a given binary value. The user’s memberships also result in a binary value which is compared with the value of the data row. If the result is greater than 0, the row is returned, otherwise not.
All logic used for returning the data rows a user has access to are included in Stored Procedures.
Requirements
SQL Tables
First of all we need three SQL tables.
Groups Table: CustomDB_Groups
The Groups table contains all groups that we need together with a unique ID of the Data Type “bigint”.
The reason why we are using bigint is because we are actually checking the row permissions with a binary representation of the number.
Bigint is 8 Byte, i.e. 64 bits. So we can use 64 groups in total.
Note:
If you need more groups, you can create additional columns with filter keys.
Users Table: CustomDB_Users
The Users table contains all user objects along with the group membership information, also of the Data Type “bigint”. One user can have multiple entries in the Users table or you can
create a row for each membership. The Stored Procedure will take care of that.
Data Table: CustomDB_Main
In my example this table holds the data rows that need to be individually secured. A special column “GroupPermissions” (again of the Data Type “bigint”) defines the row-specific access
rights based on Group IDs. The Stored Procedure will operate with a bitwise AND to filter the rows the user has access to.
SQL Stored Procedures
Then we need the logic using two SQL Stored Procedures.
(You can adjust them as needed, these are just simple examples.)
GetUserGroupIdSum
This Stored Procedure returns the sum of all group IDs a user belongs to.
In our example this Stored Procedure will return the value "6" for the user "Barry".
GetFilteredList
This Stored Procedure returns all data rows a user has access to.
Every row for which the result "where (@UserID & [GroupID]) > 0" is true will be returned.
This is just a simple example on how you can achieve requirements as described. There are different scenarios with different prerequisites, but I hope this post inspires you while solving your individual issues.
