Фильтрация секретов в SQL Server во время трассировки и аудита
????? ???? ???? ? ???????????????? ?????? (??????, ????????), ??????? ???? ?????????, ??? ??? ?? ???????? ? SQL Trace ? SQL ?????.
SQL Server ????????????? ?????????? ? ??????????????? DDL ? ?????????? ???????, ??????? ????? ????????? ????????? ?????????? (????????: OPEN SYMMETRIC KEY, EncryptByKey ? ?.?.).
????????:
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'D3m0 p4SSw0Rd&'
go
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = 'D3m0 p4SSw0Rd&'
go
? ?????????? trace ????? ????????? ????????? ??????? (??? ???????? ? ???????? ?????????? ???????):
Event class |
TextData |
Event subclass |
Object name |
Object Type |
SQL: Batch Starting |
--*CREATE SYMMETRIC KEY---------------… |
|
|
|
Audit: DB Object Mgr |
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = '******' |
1 - Create |
key00 |
19283 - SK |
SQL: Batch Completed |
--*CREATE SYMMETRIC KEY------------------ |
|
|
|
SQL: Batch Starting |
--*OPEN SYMMETRIC KEY------------------- |
|
|
|
Audit: DB Object Mgr |
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******' |
10 - Open |
key00 |
19283 - SK |
SQL: Batch Completed |
--*OPEN SYMMETRIC KEY----------------… |
|
|
|
? ?????????, ??? ????????????? ???????????? ???????? ??? ???????????????? ???????? ???????? (??? ???????? ??????????????? ????????? ??? ?????? ? ?????????????? SQL injections), SQL Server ?? ????? ??????????? ??????????, ???????????? ?? DDL ? ?????????? ???????, ?????????? ????.
????????:
EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')
EXEC( 'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')
Event class |
TextData |
Event subclass |
Object name |
Object Type |
SQL: Batch Starting |
EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''') EXEC( 'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''') |
|
|
|
Audit: DB Object Mgr |
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = '******' |
1 - Create |
key00 |
19283 - SK |
Audit: DB Object Mgr |
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******' |
10 - Open |
key00 |
19283 - SK |
SQL: Batch Completed |
EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''') EXEC( 'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''') |
|
|
|
?????????? ????????? ???? SQL Server-? ??????, ??? ?????? ??????? ???????????. ???? ??????? ? ????????????? ?????????? ???????, ? ??????? SQL Server-? ????????, ??? ??? ????? ???????????? ????????? ??????????.
????????:
DECLARE @Secret nvarchar(max)
SELECT @Secret = CASE WHEN 1=1 THEN
'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&'''
ELSE EncryptByPassphrase('','') END
EXEC(@Secret)
SELECT @Secret = CASE WHEN 1=1 THEN
'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&'''
ELSE EncryptByPassphrase('','') END
EXEC(@Secret)
go
EncryptByPassphrase() ??????? ?? ?????????? ? ???? ???????, ?? ???? ?????? ??????????? ????????????? ????????? ?????? ? Trace-? ? ??????.
????????? ????? ????????? ????????? ???????:
Event class |
TextData |
Event subclass |
Object name |
Object Type |
SQL: Batch Starting |
DECLARE @Secret nvarchar(max) --*ASSIGN--------------------------- EXEC(@Secret) --*ASSIGN--------------------------- EXEC(@Secret) |
|
|
|
Audit: DB Object Mgr |
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = '******' |
1 - Create |
key00 |
19283 - SK |
Audit: DB Object Mgr |
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******' |
10 - Open |
key00 |
19283 - SK |
SQL: Batch Completed |
DECLARE @Secret nvarchar(max) --*ASSIGN--------------------------- EXEC(@Secret) --*ASSIGN--------------------------- EXEC(@Secret) |
|
|
|