Bagikan melalui

Penemuan dan klasifikasi data di SqlClient

Berlaku untuk: .NET Framework .NET .NET Standard

Mengunduh ADO.NET

Penemuan & Klasifikasi Data adalah sekumpulan layanan tingkat lanjut untuk menemukan, mengklasifikasikan, memberi label, dan melaporkan data sensitif dalam database Anda. SqlClient menyediakan API yang mengekspos informasi Penemuan dan Klasifikasi Data baca-saja saat sumber yang mendasar mendukung fitur tersebut. Informasi ini diakses melalui SqlDataReader.

Microsoft.Data.SqlClient v2.1.0 memperkenalkan dukungan untuk informasi Klasifikasi Sensitivity Rank Data. Sensitivity Rank adalah pengidentifikasi berdasarkan serangkaian nilai yang telah ditentukan sebelumnya, yang menentukan peringkat sensitivitas. Ini dapat digunakan oleh layanan lain seperti Perlindungan Ancaman Tingkat Lanjut untuk mendeteksi anomali berdasarkan peringkatnya. API Klasifikasi Data berikut sekarang tersedia di namespace Microsoft.Data.SqlClient.DataClassification:

Contoh kode

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

Catatan

Microsoft.Data.SqlClient membaca Sensitivity Rank informasi hanya jika SQL Server mendukung Klasifikasi Data dengan peringkat. Untuk server menggunakan versi lama Klasifikasi Data tanpa peringkat, nilai peringkat untuk kueri adalah "NOT DEFINED".

Aplikasi sampel ini menunjukkan cara mengakses properti Klasifikasi Data 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();
    }
}

Lihat juga