آشنایی با SQL Server 2016 Row Level Security – محدودیت ها و کارایی (بخش سوم)

[sql] EXECUTE AS USER = N’Peon’;
GO

DECLARE @ci VARBINARY(128) = CONVERT(VARBINARY(128),N’Rep1′);
SET CONTEXT_INFO @ci;
SELECT USER_NAME(), * FROM dbo.Accounts;
GO

REVERT;
GO [/sql]

[sql] EXECUTE AS USER = N’Peon’;
GO

DECLARE @i INT = 1;
BEGIN TRY
SELECT *
FROM dbo.Accounts
WHERE CASE WHEN RepID = @i THEN
CASE WHEN AnnualFees > 75000
THEN CONVERT(INT, ‘x’) — designed to raise an exception
END
END = 1;
END TRY
BEGIN CATCH
PRINT N’Rep ‘ + RTRIM(@i) + N’ has an account with fees > $75,000.’;
END CATCH
GO

REVERT;
GO [/sql]

[sql] GRANT INSERT, UPDATE ON dbo.Accounts TO Rep1;
GO

EXEC(‘INSERT dbo.Accounts(AccountID,AnnualFees,RepID)
VALUES(99,50000,2);’) AS USER = N’Rep1′;
GO [/sql]

[sql] EXEC(‘SELECT * FROM dbo.Accounts WHERE AccountID = 99;’) AS USER = N’Rep1′;
EXEC(‘SELECT * FROM dbo.Accounts WHERE AccountID = 99;’) AS USER = N’Rep2′;
GO [/sql]

[sql] EXEC(‘UPDATE dbo.Accounts SET AnnualFees*=2 WHERE AccountID = 99;’) AS USER = N’Rep1′;
EXEC(‘SELECT * FROM dbo.Accounts WHERE AccountID = 99;’) AS USER = N’Susan’;
GO [/sql]

[sql] — Create SampleUser table
CREATE TABLE [dbo].[SampleUser](
[UserID] [int] NOT NULL PRIMARY KEY,
[Username] [varchar](30)
)
GO

— Create SampleData table and foreign key to SampleUser table
CREATE TABLE [dbo].[SampleData](
[RowKey] [int] NOT NULL PRIMARY KEY,
[CreateDate] [datetime] NOT NULL,
[OtherDate] [datetime] NOT NULL,
[VarcharColumn] [varchar](20) NULL,
[IntColumn] [int] NULL,
[FloatColumn] [float] NULL,
[UserID] [int] NOT NULL
)
GO
ALTER TABLE [dbo].[SampleData] WITH CHECK
ADD CONSTRAINT [FK_SampleData_SampleUser]
FOREIGN KEY([UserID])
REFERENCES [dbo].[SampleUser] ([UserID])
GO

— Load SampleUser table
DECLARE @val INT
SELECT @val=1
WHILE @val < 51
BEGIN
INSERT INTO SampleUser VALUES (@val,’User’ + cast(@val AS VARCHAR))
SELECT @val=@val+1
END
GO

— Load SampleData table
DECLARE @val INT
SELECT @val=1
WHILE @val < 5000000
BEGIN
INSERT INTO SampleData
VALUES
(@val,
getdate(),
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365),’2015-01-01′),
‘TEST’ + cast(round(rand()*100,0) AS VARCHAR),
round(rand()*100000,0),
round(rand()*10000,2),
round(rand()*49,0)+1)
SELECT @val=@val+1
END
GO

— create indexes
CREATE NONCLUSTERED INDEX [IX_SampleData_CreateDate] ON [dbo].[SampleData] ([CreateDate] ASC)
GO
CREATE NONCLUSTERED INDEX [IX_SampleData_UserID] ON [dbo].[SampleData] ([UserID] ASC)
GO
CREATE NONCLUSTERED INDEX [IX_SampleUser_Username] ON [dbo].[SampleUser] ([Username] ASC)
GO
[/sql]

[sql] — sysadmin user
CREATE LOGIN [test_sa] WITH PASSWORD=N’#######’, DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [test_sa]
GO

— regular user
CREATE LOGIN [User10] WITH PASSWORD=N’#######’, DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDB]
GO
CREATE USER [User10] FOR LOGIN [User10]
GO
USE [TestDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [User10]
GO [/sql]

[sql] — run using test_sa account
— query 1
SELECT *
FROM SampleData d
INNER JOIN SampleUser u on d.UserID=u.UserID
WHERE u.Username = ‘User10’
GO

— query 2
SELECT *
FROM SampleData d
WHERE CreateDate > ‘2016-02-03 19:59:47.560’

— run using User10 account
— query 3
SELECT *
FROM SampleData d
INNER JOIN SampleUser u on d.UserID=u.UserID
WHERE u.Username = user_name()
GO

— query 4
SELECT *
FROM SampleData d
INNER JOIN SampleUser u on d.UserID=u.UserID
WHERE u.Username = user_name()
AND CreateDate > ‘2016-02-03 19:59:47.560’
GO [/sql]

[sql] CREATE FUNCTION dbo.fn_securitypredicateUser (@UserID int)
RETURNS TABLE
WITH Schemabinding
AS
RETURN SELECT 1 AS [fn_securitypredicateUser_result]
FROM dbo.SampleUser SU
WHERE (SU.UserID=@UserID AND SU.Username = user_name())
OR IS_SRVROLEMEMBER(N’sysadmin’) = 1
GO

CREATE SECURITY POLICY fn_security
ADD FILTER preDICATE dbo.fn_securitypredicateUser(UserID)
ON dbo.SampleData
GO [/sql]

[sql] — run using test_sa account
— query 1
SELECT *
FROM SampleData d
INNER JOIN SampleUser u on d.UserID=u.UserID
WHERE u.Username = ‘User10’
GO

— query 2
SELECT *
FROM SampleData d
WHERE CreateDate > ‘2016-02-03 19:59:47.560’

— run using User10 account
— query 3
SELECT *
FROM SampleData d
INNER JOIN SampleUser u on d.UserID=u.UserID
WHERE u.Username = user_name()
GO

— query 4
SELECT *
FROM SampleData d
INNER JOIN SampleUser u on d.UserID=u.UserID
WHERE u.Username = user_name()
AND CreateDate > ‘2016-02-03 19:59:47.560’
GO [/sql]