Protect data in tablespaces with transparent data encryption (TDE) and dynamically mask sensitive data returned by database queries with data redaction.
Oracle Advanced Security combines these powerful data protection capabilities into a single, easy-to-configure solution.
Oracle Advanced Security licensing operates as follows:
---------------------------------------------------------------
1) It is an add-on license to Oracle Database Enterprise Edition.
Includes features like encryption, secure network communications, and data redaction.
2) Transparent Data Encryption (TDE) is a crucial feature, requiring an Oracle Advanced Security license.
3) Licensing must match the Oracle Database Enterprise Edition in quantity and metric (Processor or Named User Plus).
Oracle Advanced Security Licensing Options
-------------------------------
1) Add-on Requirement: Only available as an add-on to Oracle Database Enterprise Edition.
2) Licensing Necessity: Both Enterprise Edition and Advanced Security must be licensed.
3) Metric Usage: Available per Named User Plus and Processor metric.
4) Matching Quantity and Metric: License quantity and metric for Advanced Security must match the database license.
Transparent Data Encryption (TDE)
-----------------------------------
Feature of Advanced Security: Provides encryption at column, table, or tablespace level.
Functionality: Encrypts data when written to disk and decrypts when read, making it transparent to applications.
Licensing for TDE: Requires an Oracle Advanced Security license; not available standalone.
TDE is a key feature of Oracle Advanced Security, offering enhanced encryption for sensitive data in Oracle Databases.
To Check License
----------------
V$OPTION
show parameter control_management_pack_access;
DBA_REGISTRY displays information about the components loaded into the database
V$OPTION lists options that are installed with the Oracle server.
How to Check if Advanced Security Is Enabled
-----------------------------------------
To check if Advanced Security is enabled in an Oracle database, an Oracle DBA should query the V$OPTION view.
This view contains information about the various database features and options and their status.
By querying this view, the DBA can look specifically for entries related to Oracle Advanced Security features
like ‘Oracle Advanced Security,’ ‘Transparent Data Encryption’, or similar terms.
The query result will indicate whether these features are enabled or not.
Table Masking
---------------
You can redact column data by using one of the following methods:
Full redaction. You redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type
of the column.
For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a single space.
Partial redaction. You redact a portion of the column data. For example,
you can redact a Social Security number with asterisks (*), except for the last 4 digits.
Regular expressions. You can use regular expressions to look for patterns of data to redact.
For example, you can use regular expressions to redact email addresses, which can have varying character lengths.
It is designed for use with character data only.
Random redaction. The redacted data presented to the querying application user appears as randomly generated values each time it is displayed,
depending on the data type of the column.
No redaction. The None redaction type option enables you to test the internal operation of your redaction policies,
with no effect on the results of queries against tables with policies defined on them.
You can use this option to test the redaction policy definitions before applying them to a production environment.
Prerequisite
----------
New Separate file system /wallet -size 20 GB
select owner,segment_name,tablespace_name from dba_segments where segment_name ='TEST_PAY';
Implementation
----------------
TDE
----
1) Take a cold backup of the database to enable db restore if needed
2) Oracle encryption wallet creation
3) CREATE A NEW TABLESPACE WITH ENCRYPTION
4) Move tables into encrypted tablespace
5) Rebuild the indexes of the table
Column Masking
-----------
1) Policy Creation
2) assigning policy to Table and Column
2)
mkdir -p /wallet/TRAINING/wallet/tde
SQL> alter system set wallet_root='/wallet/TEST_DB/wallet' scope=spfile;
SQL> show parameter wallet_root
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
wallet_root string
/wallet/TESTUAT/wallet
SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;
System altered.
SQL> show parameter tde_configuration
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
tde_configuration string
KEYSTORE_CONFIGURATION=FILE
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/wallet/TESTUAT/wallet/tde' identified by TEST_12345;
keystore altered.
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/wallet/TESTUAT/wallet/tde' identified by TEST_12345;
keystore altered.
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
CON_ID
----------
FILE
/wallet/TESTUAT/wallet/tde/
NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED
0
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE identified by TEST_12345;
keystore altered.
SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE identified by TEST_12345 WITH BACKUP;
keystore altered.
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
CON_ID
----------
FILE
/wallet/TESTUAT/wallet/tde/
OPEN PASSWORD SINGLE NONE NO
0
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by TEST_12345;
keystore altered.
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
CON_ID
----------
FILE
/wallet/TESTUAT/wallet/tde/
OPEN AUTOLOGIN SINGLE NONE NO
0
SQL> DESC V$ENCRYPTION_WALLET;
Name Null? Type
----------------------------------------- -------- ----------------------------
WRL_TYPE VARCHAR2(20)
WRL_PARAMETER VARCHAR2(4000)
STATUS VARCHAR2(30)
WALLET_TYPE VARCHAR2(20)
WALLET_ORDER VARCHAR2(9)
KEYSTORE_MODE VARCHAR2(8)
FULLY_BACKED_UP VARCHAR2(9)
CON_ID NUMBER
SQL> SELECT CON_ID,WALLET_TYPE,WRL_PARAMETER,STATUS FROM V$ENCRYPTION_WALLET;
CON_ID WALLET_TYPE
---------- --------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------------------
0 AUTOLOGIN
/wallet/TESTUAT/wallet/tde/
OPEN
SQL> COL WRL_PARAMETER FOR A30
SQL> SET LINE 300 PAGES 300
SQL>
SQL>
SQL> /
CON_ID WALLET_TYPE WRL_PARAMETER STATUS
---------- -------------------- ------------------------------ ------------------------------
0 AUTOLOGIN /wallet/TESTUAT/wallet/tde/ OPEN
SQL>
set linesize 250 pagesize 250
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 con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;
select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);
3) CREATE A NEW TABLESPACE WITH ENCRYPTION
create tablespace TESTTABLESPACE_TDE datafile '/gbmpen/datafile/TESTTABLESPACE_TDE_01.dbf' size 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;
4) Move tables into encrypted tablespace
alter table ECECUSER.TEST_PAY move tablespace TESTTABLESPACE_TDE;
select count(*) from dba_objects where status='INVALID';
select status from dba_indexes where status='UNUSABLE';
8) Rebuild the indexes of the table
alter index ECECUSER.IX_ENTITYDOC_CORE_NEW rebuild tablespace TESTTABLESPACE_TDE;
alter index ECECUSER.IX_ENTITYDOC_DOCCODE_NEW rebuild tablespace TESTTABLESPACE_TDE;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Table Name- ECECUSER.TEST_PAY
Table Name : TEST_PAY
Owner : ECECUSER
Policy Name : REDACT_TEST_PAY
REDACT OR MASKING TYPE : PARTIAL
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'ECECUSER',
object_name => 'TEST_PAY',
column_name => 'REFERENCENUMBER',
policy_name => 'REDACT_TEST_PAY',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,X,1,16',
expression => '1=1');
END;
/
SET LINESIZE 250
SET PAGESIZE 150
COLUMN OWNER FORMAT A20
COLUMN INDEX_NAME FORMAT A30
COLUMN COLUMN_NAME FORMAT A20
COLUMN TABLE_NAME FORMAT A25
COLUMN COLUMN_EXPRESSION FORMAT A35
SELECT ix.owner, ic.index_name, ix. index_type, ic.table_name, ic.column_name, ic.column_position col_pos,ix.uniqueness, e.column_expression
FROM dba_indexes ix, dba_ind_columns ic, dba_ind_expressions e
WHERE ic.index_name = ix.index_name AND ix.index_name=e.index_name
AND ic.table_name='TEST_PAY'
order by 1,2,3,4,6,5;
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'ECECUSER',
object_name => 'TEST_PAY',
column_name => 'LICENSENO',
policy_name => 'REDACT_ACCOUNTS_POL',
ACTION => DBMS_REDACT.add_column,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,X,1,16',
expression => '1=1');
END;
/
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'TESTUSER',
object_name => 'TEST_MAST',
column_name => 'CARD_NUMBER',
policy_name => 'REDACT_TEST_MAST_POL',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'TESTUSER',
object_name => 'ATMREQ_MASTER',
column_name => 'CARD_NUMBER',
policy_name => 'REDACT_ATMREQ_MASTER_POL',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,X,1,16',
expression => '1=1');
END;
/
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'MIGADM',
object_name => 'ACCOUNTS',
column_name => 'UNIQUEIDNUMBER',
policy_name => 'REDACT_MIGADM_ACCOUNTS_POL',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,X,1,12',
expression => '1=1');
END;
/
set linesize 150
set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('INDEX','IDX_PHONEEMAIL_PHONENO','CRMUSER') from dual;
select a.name,b.ENCRYPTIONALG,b.ENCRYPTEDTS,b.STATUS from V$ENCRYPTED_TABLESPACES b, v$tablespace a where a.ts#=b.ts#(+) and a.name='CRM_MASTER1';
select OBJECT_OWNER,OBJECT_NAME,COLUMN_NAME,FUNCTION_PARAMETERS,FUNCTION_TYPE from redaction_columns where OBJECT_NAME='ENTITYDOCUMENT';
select OBJECT_OWNER,OBJECT_NAME,COLUMN_NAME,FUNCTION_PARAMETERS,FUNCTION_TYPE from redaction_columns where OBJECT_NAME='PHONEEMAIL';
col OBJECT_OWNER FOR A20
COL OBJECT_NAME FOR A20
COL COLUMN_NAME FOR A,FUNCTION_TYPE,FUNCTION_PARAMETERS,
select OBJECT_OWNER,OBJECT_NAME,COLUMN_NAME,FUNCTION_TYPE,FUNCTION_PARAMETERS,COLUMN_DESCRIPTION from redaction_columns;
select OBJECT_OWNER,OBJECT_NAME,COLUMN_NAME,FUNCTION_PARAMETERS,FUNCTION_TYPE from redaction_columns where OBJECT_NAME in ('ATMREQ_REQUEST','ATMREQ_MASTER','ACCOUNTS','PHONEEMAIL','ENTITYDOCUMENT');
select * from redaction_policies;
EXEC DBMS_REDACT.add_policy (object_schema => 'CMMSUPERUSER',object_name=>'DCMCRD1',
policy_name=> 'CMMSUPERUSER.DCMCRD1.REDACTION', column_name=> 'CARD_NUMBER', function_type => DBMS_REDACT.full, expression => '1=1');
=====================================
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES/1024/1024
---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------
TESTUSER C_TEST TESTUSER 24153.5547
12 rows selected.
SQL> select OWNER,INDEX_NAME from dba_indexes where TABLE_NAME='C_TEST' and OWNER='TESTUSER';
OWNER INDEX_NAME
-------------------- ----------------------------------------
TESTUSER C_IDX_TEST2
TESTUSER IDX1_CTEST
TESTUSER C_TEST_N3
TESTUSER C_IDX_TEST1
TESTUSER C_TEST_N2
TESTUSER C_IDX_TEST3
6 rows selected.
SQL>
SQL>
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='C_IDX_TEST2';
SUM(BYTES/1024/1024/1024)
-------------------------
8.02266693
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='IDX1_CTEST';
SUM(BYTES/1024/1024/1024)
-------------------------
5.59686279
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='C_TEST_N3';
SUM(BYTES/1024/1024/1024)
-------------------------
28.064476
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='C_IDX_TEST1';
SUM(BYTES/1024/1024/1024)
-------------------------
11.5082321
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='C_TEST_N2';
SUM(BYTES/1024/1024/1024)
-------------------------
9.90743256
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='C_IDX_TEST3';
SUM(BYTES/1024/1024/1024)
-------------------------
7.47758484
SQL>
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES/1024/1024
---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------
TESTUSER C_TEST TESTUSER 24153.5547
12 rows selected.
SQL> select OWNER,INDEX_NAME from dba_indexes where TABLE_NAME='C_TEST' and OWNER='TESTUSER';
OWNER INDEX_NAME
-------------------- ----------------------------------------
TESTUSER C_IDX_TEST2
TESTUSER IDX1_CTEST
TESTUSER C_TEST_N3
TESTUSER C_IDX_TEST1
TESTUSER C_TEST_N2
TESTUSER C_IDX_TEST3
ENCRYPTION
--------------
OWNER = TESTUSER
TABLENAME = C_TEST --24gb
TABLESPACE = TESTUSER
total 6 indexes
1) CREATE A NEW TABLESPACE WITH ENCRYPTION
create tablespace TESTUSER_TDE datafile '+TESTDATA_DG2' size 32G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces where tablespace_name like 'TESTUSER%';
2) Move tables into encrypted tablespace
alter table TESTUSER.C_TEST move tablespace TESTUSER_TDE;
select count(*) from dba_objects where owner='TESTUSER' And status='INVALID' and owner='TESTUSER';
select owner,status from dba_indexes where status='UNUSABLE' and owner='TESTUSER';
select index_name,owner, table_owner,table_name where status='UNUSABLE' and owner='TESTUSER';
3) Rebuild the indexes of the table
alter index TESTUSER.C_IDX_TEST2 rebuild tablespace TESTUSER_TDE PARALLEL 50;
alter index TESTUSER.IDX1_CTEST rebuild tablespace TESTUSER_TDE PARALLEL 50;
alter index TESTUSER.C_TEST_N3 rebuild tablespace TESTUSER_TDE PARALLEL 50;
alter index TESTUSER.C_IDX_TEST1 rebuild tablespace TESTUSER_TDE PARALLEL 50;
alter index TESTUSER.C_TEST_N2 rebuild tablespace TESTUSER_TDE PARALLEL 50;
alter index TESTUSER.C_IDX_TEST3 rebuild tablespace TESTUSER_TDE PARALLEL 50;
alter index TESTUSER.C_IDX_TEST2 NOPARALLEL;
alter index TESTUSER.IDX1_CTEST NOPARALLEL;
alter index TESTUSER.C_TEST_N3 NOPARALLEL;
alter index TESTUSER.C_IDX_TEST1 NOPARALLEL ;
alter index TESTUSER.C_TEST_N2 NOPARALLEL ;
alter index TESTUSER.C_IDX_TEST3 NOPARALLEL ;
select index_name,owner,TABLESPACE_NAME,LOGGING,DEGREE from dba_indexes where owner=' TESTUSER'
and index_name in ('IDX1_CTEST','C_TEST_N3','C_IDX_TEST1','C_TEST_N2','C_IDX_TEST3');
select count(*) from dba_objects where owner='TESTUSER' And status='INVALID' and owner='TESTUSER';
select owner,status from dba_indexes where status='UNUSABLE' and owner='TESTUSER';
select index_name,owner, table_owner,table_name,status where status='UNUSABLE and owner='TESTUSER';
ALTER INDEX LITTLEWOODS.CO_CN_IX REBUILD ONLINE tablespace TSPACEI02A PARALLEL 50 NOLOGGING;
----------------------
p
prerequisit
----------
New File system /wallet -10Gb (DC And DR)
Take complete backup of database
and create guarteen restore point.
Implementation
------------------
Oracle encryption wallet creation step on Production(DC)
----------------------------------------------
A) Disable DC-DR sync
1) mkdir -p /wallet/test/wallet/tde (DC And DR )
2) alter system set wallet_root='/wallet/test/wallet/tde' scope=spfile;
show parameter wallet_root
SQL> shut immediate
SQL> startup
3) alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;
show parameter tde_configuration
--------
4) ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/wallet/test/wallet/tde' identified by xxxxx345;
5) ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/wallet/test/wallet/tde' identified by XXXX345;
ls -ltr /wallet/test/wallet/tde
5) ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE identified by xxxx345;
6) ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE identified by xxxx345 WITH BACKUP;
SELECT * FROM V$ENCRYPTION_WALLET;
7) bounce the database for verification
Oracle encryption wallet creation steps on Standby
--------------------------------------------
1) mkdir -p /wallet/test/wallet/tde and cancel mrp process
2) alter system set wallet_root='/wallet/test/wallet/tde' scope=spfile;
show parameter wallet_root
SQL> shut immediate (Standby database)
SQL> startup
3) alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;
show parameter tde_configuration
4) copy two wallet files created on below location on production to DR
location =/wallet/test/wallet/tde
5) Enable DC-DR sync
6) verify now
SELECT * FROM V$ENCRYPTION_WALLET;
and bounce the DR argain
======TDE==================================
create tablespace test_TDE datafile '/DATA/test/test_TDE01.DBF' size 4G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
SELECT TABLESPACE_NAME,ENCRYPTED FROM DBA_TABLESPACES WHERE TABLESPACE_NAME LIKE '%test%';
select owner,index_name,table_name from dba_indexes where table_name='IP_TESTT';
ALTER TABLE TESTUSER.IP_TESTT MOVE TABLESPACE test_TDE;
