余小章 @ 大內殿堂

本文連結

• #心得
• #TSQL指令演練步驟
• #開發專案演練

開發環境

• VS 2017 Enterprise 15.9.1
• Entity Framework 6.2.0
• SQL Server 2016 Express Localdb

參考以下學習遮罩的使用方式

動態資料遮罩

[SQL SERVER]SQL2016-動態資料遮罩

[Day11] 資料使用安全(SQL資料遮罩)

MS SQL 資料遮罩

心得

1.遮罩設定

2.帳號有 sysadmin 和 db_owner 角色時，遮罩無效。

3.要有結構描述的標準 CREATE TABLE 和 ALTER 權限，就能建立含有動態資料遮罩。

4.新增、取代或移除資料行遮罩則需要資料表的 ALTER ANY MASK 權限和 ALTER 權限。

5.防止使用者檢視敏感機密資料，並非資料加密處理，無法保護資料；多查詢幾次，仍可推敲出結果

TSQL指令演練步驟

在 SSMS 執行語法，確定可行

建立資料表以及資料

USE [Lab.EF6.DynamicDataMask]
CREATE TABLE [dbo].[Customer] (
    [ID]         VARCHAR (11)                                         NOT NULL,
    [Name]       NVARCHAR (10)                                        NULL,
    [Birthday]   DATE MASKED WITH (FUNCTION = 'default()')            NULL,
    [Marriage]   CHAR (1) MASKED WITH (FUNCTION = 'default()')        NULL,
    [Email]      VARCHAR (50)                                         NULL,
    [Tel]        VARCHAR (20) MASKED WITH (FUNCTION = 'default()')    NULL,
    [Salary]     NUMERIC (13, 2) MASKED WITH (FUNCTION = 'default()') NULL,
    [CreditCard] VARCHAR (19)                                         NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

INSERT [Lab.EF6.DynamicDataMask].dbo.Customer(ID, Name, Birthday, Marriage, Email, Tel, Salary, CreditCard) VALUES (N'A123456789', N'小賴', '1961-06-07', N'0', N'a01@aa.bb', N'02-77203699', 3000000.00, N'1234-1234-5678-0001')


INSERT [Lab.EF6.DynamicDataMask].dbo.Customer(ID, Name, Birthday, Marriage, Email, Tel, Salary, CreditCard) VALUES (N'B123456789', N'小蔡', '1956-08-31', N'1', N'b01@aa.bb', N'03-77203699', 2000000.00, N'1234-1234-5678-0002')


INSERT [Lab.EF6.DynamicDataMask].dbo.Customer(ID, Name, Birthday, Marriage, Email, Tel, Salary, CreditCard) VALUES (N'C123456789', N'小明', '1942-03-16', N'1', N'c01@aa.bb', N'04-77203699', 1000000.00, N'1234-1234-5678-0003')

產生可以登入的帳號並授予權限

USE [master]
CREATE LOGIN UnmaskId WITH PASSWORD = N'pass@w0rd1~'
CREATE LOGIN MaskId WITH PASSWORD = N'pass@w0rd1~'
USE [Lab.EF6.DynamicDataMask]
CREATE USER UnmaskId FROM LOGIN UnmaskId
CREATE USER MaskId FROM LOGIN MaskId
--ALTER ROLE db_datareader ADD MEMBER UnmaskId
--ALTER ROLE db_datawriter ADD MEMBER UnmaskId
----
--ALTER ROLE db_datareader ADD MEMBER MaskId
--ALTER ROLE db_datawriter ADD MEMBER MaskId
GRANT SELECT ON Customer TO UnmaskId
GRANT SELECT ON Customer TO MaskId
--取消遮罩
GRANT UNMASK TO UnmaskId
--恢復遮罩
REVOKE UNMASK TO MaskId

USE [Lab.EF6.DynamicDataMask]
CREATE USER UnmaskId WITHOUT LOGIN
CREATE USER MaskId WITHOUT LOGIN
GRANT SELECT ON Customer TO UnmaskId
GRANT SELECT ON Customer TO MaskId

--取消遮罩
GRANT UNMASK TO UnmaskId

--恢復遮罩
REVOKE UNMASK TO MaskId

EXEC AS USER = 'UnmaskId'
SELECT
  *
FROM Customer
REVERT


EXEC AS USER = 'MaskId'
SELECT
  *
FROM Customer
REVERT

[TestMethod]
public void 切換沒有遮罩帳號()
{
    using (var dbContext = new TestDbContext())
    {
        if (dbContext.Database.Connection.State == ConnectionState.Closed)
        {
            dbContext.Database.Connection.Open();
        }
 
        dbContext.Database.ExecuteSqlCommand("EXECUTE AS USER = 'UnmaskId'");
        var customers = dbContext.Customers.AsNoTracking().ToList();
 
        dbContext.Database.ExecuteSqlCommand("REVERT");
        Assert.AreEqual("02-77203699", customers[0].Tel);
    }
}

把建立 TestDbContext 的動作變成一個靜態方法

public partial class TestDbContext : DbContext
{
    public TestDbContext(string connectionStringName = "TestDbContext") : base(connectionStringName)
    {
    }
 
    public static TestDbContext CreateDbContext(string connectionStringName = "TestDbContext")
    {
        var dbContext = new TestDbContext(connectionStringName);
        if (dbContext.Database.Connection.State == ConnectionState.Closed)
        {
            dbContext.Database.Connection.Open();
        }
 
        return dbContext;
    }
}

看起來就乾淨些

[TestMethod]
public void 切換遮罩帳號()
{
    using (var dbContext = TestDbContext.CreateDbContext())
    {
        dbContext.Database.ExecuteSqlCommand("EXECUTE AS USER = 'MaskId'");
        var customers = dbContext.Customers.AsNoTracking().ToList();
 
        dbContext.Database.ExecuteSqlCommand("REVERT");
        Assert.AreEqual("xxxx", customers[0].Tel);
    }
}

使用遮罩帳號登入

切換身分看起來很不優雅，直接把使用組態設定的定義登入

[TestMethod]
public void 登入遮罩帳號()
{
    using (var dbContext = TestDbContext.CreateDbContext("TestDbContext_MaskId"))
    {
        var customers = dbContext.Customers.AsNoTracking().ToList();
        Assert.AreEqual("xxxx", customers[0].Tel);
    }
}

我用的三個連線字串定義

  <connectionStrings>
    <add name="TestDbContext" 
         connectionString="data source=(localdb)\mssqllocaldb;initial catalog=Lab.EF6.DynamicDataMask;integrated security=true;MultipleActiveResultSets=True;App=EntityFramework" 
         providerName="System.Data.SqlClient" />
    <add name="TestDbContext_UnmaskId" 
         connectionString="data source=(localdb)\mssqllocaldb;initial catalog=Lab.EF6.DynamicDataMask;integrated security=false;User Id=UnmaskId;Password=pass@w0rd1~;MultipleActiveResultSets=True;App=EntityFramework" 
         providerName="System.Data.SqlClient" />
    <add name="TestDbContext_MaskId" 
         connectionString="data source=(localdb)\mssqllocaldb;initial catalog=Lab.EF6.DynamicDataMask;integrated security=false;User Id=MaskId;Password=pass@w0rd1~;MultipleActiveResultSets=True;App=EntityFramework" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>

若有謬誤，煩請告知，新手發帖請多包涵

Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET