关于oracle的TDE的一点简单测试

wgz7747147820

已于 2023-05-13 10:51:52 修改

阅读量310

点赞数

分类专栏： orale 文章标签： oracle 数据库 java

于 2023-05-13 03:20:37 首次发布

本文链接：https://blog.csdn.net/wgz7747147820/article/details/130653041

版权

orale 专栏收录该内容

203 篇文章 0 订阅

订阅专栏

v$encryption_wallet这个视图是用来显示TDE的keystore的状态的
status那一列显示的是auto login keystore的状态，而不是TDE的keystore的状态，如果auto login keystore存在的话。
我们做下下面的测试
首先设置wallet_root这个参数
SQL> alter system set wallet_root='/u01/app/v1/oradata/cdb3/wallet' scope=spfile sid='*';

System altered.
设置完后重启这个cdb
[Fri May 12 14:56:53][10898][oracle@nshqae01adm01:/u01/app/orabase/21c/dbhome1/bin][0]$ date;./srvctl stop database -d cdb3;date;
Fri May 12 14:57:00 EDT 2023
Fri May 12 14:57:38 EDT 2023
[Fri May 12 14:57:38][10898][oracle@nshqae01adm01:/u01/app/orabase/21c/dbhome1/bin][0]$ date;./srvctl start database -d cdb3;date;
Fri May 12 14:57:44 EDT 2023
Fri May 12 14:58:05 EDT 2023

设置tde_configuration这个参数
SQL> alter system set tde_configuration='keystore_configuration=file' scope=both sid='*';

System altered.
查看v$encryption_wallet这个视图，因为我们还没有创建keystore，所以这个status显示not_awailable

SQL> select * from v$encryption_wallet;

WRL_T WRL_PARAMETER                            STATUS               WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
----- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE  /u01/app/v1/oradata/cdb3/wallet/tde/     NOT_AVAILABLE        UNKNOWN              SINGLE    NONE     UNDEFINED          1
FILE                                           NOT_AVAILABLE        UNKNOWN              SINGLE    UNITED   UNDEFINED          2
FILE                                           NOT_AVAILABLE        UNKNOWN              SINGLE    UNITED   UNDEFINED          3

下面创建keystore
SQL> administer key management create keystore identified by cdb3;

keystore altered.
创建之后状态显示为closed
SQL> select * from v$encryption_wallet;

WRL_T WRL_PARAMETER                            STATUS               WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
----- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE  /u01/app/v1/oradata/cdb3/wallet/tde/     CLOSED               UNKNOWN              SINGLE    NONE     UNDEFINED          1
FILE                                           CLOSED               UNKNOWN              SINGLE    UNITED   UNDEFINED          2
FILE                                           CLOSED               UNKNOWN              SINGLE    UNITED   UNDEFINED          3

运行下面的命令open这个keystore之后，会显示状态为open_no_master_key
SQL> administer key management set keystore open identified by cdb3;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_T WRL_PARAMETER                            STATUS               WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
----- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE  /u01/app/v1/oradata/cdb3/wallet/tde/     OPEN_NO_MASTER_KEY   PASSWORD             SINGLE    NONE     UNDEFINED          1
FILE                                           CLOSED               UNKNOWN              SINGLE    UNITED   UNDEFINED          2
FILE                                           CLOSED               UNKNOWN              SINGLE    UNITED   UNDEFINED          3

将keystore关闭掉
SQL> administer key management set keystore close identified by cdb3 container=all;

keystore altered.
SQL> select * from v$encryption_wallet;

WRL_T WRL_PARAMETER                            STATUS               WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
----- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE  /u01/app/v1/oradata/cdb3/wallet/tde/     CLOSED               UNKNOWN              SINGLE    NONE     UNDEFINED          1
FILE                                           CLOSED               UNKNOWN              SINGLE    UNITED   UNDEFINED          2
FILE                                           CLOSED               UNKNOWN              SINGLE    UNITED   UNDEFINED          3

下面创建auto login keystore
SQL> administer key management create auto_login keystore from keystore identified by cdb3;

keystore altered.
创建auto login keystore之后，会在keystore所在的目录增加一个文件 cwallet.sso
[Fri May 12 15:10:01][42770][oracle@nshqae01adm01:/u01/app/v1/oradata/cdb3/wallet/tde][0]$ ls
cwallet.sso  ewallet.p12
因为每次查询v$encryption_wallet会自动登录那个auto login keystore，而这个时候v$encryption_wallet显示的是auto login keystore的状态，而不是TDE的那个keystore的状态了。所以只要有auto login keystore，则status肯定是open状态的，不能真正显示tde keystore的状态了

SQL> select * from v$encryption_wallet;

WRL_T WRL_PARAMETER                            STATUS               WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
----- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE  /u01/app/v1/oradata/cdb3/wallet/tde/     OPEN_NO_MASTER_KEY   AUTOLOGIN            SINGLE    NONE     UNDEFINED          1
FILE                                           OPEN_NO_MASTER_KEY   AUTOLOGIN            SINGLE    UNITED   UNDEFINED          2
FILE                                           OPEN_NO_MASTER_KEY   AUTOLOGIN            SINGLE    UNITED   UNDEFINED          3

