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.
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.
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.
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>
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: 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
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>
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'
No comments:
Post a Comment