Czy istnieje szybki sposób na znalezienie wszystkich kolumn w SQL Server 2008 R2, które są zaszyfrowane / mają zaszyfrowane dane?

Czy istnieje szybki sposób na znalezienie wszystkich kolumn w SQL Server 2008 R2, które są zaszyfrowane / mają zaszyfrowane dane?

Muszę zerować dane we wszystkich zaszyfrowanych kolumnach na serwerze programistycznym (zgodnie z naszymi regułami biznesowymi). Znam większość kolumn, ponieważ używamy ich regularnie, ale chcę być dokładny, a także chcę być w stanie udowodnić, że wszystkie je znalazłem.

Przeszukałem Internet, zajrzałem do INFORMACJE_SCHEMA i sprawdziłem DMV, które moim zdaniem będą przydatne, a także sys.columns i sys.objects - ale jak dotąd bez powodzenia.

Zakładając, że mówisz o danych zaszyfrowanych kluczami SQL Server, istnieje sposób na znalezienie tych kolumn.

Key_name()Funkcja zwróci nazwę klucza użytego do szyfrowania dla tej konkretnej wartości i zwróci NULL jeśli nie ma nic szyfrowane za pomocą klucza „znany” (3rd party lub zwykłego nie zaszyfrowanej).

Dzięki tej wiedzy możemy przetestować każdą kolumnę, aby zobaczyć, czy zawiera ona co najmniej jeden wiersz, który ma wartość varbinary, która zwraca nazwę klucza

funkcjonalność key_name ()

--create a test database
CREATE DATABASE [Test_ENCR]
GO

--change context
USE [Test_ENCR]
GO


--because it's possible to encrypt different rows with different keys I'll create 2 keys for this demo
-- Create a symmetric key
CREATE SYMMETRIC KEY symmetricKey1
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password01!';
GO

-- Create a second key
CREATE SYMMETRIC KEY symmetricKey2 
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password02!';
GO



--create a table that will have a column holding:
--1: encrypted row with key1
--2: encrypted row with key2
--3: a non encrypted just varbinary value

CREATE TABLE encryptedTable
(ID int IDENTITY PRIMARY KEY,
EncryptedCol varbinary(256) NOT NULL);
GO


-- open key1
OPEN SYMMETRIC KEY symmetricKey1 
    DECRYPTION BY PASSWORD = 'password01!';
GO

-- open key2
OPEN SYMMETRIC KEY symmetricKey2 
    DECRYPTION BY PASSWORD = 'password02!';
GO

--insert encrypted data with key1
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey1'), 'EncryptedText1'));
GO

--insert encrypted data with key2
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey2'), 'EncryptedText2'));
GO


--insert just varbinary data
INSERT INTO encryptedTable(encryptedCol)
VALUES (CONVERT(varbinary(256),'NotEncryptedTextJustVarBinary'))



--have a look, without the key, all varbinary for you.
SELECT * FROM encryptedTable
GO

wyniki:

--Return all key_names
SELECT DISTINCT     key_name(encryptedcol), 
                    EncryptedCol 
FROM encryptedTable;

wyniki:

Jak to zaimplementować, aby znaleźć zaszyfrowane kolumny

--How do we dynamically find all the columns that have at least one row with a encrypted value?

-- first we will find all tables and column with a varbinary datatype
-- then we will test all those columns with a simple select
-- If the key_name() function returns a value, the column and table name are stored together with the keyname

--create a table to hold all varbinary columns and tables
CREATE TABLE #TablesWithVarbinCols (    ID int IDENTITY,
                                TableName nvarchar(128),
                                ColumnName nvarchar(128)
                                );

--create a table to hold the end result
CREATE TABLE #TablesWithEncryption (
                                TableName nvarchar(128),
                                ColumnName nvarchar(128),
                                KeyName varchar(128)
                                );


