Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 40 of 212

30 C H A P T E R 2 | Better security Example 2-14: Creating a table with two masked columns CREATE TABLE [dbo].[Customer]( [CustomerId] [int] IDENTITY(1,1) NOT NULL, [TaxId] [varchar](11) MASKED WITH (FUNCTION = 'default()'), [FirstName] [nvarchar](50) MASKED WITH (FUNCTION = 'partial(3, "xyz", 1)') NULL, [LastName] [nvarchar](50) NULL, PRIMARY KEY CLUSTERED ( [CustomerId] ASC) ) ON [PRIMARY]; GO Dynamic data masking of an existing table Because dynamic data masking changes only the presentation of data returned by a query, there is no change to the underlying table structure. That means you can easily add dynamic data masking to a column in an existing table without rebuilding the table. To this, use the ALTER TABLE statement with the ALTER COLUMN and ADD MASKED arguments, as shown in Example 2-15. Example 2-15: Adding dynamic data masking to an existing table ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] ADD MASKED WITH (FUNCTION = 'default()'); Likewise, you can remove dynamic data masking quickly and easily without rebuilding a table or moving data because only metadata changes rather than the schema. You remove dynamic data masking from a column by using the ALTER TABLE statement with the ALTER COLUMN and DROP MASKED arguments, as shown in Example 2-16. Example 2-16: Removing dynamic data masking from a table ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] DROP MASKED; Understanding dynamic data masking and permissions When you use dynamic data masking, the permissions that you assign to users affect whether users see plain text values or masked values. Specifically, members of the db_owner fixed database role always see plain text values, whereas users who are not members of this role see masked data by default. If you need to grant a user permission to see plain text data in a table, you must grant the new UNMASK permission at the database level. To do this, use the GRANT UNMASK statement in the database containing the masked values, as shown in Example 2-17. Example 2-17: Granting the UNMASK permission GRANT UNMASK TO MyUser;

Articles in this issue

Archives of this issue

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