------------------------------------------------------------------------------------------
How to Setup Row Level Security for SQL Server
------------------------------------------------------------------------------------------
How to setup such a security model?
A row-level security model for a database.
Users are connecting with their Windows user accounts.
Windows groups ( Active Directory domain(s)).
Tables: Department,Employee
Need to build a mechanism on top of these tables.
Security Mapping Table ( DepartmentID,Windows group name)
-Tie : Security ->department->view
Preference: To create database roles
Advantage:
To test the Model:( Windows groups yet)
- database roles ( add or remove users)
Switch domains: Production & Development ( No need to re-seeding your mapping table)
-need to associate : right Windows security groups -> right database roles
Use Ownership chaining:
granting SELECT permissions against the view
Not granting any permissions against the underlying tables
-- Database roles:
-- Create database roles to go along with the Windows groups
CREATE ROLE HR_User;
GO
CREATE ROLE HR_User;
GO
--Security Mapping table:
-- Create the security mapping table and populate it so we can enforce row-level security
CREATE TABLE dbo.SecurityMap (
DepartmentID INT NOT NULL,
RoleName NVARCHAR(255),
CONSTRAINT PK_SecurityMap PRIMARY KEY CLUSTERED (DepartmentID, RoleName)
);
GO
CREATE TABLE dbo.SecurityMap (
DepartmentID INT NOT NULL,
RoleName NVARCHAR(255),
CONSTRAINT PK_SecurityMap PRIMARY KEY CLUSTERED (DepartmentID, RoleName)
);
GO
--Finally, we need the view that uses the SecurityMap table to do the filtering:
-- Create a view which uses row-level security to filter the roles
-- Note the OR IS_MEMBER('HR_User') = 1 as well as the OR IS_MEMBER('db_owner') = 1
-- as part of the WHERE clause. If a user is a member of either of these roles,
-- then all the rows will be returned.
-- Note the OR IS_MEMBER('HR_User') = 1 as well as the OR IS_MEMBER('db_owner') = 1
-- as part of the WHERE clause. If a user is a member of either of these roles,
-- then all the rows will be returned.
CREATE VIEW dbo.DepartmentEmployees
AS
SELECT Emp.FirstName, Emp.MiddleName, Emp.SurName, Dept.DepartmentName
FROM dbo.Department Dept
INNER JOIN dbo.Employee Emp
ON Dept.DepartmentID = Emp.DepartmentID
LEFT JOIN dbo.SecurityMap
ON Emp.DepartmentID = dbo.SecurityMap.DepartmentID
WHERE IS_MEMBER(dbo.SecurityMap.RoleName) = 1
OR IS_MEMBER('HR_User') = 1
OR IS_MEMBER('db_owner') = 1;
GO
GRANT SELECT ON dbo.DepartmentEmployees TO Manager;
GRANT SELECT ON dbo.DepartmentEmployees TO HR_User;
AS
SELECT Emp.FirstName, Emp.MiddleName, Emp.SurName, Dept.DepartmentName
FROM dbo.Department Dept
INNER JOIN dbo.Employee Emp
ON Dept.DepartmentID = Emp.DepartmentID
LEFT JOIN dbo.SecurityMap
ON Emp.DepartmentID = dbo.SecurityMap.DepartmentID
WHERE IS_MEMBER(dbo.SecurityMap.RoleName) = 1
OR IS_MEMBER('HR_User') = 1
OR IS_MEMBER('db_owner') = 1;
GO
GRANT SELECT ON dbo.DepartmentEmployees TO Manager;
GRANT SELECT ON dbo.DepartmentEmployees TO HR_User;
~
Note that the "magic" is being worked by the IS_MEMBER() function.
If the user is a member of the role which matches up to the department based on the SecurityMap table, the user sees the row containing the employee information.
..is a member of the db_owner role, both roles of which see all the data.
-- Query as
CREATE USER Abc WITHOUT LOGIN;
GO
CREATE USER Abc WITHOUT LOGIN;
GO
EXEC sp_addrolemember @membername = 'Abc', @rolename = 'HR_User';
GO
GO
EXECUTE AS USER = 'Abc';
GO
GO
EXEC sp_addrolemember @membername = 'Abc', @rolename = 'HR_User';
GO
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
~
Now, we don't want interns to have this level of permissions. We only want them to have access to specific columns. There's a way to do this. Immediately after the table name, we can specify the columns we want to grant permission to (or DENY, if we needed to do that) within a set of parentheses, like so:
GRANT SELECT ON dbo.Employee (EmployeeID, FirstName, MiddleName, SurName) TO HR_Intern;
~
Filtering Columns in SQL Server Using Views
CREATE SCHEMA MARKETING;
GO
SELECT
[name] AS [schema]
, [schema_id]
, USER_NAME(principal_id) [Owner] FROM sys.schemas;
[name] AS [schema]
, [schema_id]
, USER_NAME(principal_id) [Owner] FROM sys.schemas;
An object can have an explicit owner by using ALTER AUTHORIZATION on the object, as shown here (the CREATE USER statement creates a valid user to which to make the owner of the table being created):
CREATE USER TestUser WITHOUT LOGIN; GO
CREATE TABLE Marketing.OwnedTable (
TableValue INT ); GO
ALTER AUTHORIZATION ON Marketing.OwnedTable TO TestUser; GO
CREATE TABLE Marketing.OwnedTable (
TableValue INT ); GO
ALTER AUTHORIZATION ON Marketing.OwnedTable TO TestUser; GO
SELECT
so.[name] AS [Object]
, sch.[name] AS [Schema]
, USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [Owner]
, type_desc AS [ObjectType] FROM sys.objects so
JOIN sys.schemas sch
ON so.[schema_id] = sch.[schema_id] WHERE [type] IN ('U', 'P');
so.[name] AS [Object]
, sch.[name] AS [Schema]
, USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [Owner]
, type_desc AS [ObjectType] FROM sys.objects so
JOIN sys.schemas sch
ON so.[schema_id] = sch.[schema_id] WHERE [type] IN ('U', 'P');
