Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 35 of 212

25 C H A P T E R 2 | Better security Creating inline table functions The method by which users connect to a database determines how you need to write the inline table function. In an application that connects users to the database with their individual Windows or SQL login, the function must directly match each user's login to a value within the table. On the other hand, in an application that uses a single SQL login for authentication, you must modify the application to set the session context to use a database value that sets the row-level filtering as we explain in more detail later in this section. Either way, when you create a row-level filtering inline table function, you must enable SCHEMABINDING and the function must return a column that contains a value of 1 (or any other valid value) when the user can view the row. Note You can implement RLS on existing tables without rebuilding the tables because the inline table function that handles the filtering is a separate object in the database, which you then bind to the table after you create the function. Consequently, you can quickly and easily implement RLS in existing applications without requiring significant downtime. Application using one login per user When your application logs into the database engine by using each user's Windows or SQL login, your inline table function needs only to compare the user's login against a table in the database to determine whether the user has access to the requested rows. As an example, let's say you have an Orders application for which you want to use RLS to restrict access to order information to the person entering the order. First, your application requires an Order table, such as the one shown in Example 2-4. When your application writes a row into this table, it must store the user's login in the SalesRep column. Example 2-4: Creating an Orders table CREATE TABLE Orders ( OrderId int, SalesRep sysname ); Your next step is to create an inline table function like the one shown in Example 2-5. In this example, when a user queries the Orders table, the value of the SalesRep column passes into the @SalesRep parameter of the fn_Orders function. Then, row by row, the function compares the @SalesRep parameter value to the value returned by the USER_NAME() system function and returns a table containing only the rows for which it finds a match between the two values. Example 2-5: Creating an inline table function to restrict access by user login CREATE FUNCTION dbo.fn_Orders(@SalesRep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_Orders_result WHERE @SalesRep = USER_NAME(); GO

Articles in this issue

Archives of this issue

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