Бөлісу құралы:

Обнаружение и классификация данных в SqlClient

Область применения: .NET Framework .NET .NET Standard

Скачать ADO.NET

Набор функций обнаружения и классификации данных SQL служит для обнаружения конфиденциальных данных в базах данных, их классификации, назначения им меток и создания отчетов. SqlClient включает API, предоставляющий сведения об обнаружении и классификации данных только для чтения, если базовый источник поддерживает эту функцию. Доступ к этим сведениям осуществляется через SqlDataReader.

Microsoft.Data.SqlClient v2.1.0 добавляет поддержку информации Sensitivity Rank согласно Классификации данных. Sensitivity Rank — это идентификатор, основанный на предварительно заданном наборе значений, определяющих ранг конфиденциальности. Он может использоваться другими службами, например службой "Расширенная защита от угроз", для обнаружения аномалий в зависимости от их ранга. Следующие API Классификации данных теперь доступны в пространстве имен Microsoft.Data.SqlClient.DataClassification:

Пример кода

// 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 считывает информацию Sensitivity Rank только в том случае, если SQL Server поддерживает Классификацию данных с ранжированием. Для серверов используется старая версия Классификации данных без ранжирования, и запросы возвращают значение ранга 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();
    }
}

См. также

  • Last updated on