[toc]

一、ORACLE 12.1.0.2 TDE 配置

1.1 在Oracle非租户环境进行TDE配置

版本:12.1.0.2 nocdb

1
2
3
4
5
SQL> show pdbs
SQL> select CDB from v$database;
CDB
---
NO
TDE与非多租户 非多租户的TDE配置 在Oracle非租户环境进行TDE配置

1.1.1、创建钱包文件夹并编辑 sqlnet.ora

1
2
mkdir -p /etc/ORACLE/WALLETS/nocdb
chown -R oracle:oinstall /etc/ORACLE/WALLETS/nocdb

vi “$ORACLE_HOME”/network/admin/sqlnet.ora

1
2
3
4
5
6
7
vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/etc/ORACLE/WALLETS/nocdb)))

1.1.2、创建 Software Keystore

创建用户

需要 ADMINISTER KEY MANAGEMENT or SYSKM privilege.

1
2
3
4
5
SQL> create user sec_admin identified by "Password123";
User created.
SQL> grant SYSKM to sec_admin;
Grant succeeded.
SQL> exit
1
2
3
4
5
6
sqlplus sec_admin as syskm
Enter password: password
Connected.
SQL> show user
USER is "SYSKM"
SQL>

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘keystore_location’ IDENTIFIED BY software_keystore_password;

1
2
3
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/nocdb' IDENTIFIED BY Password23;

keystore altered.

1.1.3、打开 Software Keystore

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password [CONTAINER = ALL | CURRENT];

1
2
3
4
5
6
7
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Password23;

keystore altered.
SQL> ! ls -l /etc/ORACLE/WALLETS/nocdb
total 4
-rw-r--r-- 1 oracle oinstall 2408 Feb 18 10:09 ewallet.p12

1.1.4、设置 Master Encryption Key

ADMINISTER KEY MANAGEMENT SET KEY [USING TAG ‘tag’] IDENTIFIED BY keystore_password [WITH BACKUP [USING ‘backup_identifier’]] [CONTAINER = ALL | CURRENT];

1
2
3
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Password23 with backup USING 'nocdb_tde_key_bak';

keystore altered.

select key_id,tag,KEYSTORE_TYPE,USER,CON_ID,BACKED_UP from v$encryption_keys

1
2
3
4
5
6
SQL> select key_id,tag,KEYSTORE_TYPE,USER,CON_ID,BACKED_UP from  v$encryption_keys;

KEY_ID TAG KEYSTORE_TYPE USER CON_ID BACKED_UP
------------------------------------------------------------------------------ -------------------- ----------------- ------------------------------ ---------- ---------
AZE+jaWrrk8ivxzQvU6S8VUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYSKM 0 NO

1.1.5、加密数据

a.加密列数据

1
2
3
4
5
6
7
8
9
10
SQL> CREATE TABLE employee (
first_name VARCHAR2(128),
last_name VARCHAR2(128),
empID NUMBER,
salary NUMBER(6) ENCRYPT
); 2 3 4 5 6
salary NUMBER(6) ENCRYPT
*
ERROR at line 5:
ORA-28336: cannot encrypt SYS owned objects

无法加密sys 用户下的对象,创建普通用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> create user utde identified by "tdenocdb";

User created.

SQL> grant connect,resource to utde;

Grant succeeded.

[oracle@tcloud ~]$ sqlplus utde/tdenocdb
SQL> show user
USER is "UTDE"

SQL> CREATE TABLE employee (
first_name VARCHAR2(128),
last_name VARCHAR2(128),
empID NUMBER,
salary NUMBER(6) ENCRYPT
); 2 3 4 5 6

Table created.

普通用户可以直接创建加密列空间,需要用create /aler table权限,如果要创建加密表空间,需要creae tablespace 权限。

b.加密表空间

加密表空间,需要 create tablespace 权限。

1
2
3
4
5
6
7
8
9
10
11
SQL> CREATE TABLESPACE TEST_tde datafile  size 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

