Consider you have a database (with single dbo schema) that’s design to be used by a single client. How can you turn the database to be multi-client database with minimal impact to your existing application? Row Level Security (RLS) could provide a considerably easily solution.
Some assumptions:
- Your application code or database object (trigger, SP, function etc.) code doesn’t hard code schema name (as we will using schema to separate each client)
- Your application needs to be able to using different login for different client when connect to the database
- Same login will not be access more than one client data
Following are the steps to convert the database.
Step1: Create a dedicated login and schema for each client
CREATE LOGIN [Client1] WITH PASSWORD=N'******' ...;
CREATE LOGIN [Client2] WITH PASSWORD=N'******' ...;
CREATE USER [Client1] FOR LOGIN [Client1] WITH DEFAULT_SCHEMA=[Client1];
CREATE USER [Client2] FOR LOGIN [Client2] WITH DEFAULT_SCHEMA=[Client2];
CREATE SCHEMA [Client1] AUTHORIZATION [Client1];
CREATE SCHEMA [Client2] AUTHORIZATION [Client2];
DENY CONTROL ON SCHEMA:[Client1] TO [Client2]; --need to explicitly deny access to all other users except the schema owner. Can't deny on Public as that will result only sa and dbo can access it.
DENY CONTROL ON SCHEMA:[Client2] TO [Client1];
ALTER ROLE db_datareader ADD MEMBER [Client1];
ALTER ROLE db_datawriter ADD MEMBER [Client1];
ALTER ROLE db_datareader ADD MEMBER [Client2];
ALTER ROLE db_datawriter ADD MEMBER [Client2];
Step2: Add a common column ([ClientName] for example) to each of the existing table and include the [ClientName] as part of the PK. Set a default value USER_NAME() or this column. We use the user name as the ClientName here.
ALTER TABLE dbo.Table1 ADD [ClientName] varchar(50) NOT NULL CONSTRAINT DF_Table1_ClientName DEFAULT (USER_NAME());
Step3: Create a dedicated schema ([Security] for example) to hold all base tables
CREATE SCHEMA [Security];
Step4: Create predicate function that will be used by the RLS. You can adjust the predicate logic based on your needs.
CREATE FUNCTION [Security].[utf_securitypredicate](@ClientName AS NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS utf_securitypredicate
WHERE @ClientName = USER_NAME();
Step5: Move all dbo tables to the [Security] schema.
ALTER SCHEMA [Security] TRANSFER dbo.Table1;
Step6: Create view under each client schema to query table/columns under [Security] schema. Don’t need to include [ClientName] column in the view. You can expose different table columns for different client via the view. Remember, DO NOT alias the base table name, or the column won’t be updateable via the view.
CREATE VIEW [Client1].Table1
AS
SELECT ...
FROM [Security].Table1;
GO
CREATE VIEW [Client2].Table1
AS
SELECT ...
FROM [Security].Table1;
GO