Microsoft® SQL Server® 2012 Bible

863

Chapter 35: Row-Level Security

35

The following three commands add the constraints to the Security table:

CREATE TRIGGER PersonID_RI ON dbo.Security

AFTER INSERT, UPDATE

AS

SET NOCOUNT ON;

IF EXISTS(SELECT *

FROM Inserted

Left JOIN Person.Person

ON Inserted.PersonID = BusinessEntityID

WHERE BusinessEntityID IS NULL

OR Person.Type NOT IN ('EM','SP')

BEGIN

RAISERROR

('Foreign Key Constraint: Security.PersonID', 16, 1);

ROLLBACK TRANSACTION;

RETURN

END;

GO

ALTER TABLE dbo.Security

ADD CONSTRAINT ValidSecurityCode CHECK

(SecurityLevel IN (0,1,2,3));

ALTER TABLE dbo.Security

ADD CONSTRAINT PersonAddress UNIQUE

(PersonID, AddressID);

Assigning Permissions

Implementing row-level security requires a set of basic admin procedures to set up and

maintain the security settings. These procedures handle assigning security levels to users.

Assigning Security

For the Security table to be viewed, the fi rst procedure created is pSecurity_Fetch.

This procedure returns all the row-based security permissions, or it can be restricted to

return those permissions for a single person or a single address:

CREATE PROCEDURE pSecurity_Fetch

@AddressCode VARCHAR(15) = NULL,

@PersonCode VARCHAR(15) = NULL

AS

SET NOCOUNT ON;

SELECT p.BusinessEntityID,

a.AddressID,

c35.indd 863c35.indd 863 7/31/2012 9:59:40 AM7/31/2012 9:59:40 AM

http://www.it-ebooks.info