Transparent Data Encryption (TDE) & LogMiner

1- Change in sqlnet.ora
######################SQLNET.ORA###############################

NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10
sqlnet.authentication_services= (NTS)

################################ for TDE########################
ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
   (DIRECTORY=C:\oracle\PROD\db\tech_st\11.1.0\network\admin)))
################################################################
IFILE=C:\oracle\PROD\db\tech_st\11.1.0\network\admin\PROD_p-hq-sv-or-01\sqlnet_ifile.ora


2-  Set the key
SQL> conn / as sysdba
Connected.
SQL> alter system set key identified by "welcome1";  -- Wallet will be opened automatically
System altered.
SQL>
alter system set wallet open identified by "welcome1";  -- if instance restarted

3-  Create three users for testing purpose, here users will be crated automatically.

SQL> grant connect to JKING identified by welcome1;
Grant succeeded.
SQL> grant connect, DBA to LSMITH identified by welcome1;
Grant succeeded.
SQL> grant connect to LDORAN identified by welcome1;
Grant succeeded.

4- Create related objects for testing

SQL> conn nfs/nfs@ocm1
Connected.
SQL> create table cust_payment_info
  2    (first_name varchar2(11),
  3    last_name varchar2(10),
  4    order_number number(5),
  5    credit_card_number varchar2(16) ENCRYPT NO SALT, --no salt if you want index on this column
  6    active_card varchar2(3));
Table created.

  insert into cust_payment_info values
  ('Jon', 'Oldfield', 10001, '5446959708812985','YES');
insert into cust_payment_info values
  ('Chris', 'White', 10002, '5122358046082560','YES');
insert into cust_payment_info values
  ('Alan', 'Squire', 10003, '5595968943757920','YES');
insert into cust_payment_info values
  ('Mike', 'Anderson', 10004, '4929889576357400','YES');
insert into cust_payment_info values
  ('Annie', 'Schmidt', 10005, '4556988708236902','YES');
insert into cust_payment_info values
  ('Elliott', 'Meyer', 10006, '374366599711820','YES');
insert into cust_payment_info values
  ('Celine', 'Smith', 10007, '4716898533036','YES');
insert into cust_payment_info values
  ('Steve', 'Haslam', 10008, '340975900376858','YES');
insert into cust_payment_info values
  ('Albert', 'Einstein', 10009, '310654305412389','YES');


SQL>   create index cust_payment_info_idx on cust_payment_info (credit_card_number);
Index created.
4- Grant the appropriate rights to concerned users.
SQL> grant select on nfs.CUST_PAYMENT_INFO to LDORAN;
Grant succeeded.
SQL> grant select, update on nfs.CUST_PAYMENT_INFO to LSMITH;
Grant succeeded.
SQL> grant select on nfs.CUST_PAYMENT_INFO to JKING;
Grant succeeded.

5-
conn LSMITH/welcome1;
update oe.CUST_PAYMENT_INFO set ACTIVE_CARD='NO'
   where CREDIT_CARD_NUMBER='4556988708236902';
  
   select * from table (dbms_xplan.display_cursor);  -- see the execution plan for the previous query

6- SQL> conn nfs/nfs
Connected.
SQL> select * from user_encrypted_columns;
TABLE_NAME                     COLUMN_NAME                    ENCRYPTION_ALG                SAL
------------------------------ ------------------------------ ----------------------------- ---
CUST_PAYMENT_INFO              CREDIT_CARD_NUMBER             AES 192 bits key              NO
SQL>

7- Adding and Applying a VPD Policy
Create the HR sample schema if not existing already eg;
SQL> @C:\oracle\PROD\db\tech_st\11.1.0\demo\schema\human_resources\hr_cre

connect system/manager
create or replace function f_policy_nfs_cust_payment_info
-- Function must have the following parameters
   (schema in varchar2, tab in varchar2)
-- Function will return a string that is used as a WHERE clause                    
 return varchar2
as
 v_manager_id     number:=0;
 is_employee      number:=0;
 v_user           varchar2(20);
 out_string       varchar2(70) default '1=2 ';
