Database-Security model-Row-ColumnLevel

------------------------------------------------------------------------------------------

                     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

--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 

--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.

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;

~

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 

EXEC sp_addrolemember @membername = 'Abc', @rolename = 'HR_User';
GO

EXECUTE AS USER = 'Abc';
GO

EXEC sp_addrolemember @membername = 'Abc', @rolename = 'HR_User';

GO

------------------------------------------------------------------------------------------

                     Filtering SQL Server Columns Using Column Level Permissions

------------------------------------------------------------------------------------------

~

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

Ownership chaining in SQL Server security feature or security risk

CREATE SCHEMA MARKETING;

GO

SELECT  
    [name] AS [schema]  
  , [schema_id] 
  , USER_NAME(principal_id) [Owner] FROM sys.schemas;

SQL Server will use the owner of the respective schemas as the "owner" for the object when ownership chaining is considered.


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

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');