Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 37 of 212

27 C H A P T E R 2 | Better security SalesRep int, ProductId int, Qty int, Price numeric(8,2) ); GO Additionally, the inline table function changes to reflect the single login, as shown in Example 2-9. Notice the parameter's data type is now int instead of sysname to match the column in the table shown in Example 2-8. In addition, the predicate in the function now uses the SESSION_CONTEXT system function and outputs the result as an int data type to match the input parameter's data type. Example 2-9: Creating an inline table function for an application using a single login CREATE FUNCTION dbo.fn_Orders(@SalesRep AS int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_Orders_result WHERE @SalesRep = CONVERT(SESSION_CONTEXT(N'UserId') AS int); GO You must also modify your application code to use the sp_set_session_context system stored procedure, which sets the value returned by the SESSION_CONTEXT system function, as shown in Example 2-10. This system stored procedure supports two parameters—the key name of the value to add and the value to store for this key. In this example, the key name is UserID and its value is set to the UserId of the application user, which the application passes into the stored procedure by using the @UserId input parameter. Applications can call sp_set_session_context in line within the stored procedures or directly at application startup when the connection is created. Example 2-10: Using the sp_set_session_context system stored procedure CREATE PROCEDURE GetOrder @OrderId int, @UserId int AS EXEC sp_set_session_context @key=N'UserId', @value=@UserId; SELECT * FROM Orders WHERE OrderId = @OrderId; GO Creating security policies After creating inline table-valued functions, you next bind them to the table that you want to secure. To do this, use the CREATE SECURITY POLICY command, as shown in Example 2-11. In the security policy, you can define a filter predicate by specifying the inline table-valued function name, the column name to pass to the function, and the table to which the policy applies.

Articles in this issue

Archives of this issue

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