Découverte et classification des données dans SqlClient

S’applique à : .NET Framework .NET Standard

Télécharger ADO.NET

Découverte et classification des données est un ensemble de services avancés de découverte, de classification, d’étiquetage des données sensibles dans les bases de données et de création de rapports associés. SqlClient fournit une API permettant de mettre au jour les informations de découverte et de classification des données en lecture seule lorsque la source sous-jacente prend en charge la fonctionnalité. Ces informations sont accessibles par le biais de SqlDataReader.

Microsoft.Data.SqlClient v2.1.0 introduit le support des informations de Sensitivity Rank de la classification des données. Sensitivity Rank est un identificateur basé sur un ensemble prédéfini de valeurs qui définissent le rang de sensibilité. Il est utilisé par d’autres services tels que le service Advanced Threat Protection pour détecter les anomalies en fonction de leur rang. Les API de classification des données suivantes sont maintenant disponibles dans l’espace de noms Microsoft.Data.SqlClient.DataClassification :

Exemple de code

// 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;
}

Notes

Microsoft.Data.SqlClient lit des informations Sensitivity Rank uniquement si SQL Server prend en charge la classification des données par ordre de priorité. Pour les serveurs qui utilisent l’ancienne version de la classification des données sans classement, la valeur de classement des requêtes est « NON DÉFINI ».

Cet exemple d’application montre comment accéder aux propriétés de classification des données 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();
    }
}

Voir aussi

• Fonctionnalités de SQL Server et ADO.NET
• sys.sensitivity_classifications (Transact-SQL)
• AJOUTER UNE CLASSIFICATION DE CONFIDENTIALITÉ