貌似administer key management 语句没有删除keystore的语句，所以对于这个auto login keystore，我们可以直接删除那个文件，就算删除这个auto login keystore了
删除了auto login keystore，v$encryption_wallet里面的status就可以显示keystore的状态了，但是需要退出并重新connect sqlplus来查看keystore的状态

备份keystore

SQL> administer key management backup keystore using 'test' identified by cdb3 to '/tmp/';

keystore altered.
[Fri May 12 20:45:30][110010][oracle@nshqae01adm03:/tmp][0]$ ls -al *wallet*
-rw------- 1 oracle oinstall 5259 May 12 20:44 ewallet_2023051300445507_test.p12

将一个pdb的keystore 模式从united转换成isolated模式
pdb的名字为cdb3pdb10001 con_id为3

在没有转化之前，可以看出keystore_mode为united，并且tde_configuration这个参数的值也为空

SQL> select * from v$encryption_wallet;
WRL_TYPE   WRL_PARAMETER                            STATUS               WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- -------------------- ---------- --------- -------- --------- ----------
FILE       /u01/app/v1/oradata/cdb3/wallet/tde/     OPEN_NO_MASTER_KEY   AUTOLOGIN  SINGLE    NONE     UNDEFINED          1
FILE                                                OPEN_NO_MASTER_KEY   AUTOLOGIN  SINGLE    UNITED   UNDEFINED          2
FILE                                                OPEN_NO_MASTER_KEY   AUTOLOGIN  SINGLE    UNITED   UNDEFINED          3
SQL> show parameter tde_con;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string
运行下面的命令将这个pdb的keystore从united转化为isolated模式

SQL> administer key management isolate keystore identified by cdb3pdb10001 from root keystore identified by cdb3 with backup;

keystore altered.
转化后从这个pdb里面查询这个pdb的tde_configuration参数，就发现已经有值了
SQL> show parameter tde_con;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      KEYSTORE_CONFIGURATION=FILE
SQL> select * from v$encryption_wallet;
WRL_TYPE   WRL_PARAMETER                            STATUS               WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- -------------------- ---------- --------- -------- --------- ----------
FILE       /u01/app/v1/oradata/cdb3/wallet/tde/     OPEN                 AUTOLOGIN  SINGLE    NONE     NO                 1
FILE                                                OPEN                 AUTOLOGIN  SINGLE    UNITED   NO                 2
FILE       /u01/app/v1/oradata/cdb3/wallet/FB48ED0B OPEN                 PASSWORD   SINGLE    ISOLATED NO                 3

新建一个pdb，并且这个pdb采用isolated mode的keystore

SQL> create pluggable database cdb3pdb10002 admin user pdbadmin identified by cdb3;

Pluggable database created.

SQL> alter pluggable database cdb3pdb10002 open read write instances=all;

Pluggable database altered.

SQL> alter pluggable database cdb3pdb10002 save state instances=all;

Pluggable database altered.

SQL> alter session set container=cdb3pdb10002;

Session altered.

SQL> column wrl_type format a10
column wrl_parameter format a40
column status format a20
column wallet_type format a10
set linesize 180
SQL> SQL> SQL> SQL> SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER                            STATUS               WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- -------------------- ---------- --------- -------- --------- ----------
FILE                                                OPEN_NO_MASTER_KEY   PASSWORD   SINGLE    UNITED   UNDEFINED          4

SQL> alter system set tde_configuration='keystore_configuration=file' scope=both sid='*';
alter system set tde_configuration='keystore_configuration=file' scope=both sid='*'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-46715: The keystore of the PDB is not in a closed state on all instances.
Help: https://docs.oracle.com/error-help/db/ora-32017/
碰到上面这个问题的原因是因为auto login keystore存在，所以总是报这个错误，把auto logoin keystore删除掉之后，然后再重启cdb，就不会再出现这个问题了
从我尝试的情况看，对于ORA-46715的解决只有一个方法，那就是删除auto login keystore然后重启数据库，貌似没有别的办法了

SQL> alter session set container=cdb3pdb10002;

Session altered.

SQL> administer key management create keystore identified by cdb3pdb10002;

keystore altered.
SQL> administer key management set keystore open identified by cdb3pdb10002;

keystore altered.
SQL> administer key management set key identified by cdb3pdb10002 with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER                            STATUS               WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ---------------------------------------- -------------------- ---------- --------- -------- --------- ----------
FILE       /u01/app/v1/oradata/cdb3/wallet/FB890F35 OPEN                 PASSWORD   SINGLE    ISOLATED NO                 4
           38504AFDE053E5445B0A8F18/tde/

将一个pdb的keystore模式从united模式改为isolated 模式有两种途径，一种就是上面采用的alter system set tde_configuration这种方式来实现，这种方式实现起来比较麻烦，还是下面这种使用administer key management isolate keystore这个方式比较简单

SQL> create pluggable database cdb3pdb10003 admin user pdbadmin identified by cdb3;

Pluggable database created.

SQL> alter pluggable database cdb3pdb10003 open read write instances=all;

Pluggable database altered.

SQL> alter pluggable database cdb3pdb10003 save state instances=all;

Pluggable database altered.
SQL> administer key management set key force keystore identified by cdb3 with backup;

keystore altered.
SQL> administer key management isolate keystore identified by cdb3pdb10003 from root keystore force keystore identified by cdb3 with backup;

keystore altered.