862
Part VI: Securing Your SQL Server (Jose)
■ (^) Fetch procedure: Checks the security procedure for permission to return the data
■ Triggers: Call the security procedure to check the user’s right to perform the DML
statement on the requested rows
To demonstrate this design, the following topics implement row-level security in the
AdventureWorks2012 database. Each person in the Person table can be granted read,
write, or administer privileges for each location’s inventory and sales data. With this row-
based security scheme, security can be checked by means of a stored procedure, function,
Windows login, and trigger.
Although this is only an example of how to construct row-level security, the concepts here
should help you design and develop your own custom row-level security solution.
The Security Table
The Security table serves as a many-to-many associative table (junction table) between
the Person and Address tables. The security levels determine the level of access:0 or no row: 0 access
1 : Read access
2 : Write access
3 : Admin access
Alternatively, three-bit columns could be used for read, write, and administer rights, but
the privileges are cumulative, so an integer column seems appropriate.The security table has two logical foreign keys. The foreign key to the address table is
handled by a standard foreign-key constraint; however, the reference to the person table
should allow only contacts who are fl agged as employees, so a trigger is used to enforce
that complex referential-integrity requirement. The security assignment is meaningless
without its contact or location, so both foreign keys are cascading deletes. A constraint is
applied to the security-level column to restrict any entry to the valid security codes (0–3),
and a unique constraint ensures that a person may have only one security code per address:USE Adventureworks2012;CREATE TABLE dbo.Security (
SecurityID INT IDENTITY(1,1) NOT NULL
PRIMARY KEY NONCLUSTERED,
PersonID INT NOT NULL
REFERENCES Person.Person(BusinessEntityID) ON DELETE CASCADE,
AddressID INT NOT NULL
REFERENCES Person.Address(AddressID) ON DELETE CASCADE,
SecurityLevel INT NOT NULL DEFAULT 0
);c35.indd 862c35.indd 862 7/31/2012 9:59:40 AM7/31/2012 9:59:40 AM
http://www.it-ebooks.info