sec:oracle_tde
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
sec:oracle_tde [2018/07/08 15:46] – [Transparent Database Encryption TDE] gpipperr | sec:oracle_tde [2018/07/08 15:48] (aktuell) – [Transparent Database Encryption TDE] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ====== Transparent Database Encryption TDE ====== | ||
+ | **11g/12c** | ||
+ | |||
+ | |||
+ | <fc # | ||
+ | |||
+ | |||
+ | === Einsatz Database Vault === | ||
+ | |||
+ | Stichwort:< | ||
+ | <code bash> | ||
+ | .. | ||
+ | Separation of duties (SoD)(Also known as Segregation of Duties) is the concept of having more than one person required to complete a task. | ||
+ | .. | ||
+ | </ | ||
+ | |||
+ | => http:// | ||
+ | === Lizenz === | ||
+ | |||
+ | Für TDE wird die EE Edition + Oracle Advanced Security Option benötigt! | ||
+ | |||
+ | |||
+ | |||
+ | ==== 1. Pfad zur Wallet angeben ==== | ||
+ | |||
+ | in der sqlnet.ora unter dem $ORACLE_HOME/ | ||
+ | < | ||
+ | ENCRYPTION_WALLET_LOCATION= | ||
+ | (SOURCE=(METHOD=FILE)(METHOD_DATA= | ||
+ | | ||
+ | </ | ||
+ | |||
+ | ==== 2. Wallet erzeugen und Passwort setzen ==== | ||
+ | |||
+ | <code plsql> | ||
+ | ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY " | ||
+ | </ | ||
+ | |||
+ | !Wird das Passwort ohne "" | ||
+ | |||
+ | ==== 3. Beim Start der Datenbank das Wallet öffnen ===== | ||
+ | |||
+ | <code plsql> | ||
+ | -- nach dem Start der DB | ||
+ | -- Zugriff auf eine Tabelle im verschlüsselten Tablespace | ||
+ | FEHLER in Zeile 1: | ||
+ | ORA-28365: Wallet ist nicht geoffnet | ||
+ | |||
+ | ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY " | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ==== 4. Tablespace verschlüsseln ==== | ||
+ | |||
+ | <code plsql> | ||
+ | CREATE TABLESPACE " | ||
+ | | ||
+ | SIZE 100M | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | </ | ||
+ | |||
+ | Überwachen mit: | ||
+ | |||
+ | <code plsql> | ||
+ | sqlplus> | ||
+ | |||
+ | Tablespace ENCRYPTED | ||
+ | ---------- --- | ||
+ | SYSTEM | ||
+ | SYSAUX | ||
+ | ..... | ||
+ | SECUDATA | ||
+ | DATA YES | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Daten in einer existierenden Tabelle verschlüsseln ==== | ||
+ | |||
+ | Beispiel: | ||
+ | |||
+ | <code sql> | ||
+ | alter table scott.emp | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Die Wallet anschauen ==== | ||
+ | |||
+ | |||
+ | mit dem Befehl mkstore kann die Wallet in der Kommandozeile geöffnet werden: | ||
+ | |||
+ | <code bash> | ||
+ | |||
+ | cd / | ||
+ | |||
+ | mkstore -wrl . -list | ||
+ | |||
+ | Enter wallet password: | ||
+ | |||
+ | |||
+ | ORACLE.SECURITY.DB.ENCRYPTION.AV7BL7Db2U/ | ||
+ | ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY | ||
+ | ORACLE.SECURITY.ID.ENCRYPTION. | ||
+ | ORACLE.SECURITY.KB.ENCRYPTION. | ||
+ | |||
+ | |||
+ | #einfacher | ||
+ | |||
+ | mkstore -wrl . -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY | ||
+ | ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AV7BL7Db2U/ | ||
+ | </ | ||
+ | |||
+ | |||
+ | ==== Den Master Key in der DB anschauen ==== | ||
+ | |||
+ | Den Master Key findet man in der DB für Tabellen in der enc$, für den Controlfile in der x$kcbdbk, für Tablespaces in der x$kcbtek . | ||
+ | |||
+ | Siehe dazu auch die Node Doc ID 1541818.1 für die SQL's. | ||
+ | |||
+ | |||
+ | Zusammengefasst, | ||
+ | |||
+ | <code sql wallet.sql> | ||
+ | ttitle left "The Path to the Wallet" | ||
+ | |||
+ | |||
+ | column WRL_TYPE | ||
+ | column WRL_PARAMETER format a20 heading " | ||
+ | column STATUS | ||
+ | column WALLET_TYPE | ||
+ | column WALLET_ORDER | ||
+ | column FULLY_BACKED_UP | ||
+ | column CON_ID | ||
+ | column inst_id format 999 heading " | ||
+ | |||
+ | |||
+ | select inst_id | ||
+ | , WRL_TYPE | ||
+ | , | ||
+ | ,STATUS | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | ,CON_ID | ||
+ | from gv$encryption_wallet | ||
+ | / | ||
+ | |||
+ | |||
+ | |||
+ | column name format a40 | ||
+ | column masterkeyid_base64 format a60 | ||
+ | |||
+ | ttitle left "Get the Master Key for Tablespaces" | ||
+ | |||
+ | select | ||
+ | , | ||
+ | FROM (select t.name, RAWTOHEX(x.mkid) mkeyid | ||
+ | from v$tablespace t | ||
+ | , x$kcbtek x | ||
+ | where t.ts# | ||
+ | / | ||
+ | |||
+ | |||
+ | ttitle left "Get the Master Key for the Controlfile" | ||
+ | |||
+ | select | ||
+ | FROM (select RAWTOHEX(mkid) mkeyid | ||
+ | from x$kcbdbk) | ||
+ | / | ||
+ | |||
+ | |||
+ | ttitle left "Get the Master Key for Tables" | ||
+ | |||
+ | select mkeyid from enc$; | ||
+ | |||
+ | |||
+ | ttitle left "Witch Columns are encrypted?" | ||
+ | |||
+ | column owner format a15 heading " | ||
+ | column table_name | ||
+ | column column_name | ||
+ | column ENCRYPTION_ALG format a35 heading " | ||
+ | |||
+ | select owner | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | from dba_encrypted_columns | ||
+ | / | ||
+ | |||
+ | ttitle off | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ====Backup verschlüssen==== | ||
+ | |||
+ | Um nun nur das Backup zu verschlüsseln, | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | CONFIGURE ENCRYPTION FOR DATABASE ON; | ||
+ | </ | ||
+ | |||
+ | Alle nun folgenden Backups werden verschlüsselt. | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Wallet geht verloren=== | ||
+ | |||
+ | Um ein Backup wieder einzuspielen, | ||
+ | |||
+ | Aber wass passiert wenn die Wallet verloren geht? | ||
+ | |||
+ | Entsteht dadurch ein neuer Masterkey? | ||
+ | |||
+ | === Neuanlegen einer Wallet mit 12c=== | ||
+ | |||
+ | Wallet löschen und neu anlegen mit dem alten Passwort | ||
+ | <code sql> | ||
+ | |||
+ | sqlplus> | ||
+ | |||
+ | cd / | ||
+ | rm * | ||
+ | |||
+ | |||
+ | sqlplus> | ||
+ | |||
+ | sqlplus> | ||
+ | ERROR at line 1: | ||
+ | ORA-28374: typed master key not found in wallet | ||
+ | |||
+ | ???? | ||
+ | |||
+ | sqlplus> | ||
+ | |||
+ | sqlplus> | ||
+ | System altered. | ||
+ | |||
+ | </ | ||
+ | Wallet wird angelegt und kann beim nächsten Start auch wieder mit dem obigen Passwort geöffent werden | ||
+ | |||
+ | Hat sich der Key verändert? testen: | ||
+ | <code bash> | ||
+ | | ||
+ | |||
+ | Enter wallet password: | ||
+ | |||
+ | ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AZAZUSZj3k/ | ||
+ | |||
+ | </ | ||
+ | |||
+ | Der alte Key war " | ||
+ | |||
+ | Das alte Backup kann dann aber NICHT mehr wiederhergestellt werden, RMAN funktioniert und kann die Wallet öffnen, aber: | ||
+ | <code sql> | ||
+ | ORA-19913: unable to decrypt backup | ||
+ | </ | ||
+ | |||
+ | <note warning> | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ===== Quellen ===== | ||
+ | |||
+ | |||
+ | Support: | ||
+ | |||
+ | 11g | ||
+ | * NOTE: | ||
+ | * Step by Step Troubleshooting Guide for TDE Error ORA-28374 (Doc ID 1541818.1) | ||
+ | |||
+ | 12c | ||
+ | * Getting Started With Transparent Data Encryption in Oracle 12c (non pluggable database ) (Doc ID 1964158.1) | ||
+ | |||
+ | |||
+ | Oracle: | ||
+ | |||
+ | * http:// | ||
+ | * http:// | ||
+ | * http:// | ||
+ | * http:// | ||
+ | |||
+ | Netz: | ||
+ | * http:// | ||
+ | |||
+ | |||
+ | |||
+ | Interessante Links zum Thema: | ||
+ | [[http:// | ||
sec/oracle_tde.txt · Zuletzt geändert: 2018/07/08 15:48 von gpipperr