Clasificación y detección de datos en SqlClient
Se aplica a:
.NET Framework .NET .NET Standard
La clasificación y detección de datos es un conjunto de servicios avanzados para detectar, clasificar, etiquetar y notificar información confidencial en las bases de datos. SqlClient proporciona una API que expone información de clasificación y detección de datos de solo lectura cuando el origen subyacente es compatible con la característica. Se obtiene acceso a esta información a través de SqlDataReader.
Microsoft.Data.SqlClient v2.1.0 incluye compatibilidad con la información Sensitivity Rank de la clasificación de datos. Sensitivity Rank es un identificador basado en un conjunto de valores predefinidos que definen el rango de confidencialidad. Lo pueden usar otros servicios como Advanced Threat Protection para detectar anomalías en función de su rango. Las siguientes API de clasificación de datos ahora están disponibles en el espacio de nombres Microsoft.Data.SqlClient.DataClassification:
Ejemplo 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;
}
Nota
Microsoft.Data.SqlClient lee información Sensitivity Rank solo si SQL Server admite la clasificación de datos con rango. En el caso de los servidores que usan la versión anterior de la clasificación de datos sin rango, el valor de rango para las consultas es "NO DEFINIDO".
En esta aplicación de ejemplo se muestra cómo acceder a las propiedades de clasificación de datos de 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();
}
}
Vea también
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de