Oracle LiveLabs实验：DB Security - Transparent Data Encryption (TDE)

2024-02-04 12:18
文章标签 oracle 实验 data db database security encryption tde transparent livelabs

本文主要是介绍Oracle LiveLabs实验：DB Security - Transparent Data Encryption (TDE)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

概述

此实验申请地址在这里，时间为1小时。

实验帮助在这里。

本实验使用的数据库为19.13。

Introduction

本研讨会介绍 Oracle 透明数据加密 (TDE) 的各种特性和功能。 它使用户有机会学习如何配置这些功能以加密敏感数据。

目标

  • 如果需要，对数据库进行冷备份以启用数据库恢复
  • 在数据库中启用透明数据加密
  • 使用透明数据加密加密数据

Task 1: Allow DB Restore

此步骤是了后续将数据库恢复为未加密状态。

进入实验目录：

sudo su - oracle
cd $DBSEC_LABS/tde

运行数据库备份：

./tde_backup_db.sh

这实际是个冷备份，即将数据库shutdown后，对数据文件目录进行tar。其实还备份了pfile，因为后续会修改系统参数。

一旦完成，它将自动重启容器和可插拔数据库。

Task 2: Create Keystore

在操作系统中创建Keystore目录：

./tde_create_os_directory.sh

创建的目录如下：

/etc/ORACLE/WALLETS/cdb1/tde
/etc/ORACLE/WALLETS/cdb1/tde_seps
/etc/ORACLE/WALLETS/cdb1/okv

使用数据库参数来管理 TDE。 这将需要重新启动数据库才能使其中一个参数生效。 该脚本将为您执行重新启动。

./tde_set_tde_parameters.sh

脚本运行前后的变化如下：

## 运行前
## ${ORACLE_HOME}/network/admin/sqlnet.ora为空NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          stringNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string## 运行后
## ${ORACLE_HOME}/network/admin/sqlnet.ora仍为空NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string      /etc/ORACLE/WALLETS/cdb1NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      keystore_configuration=FILE

为容器数据库创建软件密钥库 (Oracle Wallet)。 您将看到状态结果从 NOT_AVAILABLE 变为 OPEN_NO_MASTER_KEY。

./tde_create_wallet.sh

实际执行的命令和输出为：

-- . Display the status of the Keystore
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                        STATUS                         WALLET_TYPE
---------- ---------- ------------ ------------------------------------ ------------------------------ ------------1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/        NOT_AVAILABLE                  UNKNOWN2 PDB$SEED   FILE                                              NOT_AVAILABLE                  UNKNOWN3 PDB1       FILE                                              NOT_AVAILABLE                  UNKNOWN4 PDB2       FILE                                              NOT_AVAILABLE                  UNKNOWN-- . Create the Keystore for CDB
SQL> administer key management create keystore identified by ${DBUSR_PWD};keystore altered.-- . Create the Keystore for all PDBs
SQL> administer key management set keystore open identified by ${DBUSR_PWD} container=all;keystore altered.-- . Display the status of the Keystore
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                        STATUS                         WALLET_TYPE
---------- ---------- ------------ ------------------------------------ ------------------------------ ------------1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/        OPEN_NO_MASTER_KEY             PASSWORD2 PDB$SEED   FILE                                              OPEN_NO_MASTER_KEY             PASSWORD3 PDB1       FILE                                              OPEN_NO_MASTER_KEY             PASSWORD4 PDB2       FILE                                              OPEN_NO_MASTER_KEY             PASSWORD

现在，您的 Oracle 钱包已创建，状态为打开，但还没有Master Key！

Task 3: Create Master Key

创建容器数据库 TDE 主密钥 (MEK)：

./tde_create_mek_cdb.sh

实际执行的命令和输出为：

