News

  1. Home
  2. All posts
All posts, Oracle, SQL by Team Ajara

How to verify TDE Keys at OS and Database level?

How to verify TDE Keys at OS and Database level ?

Verify Encryption is enabled or not

 

SQL> select tablespace_name,status,encrypted from dba_tablespaces;

TABLESPACE_NAME STATUS ENC
—————————— ——— —
SYSTEM        ONLINE NO
SYSAUX        ONLINE NO
UNDOTBS1  ONLINE NO
TEMP            ONLINE NO
USERS          ONLINE YES
APTBS           ONLINE YES
DATA             ONLINE YES
INDX             ONLINE YES

8 rows selected.

 

Key Verification in DB Level

Verify Keys for Every tablespace.

SQL> 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$tablesSQL> pace t, x$kcbtek x where t.ts#=x.ts#);SQL> SQL>NAME MASTERKEYID_BASE64
—————————————- ————————————————————
SYSTEM                Nr5PXOpuEU8sv6ixoqzKkvM=
TEMP                     Nr5PXOpuEU8sv6ixoqzKkvM=
SYSAUX                JPAAAAAAAAAAAAAAAAAAAAA=
UNDOTBS1          JPAAAAAAAAAAAAAAAAAAAAA=
USERS                   Nr5PXOpuEU8sv6ixoqzKkvM=
APTBS                   Nr5PXOpuEU8sv6ixoqzKkvM=
DATA                     Nr5PXOpuEU8sv6ixoqzKkvM=
INDX                     Nr5PXOpuEU8sv6ixoqzKkvM=

8 rows selected.

 

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
————————————————————
Nr5PXOpuEU8sv6ixoqzKkvM=

SQL> select key_id from v$encryption_keys;

KEY_ID
——————————————————————————
Nr5PXOpuEU8sv6ixoqzKkvMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

 

SQL> select WRL_PARAMETER from v$encryption_wallet;

WRL_PARAMETER
——————————————————————————–
/u01/app/oracle/wallet/ORCLT/tde/

Key Verification in OS Level

Verify Key value at OS Level by using orapki command

 

$ orapki wallet display -wallet /u01/app/oracle/wallet/ORCLT/tde
Oracle PKI Tool Release 23.0.0.0.0 – Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.Requested Certificates:
Subject: CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.Nr5PXOpuEU8sv6ixoqzKkvMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.Nr5PXOpuEU8sv6ixoqzKkvMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:
$

Key values can be verified in the output, the highlighted  Keys in the output  is same , so no issues in the TDE setup.

See also