Oracle TDE透明数据加密

Oracle TDE透明数据加密

𝓓𝓸𝓷 Lv6
  2024-07-11 18:30:46 2024-07-11 18:30:46 Created   2024-07-20 05:15:41 2024-07-20 05:15:41 Updated    
一、TDE简介

透明数据加密允许用户对各个表列或整个表空间进行加密。当用户向加密的列中插入数据时，透明数据加密会自动对该数据加密。当用户选择该列时，数据将自动解密。选择完毕后，数据将重新加密。
(注：下文中提到的”wallet”与”钱夹”为同一术语)
https://docs.oracle.com/database/121/ASOAG/configuring-transparent-data-encryption.htm#ASOAG10276

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Transparent Data Encryption :

TDE is an encryption mechanism present in Oracle database used to encrypt the data stored in a table column or tablespace. It protects the data stored on database files (DBF) by doing an encryption in case the file is stolen or hacked.
Transparent Data Encryption (TDE) provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database.
When using transparent encryption, the Oracle encryption wallet must be created and the wallet should be opened every time the database starts.
TDE supports two levels of encryption

Columns Level Encryption: Encrypt the table column data.
Tablespace Level Encryption: Encrypt all the data in a tablespace.
Oracle database 12c introduced a new way to manage keystores, encryption keys and secrets using the ADMINISTER KEY MANAGEMENT command. This replaces the ALTER SYSTEM SET ENCRYPTION KEY and ALTER SYSTEM SET ENCRYPTION WALLET commands for key and wallet administration from previous releases.


If the PDB is restarted, the keystore must be opened in the PDB before the data can be accessed.
If the CDB is restarted, the keystore must be opened in both the CDB and the PDBs.

image-20240715181235640

image-20240715181334231

二、Oracle12c配置TDE
1.创建wallet目录
1
2
3
4
5
6
7
8
[oracle@server ~]$ echo $ORACLE_SID
ora12c

[oracle@server01 ~]$ echo $TNS_ADMIN
/etc

[root@server ~]# mkdir -p /var/opt/oracle/wallet/ora12c
[root@server ~]# chown -R oracle.oinstall /var/opt/oracle
2.配置sqlnet
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
[oracle@server ~]$ vim /etc/sqlnet.ora 

ENCRYPTION_WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /var/opt/oracle/wallet/$ORACLE_SID)
     )
    )

#SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

Note: 当一个应用使用了SSL加密的话，SQLNET.WALLET_OVERRIDE设置为true，它会在SSL认证后，使用wallet中存储的用户密码认证DB

多实例共用一个sqlnet配置:
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/home/oracle/wallet/$ORACLE_SID)
)
)



Note the following behavior when you must edit the sqlnet.ora file in an Oracle Real Application Clusters (Oracle RAC) or a multitenant environment:

In an Oracle RAC environment: If you are using the srvctl utility and if you want to include environment variables in the sqlnet.ora configuration file, then you must set these environment variables in both the operating system and the srvctl environment. Oracle recommends that you place the keystore on a shared file system, such as Oracle Automatic Storage Management (ASM) or NFS.

In a multitenant environment: The keystore location is set for the entire multitenant container database (CDB), not for individual pluggable databases (PDBs)


If the path_to_keystore(DIRECTORY) will contain an environment variable, then set this variable in the environment where the database instance is started and before you start the database. If you are using the srvctl utility to start the database, then set the environment variable in the srvctl environment as well, using the following command:

srvctl setenv database -db database_name -env "environment_variable_name=environment_variable_value"



You can configure sqlnet.ora for an Automatic Storage Management (ASM) file system

The following example shows how to configure a software keystore location in the sqlnet.ora file for an ASM file system:

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=+disk1/mydb/wallet)))
    
    
You can configure sqlnet.ora for an Oracle Automatic Storage Management (ASM) disk group.

The following format shows how to configure a software keystore if you want to create a software keystore location on an ASM disk group:

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=+ASM_file_path_of_the_diskgroup)))
3.创建keystore
1
2
3
4
5
6
7
8
SQL> administer key management create keystore '/var/opt/oracle/wallet/ora12c' identified by wallet123;

keystore altered.

SQL> host ls -l /var/opt/oracle/wallet/ora12c
total 4
-rw------- 1 oracle oinstall 2400 Jun  8 09:51 ewallet.p12

4.打开或关闭keystore

You can open and close the keystore from the root container using the following commands. If the CONTAINER=ALL clause is omitted, the current container is assumed. Open the keystore for all containers.

1
2
3
4
5
6
7
8
9
10
-- Open
SQL> administer key management set keystore open identified by wallet123 container=all;

keystore altered.

-- Close
SQL> administer key management set keystore close identified by wallet123 container=all;

keystore altered.

