Oracle11g-19C系列版本TDE-深度揭秘

Oracle数据库可以加密的对象
1、tablespaces
2、tables
3、redo logs and archive logs
4、temporary data in temporary tablespaces (when the temporary data comes from encrypted sources)

加密密钥存储的位置
1. tablespaces: datafile headers
2. tables: dictionary table enc$
3. redo logs and archive logs: redo log/archive log file header
4. temporary tablespace: system datafile header.

The master encryption key is identified using a master key ID. The master key ID can be determined using the data dictionary tables:
enc$ - for tables
x$kcbtek - for tablespaces
x$kcbdbk - the controlfile holds a copy of the master key ID as well.

检查 Oracle数据库 之前是否实施配置过TDE：
11g
SELECT BITAND(FLAGS,8) FROM X$KCBDBK;
如果此查询返回 8，则 TDE 已/已实施。（TDE REKEY/SET KEY 已为此数据库完成，SYSTEM 表空间的 MKID 是该前一个/当前钱包文件的 MKEYID）
如果此查询返回 0，则还未 TDE（从未为此数据库执行过 TDE REKEY/SET KEY，并且 SYSTEM 表空间的 MKID 是预先生成的 ID）
12c-19c
select CON_ID,mkloc from x$kcbdbk;
如果此查询返回 1，则 TDE 已/已实施。（TDE REKEY/SET KEY 已为此数据库完成，SYSTEM 表空间的 MKID 是该前一个/当前钱包文件的 MKEYID）
如果此查询返回 0，则还未实施 TDE。（从未为此数据库执行过 TDE REKEY/SET KEY 并且 SYSTEM 表空间的 MKID 是预先生成的 ID）

######Oracle11g版本######

查看主密钥

1. tables:
select mkeyid from enc$;
###查看各个表空间的加密密钥值
2、tablespaces and temporary encryption (master key ID for temporary tablespaces can be seen in the system tablespace data):
set linesize 150
column name format a40
column masterkeyid_base64 format a60
select name,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 t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);

select a.ts#,c.name,a.encryptedkey,a.mkid from x$kcbtek a,v$tablespace c where a.ts#=c.ts#;
3. control file:
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);

4. master key id as it is within the wallet:

###通过密钥库的wallet文件来查看主密钥

4. master key id as it is within the wallet:

cd $ORACLE_BASE/admin/$ORACLE_SID/wallet

mkstore -wrl -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

or

orapki wallet display -wallet .

################ Oracle 12C-19C版本##############

查看主密钥

1. tables:
select mkeyid from enc$;
###查看各个表空间的加密密钥值
2、tablespaces and temporary encryption (master key ID for temporary tablespaces can be seen in the system tablespace data):

set linesize 150
column name format a40
column masterkeyid_base64 format a60
select con_id,name,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 t.con_id,t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts# and t.con_id=x.con_id);

####查看pdb表空间的加密主密钥
set linesize 999
column name format a30
column masterkeyid_base64 format a30
select con_name , ts#, name as ts_name,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 p.name as con_name, t.ts# , t.name, RAWTOHEX(x.mkid) mkeyid
from v$tablespace t, x$kcbtek x, v$pdbs p
where t.ts#=x.ts# and p.con_id=t.con_id and x.con_id=p.con_id)
order by con_name, ts#;

###查看cdb和pdb的主密钥
set linesize 999
col con_id for a16
col masterkeyid_base64 for a64
select con_id,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 con_id,RAWTOHEX(mkid) mkeyid from x$kcbdbk);

or
select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;

###查看PDB主密钥
select pdb.name, e.key_id, to_char(e.creation_time,'yyyy-mm-dd hh24:mi:ss') created
from v$encryption_keys e, v$pdbs pdb
where pdb.con_id=e.con_id order by pdb.name desc, created;

###通过密钥库的wallet文件来查看主密钥

4. master key id as it is within the wallet:

cd $ORACLE_BASE/admin/$ORACLE_SID/wallet

mkstore -wrl -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

or

orapki wallet display -wallet .

--查看Oracle钱夹状态
select * from v$encryption_wallet;

--RAC集群环境查看
select * from gv$encryption_wallet;

--查看钱夹状态
col WRL_TYPE format a10
col WRL_PARAMETER format a40
select con_id, wallet_type, status from v$encryption_wallet;

--查看表空间加密情况：
SELECT tablespace_name, encrypted FROM dba_tablespaces;

--查看加密表空间的加密算法
SELECT NAME, ENCRYPTIONALG ENCRYPTEDTS
FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE
WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;