На той неделе пришлось разбираться в логике работы одного бесплатного тула. Почти детективная история вышла с ее автором, который впоследствии оказался индусом канадского происхождения проживающим в Южной Америке. Конечно же, практическая ценность была не в биографии автора, а в запросах, которые отправлялись приложением на сервер.
Установил. Запустил. Стал в стоечку и начал собирать профайлером все, чем приложение должно было «порадовать» сервер. Смею разочаровать – ничего радостного сервер в ближайшие два часа не увидел. В основном встречались разного рода перлы в запросах, которые явно не претендовали на комплименты:
SELECT LogTruncations = ( SELECT TOP 1 SUM(cntr_value) FROM ##tbl_cnt WHERE counter_name = 'Log Truncations' ), LogShrinks = ( SELECT TOP 1 SUM(cntr_value) FROM ##tbl_cnt WHERE counter_name = 'Log Shrinks' ), LogGrowths = ( SELECT TOP 1 SUM(cntr_value) FROM ##tbl_cnt WHERE counter_name = 'Log Growths' ), ... Поскольку их можно написать на порядок проще и сократить логические чтения из таблицы:
SELECT LogTruncations = SUM(CASE WHEN counter_name = 'Log Truncations' THEN cntr_value END), LogShrinks = SUM(CASE WHEN counter_name = 'Log Shrinks' THEN cntr_value END), LogGrowths = SUM(CASE WHEN counter_name = 'Log Growths' THEN cntr_value END), ... FROM ##tbl_cnt На этом можно было бы и закончить… Но практически под конец я увидел, что приложение вызывает пользовательские хранимые процедуры из tempdb. Поймал себя на мысли: «Когда приложение успело их создать… и главное зачем?»
Оказывается, инсталлятор по-тихому нашел дефолтный экземпляр SQL Server на моей локальной машине и создал там хранимки. Попробовал поработать с данным тулом на именованном инстансе… Error Message!
Архитектурное решение на грани фантастики. К слову скажу, при каждом рестарте сервера база tempdb пересоздаётся… так что ж мне программу каждый раз переустанавливать? Бред! Бред… как сказал бы мой попугай.
Оки… Развернем эти хранимки на именованном сервере, а заодно посмотрим, что в них такого ценного. Открываем хранимые процедуры в Database Explorer и видим «картину маслом»:
Замочек на объектах… а значит хранимые процедуры созданы с параметром WITH ENCRYPTION и сгенерировать в SSMS команду CREATE или ALTER уже не получится:
Property TextHeader is not available for StoredProcedure ‘[dbo].[shb_get_waitstats’]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.
В метаданных мы тоже не сможем получить сорс зашифрованных объектов:
SELECT o.name, s.[definition] FROM sys.objects o JOIN sys.sql_modules s ON o.[object_id] = s.[object_id] WHERE [type] = 'P'
name definition ------------------------- ------------ shb_generate_waitstats NULL shb_get_waitstats NULL shb_get_waitstats_all NULL shb_avg_waiting_task NULL shb_expensiveqry NULL shb_get_querystats NULL shb_agent_log NULL shb_error_log NULL shb_default_trace NULL shb_spConfigure NULL Такой лайвфак лайвхак также не сработает:
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.shb_get_waitstats')) Переустанавливать приложение не хотелось и поэтому решил схитрить. Включаем «режим Бога», который разрешает подключаться к серверу через DAC (Dedicated Administrator Connection):
EXEC sys.sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure 'remote admin connections', 1 RECONFIGURE WITH OVERRIDE GO Вначале соединения в SQL документе дописываем ADMIN: и проверяем что пользователь под которым мы коннектимся является sysadmin-ом:
Если соединение пройдет успешно, то мы будем имеем абсолютную власть на сервером. Но что на практике поменяется? Ведь мы и так имеем права sysadmin, которые разрешают все что только можно.
Как оказывается, ограничение все же есть. Пробовали ли хоть раз читать из системных таблиц? В SQL Server 2000 такое поведение разрешалось. С приходом же 2005 версии секьюрность метаданных претерпела существенные изменения и доступа к системных таблицам напрямую теперь уже нет.
В основном к таблицам с метаданными можно обращаться неявно, посредством системных представлений, которых в 99% случаев бывает достаточно. Но не в нашем случае. Существует отдельная таблица sys.sysobjvalues в которой хранятся зашифрованные объекты:
SELECT * FROM sys.sysobjvalues В обычных условиях читать из нее нельзя:
Msg 208, Level 16, State 1, Line 1 Invalid object name 'sys.sysobjvalues'. но при подключении через DAC становится возможным выборка из любой системной таблицы и из нее в частности:
SELECT * FROM sys.sysobjvalues WHERE [objid] = OBJECT_ID('[dbo].[shb_get_waitstats]')
valclass objid subobjid valnum value imageval -------- ----------- ----------- ----------- --------- -------------------------------- 1 1429580131 1 0 0 0x037112F3D7F8C09E11A1A8FB.... Имея на руках зашифрованное тело хранимой процедуры можно ее расшифровать…
Для начала мы получаем бинарное представление хранимки в зашифрованном виде. Создаем заготовку хранимки с идентичным именем и параметром WITH ENCRYPTION, но вместо тела подставляем символы дефисов:
DECLARE @obj SYSNAME = '[dbo].[shb_get_waitstats]' , @enc NVARCHAR(MAX) , @enc_length INT , @obj_type NVARCHAR(100) , @obj_name SYSNAME SELECT @enc = imageval , @enc_length = (DATALENGTH(imageval) / 2) + 1 , @obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + N'.' + QUOTENAME(o.name) FROM sys.sysobjvalues v JOIN sys.objects o ON v.[objid] = o.[object_id] WHERE [objid] = OBJECT_ID(@obj, 'P') AND imageval IS NOT NULL DECLARE @header NVARCHAR(MAX) SET @header = N'ALTER PROCEDURE ' + @obj_name + N' WITH ENCRYPTION AS ' SET @header = @header + REPLICATE(CAST('-' AS NVARCHAR(MAX)), (@enc_length - LEN(@header))) DECLARE @tmp TABLE (enc NVARCHAR(MAX)) BEGIN TRANSACTION EXEC sys.sp_executesql @header INSERT INTO @tmp (enc) SELECT imageval FROM sys.sysobjvalues WHERE [objid] = OBJECT_ID(@obj) ROLLBACK TRANSACTION DECLARE @blank_enc NVARCHAR(MAX) SELECT @blank_enc = enc FROM @tmp SET @header = N'CREATE PROCEDURE ' + @obj_name + N' WITH ENCRYPTION AS ' SET @header = @header + REPLICATE(CAST('-' AS NVARCHAR(MAX)), (@enc_length - LEN(@header))) ;WITH E1(N) AS ( SELECT * FROM ( VALUES (1),(1),(1),(1),(1), (1),(1),(1),(1),(1) ) t(N) ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), E8(N) AS (SELECT 1 FROM E4 a, E4 b) SELECT ( SELECT NCHAR( UNICODE(SUBSTRING(@enc, RowNum, 1)) ^ UNICODE(SUBSTRING(@header, RowNum, 1)) ^ UNICODE(SUBSTRING(@blank_enc, RowNum, 1)) ) FROM ( SELECT TOP(@enc_length) RowNum = ROW_NUMBER() OVER (ORDER BY 1/0) FROM E8 ) t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') Далее используя XOR преобразование над полученными строками, мы можем расшифровать требуемый объект:
CREATE PROCEDURE shb_get_waitstats WITH ENCRYPTION AS BEGIN SET NOCOUNT ON; EXEC shb_generate_waitstats SELECT DISTINCT GETDATE() AS collection_time, a.category_name AS [Wait Category] , ISNULL(dt.[Wait Time (ms/sec)], 0) [Wait Time (ms/sec)] , ISNULL(dt.[Recent Wait Time (ms/sec)], 0) [Recent Wait Time (ms/sec)] , ISNULL(dt.[Average Waiter Count], 0) [Average Waiter Count] , ISNULL(dt.[Cumulative Wait Time], 0) [Cumulative Wait Time] , ISNULL(dt.[avg_waiting_task_count], 0) AS [Avg Waiting Task] FROM #am_wait_types a LEFT JOIN ( SELECT category_name , SUM(interval_wait_time_per_sec) AS [Wait Time (ms/sec)] , SUM(weighted_average_wait_time_per_sec) AS [Recent Wait Time (ms/sec)] , SUM(interval_avg_waiter_count) AS [Average Waiter Count] , SUM(resource_wait_time_cumulative) AS [Cumulative Wait Time] , SUM(interval_wait_time_per_sec) / 1000 AS avg_waiting_task_count FROM #am_resource_mon_snap GROUP BY category_name ) dt ON a.category_name = dt.[category_name] END Можно легко проверить все на простом примере:
IF OBJECT_ID('dbo.test') IS NOT NULL DROP PROCEDURE dbo.test GO CREATE PROCEDURE dbo.test (@a INT) WITH ENCRYPTION AS BEGIN RETURN 123 END GO Все вроде кажется таким элементарным, но что если нет возможности подключиться через DAC? Или требуется расшифровать скалярную функцию или представление… Есть вариант использовать самописную CLR сборку, которую можно поискать на CodeProject или воспользоваться уже готовыми решениями.
К слову о последнем варианте… Уже давно существует бесплатный продукт – dbForge SQL Decryptor, который позволяет в пакетном режиме умеет расшифровывать все скриптовые объекты:
И что самое главное, для его работы не требуется DAC подключение. Мне стало очень интересно, как без использования DAC выбрать данные из системной таблицы… оказалось все очень просто. Вначале SQL Decryptor получает список страниц, на которых хранятся данные из sys.sysobjvalues:
DBCC TRACEON(3604) DBCC IND (tempdb, [sys.sysobjvalues], 1) WITH TABLERESULTS, NO_INFOMSGS DBCC TRACEOFF(3604) Потом обходит все страницы:
DBCC TRACEON(3604) DBCC PAGE (tempdb, 1, 128, 3) DBCC PAGE (tempdb, 1, 132, 3) DBCC PAGE (tempdb, 1, 132, 3) DBCC PAGE (tempdb, 1, 138, 3) DBCC PAGE (tempdb, 1, 23, 3) DBCC TRACEOFF(3604) Смотрит на их содержимое:
0000000053B5F8C0: 07000000 a209d600 ea9b0000 66000000 00000000 ....¢ Ö.ê..f....... 0000000053B5F8D4: 66000000 00000000 16c4643f 0317383c a1a0203c f........Äd?..81¡ 1 0000000053B5F8E8: a1a0203c a1a0203c a1a0203c 00000000 00000000 ¡ 1¡ 1¡ 1........ 0000000053B5F8FC: 00000000 00000000 00000000 00000000 00000000 .................... 0000000053B5F910: 00000000 00000000 00000000 00000000 00000000 .................... 0000000053B5F924: 00000000 00000000 00000000 00000000 00000000 .................... 0000000053B5F938: 00000000 00000000 00000000 00000000 00000000 .................... 0000000053B5F94C: 00000000 00000000 00000000 00000000 00000000 .................... 0000000053B5F960: 00000000 2f000000 2f000000 05000000 14000000 ..../.../........... 0000000053B5F974: 00008841 0000cc42 00000000 00008040 0000803f ...A..ÌB......@..? 0000000053B5F988: 00008040 00008040 00008040 00000000 00000000 ..@..@..@........ 0000000053B5F99C: 00000000 00000000 00000000 00000000 00000000 .................... 0000000053B5FA14: b1050000 00000000 78010000 00000000 8f010000 ±.......x.......... 0000000053B5FA28: 00000000 a6010000 00000000 bd010000 00000000 ....¦.......½....... 0000000053B5FA3C: d4010000 00000000 eb010000 00000000 02020000 Ô.......ë........... 0000000053B5FA50: 00000000 19020000 00000000 30020000 00000000 ............0....... 0000000053B5FA64: 47020000 00000000 5e020000 00000000 75020000 G.......^.......u... И по нему достает бинарное представление объектов в зашифрованном виде. А дальше Вы уже знаете, как происходит – простое XOR преобразование.
Краткие выводы:
Что сказать по данному поводу? Если Вам нужно автоматизировать процесс расшифровки объектов, то можно написать скрипт по аналогии с моим решением. Но в большинстве случаев будет достаточно просто запустить dbForge SQL Decryptor, выбрать объект и получить его сорс:
После этого CodeReview я пообщался с индусом. Он выслушал все мои замечания по части найденных проблем с запросами и больше уже больше не выходил на связь… Вот такая выдалась продуктивная неделя 🙂
Пару слов о мероприятиях на март…
Всем кому интересна тематика SQL Server рекомендую обратить внимание на онлайн-вебинар 24 Hours of PASS, который будет проходить 16-17 марта. В течении двух дней ожидается 16 докладов. Делиться опытом будут 14 экспертов со всего мира (среди них 7 человек имеют звание MVP SQL Server).
ссылка на оригинал статьи https://habrahabr.ru/post/278737/
Добавить комментарий