begin
  -- get session user
  v_user := lower(sys_context('userenv','session_user'));
  -- Is the user an employee?
  begin
    select manager_id into v_manager_id
    from hr.employees
    where lower(email) = v_user;
    is_employee:=1;
  exception
   when no_data_found then
    is_employee:=2;
  end;
  -- create where clause when user is authorized to see parts of the table
  if     is_employee=1 and lower(v_user)='jking' and v_manager_id=146 then
  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''34%''
    or CREDIT_CARD_NUMBER like ''37%''';
  elsif  is_employee=1 and lower(v_user)='lsmith' and v_manager_id=146 then
  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''4%''';
  elsif  is_employee=1 and lower(v_user)='ldoran' and v_manager_id=146 then
  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''5%''';
  end if;
 return out_string;
end;

8- Add policy to 'nfs.cust_payment_info' table:
SQL> begin
  2  dbms_rls.add_policy('nfs','cust_payment_info','ac_cust_payment_info',
  3    'system','f_policy_nfs_cust_payment_info', policy_type => dbms_rls.context_sensitive);
  4  end;
  5  /
PL/SQL procedure successfully completed.

9- Testing the Policy
SQL> conn JKING/welcome1;
Connected.
SQL> select * from nfs.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
FIRST_NAME  LAST_NAME  ORDER_NUMBER CREDIT_CARD_NUMB ACT
----------- ---------- ------------ ---------------- ---
Steve       Haslam            10008 340975900376858  YES
Elliott     Meyer             10006 374366599711820  YES
2 rows selected.
SQL> conn LDORAN/welcome1;
Connected.
SQL> select * from nfs.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
FIRST_NAME  LAST_NAME  ORDER_NUMBER CREDIT_CARD_NUMB ACT
----------- ---------- ------------ ---------------- ---
Chris       White             10002 5122358046082560 YES
Jon         Oldfield          10001 5446959708812985 YES
Alan        Squire            10003 5595968943757920 YES
3 rows selected.
SQL> conn LSMITH/welcome1;
Connected.
SQL> select * from nfs.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
FIRST_NAME  LAST_NAME  ORDER_NUMBER CREDIT_CARD_NUMB ACT
----------- ---------- ------------ ---------------- ---
Annie       Schmidt           10005 4556988708236902 NO
Celine      Smith             10007 4716898533036    YES
Mike        Anderson          10004 4929889576357400 YES
3 rows selected.

Using LogMiner to View Redo Logs
1- connect / as sysdba;
2- View these two views for logfile info:  V$LOGFILE   V$ARCHIVED_LOG
3- SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;  --- check option is yes for logmining

SQL> show user
USER is "SYS"
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
4-  alter database add supplemental log data;  --sothat db can store additional data for logmining

SQL> alter database add supplemental log data;
Database altered.

5- Start mining
BEGIN
    DBMS_LOGMNR.ADD_LOGFILE ('C:\oracle\PROD\OCM\oradata\OCM1\ONLINELOG\O1_MF_1_79LVMBMZ_.LOG', DBMS_LOGMNR.NEW);     
    DBMS_LOGMNR.ADD_LOGFILE ('C:\oracle\PROD\OCM\oradata\OCM1\ONLINELOG\O1_MF_2_79LV7V7S_.LOG', DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.ADD_LOGFILE ('C:\oracle\PROD\OCM\oradata\OCM1\ONLINELOG\O1_MF_3_79LTQN03_.LOG', DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.START_LOGMNR ( options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
END;

SQL> show user
USER is "SYS"
SQL> BEGIN
  2      DBMS_LOGMNR.ADD_LOGFILE ('C:\oracle\PROD\OCM\oradata\OCM1\ONLINELOG\O1_MF_1_79LVMBMZ_.LOG', DBMS_LOGMNR.NEW);

  3      DBMS_LOGMNR.ADD_LOGFILE ('C:\oracle\PROD\OCM\oradata\OCM1\ONLINELOG\O1_MF_2_79LV7V7S_.LOG', DBMS_LOGMNR.ADDFILE
);
  4      DBMS_LOGMNR.ADD_LOGFILE ('C:\oracle\PROD\OCM\oradata\OCM1\ONLINELOG\O1_MF_3_79LTQN03_.LOG', DBMS_LOGMNR.ADDFILE
);
  5      DBMS_LOGMNR.START_LOGMNR ( options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>

6- ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
7- TABLE_NAME = 'CUST_PAYMENT_INFO'  -- to be mined from logs
8- use the database control file to find and add redo log files that satisfy your specified time or SCN range to the LogMiner redo log file list.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> begin
  2
  3      DBMS_LOGMNR.START_LOGMNR(STARTTIME => '18-Oct-2011 13:00:00',ENDTIME => '18-Oct-2011 14:00:00',
  4      OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 3

See how to enable archiving:
 SQL> show parameter log_archive_start
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            C:\oracle\PROD\db\tech_st\11.1.0\RDBMS
Oldest online log sequence     11
Current log sequence           13
SQL>
 After enabling run the process above again
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\PROD\db\tech_st\11.1.0\RDBMS
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
SQL>
SQL> alter system archive log current;

System altered.

SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving

SQL> alter system archive log start;
System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\PROD\db\tech_st\11.1.0\RDBMS
Oldest online log sequence     12
Next log sequence to archive   14
Current log sequence           14

 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> begin
  2
  3      DBMS_LOGMNR.START_LOGMNR(STARTTIME => '18-Oct-2011 13:00:00',ENDTIME => '18-Oct-2011 14:00:00',
  4      OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL>

9-Select Data and observe
SQL> select sql_redo from v$logmnr_contents where table_name = 'CUST_PAYMENT_INFO' and op

SQL_REDO
--------------------------------------------------------------------------------
insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Jon','Oldfield','10001',HEXTORAW('529
6a29ab8d256fa0f0f491b973bb54310b51dd3be0552aaa0c8797af4af53192e3e61f48b2ad69deef
a8244f22aff88bc84b8da'),'YES');

insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Chris','White','10002',HEXTORAW('dce2
3cddd3278aa19f8eabc6148b67a774fee3a3e6ecc59b22354ebd9bbaa65a7ce8dd4d68dd74f386f4
6bac7e423254620e473d'),'YES');

insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C

SQL_REDO
--------------------------------------------------------------------------------
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Alan','Squire','10003',HEXTORAW('f04a
f0835f141f975101995785c7d5e2559c917f3f82d9f4a63c2b64f039f3eaf04257bc6c0f7a9eba8e
eb46dcb6eccc43f7d528'),'YES');

insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Mike','Anderson','10004',HEXTORAW('4b
09d2b6a8df2d6d76cb52fa46059c4d45c908fede8f46b6afe21edc7a5afd32c1afbbb28aed0e1a37
3988d96d504dbcaea984a4'),'YES');

insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Annie','Schmidt','10005',HEXTORAW('bb

SQL_REDO
--------------------------------------------------------------------------------
b63e352618b060201c7fa898d1a1af0cf22d627f0eee87926a9a1ce67a096d35d17cd2b95c821afa
d8f1189240878dde35d3b5'),'YES');

insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Elliott','Meyer','10006',HEXTORAW('9c
8f8328a4b1e54e5897ba0738224a593da1f071082ec7fd852cc18e7d9b441763b8d5e5'),'YES');

insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Celine','Smith','10007',HEXTORAW('4a7
33dd0e9f4e84b22520f4dae199020760505f84284c9e200493b3ffa02f25bc1972f5d'),'YES');


SQL_REDO
--------------------------------------------------------------------------------
insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Steve','Haslam','10008',HEXTORAW('017
c2894cf98cd4dc2fff6d396eefc29fdd09ee3d5598429e3244211611e92c66c2fb605'),'YES');

insert into "NFS"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","C
REDIT_CARD_NUMBER","ACTIVE_CARD") values ('Albert','Einstein','10009',HEXTORAW('
177caa10580bec53eeed7766220271a6ba423b333e97209cc77420beb6fd33cf98e9ea5e'),'YES'
);
9 rows selected.
SQL>

10- Check with logminer contents also
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
   WHERE
   SEG_NAME = 'CUST_PAYMENT_INFO' AND
   SEG_OWNER = 'NFS' AND
   OPERATION = 'UPDATE'