-- . Show the status of the current Master Key (MEK)
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/            OPEN_NO_MASTER_KEY2 PDB$SEED   FILE                                                  OPEN_NO_MASTER_KEY3 PDB1       FILE                                                  OPEN_NO_MASTER_KEY4 PDB2       FILE                                                  OPEN_NO_MASTER_KEY-- . Create the CDB Master Key (MEK)
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'CDB1: Initial Master Key' IDENTIFIED BY ${DBUSR_PWD} WITH BACKUP container=current;
keystore altered.-- . Show the status of the current Master Key (MEK)
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/            OPEN2 PDB$SEED   FILE                                                  OPEN3 PDB1       FILE                                                  OPEN_NO_MASTER_KEY4 PDB2       FILE                                                  OPEN_NO_MASTER_KEY

为可插入数据库 pdb1 创建主密钥 (MEK)：

./tde_create_mek_pdb.sh pdb1

实际执行的命令和输出如下，命令和前面是一样的，但是为切换到PDB中执行：

-- 切换到PDB
SQL> alter session set container=${pdbname};-- . Show the status of the current Master Key (MEK)
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------3 PDB1       FILE                                                  OPEN_NO_MASTER_KEY-- . Create the CDB Master Key (MEK)
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG '${pdbname}: Initial Master Key' IDENTIFIED BY ${DBUSR_PWD} WITH BACKUP container=current;keystore altered.-- . Show the status of the current Master Key (MEK)
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------3 PDB1       FILE                                                  OPEN

如果你愿意，你可以对 pdb2 做同样的事情……这不是必需的，显示一些带有 TDE 的数据库和一些没有 TDE 的数据库可能会有所帮助：

./tde_create_mek_pdb.sh pdb2

现在，您有一个主密钥，您可以开始加密表空间或列！

Task 4: Create Auto-login Wallet

运行脚本以查看操作系统上的 Oracle Wallet 内容：

./tde_view_wallet_on_os.sh

输出为：

===================================================================================Display the Wallet info on the OS...
===================================================================================. Wallet location and files
/etc/ORACLE/WALLETS/cdb1
/etc/ORACLE/WALLETS/cdb1/tde
/etc/ORACLE/WALLETS/cdb1/tde/ewallet.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040512542332.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040512585020.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040513003638.p12
/etc/ORACLE/WALLETS/cdb1/tde_seps
/etc/ORACLE/WALLETS/cdb1/okv. Display the keystore from the OS-------------------------Note:To view it, run the following OS command:$ orapki wallet display -wallet /etc/ORACLE/WALLETS/cdb1/tde -pwd Oracle123-------------------------Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.9623C50C30AD638EE0532C00000A4926
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.9623C58F05F064BFE0532C00000ACDFE
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:

您可以查看 Oracle Wallet 在数据库中的样子：

./tde_view_wallet_in_db.sh

实际的执行与输出为：

-- . Display the keystore status
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                       STATUS                         WALLET_TYPE
---------- ---------- ------------ ----------------------------------- ------------------------------ ------------1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/       OPEN                           PASSWORD2 PDB$SEED   FILE                                             OPEN                           PASSWORD3 PDB1       FILE                                             OPEN                           PASSWORD4 PDB2       FILE                                             OPEN                           PASSWORD-- . Display the keys in the DB
SQL> select con_id, activation_time, key_use, tag from v$encryption_keys order by con_id;CON_ID ACTIVATION_TIME                      KEY_USE        TAG
---------- ------------------------------------ -------------- --------------------------------------------1 05-APR-22 12.54.23.463760 PM +00:00  TDE IN PDB     CDB1: Initial Master Key3 05-APR-22 12.58.50.293916 PM +00:00  TDE IN PDB     pdb1: Initial Master Key4 05-APR-22 01.00.36.560789 PM +00:00  TDE IN PDB     pdb2: Initial Master Key

现在，创建 Autologin Oracle 钱包：

./tde_create_autologin_wallet.sh

实际执行命令为：

SQL> administer key management create auto_login keystore from keystore '${WALLET_DIR}/tde' identified by ${DBUSR_PWD};keystore altered.

运行相同的查询以查看操作系统上的 Oracle Wallet 内容：

./tde_view_wallet_on_os.sh

您现在应该看到 cwallet.sso 文件。

