Fully Encrypted Database und TDE Live-Conversion: Ein neuer Evolutionsschritt der Datenbank-Verschlüsselung
Norman Sibbing |
![]() |
Das weltweit meist eingesetzte Sicherheitsfeature für die Oracle Datenbank ist definitiv die Transparent Data Encryption (TDE). Eingeführt wurde die Oracle Datenbank-Verschlüsselungstechnologie bereits 2005 mit der Datenbank Version 10.2.
Seit dem wurde sie von Version zu Version stetig erweitert und der Funktionsumfang vergrößert.
Der Grund warum eine Datenbank verschlüsselt werden sollte, wird in den folgenden Beispielen noch einmal deutlich. Überall dort, wo Datenbank-Files physikalisch im Zugriff sind, kann mittels einer einfachen Strings-Suche nach Informationen gesucht werden.
Das folgende Beispiel zeigt, wie man in einer Datendatei nach Email-Adressen suchen kann, und das mit Bordmitteln von Linux.
[oracle@dbserver pdb1]$ ls -la total 1048552 drwxr-x---. 2 oracle oinstall 4096 8. Mär 10:48 . drwxr-x---. 4 oracle oinstall 4096 8. Mär 10:47 .. -rw-rw----. 1 oracle oinstall 104865792 8. Mär 11:07 data_ts.dbf -rw-r-----. 1 oracle oinstall 461381632 8. Mär 11:12 sysaux01.dbf -rw-r-----. 1 oracle oinstall 272637952 8. Mär 11:12 system01.dbf -rw-r-----. 1 oracle oinstall 135274496 8. Mär 10:39 temp01.dbf -rw-r-----. 1 oracle oinstall 104865792 8. Mär 11:12 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 8. Mär 10:43 users01.dbf [oracle@dbserver pdb1]$ strings data_ts.dbf | grep -E -o "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}\b" Klaas.Vaak@mycompany.com Klaas.Franken@mycompany.com Bas.Degraaf@mycompany.com Joop.Jansen@mycompany.com Henk.Pietersen@mycompany.com Hugo.Borst@mycompany.com Karel.Karelse@mycompany.com tim.krabe@mycompany.com hans.krabe@mycompany.com joop.azier@mycompany.com
[oracle@dbserver pdb1]$ strings system01.dbf | grep -E -o "S:\w+;T:\w+" S:073B974663C7733814745CABBDCFE597721B722F34EE5803D9C2734B75BC;T:9793EE38331120623CC830D95364EF9F48D65921744BA528123DB7BC29EA68B7054B7DE21B72A9E455404F504AABBE28125FB8EE905EDA785D8FC7B2099C5E66195DE880E88984776778A2A8A8F303C5 S:AAB8B05C16460D5EB1ACE0C2A911D84A1499C71E2974DF524E43A1A422C5;T:4039259BD0EE6BD5EC390A9DC407BF0DD0953B0955188475486BA5D1A6547F52282304F3C0AB0BDF10723FBF7DFE22AAEBCBBF3299883B2FFF1A4F1990C7DF177140F1949961224A6F174887F767F6DB
Fully Encrypted Database
TDE Live Conversion
Online-Conversion
SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from USER_TABLESPACES; TABLESPACE_NAME STATUS ENC ------------------------------ --------- --- SYSTEM ONLINE NO SYSAUX ONLINE NO UNDOTBS1 ONLINE NO TEMP ONLINE NO USERS ONLINE NO DATA_TS ONLINE NO 6 rows selected.
SQL> ALTER TABLESPACE DATA_TS ENCRYPTION ONLINE USING 'AES192' ENCRYPT FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB1/pdb1/data_ts.dbf', '/u01/app/oracle/oradata/CDB1/pdb1/data_ts_enc.dbf'); Tablespace altered. SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from USER_TABLESPACES; TABLESPACE_NAME STATUS ENC ------------------------------ --------- --- SYSTEM ONLINE NO SYSAUX ONLINE NO UNDOTBS1 ONLINE NO TEMP ONLINE NO USERS ONLINE NO DATA_TS ONLINE YES 6 rows selected.
[oracle@dbserver pdb1]$ ls -la total 956392 drwxr-x---. 2 oracle oracle 4096 8. Mär 12:53 . drwxr-x---. 4 oracle oracle 4096 8. Mär 10:47 .. -rw-rw----. 1 oracle oracle 10493952 8. Mär 12:53 data_ts_enc.dbf -rw-r-----. 1 oracle oracle 461381632 8. Mär 12:52 sysaux01.dbf -rw-r-----. 1 oracle oracle 272637952 8. Mär 12:49 system01.dbf -rw-r-----. 1 oracle oracle 135274496 8. Mär 10:39 temp01.dbf -rw-r-----. 1 oracle oracle 104865792 8. Mär 12:51 undotbs01.dbf -rw-rw----. 1 oracle oracle 5251072 8. Mär 12:37 users01.dbf [oracle@dbserver pdb1]$ strings data_ts_enc.dbf | grep -E -o "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}\b" [oracle@dbserver pdb1]$
SQL> ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB1/pdb1/system01.dbf','/u01/app/oracle/oradata/CDB1/pdb1/system01_enc.dbf'); Tablespace altered. SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from USER_TABLESPACES; TABLESPACE_NAME STATUS ENC ------------------------------ --------- --- SYSTEM ONLINE YES SYSAUX ONLINE NO UNDOTBS1 ONLINE NO TEMP ONLINE NO USERS ONLINE NO DATA_TS ONLINE YES 6 rows selected.
[oracle@dbserver pdb1]$ ls -la total 956392 drwxr-x---. 2 oracle oracle 4096 8. Mär 13:08 . drwxr-x---. 4 oracle oracle 4096 8. Mär 10:47 .. -rw-rw----. 1 oracle oracle 10493952 8. Mär 12:53 data_ts_enc.dbf -rw-r-----. 1 oracle oracle 461381632 8. Mär 12:57 sysaux01.dbf -rw-rw----. 1 oracle oracle 272637952 8. Mär 13:08 system01_enc.dbf -rw-r-----. 1 oracle oracle 135274496 8. Mär 10:39 temp01.dbf -rw-r-----. 1 oracle oracle 104865792 8. Mär 13:08 undotbs01.dbf -rw-rw----. 1 oracle oracle 5251072 8. Mär 12:37 users01_enc.dbf [oracle@dbserver pdb1]$ strings system01_enc.dbf | grep -E -o "S:\w+;T:\w+" [oracle@dbserver pdb1]$
SQL> select TS#, ENCRYPTIONALG from V$ENCRYPTED_TABLESPACES; TS# ENCRYPT ---------- ------- 0 AES128 6 AES192
SQL> ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE USING 'AES256' REKEY FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB1/pdb1/system01_enc.dbf','/u01/app/oracle/oradata/CDB1/pdb1/system01_enc256.dbf'); Tablespace altered. SQL> select TS#, ENCRYPTIONALG from V$ENCRYPTED_TABLESPACES; TS# ENCRYPT ---------- ------- 0 AES256 6 AES192
SQL> ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB1/pdb1/sysaux01.dbf','/u01/app/oracle/oradata/CDB1/pdb1/sysaux01_enc.dbf'); Tablespace altered. SQL> ALTER TABLESPACE UNDOTBS1 ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB1/pdb1/undotbs01.dbf','/u01/app/oracle/oradata/CDB1/pdb1/undotbs01_enc.dbf'); Tablespace altered.
SQL> CREATE TEMPORARY TABLESPACE temp_01 TEMPFILE '/u01/app/oracle/oradata/CDB1/pdb1/temp01_enc.dbf' SIZE 100M AUTOEXTEND ON ENCRYPTION ENCRYPT; Tablespace created. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_01; Database altered. SQL> drop tablespace temp including contents; Tablespace dropped.
SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from USER_TABLESPACES; TABLESPACE_NAME STATUS ENC ------------------------------ --------- --- SYSTEM ONLINE YES SYSAUX ONLINE YES UNDOTBS1 ONLINE YES USERS ONLINE YES DATA_TS ONLINE YES TEMP_01 ONLINE YES 6 rows selected.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "welcome1"; ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "welcome1" * ERROR at line 1: ORA-28439: cannot close wallet when SYSTEM, SYSAUX, UNDO, or TEMP tablespaces are encrypted
SQL> startup ORACLE instance started. Total System Global Area 1090519040 bytes Fixed Size 8791816 bytes Variable Size 419432696 bytes Database Buffers 654311424 bytes Redo Buffers 7983104 bytes Database mounted. ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-28365: wallet is not open Process ID: 19071 Session ID: 4 Serial number: 13571
[oracle@dbserver ~]$ sqlplus sys as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 13 14:46:39 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1090519040 bytes Fixed Size 8791816 bytes Variable Size 419432696 bytes Database Buffers 654311424 bytes Redo Buffers 7983104 bytes Database mounted. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY '*********'; keystore altered. SQL> alter database open; Database altered. SQL>
[oracle@dbserver wallet]$ ls -la total 24 drwxr-xr-x. 2 oracle oinstall 4096 Mar 13 14:58 . drwxr-x---. 7 oracle oinstall 4096 Mar 13 11:29 .. -rw-------. 1 oracle oinstall 2056 Mar 13 13:38 ewallet.p12 [oracle@dbserver wallet]$ orapki wallet create -wallet . -auto_login Oracle PKI Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Operation is successfully completed. [oracle@dbserver wallet]$ ls -la total 40 drwxr-xr-x. 2 oracle oinstall 4096 Mar 13 14:59 . drwxr-x---. 7 oracle oinstall 4096 Mar 13 11:29 .. -rw-------. 1 oracle oinstall 2101 Mar 13 14:59 cwallet.sso -rw-------. 1 oracle oinstall 0 Mar 13 14:59 cwallet.sso.lck -rw-------. 1 oracle oinstall 2056 Mar 13 13:38 ewallet.p12 -rw-------. 1 oracle oinstall 0 Mar 13 14:59 ewallet.p12.lck [oracle@dbserver wallet]$
[oracle@dbserver wallet]$ sqlplus sys as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 13 15:02:52 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1090519040 bytes Fixed Size 8791816 bytes Variable Size 419432696 bytes Database Buffers 654311424 bytes Redo Buffers 7983104 bytes Database mounted. Database opened. SQL>
Offline Conversion
ALTER TABLESPACE users OFFLINE NORMAL; ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/CDB1/pdb1/users01.dbf' ENCRYPT; ALTER TABLESPACE users ONLINE;
Lizenzhinweis:
Weitere Informationen
Zurück zum Anfang des Artikels
Zurück zur Community-Seite