SqlClient의 데이터 검색 및 분류
적용 대상:
.NET Framework .NET .NET Standard
데이터 검색 및 분류는 데이터베이스에서 중요한 데이터를 검색, 분류, 레이블 지정 및 보고하기 위한 고급 서비스의 집합입니다. SqlClient는 기본 원본이 기능을 지원할 때 읽기 전용 데이터 검색 및 분류 정보를 공개하는 API를 제공합니다. 이 정보는 SqlDataReader를 통해 액세스됩니다.
Microsoft.Data.SqlClient v2.1.0에서는 데이터 분류의 Sensitivity Rank 정보를 지원합니다. Sensitivity Rank는 민감도 순위를 정의하는 사전 정의된 값 세트를 기반으로 하는 식별자입니다. Advanced Threat Protection과 같은 다른 서비스에서 해당 순위에 따라 변칙을 검색하는 데 사용할 수 있습니다. 이제 Microsoft.Data.SqlClient.DataClassification 네임스페이스에서 다음 데이터 분류 API를 사용할 수 있습니다.
코드 예시
// 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는 SQL Server에서 순위가 지정된 데이터 분류를 지원하는 경우에만 Sensitivity Rank 정보를 읽습니다. 서버가 순위가 존재하지 않는 이전 버전의 데이터 분류를 사용하는 경우 쿼리의 순위 값은 "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();
}
}
참고 항목
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기