Oracle動態密碼Redacted簡介

CREATE TABLE kart_bilgileri (

id NUMBER NOT NULL,

musteri_id NUMBER NOT NULL,

kart_no NUMBER NOT NULL,

kart_string VARCHAR2(19) NOT NULL,

bitis_tarihi DATE NOT NULL,

guvenlik_no NUMBER NOT NULL,

gecerlilik_tarihi DATE

);

INSERT INTO kart_bilgileri VALUES (1, 1000, 2013201320132013, '2013-2013-2013-2013', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);

INSERT INTO kart_bilgileri VALUES (2, 1001, 1989198919891989, '1989-1989-1989-1989', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);

INSERT INTO kart_bilgileri VALUES (3, 1002, 2018201820182018, '2018-2018-2018-2018', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);

INSERT INTO kart_bilgileri VALUES (4, 1003, 1453145314531453, '1453-1453-1453-1453', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);

INSERT INTO kart_bilgileri VALUES (5, 1004, 5710571057105710, '5710-5710-5710-5710', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);

COMMIT;

--expression:用來做權限控制,套用policy給誰,

--1=1即除了sys、system等系統帳號外,全部套用,

--可以透過'SYS_CONTEXT(''SYS_SESSION_ROLES'',''MASK_ROLE'') = ''TRUE'''

BEGIN

DBMS_REDACT.add_policy(

object_schema => 'SP101369',

object_name => 'kart_bilgileri',

column_name => 'kart_no',

policy_name => 'redact_kart_info',

function_type => DBMS_REDACT.full,

expression => '1=1'

);

END;

/

select * from kart_bilgileri;

#Sample 1:

--部分遮罩

--遮罩參數,不同data type參數不同,字串:5個,數字:3個,日期:使用日期專用子函數

--字串的輸入參數說明:

--ex: ' VVVVFVVVVFVVVV,VVVV-VVVV-VVVV,*,3,10 '

--  前兩個參數:

--    V：代表輸入字符串中可以編輯的每個字符。

--    F：代表輸入字符串中可以被視為分隔符的每個字符。

--   *：遮罩字符

--  3：指定密文V字符的起始位置。

--  10：指定密文 V 字符的結束位置。

BEGIN

DBMS_REDACT.alter_policy (

object_schema => 'SP101369',

object_name => 'kart_bilgileri',

policy_name => 'redact_kart_info',

action => DBMS_REDACT.modify_column,

column_name => 'kart_no',

function_type => DBMS_REDACT.partial,

function_parameters => '1,1,12'

);

END;

/

#Sample 2:

BEGIN

DBMS_REDACT.alter_policy (

object_schema => 'SP101369',

object_name => 'kart_bilgileri',

policy_name => 'redact_kart_info',

action => DBMS_REDACT.modify_column,

column_name => 'kart_no',

function_type => DBMS_REDACT.RANDOM

);

END;

/