SELECT SYS_CONTEXT ('userenv', 'SESSION_USER') FROM DUAL;
CREATE TABLE payment_details (
id NUMBER NOT NULL,
customer_id NUMBER NOT NULL,
card_no NUMBER NOT NULL,
card_string VARCHAR2(19) NOT NULL,
expiry_date DATE NOT NULL,
sec_code NUMBER NOT NULL,
valid_date DATE,
CONSTRAINT payment_details_pk PRIMARY KEY (id)
);
BEGIN
DBMS_REDACT.add_policy(
object_schema => 'chris',object_name => 'payment_details',column_name => 'card_no',policy_name => 'redact_card_info',function_type => DBMS_REDACT.full,expression => '1=1');
END;
/
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.modify_column,column_name => 'card_no',function_type => DBMS_REDACT.partial,function_parameters => '1,1,12');
END;
/
SELECT *
FROM chris.payment_details
ORDER BY id;
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.add_column,column_name => 'card_string',function_type => DBMS_REDACT.partial,function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12');
END;
/
begin
dbms_redact.add_policy(
object_schema => 'TEST',
object_name => 'TABLEA',
column_name => 'NUMBERA',
policy_name => 'MASK_PARTIAL_A',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => '9,1,5',
expression => 'SYS_CONTEXT(”USERENV”,”SESSION_USER”)= "TEST"');
end;
/
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.DROP_COLUMN,column_name => 'card_string');
END;
/
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.DROP_COLUMN,column_name => 'card_no');
END;
/
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.add_column,column_name => 'card_string',function_type => DBMS_REDACT.partial,function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12',expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''JUFER''');
END;
/
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.MODIFY_EXPRESSION,expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''CHRIS''');
END;
/
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'mavis',
object_name => 'cust_info',
column_name => 'login_username',
policy_name => 'redact_cust_rand_username',
function_type => DBMS_REDACT.RANDOM,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER''');
END;
/
select utl_inaddr.get_host_address('LaydeMacBook-Pro.local') from dual;
create orreplace trigger login_on
alfterlogon on database
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
/
select username,sid,serial#,client_info,client_identifier from v$session where sid=(select sys_context('userenv','sid') from dual);
select username,sid,serial#,MACHINE,client_info,client_identifier from v$session
10.172.10.2
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.add_column,column_name => 'card_string',function_type => DBMS_REDACT.partial,function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12',expression => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') = ''10.172.10.2''');
END;
/
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'chris',object_name => 'payment_details',policy_name => 'redact_card_info',action => DBMS_REDACT.MODIFY_EXPRESSION,expression => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') != ''10.172.10.2''');
END;
/
sys_context('userenv','ip_address') = 'xxx.xxx.x.xxx'