Обнаружение и классификация данных в SqlClient
Область применения: платформа .NET Framework
.NET Standard
Набор функций обнаружения и классификации данных SQL служит для обнаружения конфиденциальных данных в базах данных, их классификации, назначения им меток и создания отчетов. SqlClient включает API, предоставляющий сведения об обнаружении и классификации данных только для чтения, если базовый источник поддерживает эту функцию. Доступ к этим сведениям осуществляется через SqlDataReader.
Microsoft.Data.SqlClient v2.1.0 добавляет поддержку информации Sensitivity Rank
согласно Классификации данных. Sensitivity Rank
— это идентификатор, основанный на предварительно заданном наборе значений, определяющих ранг конфиденциальности. Он может использоваться другими службами, например службой "Расширенная защита от угроз", для обнаружения аномалий в зависимости от их ранга. Следующие API Классификации данных теперь доступны в пространстве имен Microsoft.Data.SqlClient.DataClassification:
Пример кода
// New in Microsoft.Data.SqlClient v2.1.0
public enum SensitivityRank
{
NOT_DEFINED = -1,
NONE = 0,
LOW = 10,
MEDIUM = 20,
HIGH = 30,
CRITICAL = 40
}
public sealed class SensitivityClassification
{
// Returns the sensitivity rank for the query associated with the active 'SqlDataReader'.
// New in Microsoft.Data.SqlClient v2.1.0
public SensitivityRank SensitivityRank;
// Returns the labels collection for this 'SensitivityClassification' Object
public ReadOnlyCollection<Label> Labels;
// Returns the information types collection for this 'SensitivityClassification' Object
public ReadOnlyCollection<InformationType> InformationTypes;
// Returns the column sensitivity for this 'SensitivityClassification' Object
public ReadOnlyCollection<ColumnSensitivity> ColumnSensitivities;
}
public sealed class SensitivityProperty
{
// Returns the sensitivity rank for this 'SensitivityProperty' Object
// New in Microsoft.Data.SqlClient v2.1.0
public SensitivityRank SensitivityRank;
// Returns the label for this 'SensitivityProperty' Object
public Label Label;
// Returns the information type for this 'SensitivityProperty' Object
public InformationType InformationType;
}
public sealed class Label
{
// Gets the name for this 'Label' object
public string Name;
// Gets the ID for this 'Label' object
public string Id;
}
public sealed class InformationType
{
// Gets the name for this 'InformationType' object
public string Name;
// Gets the ID for this 'InformationType' object
public string Id;
}
public sealed class ColumnSensitivity
{
// Returns the list of sensitivity properties as received from Server for this 'ColumnSensitivity' information
public ReadOnlyCollection<SensitivityProperty> SensitivityProperties;
}
Примечание.
Microsoft.Data.SqlClient считывает информацию Sensitivity Rank
только в том случае, если SQL Server поддерживает Классификацию данных с ранжированием. Для серверов используется старая версия Классификации данных без ранжирования, и запросы возвращают значение ранга NOT DEFINED.
В этом примере приложения показано, как получить доступ к свойствам классификации данных SqlDataReader.
using System;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.DataClassification;
class Program
{
// Name of the temporary table created for this sample program.
static string tableName = "SQLCLIENT_DATA_DISCOVERY_CLASSIFICATION";
public static void Main()
{
// To avoid storing the connection string in your code, you can retrieve it from a configuration file.
string connectionString = "Data Source=localhost; Integrated Security=true; Initial Catalog=AdventureWorks;";
// Open a connection to the AdventureWorks database.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
try
{
// Check if the target SQL Server supports Data Discovery and Classification.
if (DataClassificationSupported(connection))
{
// Create the temporary table and retrieve its Data Discovery and Classification information.
// Set rankEnabled to be true if testing with rank information.
CreateTable(connection, rankEnabled : true);
RunTests(connection, rankEnabled : true);
}
}
finally
{
// Drop the temporary table.
DropTable(connection);
}
}
}
/// <summary>
/// Verifies if SQL Data Discovery and Classification feature is available on the target server.
/// </summary>
/// <param name="connection">The SqlConnection to work with.</param>
/// <returns>True if the target SQL Server supports the feature and false otherwise.</returns>
public static bool DataClassificationSupported(SqlConnection connection)
{
try
{
SqlCommand command = new SqlCommand(null, connection);
command.CommandText = "SELECT * FROM SYS.SENSITIVITY_CLASSIFICATIONS";
command.ExecuteNonQuery();
}
catch (SqlException e)
{
// Error 208: Object Not Found
if (e.Errors != null && e.Errors[0].Number == 208)
{
Console.WriteLine("This feature is not supported on the target SQL Server.");
return false;
}
}
return true;
}
/// <summary>
/// Creates a temporary table for this sample program and sets tags for Sensitivity Classification.
/// </summary>
/// <param name="connection">The SqlConnection to work with.</param>
/// <param name="rankEnabled">True if rank information is enabled and false otherwise</param>
private static void CreateTable(SqlConnection connection, bool rankEnabled = false)
{
SqlCommand command = new SqlCommand(null, connection);
// Creates table for storing Supplier data.
command.CommandText = $"CREATE TABLE {tableName} ("
+ "[Id] [int] IDENTITY(1,1) NOT NULL,"
+ "[CompanyName] [nvarchar](40) NOT NULL,"
+ "[ContactName] [nvarchar](50) NULL,"
+ "[ContactTitle] [nvarchar](40) NULL,"
+ "[City] [nvarchar](40) NULL,"
+ "[CountryName] [nvarchar](40) NULL,"
+ "[Phone] [nvarchar](30) MASKED WITH (FUNCTION = 'default()') NULL,"
+ "[Fax] [nvarchar](30) MASKED WITH (FUNCTION = 'default()') NULL)";
command.ExecuteNonQuery();
if (rankEnabled)
{
// Set Sensitivity Classification tags for table columns with rank information
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".CompanyName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Company Name', INFORMATION_TYPE_ID='COMPANY', RANK=LOW)";
command.ExecuteNonQuery();
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".ContactName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Person Name', INFORMATION_TYPE_ID='NAME', RANK=LOW)";
command.ExecuteNonQuery();
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".Phone WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT', RANK=MEDIUM)";
command.ExecuteNonQuery();
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".Fax WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT', RANK=MEDIUM)";
command.ExecuteNonQuery();
}
else
{
// Set Sensitivity Classification tags for table columns without rank information
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".CompanyName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Company Name', INFORMATION_TYPE_ID='COMPANY')";
command.ExecuteNonQuery();
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".ContactName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Person Name', INFORMATION_TYPE_ID='NAME')";
command.ExecuteNonQuery();
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".Phone WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT')";
command.ExecuteNonQuery();
command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
+ ".Fax WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT')";
command.ExecuteNonQuery();
}
}
/// <summary>
/// Run query to fetch result set from target table.
/// </summary>
/// <param name="connection">The SqlConnection to work with.</param>
/// <param name="rankEnabled">True if rank information is enabled and false otherwise</param>
private static void RunTests(SqlConnection connection, bool rankEnabled = false)
{
SqlCommand command = new SqlCommand(null, connection);
command.CommandText = $"SELECT * FROM {tableName}";
using (SqlDataReader reader = command.ExecuteReader())
{
PrintSensitivityClassification(reader, rankEnabled);
}
}
/// <summary>
/// Prints Sensitivity Classification data as received in the result set.
/// </summary>
/// <param name="reader">The SqlDataReader to work with.</param>
/// <param name="rankEnabled">True if rank information is enabled and false otherwise</param>
private static void PrintSensitivityClassification(SqlDataReader reader, bool rankEnabled = false)
{
if (reader.SensitivityClassification != null)
{
for (int columnPos = 0; columnPos < reader.SensitivityClassification.ColumnSensitivities.Count; columnPos++)
{
foreach (SensitivityProperty sp in reader.SensitivityClassification.ColumnSensitivities[columnPos].SensitivityProperties)
{
if (sp.Label != null)
{
Console.WriteLine($"Labels received for Column : {columnPos}");
Console.WriteLine($"Label ID: {sp.Label.Id}");
Console.WriteLine($"Label Name: {sp.Label.Name}");
Console.WriteLine();
}
if (sp.InformationType != null)
{
Console.WriteLine($"Information Types received for Column : {columnPos}");
Console.WriteLine($"Information Type ID: {sp.InformationType.Id}");
Console.WriteLine($"Information Type: {sp.InformationType.Name}");
Console.WriteLine();
}
Console.WriteLine($"Sensitivity Rank: {sp.SensitivityRank.ToString()}");
}
}
Console.Writeline($"reader.SensitivityClassification.SensitivityRank : {reader.SensitivityClassification.SensitivityRank.ToString()}");
}
}
/// <summary>
/// Deletes the table created for this sample program.
/// </summary>
/// <param name="connection">The SqlConnection to work with.</param>
private static void DropTable(SqlConnection connection)
{
SqlCommand command = new SqlCommand(null, connection);
command.CommandText = $"DROP TABLE {tableName}";
command.ExecuteNonQuery();
}
}
См. также
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по