sqlplus utde/tdenocdb
CREATE TABLE testde (
first_name VARCHAR2(128),
last_name VARCHAR2(128),
empID NUMBER,
salary NUMBER(6)
) tablespace TEST_tde;

c.创建非加密表空间进行 strings 对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[oracle@tcloud ~]$ ./ora dbf|grep TEST_TDE
TEST_TDE /u01/app/oracle/oradata/NOCDB/datafile/o1_mf_test_tde_k0y2f9jc_.dbf 10 NO 0 0
[oracle@tcloud ~]$ [oracle@tcloud ~]$ strings /u01/app/oracle/oradata/NOCDB/datafile/o1_mf_test_tde_k0y2f9jc_.dbf|more
}|{z
NOCDB
|bAglbA
TEST_TDE
n0!
!`,#
Facf
6wd`
Jf!G0
_y*Z
@?Pi
R}|fN
<0gp
_41w
sFx5C7
n1%/lK
J?[
WYP|B
. h(
01!u[2)
b:h63
{X4~f
tW9c1)
52qF7
Kp\^
huGk@Aw
4-Gz
PLP6
8r`r~N
D(Z7
gTxVa
OQbI
\Gyx^
|=I]
;~5W
EV-h+
yVL%
vX"M
J73V
@B59
;W 7]
r1!0}?b
Z>W!Wd
d6mm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
CREATE TABLESPACE TEST_ENCRY
datafile size 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
create user u4tde identified by u4notde123 default tablespace TEST_ENCRY;
grant dba to u4tde;
sqlplus u4tde/u4notde123
CREATE TABLE u4tde.Persons (
PersonID int ENCRYPT,
LastName varchar(255) ENCRYPT,
FirstName varchar(255) ENCRYPT,
Address varchar(255) ENCRYPT,
City varchar(255) ENCRYPT
);

insert into u4tde.Persons values('898899','MOHAMMAD','SHADAB','SURREY HILLS','SYDNEY');
commit;
insert into u4tde.Persons values('898899','CHRIS','MARTIN','SURREY HILLS','SYDNEY');
commit;
insert into u4tde.Persons values('898899','YORKE','THOM','MANSFIELD','MANCHESTER');
commit;
[oracle@tcloud ~]$ strings /u01/app/oracle/oradata/NOCDB/datafile/o1_mf_test_enc_k0y4wwff_.dbf|more
}|{z
NOCDB
bAglbA
TEST_ENCRY
r*H|
XMMC
CVK^
wlkv
+&-|
-e8G'
U#I4B
+|/L
Y*(y[
!zRc
7Ivb
B*~`K
z2!X
bs6^
;Z[US
{:y7
AA}W/Ck
q?dq
C<k>VPxb
a(E9g
Yd#bW
&ug3
q}QU
erP1E
UKn0
\W1H
nna;
IOM!:
8h k
N*"w/vCf
"}|u=
h5/4s
UL(}
t<<5
tFH
ap7`
arlH
1r%yv`
n@.)
{{n+9
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create tablespace test datafile  size 10M;
create user u4notde identified by u4notde default tablespace test;
alter user u4notde identified by u4notde123;
grant dba to u4notde;
sqlplus u4notde/u4notde123
CREATE TABLE u4notde.Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

insert into u4notde.Persons values('898899','MOHAMMAD','SHADAB','SURREY HILLS','SYDNEY');
commit;
insert into u4notde.Persons values('898899','CHRIS','MARTIN','SURREY HILLS','SYDNEY');
commit;
insert into u4notde.Persons values('898899','YORKE','THOM','MANSFIELD','MANCHESTER');
commit;
[oracle@tcloud ~]$ ./ora dbf|grep -iw test
TEST /u01/app/oracle/oradata/NOCDB/datafile/o1_mf_test_k0y4zm02_.dbf 10 NO 0 0
[oracle@tcloud ~]$
[oracle@tcloud ~]$ strings /u01/app/oracle/oradata/NOCDB/datafile/o1_mf_test_k0y4zm02_.dbf
}|{z
NOCDB
bAglbA
TEST
YORKE
THOM MANSFIELD
MANCHESTER,
CHRIS
MARTIN
SURREY HILLS
SYDNEY,
MOHAMMAD
SHADAB
SURREY HILLS
SYDNEY
[oracle@tcloud ~]$

1.2 ORACLE 在CDB 的 TDE 配置

3 Configuring Transparent Data Encryption

https://docs.oracle.com/database/121/ASOAG/configuring-transparent-data-encryption.htm#ASOAG10474

透明数据加密

https://www.oracle.com/technetwork/topics/security/index-092808-zhs.html

Database Advanced Security Guidxs

https://docs.oracle.com/database/121/ASOAG/toc.htm

创建 PDB 语句。

1
2
3
create pluggable database tdepdb admin user pdbadmin identified by "Password123";
alter pluggable database tdepdb open instances=all;
alter pluggable database tdepdb save state instances=all;

1.2.1、创建钱包文件夹并编辑 sqlnet.ora

1
2
mkdir -p /etc/ORACLE/WALLETS/tdecdb
chown -R oracle:oinstall /etc/ORACLE/WALLETS/tdecdb

使用root 创建单独的目录,权限授予给 oracle 用户。

1
2
3
4
5
6
7
vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/etc/ORACLE/WALLETS/tdecdb)))

1.2.2、创建 Software Keystore

创建用户

需要 ADMINISTER KEY MANAGEMENT or SYSKM privilege.

1
2
3
4
5
SQL> create user c##sec_admin identified by "Password123";
User created.
SQL> grant SYSKM to c##sec_admin;
Grant succeeded.
SQL> exit
1
2
3
sqlplus c##sec_admin as syskm
Enter password: password
Connected.

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘keystore_location’ IDENTIFIED BY software_keystore_password;

1
2
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/tdecdb' IDENTIFIED BY Password23;
keystore altered.

是在 CDB$ROOT 上执行,不能在pdb级别执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> show user
USER is "SYSKM"
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/tdecdb' IDENTIFIED BY Password23;
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/tdecdb' IDENTIFIED BY Password23
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> show con_name

CON_NAME
------------------------------
TDEPDB
SQL>

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/tdecdb' IDENTIFIED BY Password23;

keystore altered.

运行此语句后,作为keystore的 ewallet.p12文件将出现在密钥库/etc/ORACLE/WALLETS/tdecdb 即 sqlnet.ora 配置的位置

SQL> ! ls -l /etc/ORACLE/WALLETS/tdecdb
total 4
-rw-r--r-- 1 oracle oinstall 2408 Feb 21 10:42 ewallet.p12

SQL>

1.2.3、 打开 Software Keystore

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password [CONTAINER = ALL | CURRENT];

  • software_keystore_password is the same password that you used to create the keystore in Step 2: Create the Software Keystore.
  • CONTAINER is for use in a multitenant environment. Enter ALL to set the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.
1
2
3
4
5
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Password23;
keystore altered.
SQL> ! ls -l /etc/ORACLE/WALLETS/tdecdb
total 4
-rw-r--r-- 1 oracle oinstall 2408 Feb 17 09:32 ewallet.p12

可以添加 CONTAINER = ALL 参数,此cdb 所有的pdb生效,使用 CONTAINER = ALL, 必须在root 容器执行,CDB$ROOT ,并且有 ADMINISTER KEY MANAGEMENT or SYSKM 权限。

默认是 current,只针对当前 PDB 生效。

可以通过V$ENCRYPTION_WALLET 视图查看状态。

1
2
3
4
5
6
7
8
9
10
 WRL_TYPE	     WRL_PARAMETER					STATUS

-------------------- -------------------------------------------------- ------------------------------

FILE /etc/ORACLE/WALLETS/tdecdb/ CLOSED

SQL> l
1* select WRL_TYPE,WRL_PARAMETER,status from V$ENCRYPTION_WALLET
SQL>

open 之后的状态为 OPEN_NO_MASTER_KEY

1
2
3
4
5
WRL_TYPE	     WRL_PARAMETER					STATUS
-------------------- -------------------------------------------------- ------------------------------
FILE /etc/ORACLE/WALLETS/tdecdb/ OPEN_NO_MASTER_KEY

SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 TDEPDB2 READ WRITE NO
SQL> select WRL_TYPE,WRL_PARAMETER,status from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------------------
FILE /etc/ORACLE/WALLETS/tdecdb/ CLOSED

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Password23;

keystore altered.

SQL> col WRL_PARAMETER for a30
SQL> select WRL_TYPE,WRL_PARAMETER,status from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS
-------------------- -------------------------------------------------- ------------------------------
FILE /etc/ORACLE/WALLETS/tdecdb/ OPEN_NO_MASTER_KEY

*
ERROR at line 1:
ORA-28365: wallet is not open
如果状态没有open 会提示 ORA-28365: wallet is not open

关闭 的话使用如下命令

1
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "Password23" CONTAINER=ALL;

1.2.4、设置 TDE Master Encryption Key

ADMINISTER KEY MANAGEMENT SET KEY [USING TAG ‘tag’] IDENTIFIED BY keystore_password [WITH BACKUP [USING ‘backup_identifier’]] [CONTAINER = ALL | CURRENT];

1
2
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Password23 with backup USING 'tdetest01_key_bak';
ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'masterkey' IDENTIFIED BY Password23 with backup USING 'tdetest01_key_bak';

select key_id,tag,KEYSTORE_TYPE,USER,CON_ID,BACKED_UP from v$encryption_keys

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> set lines 200
SQL> col tag for a20
SQL> col KEY_ID for a60
SQL> select key_id,tag,KEYSTORE_TYPE,USER,CON_ID,BACKED_UP from v$encryption_keys;

KEY_ID TAG KEYSTORE_TYPE USER CON_ID BACKED_UP
------------------------------------------------------------ ---------- ----------------- ------------------------------ ---------- ---------
AcBapz/5vk9Av29fJl/NzJwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYSKM 0 NO

SQL> select key_id,tag,KEYSTORE_TYPE,USER,CON_ID,BACKED_UP from v$encryption_keys;


KEY_ID TAG KEYSTORE_TYPE USER CON_ID BACKED_UP
------------------------------------------------------------------------------ -------------------- ----------------- ------------------------------ ---------- ---------
AWeotlcyUk+tv7GiOqqILFAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS 0 NO
AcZdrtdJe0+fvxhez7ShUQIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS 0 YES
AaKq/ko7lk9VvzWShBQ73qkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS 0 YES


ADMINISTER KEY MANAGEMENT

https://docs.oracle.com/database/121/SQLRF/statements_1003.htm#SQLRF55976

1
2
3
4
5
6
SQL>  CREATE TABLESPACE TEST_pdbtde datafile  size 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
CREATE TABLESPACE TEST_pdbtde datafile size 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT)
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
如果不创建 Master Encryption Key 会提示 ORA-28374: typed master key not found in wallet