5.查看key
1
2
3
4
5
6
7
8
9
10
11
12
13
14
set linesize 200 pagesize 200
col wrl_parameter for a40
col wrl_type for a15
col status for a18
select * from v$encryption_wallet;

WRL_TYPE        WRL_PARAMETER                            STATUS             WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
--------------- ---------------------------------------- ------------------ -------------------- --------- --------- ----------
FILE            /var/opt/oracle/wallet/ora12c/           OPEN_NO_MASTER_KEY PASSWORD             SINGLE    UNDEFINED          1


SQL> select con_id, key_id from v$encryption_keys;

no rows selected
6.备份keystore
1
2
ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'keystore_bkp' IDENTIFIED BY wallet123;
SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;
7.创建master key
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> administer key management set key identified by wallet123 with backup container=all;

keystore altered.

SQL> administer key management set key force keystore identified by wallet123 with backup container=all;

SQL> administer key management set key identified by wallet123 with backup container=all;
administer key management set key identified by wallet123 with backup container=all
*
ERROR at line 1:
ORA-46665: master keys not activated for all PDBs during REKE

SQL> select con_id, key_id from v$encryption_keys;

    CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
         3 AdNFaTnNFU9Mv24t+lQbUpEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         1 AVz3r39CGk/xvxwDSkHhSxwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
8.再次查看wallet
1
2
3
4
5
6
7
8
9
set linesize 200 pagesize 200
col wrl_parameter for a40
col wrl_type for a15
col status for a18
select * from v$encryption_wallet;

WRL_TYPE        WRL_PARAMETER                            STATUS             WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
--------------- ---------------------------------------- ------------------ -------------------- --------- --------- ----------
FILE            /var/opt/oracle/wallet/ora12c/           OPEN               PASSWORD             SINGLE    NO                 1
9.导出master key
1
2
ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET wallet123 TO 'C:\oracle\admin\mcs1\wallet\masterkey_cdb_exp.bkp' IDENTIFIED BY "mcs1$admin";

三、加密表
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
SQL> conn test/test@xprod
Connected.

CREATE TABLE tde_test (
  id    NUMBER(10),
  data  VARCHAR2(50) ENCRYPT
);

INSERT INTO tde_test VALUES (1, 'This is a secret!');
COMMIT;



