Aracılığıyla paylaş

SqlClient'da veri bulma ve sınıflandırma

Şunlar için geçerlidir: .NET Framework .NET .NET Standard

ADO.NET indirin

Veri Bulma ve Sınıflandırma , veritabanlarınızdaki hassas verileri bulmaya, sınıflandırmaya, etiketlemeye ve raporlamaya yönelik bir dizi gelişmiş hizmettir. SqlClient, temel alınan kaynak özelliği desteklediğinde salt okunur Veri Bulma ve Sınıflandırma bilgilerini gösteren bir API sağlar. Bu bilgilere SqlDataReader aracılığıyla erişilir.

Microsoft.Data.SqlClient v2.1.0, Veri Sınıflandırması Sensitivity Rank bilgileri için destek sağlar. Sensitivity Rank , duyarlılık derecesini tanımlayan önceden tanımlanmış bir değer kümesini temel alan bir tanımlayıcıdır. Derecelerine göre anomalileri algılamak için Gelişmiş Tehdit Koruması gibi diğer hizmetler tarafından kullanılabilir. Aşağıdaki Veri Sınıflandırma API'leri artık Microsoft.Data.SqlClient.DataClassification ad alanında kullanılabilir:

Kod örneği

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

Uyarı

Microsoft.Data.SqlClient bilgileri yalnızca SQL Server dereceli Veri Sınıflandırmasını destekliyorsa okur Sensitivity Rank . Sunucular derecelendirme olmadan Veri Sınıflandırmasının eski sürümünü kullandığında, sorguların derecelendirme değeri "TANıMLı DEĞİl" olur.

Bu örnek uygulama, SqlDataReader'ın Veri Sınıflandırma özelliklerine erişmeyi gösterir.

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();
    }
}

Ayrıca bakınız

  • Last updated on