WITH BACKUP创建密钥库的备份。对于基于密码的密钥库,您必须使用此选项。或者,您可以使用该USING子句添加备份的简要说明。将此说明用单引号 (‘ ‘) 括起来。此标识符附加到命名的密钥库文件(例如,作为备份标识符)。

1.2.5、 加密数据

a.加密列数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> CREATE TABLESPACE TEST_pdbtde datafile  size 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> CREATE TABLE Persons (
PersonID int ENCRYPT,
LastName varchar(255) ENCRYPT,
FirstName varchar(255) ENCRYPT,
Address varchar(255) ENCRYPT,
City varchar(255) ENCRYPT
); 2 3 4 5 6 7
PersonID int ENCRYPT,
*
ERROR at line 2:
ORA-28336: cannot encrypt SYS owned objects

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SQL> create user c##tdecdb identified by "Password23";

User created.

SQL> grant dba to user c##tdecdb;
grant dba to user c##tdecdb
*
ERROR at line 1:
ORA-00987: missing or invalid username(s)


SQL> grant dba to c##tdecdb;

Grant succeeded.

[oracle@tcloud ~]$ sqlplus c##tdecdb/Password23

SQL> show user;
USER is "C##TDECDB"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLE employee (
first_name VARCHAR2(128),
last_name VARCHAR2(128),
empID NUMBER,
salary NUMBER(6) ENCRYPT
); 2 3 4 5 6

