The value of row-level security is actually allowing or blocking reads and writes. These procedures, 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 compares that value with the value of the requested security level. If the person's permission level is sufficient, 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 different values. Use the p_Security_Fetch ...