Один из случаев пользовательского опыта по применению Oracle TDE дал некоторые полезные рекомендации о наилучшем методе шифрования существующих данных.
Прозрачное Шифрование Данных (TDE - Oracle Database Transparent Data Encryption), введенное как часть Oracle Advanced Security Option (версия 10.2 и выше), позволяет выборочно шифровать уязвимые данные, которые на нижнем уровне хранятся в файлах базы данных, а также все потоковые файловые компоненты, такие как журнальные (redo logs) файлы, архивные журналы и ленты резервного копирования. Основная цель TDE - защитить уязвимые данные, находящиеся в этих файлах операционной системы, от глаз которые вмешиваются в чужие дела и могут дать нечестным людям доступ к диску или ленте резервного копирования, а затем попытаться восстановить базу данных или просканировать файлы операционной системы, чтобы получить персональную идентификационную информацию или информацию о кредитных картах.
Я применял TDE несколько раз, как часть практического консультирования. Но до появления одного из наиболее поздних заказов я всегда использовал TDE либо для шифрования нового столбца существующей таблицы, либо для столбца, который был частью новой таблицы. Работа с TDE в обоих этих случаях проста из-за того, что целевые столбцы пусты, поэтому нет большого риска влияния, связанного с отсутствующими данными и существующими в приложении зависимостями.
Мой последний опыт использования TDE отличался от остальных. Я помогал крупной компании зашифровать существующий столбец в таблице, уже содержавшей более одного миллиона строк. Было также критичное приложение, которое зависело от этого столбца. Поэтому, можно представить, было много важных тем для размышления перед началом этого дела. После поиска в Интернете людей, имевших опыт в подобной ситуации, я нашел всего несколько ресурсов, способных помочь мне.
Эта статья - сборник прецедентов, изученных при прохождении процесса использования TDE для шифрования существующих данных. Я надеюсь, что изученное мной поможет вам быстрее справиться с похожим случаем при необходимости попытки использования TDE с существующими данными в столбце. Обратите, пожалуйста, внимание на то, что эта статья не рассказывает о TDE; если вам хочется изучить основы применения TDE, см. документацию (или этот обучающий курс).
Выявление возможных ограничений
Сначала, когда я обследовал клиентскую систему, я посмотрел на характеристики модели данных, в которой участвует целевой столбец, и которые препятствуют полному шифрованию столбца, а также информацию о столбце, которая может негативно повлиять на существующие операции. Этот анализ заключался в выявлении наличия индексов и ограничений целостности.
В документации по Oracle есть несколько ограничений, которые надо иметь в виду, если необходимо зашифровать столбец, на котором построены индексы. Oracle не разрешит шифровать столбец с bitmap-индексом, но в нашей ситуации этого не было. Но целевой столбец имел пару обычных (B-tree) индексов. Oracle разрешает шифрование столбца с обычным индексом, но запрещает шифрование "с привязкой" ("salted") индексированного столбца. Привязка (Salting) увеличивает безопасность повторяющихся данных путем добавления произвольной строки к данным перед шифрованием, что делает более сложным для злоумышленников использование техники распознавания по шаблону, применяемой для раскодировки зашифрованных данных. В результате этого первоначального анализа получилось, что шифрование столбца возможно, но без привязки (salt).
Я мог бы и остановиться на анализе индексов столбца, но следующий вопрос, на который хотелось получить ответ, был: "А используются ли эти индексы?" Я рассуждал так: если индексы бесполезны, я бы освободился от них и избавил систему от издержек, возникающих при поддержке индексных элементов, особенно с учетом дополнительного бремени шифрования. Чтобы понять, полезны ли индексы, я использовал возможности Oracle Database для отслеживания индексов. Я выяснил, что индексы, конечно же, использовались, поэтому необходимо поддерживать их и дальше.
Затем я посмотрел, участвует ли целевой столбец в ограничениях целостности. Так как каждая таблица имеет свой собственный ключ шифрования, Oracle не разрешает использовать TDE для шифрования столбцов, участвующих во внешних ключах. В нашей ситуации, целевой столбец не участвовал в ограничениях ссылочной целостности.
Оценка издержки производительности
Один из первых вопросов, заданных моим клиентом, был: "Как TDE повлияет на общую производительность приложения?" В документации по Oracle есть маленький раздел, в котором в общих словах описано, как TDE повлияет на производительность зависимых приложений. Но мой заказчик хотел получить конкретную статистику о том, как TDE может повлиять на ограниченный по времени обработки процесс загрузки данных, который происходит ежедневно.
Для выполнения требования клиента я вычислил среднее число строк, вставляемых в целевую таблицу ежедневно в течение этого ограниченного по времени процесса. Затем я создал похожую тестовую таблицу и индексы в "песочнице" (sandbox - тестовая среда, имитирующая производственную среду - прим. переводчика), идентичной клиентской, и измерил, сколько времени занимает вставка такого же количества строк до и после шифрования целевого столбца. Разница между значениями потраченного времени стала достаточно хорошей оценкой "штрафа производительности" за шифрование данных столбца во время этого процесса. Листинг 1 - пример использования SQL*Plus для этой цели.
SQL> CONNECT system Enter password: Connected. SQL> -- Конфигурирование файлов данных Oracle SQL> ALTER SYSTEM 2 SET db_create_file_dest = '/data01/oracle/' 3 SCOPE = MEMORY; System altered. SQL> -- Создание двух новых табличных пространств для демонстрации SQL> -- одного для сегментов данных, и одного для индексных сегментов SQL> CREATE TABLESPACE data_001 2 DATAFILE SIZE 1G; Tablespace created. SQL> CREATE TABLESPACE indx_001 2 DATAFILE SIZE 500M; Tablespace created. SQL> -- Создание демонстрационного пользователя SQL> CREATE USER app_001 IDENTIFIED BY app 2 DEFAULT TABLESPACE data_001 3 TEMPORARY TABLESPACE temp 4 QUOTA UNLIMITED ON data_001 5 QUOTA UNLIMITED ON indx_001; User created. SQL> GRANT CREATE SESSION, CREATE TABLE TO app_001; Grant succeeded. SQL> -- Работаем под демонстрационным пользователем SQL> CONNECT app_001/app; Connected. SQL> -- Создание демонстрационной таблицы в табличном пространстве по умолчанию SQL> CREATE TABLE app_001.transactions ( 2 trans_id INTEGER 3 CONSTRAINT transactions_pk PRIMARY KEY 4 USING INDEX TABLESPACE indx_001, 5 credit_card INTEGER NOT NULL 6 ); Table created. SQL> -- Создание индекса в табличном пространстве INDX_001 SQL> CREATE INDEX app_001.transactions_ndx1 2 ON app_001.transactions(credit_card) 3 TABLESPACE indx_001; Index created. SQL> -- Измерение времени загрузки данных SQL> SET TIMING ON; SQL> BEGIN 2 -- AMEX 3 FOR i IN 1.. 100000 LOOP 4 INSERT INTO app_001.transactions(trans_id, credit_card) 5 VALUES ( 6 i, 7 '34' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 8 ); 9 END LOOP; 10 COMMIT; 11 -- VISA 12 FOR i IN 100001.. 400000 LOOP 13 INSERT INTO app_001.transactions(trans_id, credit_card) 14 VALUES ( 15 i, 16 '4' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) 17 ); 18 END LOOP; 19 COMMIT; 20 -- MASTERCARD 21 FOR i IN 400001.. 500000 LOOP 22 INSERT INTO app_001.transactions(trans_id, credit_card) 23 VALUES ( 24 i, 25 '54' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 26 ); 27 END LOOP; 28 COMMIT; 29 END; 30 / PL/SQL procedure successfully completed. Elapsed: 00:00:56.14 SQL> SET TIMING OFF; SQL> -- Удаление существующих сгенерированных данных SQL> TRUNCATE TABLE app_001.transactions; Table truncated. SQL> -- Включение шифрования столбца credit_card SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card ENCRYPT NO SALT); Table altered. SQL> -- Измеряем время загрузки зашифрованных данных SQL> SET TIMING ON; SQL> BEGIN 2 -- AMEX 3 FOR i IN 1.. 100000 LOOP 4 INSERT INTO app_001.transactions(trans_id, credit_card) 5 VALUES ( 6 i, 7 '34' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 8 ); 9 END LOOP; 10 COMMIT; 11 -- VISA 12 FOR i IN 100001.. 400000 LOOP 13 INSERT INTO app_001.transactions(trans_id, credit_card) 14 VALUES ( 15 i, 16 '4' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) 17 ); 18 END LOOP; 19 COMMIT; 20 -- MASTERCARD 21 FOR i IN 400001.. 500000 LOOP 22 INSERT INTO app_001.transactions(trans_id, credit_card) 23 VALUES ( 24 i, 25 '54' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 26 ); 27 END LOOP; 28 COMMIT; 29 END; 30 / PL/SQL procedure successfully completed. Elapsed: 00:01:16.31 SQL> SET TIMING OFF; Листинг 1 Использование "песочницы", которая идентична производственной среде, простое сравнение времени загрузки показательного набора данных с и без шифрования столбца даст хорошее представление о том, как шифрование столбца повлияет на производительность производственной системы. |
Как и во всех тестах на производительность, я предположил, что издержки производительности шифрования столбца будут различаться от системы к системе, в зависимости от обычных параметров (количество процессоров, среднее время загрузки и т.д.). На Листинге 1 вы можете заметить, что вычисленные издержки производительности равны 36% (((56.14-76.31)/56.14)*100); однако, при использовании эмпирических данных, собранных в системе моего клиента, мы получили примерно 11% превышения времени загрузки данных, что сэкономит деньги, когда мы переведем производственную систему на TDE.
В этом примере я сосредоточил внимание на оптимизации издержек производительности шифрования данных для процесса загрузки данных при наличии индексов. Если ваша система имеет другие типы критических процессов, такие как цикл генерации отчетов по запросам, я рекомендую применить "песочницу" для сравнения времени процесса до и после шифрования данных. Далее в разделе этой статьи "Выявление потенциальных изменений в планах запросов" рассматривается особый анализ запросов и шифрования данных.
Действия при простое и выделение окна для простоя
Другое разумное рассуждение моего клиента было о том, что, возможно, потребуется остановка производственного приложения на время шифрования данных столбца в таблице с примерно миллионом строк. Сначала я думал, что по теории простой не потребуется - ведь в документации по Oracle описано, что при шифровании существующих данных столбца выполняется преимущественно многострочный update для всей таблицы. Без более глубокого размышления я не видел, почему бы одновременные вставки новых строк в таблицу и изменение существующих строк могли бы не обработаться. И так как я бубнил вот эту любимую Oracle мантру "readers не заблокируют writers, writers не заблокируют readers", то я, конечно, не ожидал, что шифрование столбца повлияет на запросы.
Но я, уже нахлебавшись работы DBA, знал, как важно тестировать теории и избегать неожиданных проблем, когда вы, наконец, фактически измените производственную систему. По ходу дела я обнаружил-таки проблему во время шифрования столбца при тестировании приложения в "песочнице". И, что более важно, заметил, что непрерывное шифрование замедляет время отклика некоторых запросов, а приложение воспринимает это как превышение времени ожидания. Эти таймауты в результате приводят к отключению сеансов, что влечет за собой неуспешное завершение транзакций, и затем это становится по-настоящему беспорядочно - если не вдаваться в детали.
Достаточно сказать, что после тестирования стало понятно, что простой был, конечно же, оправдан. А следующими вопросами были: "На какое время потребуется остановка производственного приложения? Успеем ли мы зашифровать столбец в обычное двухчасовое окно, планируемое на конец каждой недели, или потребуется более продолжительное время останова?" Для выяснения этого я просто измерил, сколько времени потребуется для шифрования столбца в "песочнице", учитывая, что ее сервер идентичен и набор данных такой же, как в производственной среде. Я нашел, что звыполние шифрования столбца занимает немного больше часа. Откровенно говоря, я был шокирован продолжительностью этого процесса, учитывая, что когда я имитировал выполнение тестового шифрования на ноутбуке, и это время было меньше пяти минут при схожих данных. Но важно было то, что это делалось на устаревшем серверном оборудовании, которое продолжало использоваться, когда надо было шифровать столбец в производственной системе.
Зная, что требуется немного больше времени с учетом выполнения других задач во время планового окна, я решил, что надо найти способы сократить час, из времени которое уходит на шифрование столбца. Моим первым инстинктивным желанием было удалить два индекса, в которые входит целевой столбец. В этом случае Oracle должен бы зашифровать только сами данные столбца таблицы, после чего я мог бы эффективно перестроить индексы без издержек журналирования. После нескольких новых тестов я сократил время, требуемое для шифрования столбца и индексов с 70 минут (индексы во время шифрования были) до 20 минут (индексы перестроены после шифрования столбца). Листинг 2 показывает пример тестов, которые я использовал, чтобы прийти к моим результатам (продолжая с того места, на котором мы остановились в Листинге 1). Еще раз обратите внимание на то, что измерение времени на листинге - в тестовой системе, используемой для написания этой статьи, а не в производственной системе, которую использовал мой клиент.
SQL> -- Удаление существующих сгенерированных данных SQL> TRUNCATE TABLE app_001.transactions; Table truncated. SQL> -- Отключение шифрования столбца credit_card SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card DECRYPT); Table altered. SQL> -- Загрузка новых генерируемых данных SQL> BEGIN 2 -- AMEX 3 FOR i IN 1.. 100000 LOOP 4 INSERT INTO app_001.transactions(trans_id, credit_card) 5 VALUES ( 6 i, 7 '34' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 8 ); 9 END LOOP; 10 COMMIT; 11 -- VISA 12 FOR i IN 100001.. 400000 LOOP 13 INSERT INTO app_001.transactions(trans_id, credit_card) 14 VALUES ( 15 i, 16 '4' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) 17 ); 18 END LOOP; 19 COMMIT; 20 -- MASTERCARD 21 FOR i IN 400001.. 500000 LOOP 22 INSERT INTO app_001.transactions(trans_id, credit_card) 23 VALUES ( 24 i, 25 '54' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 26 ); 27 END LOOP; 28 COMMIT; 29 END; 30 / PL/SQL procedure successfully completed. SQL> -- Измерение, сколько занимает шифрование данных о кредитных картах SQL> -- и соответствующих индексов SQL> SET TIMING ON; SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card ENCRYPT NO SALT); Table altered. Elapsed: 00:02:27.18 SQL> SET TIMING OFF; SQL> -- Удаление существующих сгенерированных данных SQL> TRUNCATE TABLE app_001.transactions; Table truncated. SQL> -- Удаление всех индексов, которые ссылаются на столбец credit_card SQL> DROP INDEX app_001.transactions_ndx1; Index dropped. SQL> -- Отключение шифрования столбца credit_card SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card DECRYPT); Table altered. SQL> -- Загрузка новых данных, имитирующих реальные SQL> BEGIN 2 -- AMEX 3 FOR i IN 1.. 100000 LOOP 4 INSERT INTO app_001.transactions(trans_id, credit_card) 5 VALUES ( 6 i, 7 '34' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 8 ); 9 END LOOP; 10 COMMIT; 11 -- VISA 12 FOR i IN 100001.. 400000 LOOP 13 INSERT INTO app_001.transactions(trans_id, credit_card) 14 VALUES ( 15 i, 16 '4' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)) 17 ); 18 END LOOP; 19 COMMIT; 20 -- MASTERCARD 21 FOR i IN 400001.. 500000 LOOP 22 INSERT INTO app_001.transactions(trans_id, credit_card) 23 VALUES ( 24 i, 25 '54' // TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999)) 26 ); 27 END LOOP; 28 COMMIT; 29 END; 30 / PL/SQL procedure successfully completed. SQL> -- Измерение, сколько времени занимает: SQL> -- 1. Шифрование данных credit_card без индексов SQL> -- 2. Пересоздание соответствующих индексов SQL> SET TIMING ON; SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card ENCRYPT NO SALT); Table altered. Elapsed: 00:01:15.48 SQL> CREATE INDEX app_001.transactions_ndx1 2 ON app_001.transactions(credit_card) 3 TABLESPACE indx_001 4 PARALLEL 2 5 NOLOGGING; Index created. Elapsed: 00:00:02.98 SQL> SET TIMING OFF; Листинг 2 Для ускорения процесса шифрования существующих данных, просто удалите индексы столбца перед его шифрованием, а напоследок пересоздайте индексы. |
Замечание: Предложение CREATE INDEX в среде эмуляции в этой статье уже известно. В реальности возможно использование пакета утилит DBMS_METADATA из Oracle Database для генерации предложений CREATE INDEX, которые можно использовать для воссоздания индексов после завершения шифрования данных. В итоге новая стратегия с перестроением индексов после шифрования столбца экономит намного больше времени по сравнению с более сложной проблемой процесса в целом, о которой говорится в следующем разделе.
Удаление копий-призраков незашифрованных данных
Oracle и операционная система хост-машины используют оптимальные алгоритмы для изменения данных в блоках данных, чтобы минимизировать затраты производительности на дисковые операции ввода/вывода. В особом случае, которым является шифрование существующих данных столбца, часто случается, что Oracle записывает зашифрованные данные столбца в новый блок данных, и просто помечает пространство, которое было занято незашифрованными значениями, как неиспользуемое (unused). Другими словами, Oracle не делает попыток очистить старые незашифрованные данные. Поскольку система в рабочем состоянии имеет постоянную активность изменений, то можно быть уверенными, что Oracle будет периодически переписывать старые незашифрованные данные во время повторного использования дискового пространства. Но учитывая, что мой клиент готовился к аудиту руководства, мы должны были убедиться в том, что незашифрованные уязвимые данные были сразу же дочиста стерты после процесса шифрования.
Немного поискав, я нашел FAQ в Oracle Technology Network Вебсайт, а также blog post, которые подтверждают эту специфическую проблему, и дают некоторое начальное представление о том, что с ней делать. Основная идея заключается в том, чтобы переместить все сегменты с первоначальными незашифрованными данными в новое табличное пространство (и файл данных), а затем использовать утилиты операционной системы для разрушения старого файла данных. Но в этом примере весь процесс выглядит проще, чем на самом деле. Правда в том, что вам скорее всего потребовалось бы переместить много сегментов вместе с сегментами, содержащими уязвимые данные, перед тем, как без риска удалить старое табличное пространство и разрушить его файлы данных.
Для автоматизации этого, возможно, трудоемкого и отчасти подверженного ошибкам процесса, я собрал вместе некоторые скипты, с помощью которых можно написать DDL-предложения, которые нужны, чтобы все это сделать. Должен снять шляпу перед Томом Кайтом, так как часть работы, которая здесь описана - это модифицированные запросы, которые я нашел на сайте Asktom. Листинг 3 показывает пример всего процесса, который я использовал.
Enter password:
Connected.
SQL> -- Создание нового табличного пространства для данных и сегментов индексов
SQL> CREATE TABLESPACE data_002 DATAFILE SIZE 1G;
Tablespace created.
SQL> CREATE TABLESPACE indx_002 DATAFILE SIZE 500M;
Tablespace created.
SQL> -- Генерация скрипта для перемещения существующих сегментов в новое табличное пространство
SQL> COL ORDER_COL1 NOPRINT;
SQL> COL ORDER_COL2 NOPRINT;
SQL> SET HEADING OFF;
SQL> SET VERIFY OFF;
SQL> SET ECHO OFF;
SQL> SELECT DECODE( segment_type, 'TABLE' , segment_name, table_name ) order_col1,
2 DECODE( segment_type, 'TABLE', 1, 2 ) order_col2,
3 'ALTER ' // segment_type // ' ' // LOWER(owner) // '.' // LOWER(segment_name) //
4 DECODE( segment_type, 'TABLE', ' MOVE ', ' REBUILD ' ) //
5 'TABLESPACE ' // LOWER(DECODE( segment_type, 'TABLE' , '&&NEW_DATA_TBS' , '&&NEW_INDX_TBS' )) // ';'
6 FROM dba_segments,
7 (SELECT table_name, index_name FROM dba_indexes WHERE tablespace_name = UPPER('&&OLD_INDX_TBS'))
8 WHERE segment_type in ( 'TABLE', 'INDEX' )
9 AND segment_name = index_name (+)
10 AND tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'))
11 AND owner = UPPER('&&OWNER')
12 ORDER BY 1, 2;
Enter value for new_data_tbs: data_002
Enter value for new_indx_tbs: indx_002
Enter value for old_indx_tbs: indx_001
Enter value for old_data_tbs: data_001
Enter value for owner: app_001
ALTER TABLE app_001.transactions MOVE TABLESPACE data_002;
ALTER INDEX app_001.transactions_pk REBUILD TABLESPACE indx_002;
ALTER INDEX app_001.transactions_ndx1 REBUILD TABLESPACE indx_002;
SQL> SET HEADING ON;
SQL> SET VERIFY ON;
SQL> SET ECHO ON;
SQL> -- выполнение скрипта
SQL> ALTER TABLE app_001.transactions MOVE TABLESPACE data_002;
Table altered.
SQL> ALTER INDEX app_001.transactions_pk REBUILD TABLESPACE indx_002;
Index altered.
SQL> ALTER INDEX app_001.transactions_ndx1 REBUILD TABLESPACE indx_002;
Index altered.
SQL> -- Проверка, нет ли индексов в неиспользуемом состоянии
SQL> SELECT owner, index_name, tablespace_name
2 FROM dba_indexes
3 WHERE STATUS = 'UNUSABLE';
no rows selected
SQL> -- Сбор статистики по новой схеме
SQL> EXEC dbms_stats.gather_schema_stats('app_001');
PL/SQL procedure successfully completed.
SQL> -- Проверка оставшихся сегментов в старых табличных пространствах
SQL> SELECT distinct owner
2 FROM dba_segments
3 WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'));
old 3: WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'))
new 3: WHERE tablespace_name IN (UPPER('data_001'), UPPER('indx_001'))
no rows selected
SQL> -- Проверка наличия пользователей, связанных со старыми табличными пространствами
SQL> SELECT username, default_tablespace FROM dba_users
2 WHERE default_tablespace IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'));
old 2: WHERE default_tablespace IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'))
new 2: WHERE default_tablespace IN (UPPER('data_001'), UPPER('indx_001'))
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
APP_001 DATA_001
SQL> -- Привязка нового табличного пространства по умолчанию к пользователям, если это необходимо
SQL> ALTER USER app_001
2 DEFAULT TABLESPACE data_002;
User altered.
SQL> -- Получение списка названий файлов данных для старых табличных пространств
SQL> COL tablespace_name FOR A15;
SQL> COL file_name FOR A70;
SQL> SET LINES 100;
SQL> SELECT tablespace_name, file_name
2 FROM dba_data_files
3 WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'));
old 3: WHERE tablespace_name IN (UPPER('&&OLD_DATA_TBS'), UPPER('&&OLD_INDX_TBS'))
new 3: WHERE tablespace_name IN (UPPER('data_001'), UPPER('indx_001'))
TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------------------------------------
DATA_001 /data01/oracle/db001/datafile/o1_mf_data_001_4m081w7m_.dbf
INDX_001 /data01/oracle/db001/datafile/o1_mf_indx_001_4m082l4q_.dbf
SQL> -- Удаление старых табличных пространств без удаления файлов данных
SQL> DROP TABLESPACE data_001
2 INCLUDING CONTENTS KEEP DATAFILES;
Tablespace dropped.
SQL> DROP TABLESPACE indx_001
2 INCLUDING CONTENTS KEEP DATAFILES;
Tablespace dropped.
SQL> -- Разрушение/удаление старых файлов данных
SQL> HOST shred -n 200 -z -u /data01/oracle/db001/datafile/o1_mf_data_001_4m081w7m_.dbf
SQL> HOST shred -n 200 -z -u /data01/oracle/db001/datafile/o1_mf_indx_001_4m082l4q_.dbf
Листинг 3 Удаление оставшихся незашифрованных копий-призраков данных требует серии шагов в отношении ряда зависимостей. |
Последний этап Листинга 3 является специфичным для операционной системы. В этом примере я демонстрирую использование утилиты shred в Linux/Unix. Другие утилиты, которые вы можете найти для очистки в Linux/Unix, это программы wipe, scrub и srm.
Подготовка к потенциальным операциям по смене ключа
Мой клиент хотел также быть готовым к будущим ситуациям, в результате которых может потребоваться операция по смене ключа. Смена ключа для существующих данных означает, что вы перешифровываете существующие данные с новым ключом. Это может потребоваться, если допускается, что кто-нибудь получил доступ к ключам существующей таблицы и потенциально может расшифровать уязвимые данные, находясь вне вашего контроля.
Мои шаги для выполнения операции по смене ключа похожи на шаги для первоначального шифрования существующих данных: удаление индексов по целевому столбцу, смена ключа для данных столбца и затем перестроение удаленных индексов. Дополнительно, если вы беспокоитесь о копиях-призраках соответствующих данных, зашифрованных с предыдущим ключом, надо будет повторить процесс перемещения сегментов в новое табличное пространство, удалив старое табличное пространство и разрушив файлы данных старого табличного пространства.
Замечание: по моему опыту PCI-аудиторы счастливы уже от смены основного ключа, так как это не требует никакого доступа к данным, а PCI-стандарт не содержит рекомендаций для двухуровневой архитектуры ключей, такой как в Oracle. Смены основного ключа шифрования должно хватать для соответствия требованиям PCI, а PCI-аудиторы не могут заставить компании, которые имеют биллионы строк, остановить бизнес на пару дней только для смены ключа.
Выявление потенциальных изменений в планах запросов
Документация по Oracle, несколько статей, и некоторые посты форумов, которые я прочитал, упоминают весьма обобщенно и некоторую особенную информацию о возможных изменениях, которые могут произойти с существующими планами выполнения запросов после шифрования данных столбца. В общем, необходимо обратить внимание на то, что произошло с выполнением SQL-предложений после шифрования индексированного столбца в отличие от столбца без индекса. Когда Oracle зашифровывает индексированный столбец, Oracle также зашифровывает соответствующие значения индекса. Если немного подумать, станет ясно, что предикаты с равенством целевым индексированным данным будут продолжать использовать индекс; однако, случайная природа зашифрованных значений делает цену сканирования по диапазону в зашифрованном индексе недопустимой из-за того способа, по которому значения в индексе отсортированы. Листинг 4 демонстрирует эти основные, хорошо документированные сценарии.
SQL> CONNECT app_001
Enter password:
Connected.
SQL> -- Создание таблицы для плана выполнения
SQL> @?/rdbms/admin/utlxplan.sql;
Table created.
SQL> -- Отключение шифрования столбца credit_card
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card DECRYPT);
Table altered.
SQL> -- Гарантия актуальности сбора статистики
SQL> EXEC dbms_stats.gather_schema_stats('app_001');
PL/SQL procedure successfully completed.
SQL> -- Отображение некоторых показательных данных
SQL> COL credit_card FOR 9999999999999999;
SQL> SELECT * FROM app_001.transactions
2 WHERE rownum < 5;
TRANS_ID CREDIT_CARD
---------- -----------------
389 3469681098409570
390 3441050723354352
391 3485598407754404
392 3485458104610650
SQL> -- Включение трассировки и вывода плана выполнения
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> -- Демонстрация эквивалентности поиска по условию
SQL> -- с зашифрованным столбцом
SQL> SELECT * FROM app_001.transactions
2 WHERE credit_card = 3485458104610650;
TRANS_ID CREDIT_CARD
---------- -----------------
392 3485458104610650
Execution Plan
----------------------------------------------------------
Plan hash value: 32329967
-------------------------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
-------------------------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 1 / 14 / 5 (0)/ 00:00:01 /
/ 1 / TABLE ACCESS BY INDEX ROWID/ TRANSACTIONS / 1 / 14 / 5 (0)/ 00:00:01 /
/* 2 / INDEX RANGE SCAN / TRANSACTIONS_NDX1 / 1 / / 3 (0)/ 00:00:01 /
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREDIT_CARD"=3485458104610650)
SQL> -- Демонстрация обработки предиката с диапазоном
SQL> -- по зашифрованному столбцу
SQL> SELECT * FROM app_001.transactions
2 WHERE credit_card BETWEEN 3499990000000000 AND 3499999999999999;
TRANS_ID CREDIT_CARD
---------- -----------------
4629 3499990987277941
18597 3499993250694089
13364 3499996558049599
79326 3499996616476145
60420 3499997873591732
24392 3499998608513414
97433 3499999831086288
72183 3499999977925392
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 32329967
-------------------------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
-------------------------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 3 / 42 / 6 (0)/ 00:00:01 /
/ 1 / TABLE ACCESS BY INDEX ROWID/ TRANSACTIONS / 3 / 42 / 6 (0)/ 00:00:01 /
/* 2 / INDEX RANGE SCAN / TRANSACTIONS_NDX1 / 3 / / 3 (0)/ 00:00:01 /
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREDIT_CARD">=3499990000000000 AND "CREDIT_CARD"<=3499999999999999)
SQL> -- Отключение трассировки и вывода плана выполнения
SQL> SET AUTOTRACE OFF;
SQL> -- Шифрование столбца (и индексов)
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card ENCRYPT NO SALT);
Table altered.
SQL> -- Гарантия актуальности сбора статистики
SQL> EXEC dbms_stats.gather_schema_stats('app_001');
PL/SQL procedure successfully completed.
SQL> -- Включение трассировки и вывода плана выполнения
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> -- Повторное выполнение предыдущих запросов, сравнение планов выполнения
SQL> SELECT * FROM app_001.transactions
2 WHERE credit_card = 3485458104610650;
TRANS_ID CREDIT_CARD
---------- -----------------
392 3485458104610650
Execution Plan
----------------------------------------------------------
Plan hash value: 32329967
-------------------------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
-------------------------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 1 / 14 / 5 (0)/ 00:00:01 /
/ 1 / TABLE ACCESS BY INDEX ROWID/ TRANSACTIONS / 1 / 14 / 5 (0)/ 00:00:01 /
/* 2 / INDEX RANGE SCAN / TRANSACTIONS_NDX1 / 1 / / 3 (0)/ 00:00:01 /
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREDIT_CARD"=3485458104610650)
SQL> SELECT * FROM app_001.transactions
2 WHERE credit_card BETWEEN 3499990000000000 AND 3499999999999999;
TRANS_ID CREDIT_CARD
---------- -----------------
60420 3499997873591732
4629 3499990987277941
18597 3499993250694089
13364 3499996558049599
24392 3499998608513414
79326 3499996616476145
72183 3499999977925392
97433 3499999831086288
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1321366336
----------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
----------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 1250 / 17500 / 914 (2)/ 00:00:11 /
/* 1 / TABLE ACCESS FULL/ TRANSACTIONS / 1250 / 17500 / 914 (2)/ 00:00:11 /
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("CREDIT_CARD")>=3499990000000000 AND
INTERNAL_FUNCTION("CREDIT_CARD")<=3499999999999999)
Execution Plan
----------------------------------------------------------
Plan hash value: 1321366336
----------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
----------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 1250 / 17500 / 914 (2)/ 00:00:11 /
/* 1 / TABLE ACCESS FULL/ TRANSACTIONS / 1250 / 17500 / 914 (2)/ 00:00:11 /
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("CREDIT_CARD")>=3499990000000000 AND
INTERNAL_FUNCTION("CREDIT_CARD")<=3499999999999999)
SQL> -- Disable tracing and explain plan output
SQL> SET AUTOTRACE OFF;
SQL> -- Отключение трассировки и вывода плана выполнения SQL> SET AUTOTRACE OFF;
Листинг 4 Тщательно поищите SQL-предложения, которые ссылаются на данные зашифрованного столбца, и затем сравните планы выполнения этих предложений до и после шифрования, чтобы увидеть, есть изменения или нет. |
Я хотел также узнать, могут ли издержки шифрования изменить цену плана и выбор оптимизатора, даже в тех случаях, когда документация предполагает, что этого быть не должно. Чтобы убедиться (это я точно узнал), что случится с критически-важным приложением после перехода на производственную среду, я проделал некоторую дополнительную работу в "песочнице". Сначала я определил список Top SQL-предложений (CPU, Gets, и I/O) из различных моментальных снимков Automatic Workload Repository (AWR). Затем я сравнил планы выполнения запросов каждого SQL-предложения до и после шифрования столбца. Я нашел сложный запрос, который использовал предикат с несколькими условиями на равенство, одно из которых по целевому столбцу, который надо было зашифровать. К моему удивлению, план выполнения этого запроса изменился после шифрования столбца. К сожалению, для этой статьи я не смог продублировать эти результаты в моей тестовой среде, и я не совсем понимаю, почему план выполнения изменился. Однако, я упоминаю здесь этот случай, чтобы заострить внимание на том, что всегда полезно посмотреть на планы выполнения ключевых запросов производственного приложения в тестовой среде перед тем, как вносить изменения в производственную систему. Если бы я предположил, что ни один из наиболее частых запросов не будет изменен, мы бы внесли изменения в производственную систему, и должны были бы в спешке хвататься за решение.
Урок заключается в том, что следует всегда выполнять тестирование до того, как делать изменения, независимо от того, что написано в документации и других источниках.
Заключение
Использование возможностей Oracle TDE для шифрования столбцов новых таблиц, таблиц без данных, или новых столбцов в существующих таблицах тривиально, потому что не существует никаких зависимостей, о которых необходимо беспокоиться. А вот шифрование существующих данных столбца требует внимательного изучения и тестирования в "песочнице" перед тем, как реализовывать план в живой производственной системе, из-за множества зависимостей, на которые может повлиять шифрование.