Descoberta e classificação de dados no SqlClient
Aplicável a: .NET Framework .NET .NET Standard
A Descoberta e Classificação de Dados é um conjunto de serviços avançados para descobrir, classificar, rotular e relatar os dados confidenciais em seus bancos de dados. O SqlClient fornece uma API que expõe informações de Descoberta e Classificação de Dados somente leitura quando a fonte subjacente dá suporte ao recurso. Essas informações são acessadas por meio do SqlDataReader.
O Microsoft.Data.SqlClient v2.1.0 apresenta o suporte para informações de Sensitivity Rank
da classificação de dados. Sensitivity Rank
é um identificador baseado em um conjunto de valores predefinidos que determinam a classificação de sensibilidade. Ele pode ser usado por outros serviços, como a Proteção Avançada contra Ameaças, para detectar anomalias com base em sua classificação. As seguintes APIs de classificação de dados agora estão disponíveis no namespace Microsoft.Data.SqlClient.DataClassification:
Exemplo de código
// 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;
}
Observação
O Microsoft.Data.SqlClient lerá informações de Sensitivity Rank
somente se o SQL Server der suporte à Classificação de dados com classificação. Para servidores com uma versão antiga da Classificação de dados sem classificação, o valor de classificação para consultas é "NÃO DEFINIDO".
Este aplicativo de exemplo demonstra como acessar as propriedades de Classificação de Dados do 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();
}
}
Confira também
Comentários
https://aka.ms/ContentUserFeedback.
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulteEnviar e exibir comentários de