Skip to content

Instantly share code, notes, and snippets.

@Philo
Last active October 31, 2023 15:50
Show Gist options
  • Save Philo/e219433d7571f214a9537d28755b67a5 to your computer and use it in GitHub Desktop.
Save Philo/e219433d7571f214a9537d28755b67a5 to your computer and use it in GitHub Desktop.
Generate EF migration to add or drop dynamic data masking on a column
// https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15
public static class DynamicMaskingMigrationHelperExtension
{
// Apply a data masking function to a column that previously had no mask function applied
public static void AddDynamicColumnMask(this MigrationBuilder migrationBuilder, string tableName, string columnName, string maskFunction = "default()")
{
if(migrationBuilder.IsSqlServer())
{
migrationBuilder.Sql($@"ALTER TABLE [{tableName}] ALTER COLUMN [{columnName}] ADD MASKED WITH(FUNCTION = '{maskFunction}');");
}
}
// Alter must be used when a column has existing mask function applied, I'm not aware of a way to detect or easily query this ahead of time
public static void AlterDynamicColumnMask(this MigrationBuilder migrationBuilder, string tableName, string columnName, string columnDataType, string maskFunction = "default()")
{
if (migrationBuilder.IsSqlServer())
{
migrationBuilder.Sql($@"ALTER TABLE [{tableName}] ALTER COLUMN [{columnName}] {columnDataType} MASKED WITH(FUNCTION = '{maskFunction}');");
}
}
// Removing a mask function from a column
public static void DropDynamicColumnMask(this MigrationBuilder migrationBuilder, string tableName, string columnName)
{
if (migrationBuilder.IsSqlServer())
{
migrationBuilder.Sql($@"ALTER TABLE [{tableName}] ALTER COLUMN [{columnName}] DROP MASKED;");
}
}
}
@Philo
Copy link
Author

Philo commented Oct 27, 2023

You can test masking as follows:

CREATE USER MaskingTestUser WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::dbo TO MaskingTestUser;
 
-- impersonate for testing:
EXECUTE AS USER = 'MaskingTestUser';

SELECT * FROM <Table>

REVERT;

@Philo
Copy link
Author

Philo commented Oct 31, 2023

GRANT UNMASK TO [App-Identity];

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment