oracle TDE使用(orapki方式)

环境：

OS:Centos 6

DB:11.2.0.4 3节点组成的rac环境

 

说明：创建了wallet后一定要备份wallet目录下的文件,否则丢失了的话，使用该wallet加密的数据和表空间也会丢失.

 

1.先要创建一个"wallet钱包",这个钱包里面保存着密钥,Oracle就是通过这个密钥对列进行加密和解密的.
在其中一个节点上操作,我这里是在节点1上操作
su - oracle
[oracle@rac01 ~]$ mkdir $ORACLE_BASE/wallet
[oracle@rac01 ~]$ cd $ORACLE_BASE/wallet
[oracle@rac01 wallet]$ pwd
/u01/oracle/app/wallet

 

2.生成wallet钱包之前先要设定wallet钱包的保存位置
这里是在oracle账号下的sqlnet.ora,而不是grid用户下的sqlnet.ora
设置wallet钱包位置的文件$ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=(
SOURCE=(
(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/u01/oracle/app/wallet))
)
)

3.数据库里创建wallet并且设置访问密码 
SQL> connect / as sysdba
alter system set encryption key authenticated by "123456";

 

4.创建带有加密列的表，插入相关数据
connect hxl/oracle
create table hxl.en_test (id int,name varchar(20),salary number(10,2) encrypt using 'AES256');
insert into hxl.en_test values(1,'hwb',50000.00);
insert into hxl.en_test values(2,'hwb2',30000.00);
commit;

 

或是可以创建加密表空间

 

SQL> CREATE TABLESPACE tps_sec
  2  DATAFILE '/u01/app/oracle/oradata/ora11g/tps_sec01.dbf'
  3  SIZE 100M
  4  ENCRYPTION
  5  DEFAULT STORAGE (ENCRYPT);

Tablespace created.

 

 

 

 

5.设置了密码后可以手动打开或者关闭wallet
打开wallet
alter system set encryption wallet open authenticated by "123456";

关闭wallet
alter system set wallet close identified by "123456";

 

这个时候如下的两个命令输出结果是一致的,若是不一致的话，说明密钥修改过

 

[oracle@ora11g wallet]$ mkstore -wrl /u01/app/oracle/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
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.SECURITY.DB.ENCRYPTION.MASTERKEY = AY9BaFi0qU9Bvys187OmwCwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> 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
--------------------------------------------------------------------------------
AY9BaFi0qU9Bvys187OmwCw=

 

 

 

 

6.关闭wallet查看加密列
如果有查询权限,不加密的列可以查询,加密的无法查询,必须打开wallet才可以
SQL> alter system set wallet close identified by "123456";
SQL> select wrl_type,wrl_parameter,status from gv$encryption_wallet;
SQL> desc hxl.en_test;
SQL> select id,name from hxl.en_test;
SQL> select SALARY from hxl.en_test;

 

7.打开wallet,可以正常访问加密列
SQL> alter system set encryption wallet open authenticated by "123456";
SQL> select SALARY from hxl.en_test;

 

8.数据字典 查看那些列被加密
select * from dba_encrypted_columns;

 

#################################RAC环境#############################
9.rac环境尝试在另外的节点上查询该表
SQL> select * from hxl.en_test;
select * from hxl.en_test
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> alter system set encryption wallet open authenticated by "123456";
alter system set encryption wallet open authenticated by "123456"
*
ERROR at line 1:
ORA-28367: wallet does not exist

另外的节点也需要配置钱包


节点2:
su - oracle
[oracle@rac01 ~]$ mkdir $ORACLE_BASE/wallet
[oracle@rac01 ~]$ cd $ORACLE_BASE/wallet

将节点1上的钱包文件拷贝到节点2相应的目录
su - oracle
scp /u01/oracle/app/wallet/ewallet.p12 oracle@192.168.56.102:/u01/oracle/app/wallet/


同样生成wallet钱包之前先要设定wallet钱包的保存位置
设置wallet钱包位置的文件$ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=(
SOURCE=(
(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/u01/oracle/app/wallet))
)
)

重新登录查询
SQL>connect / as sysdba
SQL>alter system set encryption wallet open authenticated by "123456";
SQL> select * from hxl.en_test;

ID NAME SALARY
---------- -------------------- ----------
1 hwb 50000
2 hwb2 30000

节点3的做法与节点2一样

 

###############expdp/impdp验证###########################

expdp导出测试

钱包关闭的情况下(非加密的表会正常导出)
SQL> alter system set wallet close identified by "123456";

expdp system/oracle tables=hxl.en_test dumpfile=en_test_wallet.dmp directory=DATA_PUMP_DIR
会报如下的错误:
ORA-31693: Table data object "HXL"."EN_TEST" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-28365: wallet is not open
ORA-39173: Encrypted data has been stored unencrypted in dump file set.

