Datenermittlung und -klassifizierung in SqlClient
Gilt für:
.NET Framework .NET .NET Standard
Bei der Datenermittlung und -klassifizierung handelt es sich um mehrere erweiterte Dienste für die Ermittlung, Klassifizierung, Bezeichnung und Berichterstellung für vertrauliche Daten in Ihren Datenbanken. SqlClient stellt eine API bereit, die schreibgeschützte Datenermittlungs- und Datenklassifizierungsinformationen verfügbar macht, wenn die zugrunde liegende Quelle das Feature unterstützt. Der Zugriff auf diese Informationen erfolgt über SqlDataReader.
Version 2.1.0 von Microsoft.Data.SqlClient bietet jetzt Unterstützung von Informationen zu Sensitivity Rank zur Datenklassifizierung. Sensitivity Rank ist ein Bezeichner, der auf vordefinierten Werten basiert, die die Vertraulichkeitsbewertung bestimmen. Er kann von anderen Diensten wie Advanced Threat Protection verwendet werden, um Anomalien basierend auf ihrer Bewertung zu erkennen. Die folgenden Datenklassifizierungs-APIs sind jetzt im Namespace Microsoft.Data.SqlClient.DataClassification verfügbar:
Codebeispiel
// 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;
}
Hinweis
Microsoft.Data.SqlClient liest die Informationen von Sensitivity Rank nur, wenn SQL Server die Datenklassifizierung mit Bewertung unterstützt. Für Server, die die alte Version der Datenklassifizierung ohne Bewertung verwenden, lautet der Wert für Abfragen NICHT DEFINIERT.
Diese Beispielanwendung zeigt, wie auf die Datenklassifizierungseigenschaften von SqlDataReader zugegriffen wird.
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();
}
}
Siehe auch
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für