Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 38 of 212

28 C H A P T E R 2 | Better security Example 2-11: Creating a security policy CREATE SECURITY POLICY dbo.OrderPolicy ADD FILTER PREDICATE dbo.fn_Orders(SalesRep) ON dbo.Orders WITH (STATE=ON); You can specify multiple filter predicates in the security policy when you want to filter rows in different tables, as shown in Example 2-12. Example 2-12: Creating one security policy for multiple tables CREATE SECURITY POLICY dbo.OrderPolicy ADD FILTER PREDICATE dbo.fn_Orders(SalesRep) ON dbo.Orders, ADD FILTER PREDICATE dbo.fn_OrderHistory(OrderId) ON dbo.OrderHistory WITH (STATE = ON); Using block predicates When you use the filter predicate as shown in the examples in the preceding section, the security policy affects "get" operations only. Users are still able to insert rows that they cannot subsequently query. They can also update rows they can currently access and even change the rows to store values that block further access. You must decide whether your application should allow this behavior or should prevent users from inserting rows to which they do not have access. To do this, use a block predicate in addition to a filter predicate. As shown in Example 2-13, you can use both filter and block predicates in a security policy. In this example, the security policy allows users to query for rows using the SELECT statement and returns only rows to which the user has access. A user can insert new rows into the table as long as the SalesRep value matches the user's login. Otherwise, the insert fails and returns an error to the user. Similarly, an update to the table succeeds as long as the user doesn't attempt to change the value of the SalesRep column. In that case, the update fails and returns an error to the user. Example 2-13: Using block and filter predicates in a single security policy CREATE SECURITY POLICY dbo.OrderPolicy ADD FILTER PREDICATE dbo.fn_Orders(SalesRep) ON dbo.Orders, ADD BLOCK PREDICATE dbo.fn_Orders(SalesRep) ON dbo.Orders AFTER INSERT, ADD BLOCK PREDICATE dbo.fn_Orders(SalesRep) ON dbo.Orders AFTER UPDATE WITH (STATE = ON); Note You can use a filter predicate to prevent users from updating or deleting records they cannot read, but the filter is silent. By contrast, the block predicate always returns an error when performing these operations.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016