Table created.

b.加密表空间

字短加密和表空间加密跟 nocdb的方式相同。

详见 Step 5: Encrypt Your Data

1
select tablespace_name, encrypted from dba_tablespaces;

查看表空间是否加密

1.2.6、修改 Software Keystore Password

由于cdb和pdb统一使用一个钱包,修改cdb的密码之后pdb的密码也一并修改。

ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY
old_password SET new_password [WITH BACKUP [USING ‘backup_identifier’]];

如果要修改密码需要保证其状态为 open 状态,否则会报

1
2
ERROR at line 1:
ORA-46658: keystore not open in the container

以下是打开命令

1
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY newPassword23;

修改密码具体测试如下:

1
2
3
ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY Password23 set newPassword23 WITH BACKUP;
keystore altered.
SQL>

修改成功。

1.2.7、TDE 检查状态的脚本

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM V$ENCRYPTION_WALLET;
SELECT * FROM V$ENCRYPTION_KEYS;
SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;
SELECT KEY_ID,KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;
SELECT KEY_ID FROM V$ENCRYPTION_KEYS;
SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;
SELECT WRL_PARAMETER FROM V$ENCRYPTION_WALLET;
SELECT STATUS FROM V$ENCRYPTION_WALLET;
SELECT * FROM V$ENCRYPTED_TABLESPACES;
SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;
SELECT * FROM DBA_ENCRYPTED_COLUMNS;