--find and store all table and column names of user tables containing a varbinary column
INSERT INTO #TablesWithVarbinCols (TableName,ColumnName)
SELECT      o.[name] as TableName,
            c.[name] as ColumnName
FROM        sys.objects o
INNER JOIN  sys.columns c
ON          o.[object_id]=c.[object_id] 
INNER JOIN  sys.types t
ON          c.system_type_id=t.system_type_id
WHERE       o.[type]='U'
AND         t.name=N'varbinary'
AND         c.max_length > -1;


DECLARE @col nvarchar(256)
DECLARE @tab nvarchar(256)
DECLARE @c int = 1
DECLARE @MaxC int
DECLARE @SQL varchar(max)

SELECT @MaxC=MAX(ID)
FROM #TablesWithVarbinCols

--loop the previous result and create a simple select statement with a key_name() is not null where clause. 
--If you have a result, store the details
WHILE @c <= @MaxC
BEGIN
    SELECT  @Tab=TableName,
         @col=ColumnName
    FROM    #TablesWithVarbinCols
    WHERE   ID=@c

    SET @SQL='  INSERT INTO #TablesWithEncryption (TableName, ColumnName, KeyName)
                SELECT DISTINCT '''+@Tab +''',''' +@col +''', key_name('+@Col +') from '+ @tab +' 
                WHERE key_name('+@Col +') is not null;'
    exec (@SQL)

    DELETE
    FROM #TablesWithVarbinCols
    WHERE id=@c;
    SET @c=@c+1
END

--select the result
SELECT * FROM #TablesWithEncryption;

wyniki:

--cleanup
DROP TABLE #TablesWithVarbinCols;
DROP TABLE #TablesWithEncryption;

Problem z szyfrowaniem na poziomie komórki polega na tym, że sama kolumna nie jest tak naprawdę zaszyfrowana, to dane zawarte w tej kolumnie. Same kolumny są tylko kolumnami varbinary (ponieważ to jest wymagane) i mogą zawierać całkowicie czytelne dane. To użycie ENCRYPTBY*i DECRYPTBY*funkcji, które naprawdę szyfrują dane.

Możesz zacząć od wysłania zapytania do widoku sys.columns dla wszystkich kolumn, które są varbinary:

select
  object_name(a.object_id) [objectname]
  ,a.name [columnname]
  ,a.column_id
from
   sys.columns a
   join sys.types b on (a.system_type_id = b.system_type_id)
where
   b.name = N'varbinary';

W przeciwnym razie musisz przejrzeć kod, aby określić, gdzie używane są funkcje szyfrowania / deszyfrowania:

select
   object_name(object_id) [objectname]
   ,definition
from
   sys.sql_modules
where
   definition like N'%ENCRYPT%' 
   OR definition like N'%DECRYPT%';

Uruchom poniższe zapytanie na konkretnej bazie danych

select 
    t.name as [Table],
    c.name as [Column], 
    c.encryption_type_desc
from   
    sys.all_columns c inner join
    sys.tables t on c.object_id = t.object_id
where  
    c.encryption_type is not null 
order by
    t.name,
    c.name

Nieznacznie zmieniony z

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ca9d8360-5d7e-4de1-abe6-ce1afda595a9/how-to-find-which-table-columns-are-encrypted?forum=sqlsecurity

Kolumny zaszyfrowane kluczami \ certyfikatami można znaleźć, wyszukując wszystkie kolumny varbinary i sprawdzając klucz szyfrowania za pomocą funkcji KEY_NAME.

Ten proces jest jednak nieco drogi i czasochłonny. Jeśli chcesz regularnie znajdować te kolumny, sugeruję „otagowanie” kolumn o rozszerzonych właściwościach. Możemy budować na rozwiązaniu Edwarda Dortlanda i „tagować” znalezione kolumny o rozszerzonych właściwościach, takich jak szyfrowane, szyfrowane i szyfrowane.