===================================================================================Display the Wallet info on the OS...
===================================================================================. Wallet location and files
/etc/ORACLE/WALLETS/cdb1
/etc/ORACLE/WALLETS/cdb1/tde
/etc/ORACLE/WALLETS/cdb1/tde/ewallet.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040512542332.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040512585020.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040513003638.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet.p12.lck
/etc/ORACLE/WALLETS/cdb1/tde/cwallet.sso <- 说的就是这一行
/etc/ORACLE/WALLETS/cdb1/tde_seps
/etc/ORACLE/WALLETS/cdb1/okv. Display the keystore from the OS-------------------------Note:To view it, run the following OS command:$ orapki wallet display -wallet /etc/ORACLE/WALLETS/cdb1/tde -pwd Oracle123-------------------------Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.9623C50C30AD638EE0532C00000A4926
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.9623C58F05F064BFE0532C00000ACDFE
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:

并且数据库中的 Oracle Wallet 没有任何变化。

./tde_view_wallet_in_db.sh

现在您的自动登录Wallet已创建！

Task 5: Encrypt Existing Tablespace

使用 Linux 命令 strings 查看与 EMPDATA_PROD 表空间关联的数据文件 empdata_prod.dbf 中的数据。 这是一个绕过数据库查看数据的操作系统命令。 这被称为“旁路攻击”，因为数据库不知道它。

./tde_strings_data_empdataprod.sh

输出如下：

===================================================================================View the datafile data of the tablespace EMPDATA_PROD...
===================================================================================. Search the datafile path of the tablespaces EMPDATA_PRODFILE_NAME                                     ONLINE_STATUS
--------------------------------------------- ---------------
/u01/oradata/cdb1/pdb1/empdata_prod.dbf       ONLINE. View the datafile content directly through the OS file----------------Note:To view the datafile content directly through the OS file, we use the command:$ strings /u01/oradata/cdb1/pdb1/empdata_prod.dbf | tail -40----------------[...]
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
/D8@
aKd4
/D8@
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
aKd4
testuser
rwark
rlowenth
pjones
mmalfoy
malfoy
hradmin
eu_evan
ebabelcan_candy
bbest
agoodie
aKd4
AAAAAAAAp       V       <       "w       k       _       S       G       ;       /       #t       h       \       P       D       8       ,
;       /       #

接下来，通过加密整个表空间来加密数据：

./tde_encrypt_tbs.sh

实际执行的命令和输出如下：

===================================================================================Encrypt the tablespace EMPDATA_PROD...
===================================================================================-- . Check if the tablespace EMPDATA_PROD is encrypted or not
SQL> select tablespace_name, encrypted from dba_tablespaces where tablespace_name = 'EMPDATA_PROD';TABLESPACE_NAME                ENCRYPTED
------------------------------ ----------
EMPDATA_PROD                   NO-- . Encrypt the tablespace EMPDATA_PROD
SQL> ALTER TABLESPACE EMPDATA_PROD ENCRYPTION ONLINE USING 'AES256' ENCRYPT;Tablespace altered.-- . Check if the tablespace EMPDATA_PROD is encrypted now
SQL> select tablespace_name, encrypted from dba_tablespaces where tablespace_name = 'EMPDATA_PROD';TABLESPACE_NAME                ENCRYPTED
------------------------------ ----------
EMPDATA_PROD                   YES-- . Display all the encrypted tablespaces in the DB
SQL> select a.name pdb_name, b.name tablespace_name, c.ENCRYPTIONALG algorithmfrom v$pdbs a, v$tablespace b, v$encrypted_tablespaces cwhere a.con_id = b.con_idand b.con_id = c.con_idand b.ts# = c.ts#;PDB_NAME             TABLESPACE_NAME                ALGORITHM
-------------------- ------------------------------ ----------
PDB1                 EMPDATA_PROD                   AES256

现在，再次尝试“旁路攻击”：

./tde_strings_data_empdataprod.sh

输出如下：

