On commence par créer une nouvelle BD pour les test.
Notes:
-Le SID de ma BD de test : TDE11G
-L'emplacement de mon wallet : /u01/PATH/TDE/$ORACLE_SID
-Le mot de passe de mon wallet : MonMotDePasse
Une fois créée la nouvelle BD :
#######################################################
## Vérification les clés avant de commencer: ##
#######################################################
SELECT MKEYID FROM ENC$;
no rows selected
SET LINESIZE 150
COLUMN NAME FORMAT A40
COLUMN MASTERKEYID_BASE64 FORMAT A60
SELECT NAME,UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE('01'||SUBSTR(MKEYID,1,4))) || UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE(SUBSTR(MKEYID,5,LENGTH(MKEYID)))) MASTERKEYID_BASE64
FROM (SELECT T.NAME, RAWTOHEX(X.MKID) MKEYID FROM V$TABLESPACE T, X$KCBTEK X WHERE T.TS#=X.TS#);
NAME MASTERKEYID_BASE64
---------------------------------------- ------------------------------
SYSTEM AVpgMVXgS8u5ts768mpzTuQ=
SYSAUX AQAAAAAAAAAAAAAAAAAAAAA=
UNDOTBS1 AQAAAAAAAAAAAAAAAAAAAAA=
USERS AQAAAAAAAAAAAAAAAAAAAAA=
TEMP AQAAAAAAAAAAAAAAAAAAAAA=
SELECT UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE('01'||SUBSTR(MKEYID,1,4))) || UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE(SUBSTR(MKEYID,5,LENGTH(MKEYID)))) MASTERKEYID_BASE64
FROM (SELECT RAWTOHEX(MKID) MKEYID FROM X$KCBDBK);
MASTERKEYID_BASE64
--------------------------
AVpgMVXgS8u5ts768mpzTuQ=
SELECT * FROM V$WALLET ;
no rows selected
#######################################################
## Valider l''état du wallet : ##
#######################################################
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID CLOSED
Copier le wallet sur : /u01/PATH/TDE/TDE11G#
cd /u01/PATH/TDE/$ORACLE_SID
chmod 600 *
$/u01/PATH/TDE/TDE11G# ll
-rw------- 1 oraXXXXX oinstall 3.9K May 26 09:07 ewallet.p12
orapki wallet display -wallet ewallet.p12
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Requested Certificates:
User Certificates:
Subject: OU=VTIJERO_OU,O=VTIJERO_COMPANY_TDE,EmailAddress=VTIJERO@COMPANY.COM,C=CA,ST=EVTA_QUEBEC,CN=VTIJERO_TDE_COMMONNAME
Trusted Certificates:
Subject: CN=VTIJERO_ROOT_COMMONNAME,C=CA,ST=QUEBEC,L=QUEBEC,EmailAddress=VTIJERO@COMPANY.COM,OU=VTIJERO_OU,O=VTIJERO_COMPANY
mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
#######################################################
## Création des Tablespaces et Tables sans chiffrer :##
#######################################################
Création des Tablespace/user :
CREATE TABLESPACE tde_test DATAFILE '+DATA' SIZE 10m AUTOEXTEND ON NEXT 1M;
CREATE USER TDETEST IDENTIFIED BY tdetest DEFAULT TABLESPACE tde_test;
ALTER USER TDETEST QUOTA UNLIMITED ON TDE_TEST;
GRANT CONNECT TO TDETEST;
GRANT CREATE TABLE TO TDETEST;
Création des objets
CONN TDETEST/TDETEST
CREATE TABLE TDE_TEST_NORMAL ( ID NUMBER(10), DATA VARCHAR2(50)) TABLESPACE TDE_TEST;
INSERT INTO TDE_TEST_NORMAL (id, data) VALUES (1, 'This is visible');
COMMIT;
SELECT * FROM TDE_TEST_NORMAL ;
ID DATA
---------- --------------------------------------------------
1 This is visible
En tant que SYS :
ALTER SYSTEM SWITCH LOGFILE ; -- 3 fois
#######################################################
## Valider et utiliser le certificate ##
#######################################################
Ouvrir le wallet
SELECT CERT_ID,ISSUER,DN FROM V$WALLET;
no rows selected
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "MonMotDePasse";
SELECT CERT_ID, DN, STATUS FROM V$WALLET;
CERT_ID DN STATUS
---------------------------------------------------- ----------------------------------------------------------------------------------------------------------- ---------
AoquDzCabPW4t20s/609CHsBAAAAAAAAAAAAAAAAAAAAAAAAAAAA CN=VTIJERO_TDE_COMMONNAME,ST=EVTA_QUEBEC,C=CA,EMAIL=VTIJERO@COMPANY.COM,O=VTIJERO_COMPANY_TDE,OU=VTIJERO_OU AVAILABLE
ALTER SYSTEM SWITCH LOGFILE ;
Ici, il faut se servir de la valeur du CERT_ID
ALTER SYSTEM SET ENCRYPTION KEY "AoquDzCabPW4t20s/609CHsBAAAAAAAAAAAAAAAAAAAAAAAAAAAA" identified by "MonMotDePasse";
ALTER SYSTEM SWITCH LOGFILE ;
Valider l''état du wallet
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID OPEN
#######################################################
## Création de la Table avec colonnes chiffrées : ##
#######################################################
Création des objets :
CONN tdetest/tdetest
CREATE TABLE TDE_TEST_ENCRYPT ( id NUMBER(10), data VARCHAR2(50) ENCRYPT NO SALT ) TABLESPACE TDE_TEST;
INSERT INTO tde_test_encrypt (id, data) VALUES (1, 'This is a secret!');
COMMIT;
SELECT * FROM tde_test_encrypt ;
ID DATA
---------- --------------------------------------------------
1 This is a secret!
SELECT * FROM tde_test_normal ;
ID DATA
---------- --------------------------------------------------
1 This is visible
Fermer le wallet :
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "MonMotDePasse" ;
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID CLOSED
SELECT * FROM tdetest.tde_test_encrypt ;
ERROR at line 1:
ORA-28365: wallet is not open
SELECT * FROM tdetest.tde_test_normal ;
ID DATA
---------- --------------------------------------------------
1 This is visible
#######################################################
## Valider le MasterKey : ##
#######################################################
cd /u01/PATH/TDE/TDE11G#
orapki wallet display -wallet ewallet.p12
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Requested Certificates:
User Certificates:
Subject: OU=VTIJERO_OU,O=VTIJERO_COMPANY_TDE,EmailAddress=VTIJERO@COMPANY.COM,C=CA,ST=EVTA_QUEBEC,CN=VTIJERO_TDE_COMMONNAME
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AoquDzCabPW4t20s/609CHsBAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Trusted Certificates:
Subject: CN=VTIJERO_ROOT_COMMONNAME,C=CA,ST=QUEBEC,L=QUEBEC,EmailAddress=VTIJERO@COMPANY.COM,OU=VTIJERO_OU,O=VTIJERO_COMPANY
#######################################################
## Vérification les clés une fois tout configuré ##
#######################################################
mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AoquDzCabPW4t20s/609CHsBAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
SELECT MKEYID FROM ENC$;
MKEYID
----------------------------------------------------------------
AoquDzCabPW4t20s/609CHsBAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SET LINESIZE 150
COLUMN NAME FORMAT A40
COLUMN MASTERKEYID_BASE64 FORMAT A60
SELECT NAME,UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE('01'||SUBSTR(MKEYID,1,4))) || UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE(SUBSTR(MKEYID,5,LENGTH(MKEYID)))) MASTERKEYID_BASE64
FROM (SELECT T.NAME, RAWTOHEX(X.MKID) MKEYID FROM V$TABLESPACE T, X$KCBTEK X WHERE T.TS#=X.TS#);
NAME MASTERKEYID_BASE64
---------------------------------------- ------------------------------------------------------------
SYSTEM AVpgMVXgS8u5ts768mpzTuQ=
SYSAUX AQAAAAAAAAAAAAAAAAAAAAA=
UNDOTBS1 AQAAAAAAAAAAAAAAAAAAAAA=
USERS AQAAAAAAAAAAAAAAAAAAAAA=
TDE_TEST AQAAAAAAAAAAAAAAAAAAAAA=
TEMP AQAAAAAAAAAAAAAAAAAAAAA=
SELECT UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE('01'||SUBSTR(MKEYID,1,4))) || UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE(SUBSTR(MKEYID,5,LENGTH(MKEYID)))) MASTERKEYID_BASE64
FROM (SELECT RAWTOHEX(MKID) MKEYID FROM X$KCBDBK);
MASTERKEYID_BASE64
------------------------------------------------------------
AVpgMVXgS8u5ts768mpzTuQ=
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "MonMotDePasse" ;
SELECT * FROM V$WALLET ;
CERT_ID DN SERIAL_NUM ISSUER KEYSIZE STATUS
---------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -------------- ----------------------------------------------------------------------------------------------------------------------- ---------- ----------------
AoquDzCabPW4t20s/609CHsBAAAAAAAAAAAAAAAAAAAAAAAAAAAA CN=VTIJERO_TDE_COMMONNAME,ST=EVTA_QUEBEC,C=CA,EMAIL=VTIJERO@COMPANY.COM,O=VTIJERO_COMPANY_TDE,OU=VTIJERO_OU 0x01 O=VTIJERO_COMPANY,OU=VTIJERO_OU,EMAIL=VTIJERO@COMPANY.COM,L=QUEBEC,ST=QUEBEC,C=CA,CN=VTIJERO_ROOT_COMMONNAME 2048 IN USE
SET LINESIZE 2000
SELECT OWNER, TABLE_NAME, COLUMN_NAME
FROM DBA_ENCRYPTED_COLUMNS
ORDER BY OWNER ;
OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
TDETEST TDE_TEST_ENCRYPT DATA
#######################################################
#######################################################
## ##
## TEST : Après de l'expiration du wallet ##
## ##
#######################################################
#######################################################
État du Wallet :
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID OPEN
SELECT * FROM TDETEST.TDE_TEST_NORMAL ;
ID DATA
---------- --------------------------------------------------
1 This is visible
SELECT * FROM TDETEST.TDE_TEST_ENCRYPT ;
ID DATA
---------- --------------------------------------------------
1 This is a secret!
Fermer le wallet :
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "MonMotDePasse" ;
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID CLOSED
SELECT * FROM TDETEST.TDE_TEST_NORMAL ;
ID DATA
---------- --------------------------------------------------
1 This is visible
SELECT * FROM TDETEST.TDE_TEST_ENCRYPT ;
ERROR at line 1:
ORA-28365: wallet is not open
Ouvrir le wallet :
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "MonMotDePasse" ;
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID OPEN
SELECT * FROM TDETEST.TDE_TEST_NORMAL ;
ID DATA
---------- --------------------------------------------------
1 This is visible
SELECT * FROM TDETEST.TDE_TEST_ENCRYPT ;
ERROR at line 1:
ORA-28365: wallet is not open
Repartir la BD :
État du Wallet :
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID CLOSED
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "MonMotDePasse" ;
SET LINESIZE 2000
COL WRL_PARAMETER FORMAT A50
SELECT * FROM V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------
file /u01/PATH/TDE/$ORACLE_SID OPEN
SELECT * FROM TDETEST.TDE_TEST_NORMAL ;
ID DATA
---------- --------------------------------------------------
1 This is visible
SELECT * FROM TDETEST.TDE_TEST_ENCRYPT ;
ID DATA
---------- --------------------------------------------------
1 This is a secret!
Conclusion :
Après ces essais l'accès aux données est toujours possible même avec un certificat qui a expiré.
