Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 39 of 212

29 C H A P T E R 2 | Better security Using RLS in SQL Database You can use RLS in SQL database by using the same T-SQL commands described in this chapter. At the time of this writing, you cannot use the Azure portal to implement RLS. Dynamic data masking When you have a database that contains sensitive data, you can use dynamic data masking to obfuscate a portion of the data unless you specifically authorize a user to view the unmasked data. To mask data, you can use one of the following four masking functions to control how users see the data returned by a query: Default Use this function to fully mask values by returning a value of XXXX (or fewer Xs if a column length is less than 4 characters) for string data types, 0 for numeric and binary data types, and 01.01.2000 00:00:00.0000000 for date and time data types. Email Use this function to partially mask email addresses like this: This pattern masks not only the email address but also the length of the email address. Partial Use this function to partially mask values by using a custom definition requiring three parameters as described in the following table: Parameter Description Prefix Number of starting characters to display, starting from the first character in the value. Padding Value to be displayed between the prefix and suffix characters. Suffix Number of ending characters to display, starting from the last character in the value. Random Use this function to fully mask numeric values by using a random value between a lower and upper boundary that you specify. Random function may display unmasked data The Random() data-masking function may on occasion display the actual value that is stored in the table. This behavior is the result of using a random value that could match the value to mask if it is within the specified range. You should consider whether the business rules of your application allow for this behavior before using this masking function. Whenever possible, use a range of values outside the possible range of values to mask to ensure that there is no possibility of an accidental data leak. While it is possible that the random value will return the actual value, there is no way of knowing that the displayed random value is in fact the actual value without knowing the actual value. Dynamic data masking of a new table To configure dynamic data masking for a new table, use the CREATE TABLE statement with the MASKED WITH argument, as shown in Example 2-14. In this example, the default() function masks the TaxId column for complete masking, and the partial() function masks the FirstName column by displaying its first three characters and its final character and replacing the remaining characters with xyz.

Articles in this issue

Archives of this issue

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