Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 34 of 212

24 C H A P T E R 2 | Better security Step 5: Update the application's connection string Once the tables are changed, the application needs to know to use Always Encrypted. To do this, change the application's connection string to use the new Column Encryption Setting=enabled attribute or release a new version of the application that uses the SqlCommandColumnEncryptionSetting method on the connection object within the .NET code. Using Always Encrypted in Microsoft Azure SQL Database Always Encrypted is fully supported by the SQL Database platform, as we describe in Chapter 8, "Improved Azure SQL Database." You configure Always Encrypted for a SQL Database just as you do for an on-premises SQL Server 2016 deployment by using T-SQL commands. At the time of this writing, there are no enhancements in the Microsoft Azure portal for configuring Always Encrypted in SQL Database. Row-Level Security Row-Level Security (RLS) allows you to configure tables such that users see only the rows within the table to which you grant them access. This feature limits which rows are returned to the user, regardless of which application they are using, by automatically applying a predicate to the query. You can use a filter predicate to silently filter the rows that are accessible by the user when using INSERT, UPDATE, or DELETE statements. In addition, you can use the following block predicates to block the user from writing data: AFTER INSERT, AFTER UPDATE, BEFORE UPDATE and BEFORE DELETE. These block predicates return an error to the application indicating that the user is attempting to modify rows to which the user does not have access. You implement RLS by creating an inline table function that identifies the rows accessible to users. The function you create can be as simple or complex as you need. Then you create a security policy to bind the inline table function to one or more tables. Note Although you can create a complex RLS inline table function, bear in mind that complex queries are typically slow to execute. Besides ensuring that your function properly limits access to specific rows in a table, you should take care that it does so with minimal impact to application performance. RLS is designed to simplify your application code by centralizing access logic within the database. It should be noted that, as with any RLS solution and workarounds, it is possible for users with the ability to execute arbitrary T-SQL commands to infer the existence of data that should be filtered, via side- channel attacks. Therefore, RLS is intended for scenarios where the queries that users can execute are controlled, such as through a middle-tier application. Be aware that RLS impacts all users of a database, including members of the db_owner fixed database role. Members of this role have the ability to remove the RLS configuration from tables in the database. However, by doing so, all other users again have access to all rows in the table. Note You can use branching logic in the inline table function for RLS when you need to allow members of the db_owner fixed database role to access all rows in the table.

Articles in this issue

Archives of this issue

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