网站管理助手ftp连接不上,网站建设发展趋势,济南槐荫区最新消息,沈阳点金网站建设1. 钱夹密码千万不能忘记#xff0c;这也是使用TDE 需要承担的风险。
2. 只要将wallet cwallet.sso 拷贝过去#xff0c;加密没有意义#xff01; 钱夹的备份 正如上述#xff0c;已经加密过的表列或者表空间#xff0c;钱夹必须打开才能够查询到里面的数据。如果钱夹丢…1. 钱夹密码千万不能忘记这也是使用TDE 需要承担的风险。
2. 只要将wallet cwallet.sso 拷贝过去加密没有意义 钱夹的备份 正如上述已经加密过的表列或者表空间钱夹必须打开才能够查询到里面的数据。如果钱夹丢失那就意味着加密数据的丢失所以钱夹的备份是及其重要的。钱夹一开始创建就应该得到有效的备份放在不同的磁盘上。不要和数据库文件所在磁盘相同这可以避免数据文件和钱夹同时被盗当然了如果同时被盗想通过数据库查询加密数据不知道钱夹的密码也是无法查询到加密数据的。 除了钱夹需要有效的备份之外钱夹的密码千万不能忘记。如果忘记了钱夹密码钱夹就无法打开加密的数据也就无法查询到这也就意味着加密数据的丢失。 Oracle 没有提供解决钱夹密码丢失的方法 。 综上如果使用透明加密来加密数据要想加密数据不丢失必须做到两点1. 钱夹必须存在备份的重要性2. 钱夹的密码不能够忘记。 这以加密表空间为例
用户连入数据库进行数据更新或者查询时所涉及到的对象会自动加密或自动解密
但加密表空间里的所有数据都是以加密的格式被存储在磁盘上磁盘或备份介质被盗时里面的数据也不会被盗取因为里面是乱码的。
如果使用操作系统命令 strings 直接查看文件中内容未加密表空间的数据文件中的内容为明文而加密过表空间对应的数据文件中的内容则为乱码。
[rootljw jiami]# strings books01.dbf 未加密
}|{z
WORCL
BOOKS
name,
good,
note [rootljw jiami]# strings secure01.dbf 加密过
MMFJ?8E
iHa!c
jVIa9o
y8wt0
zcnf
。
内存中为明文硬盘中为密文 注如果文件很大则可以使用管道 head –n 行数 指定要显示的行数 例 [oracleljw orcl]$ strings books01.dbf | head -n 3 结论导出工具 EXP 无法导出加密过的表具体测试如下
钱包打开和钱包关闭时导出加密过的表进行测试 钱包关闭时
SQL select * from dba_encrypted_columns; SQL select * from v$encryption_wallet;
CLOSED [oracleljw backup]$ exp system/oracle tablestest.tde filetde.dmp logtde.log
About to export specified tables via Conventional Path ...
Current user changed to TEST
EXP-00107: Feature (COLUMN ENCRYPTION) of column DATA in table TEST.TDE is not supported. The table will not be exported.
Export terminated successfully with warnings. 钱包打开时
SQL alter system set encryption wallet open identified by hzmcdba123;
[oracleljw backup]$ exp system/oracle tablestest.tde filetde.dmp logtde.log
Export: Release 11.2.0.4.0 - Production on Sat Jun 24 03:58:43 2017
About to export specified tables via Conventional Path ...
Current user changed to TEST
EXP-00107: Feature (COLUMN ENCRYPTION) of column DATA in table TEST.TDE is not supported. The table will not be exported.
Export terminated successfully with warnings. 结论expdp 1. 导出时钱包需要打开否则都会报错。
2. 导出后导入时钱包也需要打开否则也都会报错。
3. 导出后导入另一个数据库如果那个数据库没有钱夹导入失败。如果那个数据库有钱夹并且钱夹打开测试中另一库中新建钱夹密码与原库不一样也会导入成功具体测试如下 1. 导出时钱包需要打开否则都会报错 钱夹关闭
SQL select * from v$encryption_wallet; CLOSED 导出
[oracleljw backup]$ expdp system/oracle directorydump_file_dir dumpfiletde.dmp tablestest.tde ORA-28365: wallet is not open
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table SYSTEM.SYS_EXPORT_TABLE_01 successfully loaded/unloaded 2. 导出后导入时钱包也需要打开否则也都会报错 钱夹打开
SQL alter system set encryption wallet open identified by hzmcdba123; . . exported TEST.TDE 5.570 KB 10 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table SYSTEM.SYS_EXPORT_TABLE_01 successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /oracle/backup/tde.dmp
Job SYSTEM.SYS_EXPORT_TABLE_01 completed with 1 error(s) at Sat Jun 24 04:33:44 2017 elapsed 0 00:00:06 导入同一个库
钱夹关闭时导入
SQL alter system set encryption wallet close identified by hzmcdba123; [oracleljw backup]$ impdp system/oracle directorydump_file_dir dumpfiletde.dmp Import: Release 11.2.0.4.0 - Production on Sat Jun 24 04:59:04 2017 ORA-28365: wallet is not open 钱夹打开导入
SQL alter system set encryption wallet open identified by hzmcdba123; System altered. [oracleljw backup]$ impdp system/oracle directorydump_file_dir dumpfiletde.dmp 注钱夹打开从同一个库中导出后导入成功。 导入另一个库
[oracleljw backup]$ impdp system/oracle directorydump_file_dir dumpfiletde.dmp Starting SYSTEM.SYS_IMPORT_FULL_01: system/******** directorydump_file_dir dumpfiletde.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:TEST.TDE failed to create with error:
ORA-28365: wallet is not open 注导入另一个库失败因为没有启用透明加密钱夹。 创建钱包导入
SQL alter system set encryption key identified by hzmcdba; [oracleljw backup]$ impdp system/oracle directorydump_file_dir dumpfiletde.dmp . . imported TEST.TDE 5.570 KB 10 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job SYSTEM.SYS_IMPORT_FULL_01 successfully completed at Sat Jun 24 05:41:37 2017 elapsed 0 00:00:04 注导入另一库需要有钱包钱包打开即可。 结论不管钱包是否打开都可以正常备份恢复时需要打开钱包。 备份
SQL select table_name from user_tables where tablespace_nameENCRYPTEDTBS; 钱夹关闭
SQL alter system set encryption wallet close identified by hzmcdba123; RMAN backup datafile 7; Starting Control File and SPFILE Autobackup at 24-JUN-17
piece handle/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-09 commentNONE
Finished Control File and SPFILE Autobackup at 24-JUN-17 钱夹打开
SQL alter system set encryption wallet open identified by hzmcdba123; System altered. RMAN backup datafile 7; Starting Control File and SPFILE Autobackup at 24-JUN-17
piece handle/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-0a commentNONE
Finished Control File and SPFILE Autobackup at 24-JUN-17 恢复
钱夹关闭
[oracleljw orcl]$ rm secure01.dbf 1. 用之前关闭钱夹备份的备份集经进行恢复
RMAN restore datafile 7 from tagTAG20170624T084727; channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-JUN-17 RMAN recover datafile 7 from tagTAG20170624T084727; ORA-28365: wallet is not open SQL alter system set encryption wallet open identified by hzmcdba123;
RMAN recover datafile 7 from tagTAG20170624T084727; Finished recover at 24-JUN-17 2. 用打开钱夹备份的备份集经进行恢复 RMAN restore datafile 7 from tagTAG20170624T084842; Starting restore at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID17 device typeDISK channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oracle/app/orcl/secure01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1
channel ORA_DISK_1: piece handle/oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1 tagTAG20170624T084842
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-JUN-17 RMAN recover datafile 7 from tagTAG20170624T084842; ORA-28365: wallet is not open SQL alter system set encryption wallet open identified by hzmcdba123; System altered. RMAN recover datafile 7 from tagTAG20170624T084842; Starting recover at 24-JUN-17
using channel ORA_DISK_1 starting media recovery
media recovery complete, elapsed time: 00:00:00 Finished recover at 24-JUN-17 打开数据能否可查测试异地回复
结论数据文件可打开加密数据可查当然了钱夹也拷过去钱夹密码也知道。 只拷贝数据文件钱夹没有拷贝加密数据不可查模拟数据文件被盗 情景一 SQL drop tablespace dabiao including contents and datafiles; Tablespace dropped. SQL create tablespace dabiao datafile /oracle/app/orcl/dabiao.dbf size 100m autoextend on; Tablespace created. SQL create table dabiao(id number,name varchar(10)) tablespace dabiao; Table created. SQL begin 2 for i in 1 .. 10 3 loop 4 insert into dabiao values ( i, ljw ); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL alter table dabiao modify(name encrypt); Table altered. SQL alter system set encryption wallet close identified by hzmcdba; System altered. SQL select * from dabiao;
select * from dabiao *
ERROR at line 1:
ORA-28365: wallet is not open 关闭数据
SQL conn / as sysdba
Connected.
SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down. scp -r /oracle/* oracle192.168.142.11:/oracle/ 另一台 SQL startup
ORACLE instance started. Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL conn test/test;
Connected.
SQL select * from dabiao;
select * from dabiao *
ERROR at line 1:
ORA-28365: wallet is not open SQL alter system set encryption wallet open identified by hzmcdba; System altered. SQL select * from dabiao; 10 rows selected. 打开查询成功 目标库
相关文件被传过来 SQL startup
ORACLE instance started. Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL conn test/test;
Connected.
SQL select * from tde;
select * from tde *
ERROR at line 1:
ORA-28365: wallet is not open SQL alter system set encryption wallet open identified by hzmcdba123;
alter system set encryption wallet open identified by hzmcdba123
*
ERROR at line 1:
ORA-28367: wallet does not exist 注如果文件被盗如果想通过数据库查看数据已被加密的表是无法查询到数据的因为有钱夹的保护。 测试其随着表的变大加密表空间大小和加密时间所需长短
结论 1 . 当数据文件没有打开自动扩展对表中列进行加密空间不足时加密会失败。
2 如下表所示数据文件打开自动扩展 加密数据量 50 万行 8M 100 万行 16M 300 万行 49M 加密前数据文件大小 100 100 100 加密后数据文件大小 100 119.25 345.5 加密前表空间 FREE 大小 91 83 50 加密前表大小 8 16 49 加密后表空间 FREE 大小 44 6.25 16.5 加密后表大小 55 112 328 加密所用时间 1 分钟 1 分 45 秒 5 分 35 秒 SQL alter system set encryption wallet open identified by hzmcdba; System altered. SQL create tablespace dabiao datafile /oracle/app/orcl/dabiao.dbf size 100m; Tablespace created. SQL create table dabiao(id number,name varchar(10)) tablespace dabiao; Table created. SQL select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
DABIAO 100
SYSAUX 600
UNDOTBS1 200
BOOKS 5
USERS 5
TEST 5
SYSTEM 700 7 rows selected. SQL select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
DABIAO 99
SYSAUX 475.5
UNDOTBS1 189.5625
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 423.9375 7 rows selected. SQL SELECT segment_name AS TABLENAME,BYTES/1024/1024||M FROM user_segments WHERE segment_nameDABIAO; no rows selected SQL begin 2 for i in 1 .. 3000000 3 loop 4 insert into dabiao values ( i, ljw ); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
DABIAO 50
SYSAUX 475.5
UNDOTBS1 2
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 423.9375 7 rows selected. SQL SELECT segment_name AS TABLENAME,BYTES/1024/1024||M FROM user_segments WHERE segment_nameDABIAO; TABLENAME
--------------------------------------------------------------------------------
BYTES/1024/1024||M
-----------------------------------------
DABIAO
49M 为该表加密 SQL alter table dabiao modify(name encrypt);
alter table dabiao modify(name encrypt)
*
ERROR at line 1:
ORA-01653: unable to extend table TEST.DABIAO by 1024 in tablespace DABIAO SQL select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
DABIAO 3
SYSAUX 475.5
UNDOTBS1 1.625
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 423.9375 7 rows selected. SQL SELECT segment_name AS TABLENAME,BYTES/1024/1024||M FROM user_segments WHERE segment_nameDABIAO; TABLENAME
--------------------------------------------------------------------------------
BYTES/1024/1024||M
-----------------------------------------
DABIAO
96M SQL alter system set encryption wallet close identified by hzmcdba; System altered. SQL select * from dabiao where id 2999995; ID NAME
---------- ---------- 2999996 ljw 2999997 ljw 2999998 ljw 2999999 ljw 3000000 ljw 结论钱夹丢失后恢复即可重新打开钱包。重新创建一个一模一样的钱包是不可行的无法查询到之前已经加密过的数据 1. 备份钱包后删掉钱包进行测试
数据库没关掉之前加密的数据仍然是可查的 SQL select * from tde; 关库重启
SQL shutdown immediate;
SQL startup;
SQL select * from tde;
select * from tde *
ERROR at line 1:
ORA-28365: wallet is not open SQL alter system set encryption wallet open identified by hzmcdba123;
alter system set encryption wallet open identified by hzmcdba123
*
ERROR at line 1:
ORA-28367: wallet does not exist 把钱包恢复
[oracleljw db_1]$ cp ewallet.p12.bak ewallet.p12 SQL alter system set encryption wallet open identified by hzmcdba123; System altered. SQL select * from tde; ID DATA
---------- -------------------------------------------------- 34 JSS 35 TEST 0 SYS 5 SYSTEM 31 APPQOSSYS 9 OUTLN 14 DIP 30 DBSNMP 32 WMSYS 21 ORACLE_OCM 10 rows selected. 数据可查
2. 重新创建一个一模一样钱夹可行性测试 删除钱夹文件
[oracleljw db_1]$ rm ewallet.p12 SQL alter system set encryption wallet close identified by hzmcdba123; // 钱包可关闭 SQL alter system set encryption wallet open identified by hzmcdba123;
alter system set encryption wallet open identified by hzmcdba123
*
ERROR at line 1:
ORA-28367: wallet does not exist SQL alter system set encryption key identified by hzmcdba123;
alter system set encryption key identified by hzmcdba123
*
ERROR at line 1:
ORA-28362: master key not found 重启 SQL alter system set encryption key identified by hzmcdba123;
alter system set encryption key identified by hzmcdba123
*
ERROR at line 1:
ORA-28396: rekey of enc$ dictionary table failed 虽然报错但新的 wallet 文件还是生成了上面的 ORA-28362 意指数据库中还存在有使用老的 masterkey 加密的 encryption key 但这个老的 masterkey 没有包含在当前新建的 wallet 文件里 -rw-r--r-- 1 oracle oinstall 2845 Jun 25 07:11 ewallet.p12 SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/product/11.2.0/db_1
OPEN SQL select * from test.tde;
select * from test.tde *
ERROR at line 1:
ORA-28362: master key not found 重新建钱包不可行只能有之前的备份进行恢复 即使密码一样 场景九钱夹的重建测试
结论钱夹可以重建但是重建后的钱夹不能查询使用旧钱夹中的主密钥加密的数据即先前加密的数据丢失。 [oracleljw orcl]$ mv ewallet.p12 ewallet.p12.bak SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
CLOSED SQL alter system set encryption key identified by hzmcdba123;
alter system set encryption key identified by hzmcdba123
*
ERROR at line 1: 其实就是一条告警信息
ORA-28362: master key not found 新的 wallet 文件会生成。提示信息上面的 ORA-28362 意指数据库中还存在有使用老的 masterkey 加密的 encryption key 但这个老的 masterkey 没有包含在当前新建的 wallet 文件里这意味着用老的 masterkey 加密的 encryption key 无法被解密之前加密的数据不可访问即数据丢失。 新的钱夹生成
SQL conn test/test;
Connected. SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
OPEN SQL select * from tde; 之前加密的表tde
select * from tde *
ERROR at line 1:
ORA-28362: master key not found
之前加密的数据丢失 SQL create table new_tde (id number(10),data varchar2(50) encrypt); Table created. SQL insert into new_tde select user_id,username from dba_users; 10 rows created. SQL select * from new_tde; 10 rows selected. SQL alter system set encryption wallet close identified by hzmcdba123; System altered. SQL select * from tde;
select * from tde *
ERROR at line 1:
ORA-28365: wallet is not open SQL select * from new_tde;
select * from new_tde *
ERROR at line 1:
ORA-28365: wallet is not open 结论创建一个表加密列默认 salt 不能再该加密列上创建索引。如果需要创建索引必须指定为 no salt 。 SQL select * from v$encryption_wallet; OPEN SQL create table salt (id number,name varchar(10) encrypt);
SQL insert into salt values(1,dba); SQL create index salt_name_index on salt(name);
create index salt_name_index on salt(name) *
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt SQL alter table salt modify(name encrypt no salt); Table altered. SQL create index salt_name_index on salt(name); Index created. SQL alter table salt modify(name encrypt salt);
alter table salt modify(name encrypt salt) *
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt SQL drop index salt_name_index; Index dropped. SQL alter table salt modify(name encrypt salt); Table altered. 结论主外键列不能够被加密 SQL conn test/test;
Connected. SQL create table primarykey(id number,name varchar(10),constraint pkey primary key(name)); Table created. SQL insert into primarykey values(1,hzmcdba); 1 row created. SQL create table foreignkey(name varchar(10),score number,constraint fkey foreign key(name) references primarykey(name)); Table created. SQL insert into foreignkey values(hzmcdba,98); 1 row created. SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/product/11.2.0/db_1
CLOSED SQL alter system set encryption wallet open identified by hzmcdba; System altered. SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/product/11.2.0/db_1
OPEN 为主键加密 SQL alter table primarykey modify(name encrypt);
alter table primarykey modify(name encrypt) *
ERROR at line 1:
ORA-28335: referenced or referencing FK constraint column cannot be encrypted 为外键加密
SQL alter table foreignkey modify(name encrypt);
alter table foreignkey modify(name encrypt) *
ERROR at line 1:
ORA-28335: referenced or referencing FK constraint column cannot be encrypted 结论 blob 字段不能被加密 SQL alter table table_blob modify(ph encrypt);
alter table table_blob modify(ph encrypt)
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation 结论不能将加密列作为分区键这样会导致虽然表能成功创建但是创建出来的表不是分区表的情况。但是不作为分区键的列可以成为加密列。 创建加密表空间
SQL CREATE TABLESPACE encryptedtbs02 2 DATAFILE /oracle/app/oradata/orcl/encryptedtbs02.dbf SIZE 100M 3 ENCRYPTION USING AES256 4 DEFAULT STORAGE(ENCRYPT); Tablespace created. SQL CREATE TABLESPACE encryptedtbs03 2 DATAFILE /oracle/app/oradata/orcl/encryptedtbs03.dbf SIZE 100M 3 ENCRYPTION USING AES256 4 DEFAULT STORAGE(ENCRYPT); 创建加密表 CREATE TABLE test (
first_name VARCHAR2(128),
empID NUMBER ENCRYPT NOMAC NO SALT ,
salary NUMBER(6)
)
partition by hash(empID)
(
partition part_01 tablespace encryptedtbs02,
partition part_02 tablespace encryptedtbs03
);
Table created. 虽然表是创建成功但是有一个错误提示
ERROR at line 1:
ORA-28346: an encrypted column cannot serve as a partitioning column
ora - 28346: 一个加密列不能作为分区列 那么我们去查询表的加密信息及分区信息
SQL select table_name,column_name from DBA_ENCRYPTED_COLUMNS; TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE ID
TEST EMPID SQL select * from USER_TAB_PARTITIONS;
no rows selected 可以看到该表虽然创建成功但是并没有分区成功 作为对比我们创建另外一个分区列不是加密列的表
CREATE TABLE test01 (
first_name VARCHAR2(128),
empID NUMBER ENCRYPT NOMAC NO SALT ,
salary NUMBER(6)
)
partition by hash(first_name)
(
partition part_01 tablespace encryptedtbs02,
partition part_02 tablespace encryptedtbs03
);
Table created. 同样查询信息
SQL select table_name,column_name from DBA_ENCRYPTED_COLUMNS; TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE ID
TEST EMPID
TEST01 EMPID SQL select table_name,partition_name,tablespace_name from USER_TAB_PARTITIONS where table_nameTEST01;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST01 PART_01 ENCRYPTEDTBS02
TEST01 PART_02 ENCRYPTEDTBS03 结论如果一台服务器上有多个数据库创建一个钱夹即可使用于多个数据库。钱夹的配置只需在 sqlnet.ora 中指定钱夹存放位置然后 alter system set encryption wallet open identified by hzmcdba 创建钱夹此时只有记住这个密码这个钱夹 copy 到哪个数据库中都可以使用但是不建议这样做 oracle 建议还是一个钱夹对一个库具体测试看情节二。 情景一 [oracleljw ~]$ export ORACLE_SIDtest;
[oracleljw ~]$ echo $ORACLE_SID
test
[oracleljw ~]$ sqlplus / as sysdba; SQL show parameter db_name; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string test
SQL select * from v$encryption_wallet; CLOSED SQL show user;
USER is SYS
SQL alter system set encryption wallet open identified by hzmcdba; SQL select * from v$encryption_wallet; OPEN SQL create user test identified by test; User created. SQL grant dba to test; Grant succeeded. SQL conn test/test;
Connected.
SQL create table jiami(id number,name varchar(10) encrypt); Table created. SQL insert into jiami values(1,hzmcdba); 1 row created. SQL select * from jiami; ID NAME
---------- ---------- 1 hzmcdba SQL alter system set encryption wallet close identified by hzmcdba; System altered. SQL select * from jiami;
select * from jiami *
ERROR at line 1:
ORA-28365: wallet is not open 情景二 [oracleljw admin]$ vi sqlnet.ora ENCRYPTION_WALLET_LOCATION
(SOURCE(METHODFILE)(METHOD_DATA(DIRECTORY/oracle/app/wallet/$ORACLE_SID)))
test 库
SQL exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracleljw ~]$ sqlplus / as sysdba; SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 30 08:29:37 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL select * from v$encryption_wallet; CLOSED SQL alter system set encryption key identified by hzmcdba123;
alter system set encryption key identified by hzmcdba123
*
ERROR at line 1:
ORA-28362: master key not found 因为之前已经配置钱包照样生成 SQL alter system set encryption wallet close identified by hzmcdba123; System altered. SQL alter system set encryption wallet open identified by hzmcdba123; System altered. SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
OPEN 场景十五数据库文件和自动打开钱夹文件被拷到另一台数据库上打开测试
结论相关数据文件被拷贝到其他数据库进行打开如果自动打开钱夹也被拷过去能查询到被加密的数据。 [oracleljw orcl]$ orapki wallet create -wallet /oracle/app/wallet/orcl/ -auto_login_local
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup;
ORACLE instance started. Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened. SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
OPEN SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down. [oracleljw orcl]$ ls -rtl
total 8
-rw-r--r-- 1 oracle oinstall 2845 Jun 24 05:40 ewallet.p12
-rw------- 1 oracle oinstall 2923 Jul 4 01:15 cwallet.sso [oracleljw orcl]$ scp * oracle192.168.142.11:/oracle/app/orcl/
oracle192.168.142.11s password:
Permission denied, please try again.
oracle192.168.142.11s password:
books01.dbf 100% 5128KB 5.0MB/s 00:00
control01.ctl 100% 9840KB 9.6MB/s 00:00
control02.ctl 100% 9840KB 9.6MB/s 00:00
redo01.log 100% 50MB 50.0MB/s 00:01
redo02.log 100% 50MB 50.0MB/s 00:01
redo03.log 100% 50MB 50.0MB/s 00:00
sysaux01.dbf 100% 600MB 26.1MB/s 00:23
system01.dbf 100% 700MB 31.8MB/s 00:22
temp01.dbf 100% 20MB 20.0MB/s 00:00
test.dbf 100% 5128KB 5.0MB/s 00:00
test_tde 100% 5128KB 5.0MB/s 00:01
undotbs01.dbf 100% 1710MB 30.0MB/s 00:57
users01.dbf 100% 5128KB 5.0MB/s 00:00 [oracleljw dbs]$ scp * oracle192.168.142.11:/oracle/app/product/11.2.0/db_1/dbs/
oracle192.168.142.11s password:
25s4jm22_1_1 100% 307MB 43.9MB/s 00:07
27s4k331_1_1 100% 279MB 23.3MB/s 00:12
arch1_152_944363414.dbf 100% 50MB 49.9MB/s 00:01
c-1471212201-20170519-00 100% 9664KB 9.4MB/s 00:00
c-1471212201-20170519-05 100% 9664KB 9.4MB/s 00:00
c-1471212201-20170519-06 100% 9664KB 9.4MB/s 00:00
c-1471212201-20170617-00 100% 9664KB 9.4MB/s 00:01
c-1471212201-20170617-01 100% 9664KB 9.4MB/s 00:01
c-1471212201-20170617-02 100% 9664KB 9.4MB/s 00:00
c-1471212201-20170617-03 100% 9664KB 9.4MB/s 00:00
c-1471212201-20170617-04 100% 9664KB 9.4MB/s 00:00
c-1471212201-20170617-05 100% 9664KB 9.4MB/s 00:01
c-1471212201-20170627-00 100% 9728KB 9.5MB/s 00:00
c-1471212201-20170627-01 100% 9792KB 9.6MB/s 00:01
c-1471212201-20170627-02 100% 9792KB 9.6MB/s 00:00
c-1471212201-20170627-03 100% 9920KB 9.7MB/s 00:00
c-1471212201-20170630-00 100% 9920KB 4.8MB/s 00:02
c-1471212201-20170630-01 100% 9920KB 9.7MB/s 00:00
hc_orcl.dat 100% 1544 1.5KB/s 00:00
hc_test.dat 100% 1544 1.5KB/s 00:00
init.ora 100% 2851 2.8KB/s 00:00
initTEST.ora 100% 705 0.7KB/s 00:00
lkORCL 100% 24 0.0KB/s 00:00
lkTEST 100% 24 0.0KB/s 00:00
orapworcl 100% 1536 1.5KB/s 00:00
orapwtest 100% 1536 1.5KB/s 00:00
snapcf_orcl.f 100% 9840KB 9.6MB/s 00:00
spfileorcl.ora 100% 2560 2.5KB/s 00:00
spfiletest.ora 100% 2560 2.5KB/s 00:00 [oracleljw orcl]$ scp cwallet.sso oracle192.168.142.11:/oracle/app/wallet/orcl/
oracle192.168.142.11s password:
cwallet.sso 100% 2923 2.9KB/s 00:00 192.168.142.11 [oracleljw admin]$ vi sqlnet.ora ENCRYPTION_WALLET_LOCATION
(SOURCE(METHODFILE)(METHOD_DATA(DIRECTORY/oracle/app/wallet/orcl))) SQL startup
ORACLE instance started. Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL select * from v$encryption_wallet; WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/orcl
OPEN SQL select * from test.tde; ID DATA
---------- -------------------------------------------------- 34 JSS 35 TEST 0 SYS 5 SYSTEM 31 APPQOSSYS 9 OUTLN 14 DIP 30 DBSNMP 32 WMSYS 21 ORACLE_OCM 10 rows selected. 场景十六对一张现有的表进行加密是否影响其触发器测试
结论对一张已有表进行加密不会对其触发器产生影响 SQL create table clean (id number(10),data varchar2(50)); Table created. SQL insert into clean select user_id,username from dba_users; 10 rows created. SQL create table del_clean (id number(10),data varchar2(50)); Table created. SQL create or replace trigger tr_del_clean 2 before delete 3 on clean 4 for each row 5 begin 6 insert into del_clean(id,data) values(:old.id,:old.data); 7 end; 8 / Trigger created. SQL select * from clean; ID DATA
---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 0 SYS 5 SYSTEM 9 OUTLN 10 rows selected. SQL delete clean where id0; 1 row deleted. SQL select * from del_clean; ID DATA
---------- -------------------------------------------------- 0 SYS SQL select * from clean; ID DATA
---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 5 SYSTEM 9 OUTLN 9 rows selected. 对表clean 进行加密 SQL alter table clean modify(data encrypt); Table altered. SQL delete clean where id5; 1 row deleted. SQL select * from clean; ID DATA
---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 9 OUTLN 8 rows selected. SQL select * from del_clean; ID DATA
---------- -------------------------------------------------- 0 SYS 5 SYSTEM 1. 现有表空间是否能够直接加密 验证结果现有表空间 不能直接加密但可以建个加密表空间然后将该表空间中表move 到加密表空间中。 2. 钱夹密码忘记了怎么办是否有恢复方法 Oracle 没有提供解决钱夹密码丢失的方法。所以钱夹密码千万不能忘记这也是使用TDE 需要承担的风险。