1.3 在Oracle PDB中进行TDE配置

测试信息:
版本:12.1.0.2
PDB

1
2
3
create pluggable database tdepdb admin user pdbadmin identified by "Password123";
alter pluggable database tdepdb open instances=all;
alter pluggable database tdepdb save state instances=all;

1.3.1、创建 WALLETS 的文件夹

使用root 创建单独的目录,权限授予给 oracle 用户。

1
2
mkdir -p /etc/ORACLE/WALLETS/tdecdb
chown -R oracle:oinstall /etc/ORACLE/WALLETS/tdecdb

1.3.2、编辑 sqlnet.ora

编辑 $ORACLE_HOME/network/admin/sqlnet.ora 新增 ENCRYPTION_WALLET_LOCATION 配置

1
2
3
4
5
6
7
vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/etc/ORACLE/WALLETS/tdecdb)))

1.3.3、创建 Software Keystore

本步骤需要在root 容器即在 CDB$ROOT 上操作执行。

需要 ADMINISTER KEY MANAGEMENT or SYSKM privilege. 或者使用 sys / as sysdba创建,本次单独创建用户。

1
2
3
4
5
SQL> create user c##sec_admin identified by "Password123";
User created.
SQL> grant SYSKM to c##sec_admin;
Grant succeeded.
SQL> exit

登录

1
2
3
sqlplus c##sec_admin as syskm
Enter password: password
Connected.
1
2
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/tdecdb' IDENTIFIED BY Password23;
keystore altered.

是在 CDB$ROOT 上执行,不能在pdb级别执行。

创建智慧,作为keystore的 ewallet.p12文件将出现在密钥库/etc/ORACLE/WALLETS/tdecdb 即 sqlnet.ora 配置的位置

1
2
3
SQL>  ! ls -l /etc/ORACLE/WALLETS/tdecdb
total 4
-rw-r--r-- 1 oracle oinstall 2408 Feb 21 10:42 ewallet.p12

