Microsoft® SQL Server® 2012 Bible

870

Part VI: Securing Your SQL Server (Jose)

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

120 1 3

120 2 1

Checking Permissions

The value of row-level security is actually allowing or blocking reads and writes. These proce-

dures, functions, and triggers are examples of how to build row-level read/write validation.

The Security-Check Stored Procedure

The security-check stored procedure, p_SecurityCheck, is central to the row-based

security system. It’s designed to return a true or false for a security request for a person,

an address, and a requested security level.

The procedure selects the security level of the person for the given location and then com-

pares that value with the value of the requested security level. If the person’s permission

level is suffi cient, then a 1 (indicating true) is returned; otherwise, a 0 (for false) is

returned:

CREATE PROCEDURE p_SecurityCheck

@PersonCode VARCHAR(15),

@AddressCode VARCHAR(15),

@SecurityLevel INT,

@Approved BIT OUTPUT

AS

SET NOCOUNT ON;

DECLARE @ActualLevel INT = 0;

SELECT @ActualLevel = s.SecurityLevel

FROM dbo.Security AS s

INNER JOIN Person.Person AS p

ON s.PersonID = p.BusinessEntityID

INNER JOIN Person.Address AS a

ON s.AddressID = a.AddressID

WHERE p.BusinessEntityID = @PersonCode

AND a.AddressID = @AddressCode;

IF @ActualLevel < @SecurityLevel

SET @Approved = CAST(0 AS bit);

ELSE

SET @Approved = CAST(1 AS bit);

RETURN 0;

The following batch calls the p_SecurityCheck procedure and uses the @OK local variable

to capture the output parameter. When testing this from the script on the web, try several

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

http://www.it-ebooks.info