Row-Level Security is a centralized
security feature within the database which is helping us to maintain a
consistent data access policy and reduce the risk of accidental data leakage.
Most amazing thing is that RLS works transparently and does not require changing
our application code because it has an ability to control both read and write operations
to specific rows of data. It supports flexible access criteria means provides
Fine-Grained Access Control on our database.
How
Row-Level Security works?
To understand the RLS, we need to take a
look on the necessary contents for applying this feature on our database table. So,
we are using our Demo database and creating a customer table without RLS where each customer belong a territory. We will create another table called Employees table with RLS where each customer belong a territory. With the help of Employees table, we can apply RLS feature on Customers table also because both tables are linked each other with territory
column as given below:
|
Use Demo
Go
------- Customer
Table without RLS
CREATE TABLE [dbo].[Customers]
(
[CustID] [int] IDENTITY(1,1) NOT NULL Primary Key,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[PhoneNo] [varchar](12) NULL,
[Email] [varchar](100) NULL,
[TerritoryId] [int] NULL
)
----- Employee
Table with RLS
CREATE TABLE [dbo].[Employees]
(
[UserID] [int] IDENTITY(1,1) NOT NULL Primary Key,
[UserName] [varchar](100) NULL,
[TerritoryId] [int] NULL
)
----- Insert values into Customers
table
INSERT [Customers] (FirstName, LastName, PhoneNo, Email, TerritoryId) VALUES
('Ryan', 'Arjun', '555-123-4567', 'ryan.arjun@gmail.com',1),
('Kimmy', 'Wang', '555-936-4568', 'kimmy,wang@yahoo.com.co',2),
('Black', 'Smith', '932-123-0569', 'black.smith@aol.com',3),
('John', 'Ray', '932-025-0523', 'john.ray@rediffmail.com',2);
----- Insert values into Employees
table
INSERT [Employees] (UserName, TerritoryId) VALUES
('RyanArjun',1),
('KimmyWang',2);
---- Pull values
from Customers table
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
FROM [dbo].[Customers]
---- Pull values
from Employees table
SELECT UserId, UserName,TerritoryId
FROM [dbo].[Employees]
|
With the help of territory column we can
apply RLS feature on customer table because Sales Res can access results from
their assigned territory.
Inline
table-valued function -
We need to create inline table-valued
function (predicate function) that defines user access criteria. This function is
going to apply all queries to against our data table and play an important role
to authenticate the user for row level data access. This function at least has
a parameter that corresponds to a column from our data table and returns one
cell underlying our data table will be accessible.
In a very simple way, if this function
returns true value against the requested user than data raw will be visible to
him as given below:
Use Demo
Go
----- Create RLS
policy Schema
CREATE SCHEMA Security
GO
---- create inline
table-valued function (predicate function)
CREATE FUNCTION Security.CustomerAccessPredicates(@TerritoryId Int)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT AccessResult=1 From [dbo].[Employees]
WHERE
----- Sales Res
can access results from their assigned territory
(UserName=USER_NAME() And TerritoryId=@TerritoryId)
OR
----- Database
admin can access all rows
IS_MEMBER('db_owner')=1
Go
|
In the above function, we are using territory
as parameter to the function and will be used for the row-level security for
corresponding data table Customer.
Security
Policy
This is another term of RLS feature because
security policy will bind with our inline table-valued function and adds a
security predicates on our data table. So, we need to create a security policy
and add it with our predicate function. At the same time, we can use the predicate
function as block predicate to restrict users for DDL operations as given below:
Use Demo
Go
---- Create
Security policy
CREATE SECURITY POLICY Security.CustomerAccessPolicy
----- Filter
Predicates to Customer Table
ADD FILTER PREDICATE Security.CustomerAccessPredicates(TerritoryId) on [dbo].[Customers],
----- Filter
Predicates to Customer Table
ADD BLOCK PREDICATE Security.CustomerAccessPredicates(TerritoryId) on [dbo].[Customers]
|
Authenticated
Users to the database
We need to create various users to verify
the functionality of Row-Level Security as given below:
Use Demo
Go
----- Create Users
----- Territory=1:
Create Users
CREATE USER RyanArjun WITHOUT LOGIN;
----- Territory=2:
Create Users
CREATE USER KimmyWang WITHOUT LOGIN;
|
Pull
Records after applying RLS feature
After creating the users for RLS, we can
pull the data by authenticated them as given below:
If user wants to do other
DDL operations then he can work around in his assigned territory area only and block predicate policy will not allow him to access other territories.|
Use Demo
Go
------RLS Territory=1:
RyanArjun
EXECUTE AS USER = 'RyanArjun';
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
FROM [dbo].[Customers];
REVERT;
------RLS Territory=2:
KimmyWang
EXECUTE AS USER = 'KimmyWang';
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
FROM [dbo].[Customers];
REVERT;
---- Pull values
from Customers table with db_owner credentials
SELECT CustId, FirstName, LastName, PhoneNo, Email, TerritoryId
FROM [dbo].[Customers]
|
No comments:
Post a Comment