--create a table to hold all varbinary columns and tables
CREATE TABLE #TablesWithVarbinCols (    ID int IDENTITY,
                            SchemaName nvarchar(128),
                            TableName nvarchar(128),
                            ColumnName nvarchar(128)
                            );

--find and store all table and column names of user tables containing a 
varbinary column
INSERT INTO #TablesWithVarbinCols (SchemaName,TableName,ColumnName)
SELECT      s.[name] as SchemaName,
            o.[name] as TableName,
            c.[name] as ColumnName
FROM        sys.objects o
INNER JOIN  sys.schemas s
ON          s.[schema_id] = o.[schema_id]
INNER JOIN  sys.columns c
ON          o.[object_id]=c.[object_id] 
INNER JOIN  sys.types t
ON          c.system_type_id=t.system_type_id
WHERE       o.[type]='U'
AND         t.name=N'varbinary'
AND         c.max_length > -1;

DECLARE @sch nvarchar(128)
DECLARE @col nvarchar(256)
DECLARE @tab nvarchar(256)
DECLARE @key nvarchar(256)
DECLARE @cert nvarchar(256)
DECLARE @c int = 1
DECLARE @MaxC int
DECLARE @SQL nvarchar(max)

SELECT @MaxC=MAX(ID)
FROM #TablesWithVarbinCols

--loop the previous result and create a simple select statement with a 
key_name() is not null where clause. 
--If you have a result, store the details
WHILE @c <= @MaxC
BEGIN
    SET @key = NULL;
    SELECT  @sch=SchemaName,
            @Tab=TableName,
            @col=ColumnName
    FROM    #TablesWithVarbinCols
    WHERE   ID=@c


SET @SQL='SELECT DISTINCT @key= key_name('+@Col +') from '+ @tab +' 
            WHERE key_name('+@Col +') is not null;'


exec sp_executesql @SQL, N'@key nvarchar(256) out', @key out

SELECT @cert =  c.name 
from sys.symmetric_keys sk
join sys.key_encryptions ke
    on
    sk.symmetric_key_id= ke.key_id
join sys.certificates c
    on
    ke.thumbprint=c.thumbprint
where sk.name = @key


IF (@key IS NOT NULL)
BEGIN

    SET @SQL=
    'EXEC sp_addextendedproperty @name = N''encrypted'', @value = N''1'', '+
    '@level0type = N''Schema'', @level0name = '''+@Sch+''', '+
    '@level1type = N''Table'', @level1name = '''+@tab+''','+
    '@level2type = N''Column'', @level2name = '''+@col+'''
    '

    EXEC sp_executesql @SQL

    SET @SQL=
    'EXEC sp_addextendedproperty @name = N''encryptkey'', @value = '''+@key+''', '+
    '@level0type = N''Schema'', @level0name = '''+@Sch+''', '+
    '@level1type = N''Table'', @level1name = '''+@tab+''','+
    '@level2type = N''Column'', @level2name = '''+@col+'''
    '

    EXEC sp_executesql @SQL

    SET @SQL=
    'EXEC sp_addextendedproperty @name = N''encryptcert'', @value = '''+@cert+''', '+
    '@level0type = N''Schema'', @level0name = '''+@Sch+''', '+
    '@level1type = N''Table'', @level1name = '''+@tab+''','+
    '@level2type = N''Column'', @level2name = '''+@col+'''
    '

    EXEC sp_executesql @SQL

END

DELETE
FROM #TablesWithVarbinCols
WHERE id=@c;
SET @c=@c+1

END

drop table #TablesWithVarbinCols

Następnie możemy łatwo znaleźć zaszyfrowane kolumny, przeszukując rozszerzone właściwości.

--Adjust WHERE clause depending on what tags you are looking for
SELECT
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,    
   tbl.name AS TableName, 
   clmns.name AS ColumnName,
  p.name AS ExtendedPropertyName,    --remove for programming
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.tables AS tbl
   INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
   INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=clmns.column_id AND p.class=1
WHERE p.name in ('encrypted','encryptkey','encryptcert')