钱包打开的情况
SQL> alter system set wallet open identified by "123456";

expdp system/oracle tables=hxl.en_test dumpfile=en_test_wallet_open.dmp directory=DATA_PUMP_DIR
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HXL"."EN_TEST" 5.882 KB 2 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************

这个时候可以导出成功

 

导入测试
我们这里导入使用成功导出的文件进行导入
钱包关闭的情况下
SQL> alter system set wallet close identified by "123456";
SQL> drop table hxl.en_test;

impdp system/oracle directory=data_pump_dir dumpfile=en_test_wallet_open.dmp tables=hxl.en_test
导入提示:
ORA-39083: Object type TABLE:"HXL"."EN_TEST" failed to create with error:
ORA-28365: wallet is not open

钱包打开的情况下
SQL> alter system set wallet open identified by "123456";

impdp system/oracle directory=data_pump_dir dumpfile=en_test_wallet_open.dmp tables=hxl.en_test
这个还是导入成功:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HXL"."EN_TEST" 5.882 KB 2 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 18 15:48:42 2023 elapsed 0 00:00:01

 

###############rman验证###########################
钱包关闭的情况
SQL> alter system set wallet close identified by "123456";

run
{
allocate channel ch1 device type disk;
backup as compressed backupset full filesperset 5 database format '+REDO';
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '+REDO';
backup current controlfile format '+REDO';
backup spfile format '+REDO';
release channel ch1;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
}

这种方式会自动生成如下目录的备份集,即所谓的omf管理
piece handle=+REDO/slnngk/backupset/2023_10_18/nnndf0_tag20231018t160353_0.358.1150560233 tag=TAG20231018T160353 comment=NONE

这种方式的备份可读性不强,我们可以自己创建一个备份目录,文件名按照特定格式生成,脚本如下
run
{
allocate channel ch1 device type disk;
backup as compressed backupset full filesperset 5 database format '+REDO/rman_backup/db_fullbackup_%d_%s_%p_%T';
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '+REDO/rman_backup/arch_%d_%s_%p_%T';
backup current controlfile format '+REDO/rman_backup/ctl_%d_%s_%p_%T';
backup spfile format '+REDO/rman_backup/spfile_%d_%s_%p_%T';
release channel ch1;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
}

前提需要创建目录+REDO/rman_backup

[grid@rac02 ~]$ asmcmd
ASMCMD> cd redo
ASMCMD> mkdir rman_backup
ASMCMD> cd rman_backup
ASMCMD> pwd
+redo/rman_backup

asm查看备份文件,我们自己命名的文件其实是一个连接符,指向omf具体的文件
ASMCMD> ls -al
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'

Type Redund Striped Time Sys Name
N arch_slnngk_26_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/annnf0_TAG20231018T161144_0.491.1150560705
N ctl_slnngk_27_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/ncnnf0_TAG20231018T161146_0.363.1150560707
N db_fullbackup_slnngk_23_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/nnndf0_TAG20231018T161039_0.436.1150560639
N db_fullbackup_slnngk_24_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/nnndf0_TAG20231018T161039_0.392.1150560665
N spfile_slnngk_28_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/nnsnf0_TAG20231018T161148_0.494.1150560709
ASMCMD>

说明:使用wallet对rman不会有影响

 

 

#####################开启自动打开钱包##########################
在命令行模式先打开钱包
su - oracle
SQL>connect / as sysdba
SQL> alter system set wallet open identified by "123456";

然后使用oracle账号登录图形界面,输入owm

 

 

 

 

选择auto login后点击保存。

 

或是使用命令的方式

设置开启自动开启钱包
su - oracle
[oracle@rac01 wallet]$ orapki wallet create -wallet /u01/oracle/app/wallet -auto_login_local
[oracle@rac01 wallet]$ ls
cwallet.sso ewallet.p12

这个时候会生成sso文件

 

说明:

1.设置了 auto_login_local后,数据库启动是自动打开钱包的，解密的字段可以直接查询，想手工关闭，不能使用如下命令进行关闭和启动钱包

 

SQL> alter system set wallet close identified by "123456";
alter system set wallet close identified by "123456"
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> alter system set encryption wallet open authenticated by "123456";
alter system set encryption wallet open authenticated by "123456"
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open

 

关闭所有钱包

alter system set encryption wallet close;

 

若想手工关闭和启动钱包，需要将钱包目录下的sso文件删除掉(重启数据库生效).

 

 2.钱夹可以重建,但是重建后的钱夹不能查询使用旧钱夹中的主密钥加密的数据，即先前加密的数据丢失.

 

 

posted @ 2023-10-18 17:13  slnngk  阅读(274)  评论(0)  编辑  收藏  举报