874
Part VI: Securing Your SQL Server (Jose)
IF dbo.fSecurityCheckNT('2', 3) = 0
BEGIN;
RAISERROR('Security Violation', 16,1);
ROLLBACK TRANSACTION;
RETURN;
END;The function did not return an error, so you can complete the transaction.The Security-Check Trigger
The security-check stored procedure and function both work well when included within a
stored procedure, such as the fetch, addnew, update, or delete procedures mentioned
in the beginning of this chapter; but to implement row-based security in a database that
allows access from views, ad hoc queries, or direct table DML statements, you must handle
the row-based security with a trigger. The trigger can prevent updates, however, it cannot
check data reads. If row-based security is a requirement for reads, then all reads must go
through a stored procedure or a view.The following trigger is similar to the security-check function. It differs in that the
trigger must allow for multiple orders with potential multiple addresses. The joins must
match up [SalesOrderHeader] rows and their address with the user’s security level
for each location. The join can go directly from the PersonLogin table to the Security
table. Because this is an insert and update trigger, any security level below 2 for any
order being written will be rejected and a security-violation error will be raised. The
rollback transaction command will undo the original DML command that fi red the
trigger and all other modifi cations made as part of the same transaction:CREATE TRIGGER OrderSecurity ON Sales.SalesOrderHeader
AFTER INSERT, UPDATE
AS
IF @@ROWCOUNT = 0 RETURN;
IF EXISTS (
SELECT *
FROM dbo.Security AS s
INNER JOIN dbo.PersonLogin AS pl
ON s.PersonID = pl.PersonID
INNER JOIN Inserted AS i
ON i.BillToAddressID = s.AddressID
WHERE pl.NTLogin = suser_sname()
AND s.SecurityLevel < 2 )
BEGIN;
RAISERROR('Security Violation', 16,1);
ROLLBACK TRANSACTION;
END;c35.indd 874c35.indd 874 7/31/2012 9:59:41 AM7/31/2012 9:59:41 AM
http://www.it-ebooks.info