===================================================================================View the datafile data of the tablespace EMPDATA_PROD...
===================================================================================. Search the datafile path of the tablespaces EMPDATA_PRODFILE_NAME                                     ONLINE_STATUS
--------------------------------------------- ---------------
/u01/oradata/cdb1/pdb1/empdata_prod.dbf       ONLINE. View the datafile content directly through the OS file----------------Note:To view the datafile content directly through the OS file, we use the command:$ strings /u01/oradata/cdb1/pdb1/empdata_prod.dbf | tail -40----------------[...]
.c</v
}(by
o$4bw
,l\/
1'vv
>HO/R
9W55
V]JMP
8Jbxf
EY8F
roVu
O0k^
J?.1
#]Bs
O(^1
lLEu
?iRV
)Xe5
,IF7
YfeH
ZRHy
FYm|
1NYj;
'"OL
oM}KCG7q.
RuA:
.SGc:B
8mJC
%\6]
E        M
~l)v
u>"L:
][5:
i> 4
AUgT^y
)f(*a
Bi*o
Tn_A
gKK:$

您会看到所有数据现在都已加密并且不再可见！

Task 6: Encrypt All New Tablespaces

首先，检查初始化参数的当前设置：

./tde_check_init_params.sh

实际执行的命令和输出为：

SQL> select name, valuefrom v$parameterwhere name in ('encrypt_new_tablespaces','tde_configuration','external_keystore_credential_location','wallet_root','one_step_plugin_for_pdb_with_tde');NAME                                     VALUE
---------------------------------------- ----------------------------------------
encrypt_new_tablespaces                  CLOUD_ONLY
one_step_plugin_for_pdb_with_tde         FALSE
external_keystore_credential_location
wallet_root                              /etc/ORACLE/WALLETS/cdb1
tde_configuration                        keystore_configuration=FILE

接下来，将初始化参数 ENCRYPT_NEW_TABLESPACES 更改为 ALWAYS，以便所有新表空间都被加密：

./tde_encrypt_all_new_tbs.sh

实际执行的命令和输出为：

===================================================================================Encrypt all new tablespaces...
===================================================================================-- . Show parameters like ENCRYPT
SQL> show parameter %encrypt%
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_tablespace_encryption_default_algor string      AES256
ithm
encrypt_new_tablespaces              string      CLOUD_ONLY-- . Set "ALWAYS" to the hidden parameter "encrypt_new_tablespaces"
SQL> alter system set encrypt_new_tablespaces = 'ALWAYS' scope=both;System altered.-- . Set "AES256" to the hidden parameter "_tablespace_encryption_default_algorithm"
SQL> alter system set "_tablespace_encryption_default_algorithm" = 'AES256' scope = both;System altered.-- . Show parameters like ENCRYPT
SQL> show parameter %encrypt%
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_tablespace_encryption_default_algor string      AES256
ithm
encrypt_new_tablespaces              string      ALWAYS

最后，创建一个表空间来测试它。 表空间 TEST 将在不指定加密参数的情况下创建（默认加密为 AES256），之后将被删除。

./tde_create_new_tbs.sh

实际执行的命令和输出为：

===================================================================================Create a new tablespace to show it was encrypted...
===================================================================================. Display all the encrypted tablespaces in the DB
SQL> select a.name pdb_name, b.name tablespace_name, c.ENCRYPTIONALG algorithmfrom v$containers a, v$tablespace b, v$encrypted_tablespaces cwhere a.con_id = b.con_idand b.con_id = c.con_idand b.ts# = c.ts#;PDB_NAME     TABLESPACE_NAME           ALGORITHM
------------ ------------------------- ----------
PDB1         EMPDATA_PROD              AES256. Create a new tablespace TEST without encryption syntax
SQL> create tablespace TEST datafile '${DATA_DIR}/test.dbf' size 15m;
Tablespace created.. Create a table TEST in this new tablespace
SQL> create table test_objects tablespace TEST as select * from dba_objects;Table created.. Verify that this table is correctly located on this tablespace and check if it's encrypted
SQL> select tablespace_name, encrypted from dba_tablespaces where tablespace_name = 'TEST';TABLESPACE_NAME           ENCRYPTED
------------------------- ----------
TEST                      YES. Display all the encrypted tablespaces in the DB
SQL> select a.name pdb_name, b.name tablespace_name, c.ENCRYPTIONALG algorithmfrom v$containers a, v$tablespace b, v$encrypted_tablespaces cwhere a.con_id = b.con_idand b.con_id = c.con_idand b.ts# = c.ts#;PDB_NAME     TABLESPACE_NAME           ALGORITHM
------------ ------------------------- ----------
PDB1         EMPDATA_PROD              AES256
PDB1         TEST                      AES256. Display the keys in the DB
SQL> select a.name pdb_name, b.key_id, substr(b.CREATION_TIME,1,29) creation_time, b.tagfrom v$containers a, v$encryption_keys bwhere a.con_id = b.con_id;PDB_NAME     KEY_ID                              CREATION_TIME                 TAG
------------ ----------------------------------- ----------------------------- ---------------------------------------------
PDB1         AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAA 05-APR-22 12.58.50.293914 PM  pdb1: Initial Master KeyAAAAAAAAAAAAAAAAA. View the output of strings against test.dbf data file
SQL> !ls -al ${DATA_DIR}/test.dbf-rw-r-----. 1 oracle oinstall 15736832 Apr  5 13:27 /u01/oradata/cdb1/test.dbfSQL> !strings ${DATA_DIR}/test.dbf | head -20
}|{z
2i;CDB1
TEST
_N[v\m
n6j=
t9d<:
Y9f~t7d
ic)
J;zC?`
=aeK
[0      pH
fY(Rx
!Q.]
lH,&)
k]c-
.$yH
N%h"
9X-p6
@_~+
&{@_E. Drop the tablespace TEST
SQL> drop tablespace TEST including contents and datafiles;
Tablespace dropped.

现在，您的新表空间将默认加密！

Task 7: Rekey Master Key

要重新生成容器数据库 TDE 主密钥 (MEK) 的密钥，请运行以下命令：

./tde_rekey_mek_cdb.sh

实际执行的命令和输出为：

===================================================================================Rekey the master key for the container database...
===================================================================================
CDB1: Master Key rekey on 20220405_1331CON_NAME
------------------------------
CDB$ROOT. Show the keystore
SQL> select a.con_id, b.name pdb_name, a.wrl_type, a.wrl_parameter, a.status from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID PDB_NAME   WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/            OPEN2 PDB$SEED   FILE                                                  OPEN3 PDB1       FILE                                                  OPEN4 PDB2       FILE                                                  OPEN. Show the keys before rekeying
SQL> select b.name pdb_name, a.key_id, a.activation_time, a.tag from v$encryption_keys a, v$containers b where a.con_id=b.con_id order by a.con_id, a.activation_time;PDB_NAME   KEY_ID                               ACTIVATION_TIME                      TAG
---------- ------------------------------------ ------------------------------------ ----------------------------------------------------
CDB$ROOT   AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAA 05-APR-22 12.54.23.463760 PM +00:00  CDB1: Initial Master KeyAAAAAAAAAAAAAAAAPDB1       AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAA 05-APR-22 12.58.50.293916 PM +00:00  pdb1: Initial Master KeyAAAAAAAAAAAAAAAAPDB2       ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAA 05-APR-22 01.00.36.560789 PM +00:00  pdb2: Initial Master KeyAAAAAAAAAAAAAAAA. Rekey the CDB key
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG '${TAG_DATA}' FORCE KEYSTORE IDENTIFIED BY ${DBUSR_PWD} WITH BACKUP container=current;keystore altered.. Show the keys after rekeying
SQL> select b.name pdb_name, a.key_id, a.activation_time, a.tag from v$encryption_keys a, v$containers b where a.con_id=b.con_id order by a.con_id, a.activation_time;PDB_NAME   KEY_ID                               ACTIVATION_TIME                      TAG
---------- ------------------------------------ ------------------------------------ ----------------------------------------------------
CDB$ROOT   AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAA 05-APR-22 12.54.23.463760 PM +00:00  CDB1: Initial Master KeyAAAAAAAAAAAAAAAACDB$ROOT   AcvoCtxfzE9Rv1rfi4uRt2cAAAAAAAAAAAAA 05-APR-22 01.31.12.521638 PM +00:00  CDB1: Master Key rekey on 20220405_1331AAAAAAAAAAAAAAAAPDB1       AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAA 05-APR-22 12.58.50.293916 PM +00:00  pdb1: Initial Master KeyAAAAAAAAAAAAAAAAPDB2       ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAA 05-APR-22 01.00.36.560789 PM +00:00  pdb2: Initial Master KeyAAAAAAAAAAAAAAAA

注意输出中多了1行，表示CDB的MEK已经rekey。

要为可插入数据库 pdb1 重新生成主密钥 (MEK)，请运行以下命令：

./tde_rekey_mek_pdb.sh pdb1

实际执行的命令和输出为：

===================================================================================Rekey the Master Key (MEK) for pluggable database ...
===================================================================================
pdb1: Master Key rekey on 20220405_1335
SQL> alter session set container=${pdbname};
Session altered.SQL> show con_name;CON_NAME
------------------------------
PDB1. Show the keystore
SQL> select a.con_id, b.name pdb_name, a.wrl_type, a.wrl_parameter, a.status from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID PDB_NAME   WRL_TYPE     WRL_PARAMETER                            STATUS
---------- ---------- ------------ ---------------------------------------- ------------------------------3 PDB1       FILE                                                  OPEN. Show the keys before rekeying
SQL> select b.name pdb_name, a.key_id, a.activation_time, a.tag from v$encryption_keys a, v$containers b where a.con_id=b.con_id order by a.con_id, a.activation_time;PDB_NAME   KEY_ID                               ACTIVATION_TIME                      TAG
---------- ------------------------------------ ------------------------------------ ----------------------------------------------------
PDB1       AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAA 05-APR-22 12.58.50.293916 PM +00:00  pdb1: Initial Master KeyAAAAAAAAAAAAAAAA. Rekey the PDB key
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG '${TAG_DATA}' FORCE KEYSTORE IDENTIFIED BY ${DBUSR_PWD} WITH BACKUP container=current;
keystore altered.. Show the keys after rekeying
SQL> select b.name pdb_name, a.key_id, a.activation_time, a.tag from v$encryption_keys a, v$containers b where a.con_id=b.con_id order by a.con_id, a.activation_time;
PDB_NAME   KEY_ID                               ACTIVATION_TIME                      TAG
---------- ------------------------------------ ------------------------------------ ----------------------------------------------------
PDB1       AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAA 05-APR-22 12.58.50.293916 PM +00:00  pdb1: Initial Master KeyAAAAAAAAAAAAAAAAPDB1       AXbYD22K6k95vwySAgvCGKQAAAAAAAAAAAAA 05-APR-22 01.35.37.637522 PM +00:00  pdb1: Master Key rekey on 20220405_1335AAAAAAAAAAAAAAAA

注意输出中多了1行，表示PDB的MEK已经rekey。

Task 8: View Keystore Details

一旦你有了一个密钥库，你就可以运行这些脚本中的任何一个。 您会注意到 ewallet.p12 文件有多个副本。 每次进行更改（包括创建或重新设置密钥）时，都会备份 ewallet.p12 文件。 您还将使用 orapki 查看 Oracle Wallet 文件的内容。

查看与密钥库相关的操作系统文件：

./tde_view_wallet_on_os.sh

其输出为：


===================================================================================Display the Wallet info on the OS...
===================================================================================. Wallet location and files
/etc/ORACLE/WALLETS/cdb1
/etc/ORACLE/WALLETS/cdb1/tde
/etc/ORACLE/WALLETS/cdb1/tde/ewallet.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040512542332.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040512585020.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040513003638.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet.p12.lck
/etc/ORACLE/WALLETS/cdb1/tde/cwallet.sso
/etc/ORACLE/WALLETS/cdb1/tde/cwallet.sso.lck
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040513311231.p12
/etc/ORACLE/WALLETS/cdb1/tde/ewallet_2022040513353752.p12
/etc/ORACLE/WALLETS/cdb1/tde_seps
/etc/ORACLE/WALLETS/cdb1/okv. Display the keystore from the OS-------------------------Note:To view it, run the following OS command:$ orapki wallet display -wallet /etc/ORACLE/WALLETS/cdb1/tde -pwd Oracle123-------------------------Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AcvoCtxfzE9Rv1rfi4uRt2cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AXbYD22K6k95vwySAgvCGKQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.9623C50C30AD638EE0532C00000A4926
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.9623C58F05F064BFE0532C00000ACDFE
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.AcvoCtxfzE9Rv1rfi4uRt2cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AXbYD22K6k95vwySAgvCGKQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AcvoCtxfzE9Rv1rfi4uRt2cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.ATUz2AQZmU8Nvyj893ElHvIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AVZFZbolYE+9v8Cjqpx1z9cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AXbYD22K6k95vwySAgvCGKQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KT.ENCRYPTION.AZkQFsCj3E+nv0hvGcuDcG4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:

查看数据库中的keystore数据：

./tde_view_wallet_in_db.sh

其输出为：

===================================================================================Display the Wallet info in the DB...
===================================================================================--. Display the keystore status
SQL> select a.con_id, b.name, a.wrl_type, a.wrl_parameter, a.status, a.wallet_type from v$encryption_wallet a, v$containers b where a.con_id=b.con_id order by a.con_id;CON_ID NAME       WRL_TYPE     WRL_PARAMETER                       STATUS                         WALLET_TYPE
---------- ---------- ------------ ----------------------------------- ------------------------------ ------------1 CDB$ROOT   FILE         /etc/ORACLE/WALLETS/cdb1/tde/       OPEN                           PASSWORD2 PDB$SEED   FILE                                             OPEN                           PASSWORD3 PDB1       FILE                                             OPEN                           PASSWORD4 PDB2       FILE                                             OPEN                           PASSWORD-- . Display the keys in the DB
SQL> select con_id, activation_time, key_use, tag from v\$encryption_keys order by con_id;CON_ID ACTIVATION_TIME                      KEY_USE        TAG
---------- ------------------------------------ -------------- --------------------------------------------1 05-APR-22 12.54.23.463760 PM +00:00  TDE IN PDB     CDB1: Initial Master Key1 05-APR-22 01.31.12.521638 PM +00:00  TDE IN PDB     CDB1: Master Key rekey on 20220405_13313 05-APR-22 01.35.37.637522 PM +00:00  TDE IN PDB     pdb1: Master Key rekey on 20220405_13353 05-APR-22 12.58.50.293916 PM +00:00  TDE IN PDB     pdb1: Initial Master Key4 05-APR-22 01.00.36.560789 PM +00:00  TDE IN PDB     pdb2: Initial Master Key

Task 9: (Optional) Restore Before TDE

注意：如果您想稍后执行 Oracle Key Vault 实验，请勿运行此实验！

首先，恢复pfile：

./tde_restore_init_parameters.sh

实际执行为：

create spfile from pfile='$ORACLE_HOME/dbs/pfile_pre-tde.ora';

然后，恢复数据库(就是通过tar恢复)：

./tde_restore_db.sh

接下来，删除相关的 Oracle Wallet 文件：

./tde_delete_wallet_files.sh

然后，启动容器和可插拔数据库：

./tde_start_db.sh

最后，验证初始化参数中没有设置TDE：

./tde_check_init_params.sh

实际执行与输出为：

SQL> select name, valuefrom v$parameterwhere name in ('encrypt_new_tablespaces','tde_configuration','external_keystore_credential_location','wallet_root','one_step_plugin_for_pdb_with_tde');NAME                                     VALUE
---------------------------------------- ----------------------------------------
encrypt_new_tablespaces                  CLOUD_ONLY
one_step_plugin_for_pdb_with_tde         FALSE
external_keystore_credential_location
wallet_root
tde_configuration

Appendix: About the Product

在 Oracle 数据库核心产品中硬编码，此功能是高级安全选项 (ASO) 的一部分

TDE 使您能够加密数据，以便只有授权的接收者才能读取它。

使用加密来保护潜在未受保护环境中的敏感数据，例如您放置在备份媒体上并发送到场外存储位置的数据。 您可以加密数据库表中的各个列，也可以加密整个表空间。

数据加密后，当授权用户或应用程序访问该数据时，该数据被透明地解密。如果存储介质或数据文件被盗，TDE 有助于保护存储在介质上的数据（也称为静态数据）。

Oracle 数据库使用身份验证、授权和审计机制来保护数据库中的数据，而不是存储数据的操作系统数据文件中的数据。为了保护这些数据文件，Oracle 数据库提供了透明数据加密 (TDE)。 TDE 对存储在数据文件中的敏感数据进行加密。为了防止未经授权的解密，TDE 将加密密钥存储在数据库外部的安全模块中，称为密钥库。

您可以将 Oracle Key Vault 配置为 TDE 实施的一部分。这使您能够集中管理企业中的 TDE 密钥库（在 Oracle Key Vault 中称为 TDE 钱包）。例如，您可以将软件密钥库上传到 Oracle Key Vault，然后将此密钥库的内容提供给其他启用 TDE 的数据库。

Want to Learn More?

参考文档：Transparent Data Encryption (TDE) 19c

还有2个视频，是高阶培训：

  • Understanding Oracle Transparent Data Encryption (TDE) - Part1 (February 2020)
  • Understanding Oracle Transparent Data Encryption (TDE) - Part2 (February 2020)
  • Database Security Office Hours

Acknowledgements

本实验的作者为Hakim Loumi，数据库安全的PM。贡献者为Rene Fontcha。

这篇关于Oracle LiveLabs实验：DB Security - Transparent Data Encryption (TDE)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/677467。

相关文章

SQL server数据库如何下载和安装

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步：打开官网下载对应文件第二步：程序安装配置第三部：安装工具SQL Server Manageme
阅读更多...
C#连接SQL server数据库命令的基本步骤

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用：如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.
阅读更多...
全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念：DATEDIFF 究竟在计算什么？2. 主流数据库中的 DATEDIFF 实现2.1
阅读更多...
MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

《MySQL多列IN查询之语法、性能与实战技巧(最新整理)》本文详解MySQL多列IN查询,对比传统OR写法,强调其简洁高效,适合批量匹配复合键,通过联合索引、分批次优化提升性能,兼容多种数据库... 目录一、基础语法：多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析
阅读更多...
MySQL中的LENGTH()函数用法详解与实例分析

MySQL中的LENGTH()函数用法详解与实例分析

《MySQL中的LENGTH()函数用法详解与实例分析》MySQLLENGTH()函数用于计算字符串的字节长度,区别于CHAR_LENGTH()的字符长度,适用于多字节字符集（如UTF-8）的数据验证... 目录1. LENGTH()函数的基本语法2. LENGTH()函数的返回值2.1 示例1：计算字符串
阅读更多...
浅谈mysql的not exists走不走索引

浅谈mysql的not exists走不走索引

《浅谈mysql的notexists走不走索引》在MySQL中,​NOTEXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引,下面就来介绍一下mysql的notexists走不走索... 在mysql中，​NOT EXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引。以下
阅读更多...
Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys
阅读更多...
SQL中如何添加数据(常见方法及示例)

SQL中如何添加数据(常见方法及示例)

《SQL中如何添加数据(常见方法及示例)》SQL全称为StructuredQueryLanguage,是一种用于管理关系数据库的标准编程语言,下面给大家介绍SQL中如何添加数据,感兴趣的朋友一起看看吧... 目录在mysql中，有多种方法可以添加数据。以下是一些常见的方法及其示例。1. 使用INSERT I
阅读更多...
Qt使用QSqlDatabase连接MySQL实现增删改查功能

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构
阅读更多...
MySQL 中的 CAST 函数详解及常见用法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2