Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 36 of 212

26 C H A P T E R 2 | Better security Note The data type of the parameter in your inline table function must match the corresponding column data type in the table that you plan to secure with RLS, although it is not necessary for the parameter name to match the column name. However, managing your code is easier if you keep the names consistent. Now let's consider what happens if your database contains related information in another table, such as the OrderDetails table shown in Example 2-6. Example 2-6: Creating an OrderDetails table CREATE TABLE OrderDetails ( OrderId int, ProductId int, Qty int, Price numeric(8,2) ); GO To apply the same security policy to this related table, you must implement additional filtering by creating another inline table-valued function, such as the one shown in Example 2-7. Notice that you continue to use the USER_NAME() system function to secure the table by a user-specific login. However, this time the inline table-valued function's parameter is @OrderId, which is used in conjunction with the SalesRep column. Example 2-7: Creating an inline table function to restrict access by user login in a related table CREATE FUNCTION dbo.fn_OrderDetails(@OrderId AS int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_Orders_result FROM Orders WHERE OrderId = @OrderId AND SalesRep = USER_NAME(); GO Application using one login for all users When your application uses a single login for all users of the application, also known as an application account, you use similar logic as you do when the application passes user logins to the database. Let's continue with a similar example as the one in the previous section, but let's add some additional columns to the Orders table, as shown in Example 2-8. In this version of the Orders table, the SalesRep column has an int data type instead of the sysname data type in the earlier example. Example 2-8: Creating a variation of the Orders table CREATE TABLE Orders ( OrderId int,

Articles in this issue

Archives of this issue

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