1.3.4、启用 Software Keystore

1
2
3
4
5
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Password23;
keystore altered.
SQL> ! ls -l /etc/ORACLE/WALLETS/tdecdb
total 4
-rw-r--r-- 1 oracle oinstall 2408 Feb 17 09:32 ewallet.p12

在 PDB 上启用的前提是需要在 root container 上启用。可以通过 添加 CONTAINER = ALL 参数,使所有的 PDB 都生效生效,如果需要单独启用某个pdb,需要先在 root container 上启用,然后再在 PDB 上启用。默认是 current,只针对当前 PDB 生效。

a.CDB 级别即 root container 上启用

1
2
3
4
5
6
SQL>  show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Password23;
keystore altered.

b.登录 PDB

然后登录 tdepdb pdb,启用 Software Keystore

1
2
3
4
5
6
7
SQL>  alter session set container=TDEPDB;

Session altered.
SQL> show con_name
CON_NAME
------------------------------
TDEPDB

c.检查状态

1
2
3
4
5
6
7
SQL>  select WRL_TYPE,WRL_PARAMETER,status from	V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS

-------------------- ------------------------------ ------------------------------

FILE /etc/ORACLE/WALLETS/tdecdb/ CLOSED

d.启用 PDB Software Keystore

1
2
3
SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Password23;

keystore altered.

在没有创建 MASTER_KEY的时候,第一次启动其状态是 OPEN_NO_MASTER_KEY

1
2
3
WRL_TYPE	     WRL_PARAMETER			      STATUS
-------------------- ---------------------------------------- ------------------------------
FILE /etc/ORACLE/WALLETS/tdecdb/ OPEN_NO_MASTER_KEY

如果之前测试过,创建过 MASTER_KEY,状态是 open

1
2
3
4
SQL>   select WRL_TYPE,WRL_PARAMETER,status from	V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ------------------------------ ------------------------------
FILE /etc/ORACLE/WALLETS/tdecdb/ OPEN

e.关闭KEYSTORE

关闭 的话使用如下命令

1
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "Password23" CONTAINER=ALL;

1.3.5、 创建 Master Encryption Key

每个pdb 都需要创建 Encryption Key。可以通过CONTAINER = ALL 参数创建所有pdb都生效。如果不创建会提示如下ORA-28374: typed master key not found in wallet

1
2
ERROR at line 1:
ORA-28374: typed master key not found in wallet

a.创建 Master Encryption Key

1
2
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Password23 with backup; 
keystore altered.

b.检查 Master Encryption Key

1
select key_id,tag,KEYSTORE_TYPE,USER,CON_ID,BACKED_UP from  v$encryption_keys
1
2
3
4
5
6
7
SQL> set lines 200
SQL> col tag for a20
SQL> col KEY_ID for a60
SQL> select key_id,tag,KEYSTORE_TYPE,USER,CON_ID,BACKED_UP from v$encryption_keys
KEY_ID TAG KEYSTORE_TYPE USER CON_ID BACKED_UP
------------------------------------------------------------ -------------------- ----------------- ------------------------------ ---------- ---------
AWu5OW5+ME/0v1fb0UTWgLQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS 0 NO

WITH BACKUP创建密钥库的备份。对于基于密码的密钥库,您必须使用此选项。或者,您可以使用该USING子句添加备份的简要说明。将此说明用单引号 (‘ ‘) 括起来。此标识符附加到命名的密钥库文件(例如,作为备份标识符)。

1.4 TDE 配置总结

纵观以上几种情况,在12cR1环境配置 TDE ,主要是0.创建 WALLETS 的文件夹;1.编辑 sqlnet.ora;2.创建 Software Keystore;3.启用 Software Keystore;4.创建 Master Encryption Key 五个步骤。

原文作者: liups.com

原文链接: http://www.liups.com/posts/b3a6f1c4/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议