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.
For the Security table to be viewed, the first 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, 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 (a.AddressID = @AddressCode OR @AddressCode IS NULL) AND (p.BusinessEntityID = @PersonCode OR @PersonCode IS NULL);
Adding or altering rows in the Security table, which serves as a junction between person and location, in keeping with the theme of server-side code, the pSecurity_Assign stored procedure assigns a security level to the person/address combination. There's nothing new about this procedure. It accepts a person code and address code, and then performs the insert:
CREATE PROCEDURE pSecurity_Assign @PersonCode VARCHAR(15), @AddressCode VARCHAR(15), @SecurityLevel INT AS SET NOCOUNT ON; DECLARE @PersonID int, @AddressID int; -- Get PersonID SELECT @PersonID = BusinessEntityID FROM Person.Person WHERE ...