Shulou(Shulou.com)06/01 Report--

This article shares with you the content of a case study of SQLServer dynamic masks. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Dynamic data Mask (DDM) is a new feature introduced by SQL Server 2016. The goal is to restrict people who do not have access to private information. Administrator users can decide which fields need to be masked, so how to mask without changing the application code? Also make sure that no matter how you access the data, it is consistent.

This is the first feature introduced in the Azure SQL database, which is tested by users on the cloud and has been migrated to local products. I think many other new features will follow this approach (cloud-local).

It is important to note that, like my previous row-level data security, these are all related to data security (recommended course: MySQL tutorial)

Column data mask

First create a table with a mask on some data. I will add a mask to a field that starts in the table definition. Note that the way to do this is to use the format "mask with ()" after the data type, but before NULL and the default option, include FUNCTION = "in parentheses, which specifies our function. Inside the quotation marks, we specify the mask. The CREATE TABLE statement is as follows

CREATE TABLE MyTable (MySSN VARCHAR (10) MASKED WITH (FUNCTION = 'default ()') DEFAULT ('0000000000'), MyName VARCHAR (200C) DEFAULT (''), MyEmail VARCHAR DEFAULT (''), MyInt int) GOINSERT dbo. MyTable (MySSN, MyName, MyEmail, MyInt) VALUES ('1234567890,' Steve Jones', 'SomeSteve@SomeDomain.com', 10)

If the creator queries this table, you will see a normal table. I got all the data when it was inserted. This is because I am an authorized user. Similarly, users with dbo privileges (db_owner or sysadmin roles) will not see masked data. Now create a normal user that does not have high privileges. Of course, I need to grant normal SQL Server permissions to view the data in the table.

CREATE USER mytest WITHOUT LOGINGRANT SELECT ON mytable TO mytest

Now we can use this user to query the table and see what the difference is.

We can see that the first column contains mask data. Only x appears in the location of the data. This does what I want, which is to hide data from unprivileged users. Note that the data on the disk has not changed. Data is masked only when it is returned to an unprivileged user.

I can see this in the last part of the implementation plan. I need to grant the user permission to view the plan, but when I do so, I see the user's plan, using the same query as above.

I can define other types of masks on the table. There is a custom mask format that allows you to control what is displayed, a mask for an email address, and a random digital mask. We will discuss these issues in detail in another article.

Now you can add a mask to another column, such as the message MyEmail column, and you can use the format of the message mask as follows:

ALTER TABLE dbo.MyTable ALTER COLUMN MyEmail VARCHAR (250) MASKED WITH (FUNCTION='email ()') GO

Then the query results are as follows:

Multiple sum columns can also be masked

CREATE TABLE MySecondTable (MyEmail VARCHAR) MASKED WITH (FUNCTION= 'email ()'), MySSN VARCHAR (10) MASKED WITH (FUNCTION= 'default ()'), MyID INT MASKED WITH (FUNCTION= 'random (1) GOINSERT MySecondTable VALUES (' myname@mydomain.com', '1234567890), (' abrother@mycorp.com', '0123456789', 555), ('somesister@somecompany.org',' 9876543210', 999)

The query results are as follows:

As we can see, I get different masks from different rows, and each mask is applied to the data of a particular row.

Allow users to see mask real data

There is a new DDM permission in SQL Server 2016. This is the UNMASK permission, which is granted like any other permission. Let's see how this works. I will create a new user with the same permissions as the existing user. Then I will query the table.

The result is similar to that before, and then we are using authorization to open the mask.

Now we can see that the data is displayed in the same way as privileged users. For NewTester users, all data is "unmasked".

However, there is a downside to this. UNMASK permissions are granted to users within the scope of the database. There is no granularity by table or column. If users have UNMASK, they can view all data with SELECT permissions in tables stored in the database. We can see this by querying the first table using Newtest.

Remove mask

The code is as follows:

ALTER TABLE dbo.MySecondTable ALTER COLUMN MySSN DROP MASKED

Once I do this, the user will see the real data directly.

The data of the MySSN column is unmasked, but the data of MyEmail and MyID is still masked

Dynamic data masking is a good new feature designed to more easily protect data from unprivileged users. This can be done in the database without changing any application code, allowing you to shield sensitive data from application users with minimal cost and effort. I would also like to remind you that this is not a real security feature. Data stored on disks and tables is not changed in any way. This is still plain text data, and if users can query the system, they can still potentially query your data and find its value.

Thank you for reading! This is the end of the case study on SQLServer dynamic mask. I hope the above content can be helpful to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.