[oracle@server ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 8 10:36:10 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XPROD                          READ WRITE NO

SQL> administer key management set keystore close identified by wallet123;

keystore altered.

SQL> alter session set container=xprod;

Session altered.

SQL> set linesize 200 pagesize 200
SQL> col wrl_parameter for a40
SQL> col wrl_type for a15
SQL> col status for a18
SQL> select * from v$encryption_wallet;

WRL_TYPE        WRL_PARAMETER                            STATUS             WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
--------------- ---------------------------------------- ------------------ -------------------- --------- --------- ----------
FILE                                                     CLOSED             UNKNOWN              SINGLE    UNDEFINED          3


SQL> select * from test.tde_test;
select * from test.tde_test
                   *
ERROR at line 1:
ORA-28365: wallet is not open



SQL> conn test/test@xprod
Connected.
SQL> select * from tde_test;
select * from tde_test
              *
ERROR at line 1:
ORA-28365: wallet is not open


[oracle@server ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 8 10:40:55 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> administer key management set keystore open identified by wallet123 container=all;

keystore altered.

SQL> alter session set container=xprod;

Session altered.

SQL> select * from test.tde_test;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!


[oracle@server ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 8 11:10:41 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> administer key management set keystore close identified by wallet123;

keystore altered.



四、加密表空间
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
You cannot change an existing tablespace to make it encrypted. You can, however, import data into an encrypted tablespace by using Oracle Data Pump. You can also use a SQL statement such as CREATE TABLE...AS SELECT...or ALTER TABLE...MOVE... to move data into an encrypted tablespace. The CREATE TABLE...AS SELECT... statement creates a table from an existing table. The ALTER TABLE...MOVE... statement moves a table into the encrypted tablespace.

For security reasons, you cannot encrypt a tablespace with the NO SALT option.

You can query the ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES data dictionary views to verify if a tablespace was encrypted.



-- Encrypted tablespacew
[oracle@server ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 8 10:51:43 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=xprod;

Session altered.

SQL> CREATE TABLESPACE encrypted_ts
DATAFILE '/opt/Ora12cData/ora12c/xprod/encrypted_ts01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

Tablespace created.

select tablespace_name,encrypted from dba_tablespaces;


SQL> ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

User altered.

CONN test/test@xprod

CREATE TABLE tde_ts_test (
  id    NUMBER(10),
  data  VARCHAR2(50)
) TABLESPACE encrypted_ts;

INSERT INTO tde_ts_test VALUES (1, 'This is also a secret!');
COMMIT;

SQL> select * from tde_ts_test;

        ID DATA
---------- --------------------------------------------------
         1 This is also a secret!
         


SQL> administer key management set keystore close identified by wallet123;

keystore altered.

SQL> set linesize 200 pagesize 200
SQL> col wrl_parameter for a40
SQL> col wrl_type for a15
SQL> col status for a18
SQL> select * from v$encryption_wallet;

WRL_TYPE        WRL_PARAMETER                            STATUS             WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
--------------- ---------------------------------------- ------------------ -------------------- --------- --------- ----------
FILE                                                     CLOSED             UNKNOWN              SINGLE    UNDEFINED          3


SQL> select * from test.tde_ts_test;
select * from test.tde_ts_test
                   *
ERROR at line 1:
ORA-28365: wallet is not open


SQL> administer key management set keystore open identified by wallet123;

keystore altered.

SQL> select * from test.tde_ts_test;

        ID DATA
---------- --------------------------------------------------
         1 This is also a secret!    
         
         
         
         
---查看加密表空间
SQL> select * from v$tablespace;

五、配置自动启动Auto-Login Keystores
1
2
3
4
5
6
7
8
9
配置自动启动keystore之前打开keystore使用以下步骤:
STARTUP MOUNT;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
ALTER DATABASE OPEN;


SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/var/opt/oracle/wallet/ora12c' IDENTIFIED BY wallet123;

keystore altered.
六、TDE相关视图
View Description
ALL_ENCRYPTED_COLUMNS Displays encryption information about encrypted columns in the tables accessible to the current user
DBA_ENCRYPTED_COLUMNS Displays encryption information for all of the encrypted columns in the database
USER_ENCRYPTED_COLUMNS Displays encryption information for encrypted table columns in the current user’s schema
DBA_TABLESPACE_USAGE_METRICS Describes tablespace usage metrics for all types of tablespaces, including permanent, temporary, and undo tablespaces
V$CLIENT_SECRETS Lists the properties of the strings (secrets) that were stored in the keystore for various features (clients).In a multitenant environment, when you query this view in a PDB, then it displays information about keys that were created or activated for the current PDB. If you query this view in the root, then it displays this information about keys for all of the PDBs.
V$ENCRYPTED_TABLESPACES Displays information about the tablespaces that are encrypted
V$ENCRYPTION_KEYS When used with keys that have been rotated with the ADMINISTER KEY MANAGEMENT statement, displays information about the TDE master encryption keys.In a multitenant environment, when you query this view in a PDB, it displays information about keys that were created or activated for the current PDB. If you query this view in the root, it displays this information about keys for all of the PDBs.
V$ENCRYPTION_WALLET Displays information on the status of the keystore and the keystore location for TDE
V$WALLET Displays metadata information for a PKI certificate, which can be used as a master encryption key for TDE

wallet开启自动打开后，无法手工关闭的处理方法:

1
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=546115240614976&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1944507.1&_afrWindowMode=0&_adf.ctrl-state=3rky2yj43_109
七、报错问题处理
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> administer key management set keystore open identified by wallet123 container=all;
administer key management set keystore open identified by wallet123 container=all
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open

SQL> administer key management set key identified by wallet123 with backup container=all;
administer key management set key identified by wallet123 with backup container=all
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

SQL> administer key management set keystore close identified by wallet123 container=all;
administer key management set keystore close identified by wallet123 container=all
*
ERROR at line 1:
ORA-28417: password-based keystore is not open



SQL> administer key management set keystore close;

keystore altered.

this will close the removed auto-open wallet in the database, then open the password based wallet and retry the original Set Key statement:

[oracle@server ~]$ cd  /var/opt/oracle/wallet/ora12c/
[oracle@server ora12c]$ rm -rf cwallet.sso

administer key management set keystore open identified by wallet123;
administer key management set key identified by <wallet_password> with backup;


  • Title: Oracle TDE透明数据加密
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-07-11 18:30:46
  • Updated at : 2024-07-20 05:15:41
  • Link: https://www.zhangdong.me/oracle-wallet-tde.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
Oracle11g配置SSL连接 Oracle11g配置SSL连接 Oracle12c克隆PDB Oracle12c克隆PDB Oracle19c重命名PDB Oracle19c重命名PDB
推荐文章
Oracle11g配置SSL连接 Oracle11g配置SSL连接 Oracle12c克隆PDB Oracle12c克隆PDB
评论
  1. 一、TDE简介
  2. 二、Oracle12c配置TDE
    1. 1.创建wallet目录
    2. 2.配置sqlnet
    3. 3.创建keystore
    4. 4.打开或关闭keystore
    5. 5.查看key
    6. 6.备份keystore
    7. 7.创建master key
    8. 8.再次查看wallet
    9. 9.导出master key
  3. 三、加密表
  4. 四、加密表空间
  5. 五、配置自动启动Auto-Login Keystores
  6. 六、TDE相关视图
  7. 七、报错问题处理