871
Chapter 35: Row-Level Security
35
different values. Use the p_Security_Fetch procedure to determine possible parameters.
The following code checks whether person code 118 has administrative privileges at the
Charlotte warehouse:DECLARE @OK BIT;
EXEC p_SecurityCheck
@PersonCode = '118',
@AddressCode = '2',
@SecurityLevel = 3,
@Approved = @OK OUTPUT;
SELECT @OK;Result:0The Security-Check Function
The security-check function, fSecurityCheck, includes the same logic as the
p_Security_Check stored procedure. The advantage of a function is that it can be
used directly within an if command without a local variable being used to store the
output parameter. The function uses the same three input parameters as the stored-
procedure version and the same internal logic, but it returns the approved bit as the
return of the function, rather than as an output parameter. Here’s the function’s
code:CREATE FUNCTION dbo.fSecurityCheck (
@PersonCode VARCHAR(15),
@AddressCode VARCHAR(15),
@SecurityLevel INT)
RETURNS BIT
AS
BEGIN;
DECLARE @Approved BIT = CAST(0 AS bit);IF (SELECT 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) >= @SecurityLevel
BEGIN;
SET @Approved = CAST(1 AS bit);
END;c35.indd 871c35.indd 871 7/31/2012 9:59:40 AM7/31/2012 9:59:40 AM
http://www.it-ebooks.info