Microsoft® SQL Server® 2012 Bible

869

Chapter 35: Row-Level Security

35

UPDATE dbo.Security

SET SecurityLevel = @SecurityLevel

WHERE PersonID = @PersonID

AND PersonID = @PersonID;

IF @@ERROR <> 0 RETURN -100;

END;

-- Insert

ELSE

BEGIN;

INSERT dbo.Security

(PersonID,AddressID, SecurityLevel)

VALUES (@PersonID, @AddressID, @SecurityLevel);

IF @@ERROR <> 0 RETURN -100;

END;

RETURN;

The following script tests the new procedure’s capability to modify security permission for

a person/address combination. The fi rst command adds security for person 120:

EXEC pSecurity_Assign

@PersonCode = '120',

@AddressCode = '1',

@SecurityLevel = 2;

EXEC pSecurity_Fetch

@PersonCode = '120';

Result:

PersonCode AddressCode SecurityLevel

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

120 1 2

The following two commands issue new security permissions and edit existing security

permissions. The third command fetches the security permissions for person code 120:

EXEC pSecurity_Assign

@PersonCode = '120',

@AddressCode = '2',

@SecurityLevel = 1;

EXEC pSecurity_Assign

@PersonCode = '120',

@AddressCode = '1',

@SecurityLevel = 3;

EXEC pSecurity_Fetch

@PersonCode = '120';

Result:

PersonCode AddressCode SecurityLevel

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

http://www.it-ebooks.info