Code explanation of Oracle self-defined desensitization function

For information security requirements, sensitive fields containing user identity information need to be desensitized during data delivery and synchronization, including user name, ID number, address, etc. The following is the code of the custom function

CREATE OR REPLACE FUNCTION F_GET_SENSITIVE(IN_STR VARCHAR, IN_TYPE NUMBER) RETURN VARCHAR2 IS V_STR_LENGTH NUMBER; V_NAME VARCHAR2(1000); V_N NUMBER; V_HID VARCHAR2(200); V_SQL VARCHAR2(200); V_NUM_FLAG NUMBER; /** ** N_TYPE Desensitization Field Type 1: Name 11: Address 2: ID 3: Bank Account 4: Contact Phone 5: Access Number ***/BEGIN V_STR_LENGTH := LENGTH(IN_STR); V_N := 0; IF V_STR_LENGTH=0 THEN RETURN(NULL); END IF; /** ******* Name and Address Desensitization Rules ********/ IF IN_TYPE = 1 OR IN_TYPE=11 THEN IF V_STR_LENGTH = 2 OR V_STR_LENGTH = 3 THEN V_NAME := REGEXP_REPLACE(IN_STR, '(.) ', '*', 2, 1); ELSIF V_STR_LENGTH

< 2 THEN V_NAME :=IN_STR; ELSE WHILE V_N < V_STR_LENGTH / 2 LOOP V_N := V_N + 1; V_HID := V_HID || '*'; END LOOP; V_NAME := SUBSTR(IN_STR, 0, V_STR_LENGTH / 2) || V_HID; END IF; RETURN(V_NAME); END IF; /**********证件脱敏规则**********/ IF IN_TYPE = 2 THEN IF V_STR_LENGTH = 15 THEN V_NAME := SUBSTR(IN_STR, 0, 6) || '******' || SUBSTR(IN_STR, -3, 3); ELSIF V_STR_LENGTH = 18 THEN V_NAME := SUBSTR(IN_STR, 0, 6) || '********' || SUBSTR(IN_STR, -4, 4); ELSE WHILE V_N < V_STR_LENGTH / 3 LOOP V_N := V_N + 1; V_HID := V_HID || '*'; END LOOP; V_NAME := SUBSTR(IN_STR, 0, V_STR_LENGTH / 3) || V_HID || SUBSTR(IN_STR, -V_STR_LENGTH / 3, V_STR_LENGTH / 3); END IF; RETURN(V_NAME); END IF; /**********银行账号脱敏规则**********/ IF IN_TYPE = 3 THEN IF V_STR_LENGTH >

15 THEN V_NAME := SUBSTR(IN_STR, 0, 4) || '********' || SUBSTR(IN_STR, -4, 4); ELSE V_NAME :=IN_STR; END IF; RETURN(V_NAME); END IF; /** ******* Contact Phone Desensitization Rules ********/ IF IN_TYPE = 4 THEN V_NAME := SUBSTR(IN_STR, 0, V_STR_LENGTH - 4)|| '****'; RETURN(V_NAME); END IF; /** ******** Access Number Desensitization Rules ********/ IF IN_TYPE = 5 THEN V_SQL : ='SELECT COUNT(1) FROM DUAL WHERE LENGTH (''|| IN_STR || ''') = LENGTH(REGEXP_REPLACE(''' || IN_STR || ''', ''[^0-9]''))'; EXECUTE IMMEDIATE V_SQL INTO V_NUM_FLAG; IF V_NUM_FLAG = 1 AND (V_STR_LENGTH = 7 OR V_STR_LENGTH = 8) THEN V_NAME := SUBSTR(IN_STR, 0, 2) || '****' || SUBSTR(IN_STR, -2, 2); ELSIF V_NUM_FLAG = 1 AND V_STR_LENGTH = 11 THEN V_NAME := SUBSTR(IN_STR, 0, 3) || '*****' || SUBSTR(IN_STR, -3, 3); ELSE V_NAME := IN_STR; END IF; RETURN(V_NAME); END IF; RETURN(IN_STR);EXCEPTION WHEN OTHERS THEN -- DBMS_OUTPUT.PUT_LINE('1'||V_SQL); V_NAME := '-1'; RETURN V_NAME;END F_GET_SENSITIVE;

summary

The above is a detailed explanation of the Oracle custom desensitization function introduced by Xiaobian. I hope it will help you. If you have any questions, please leave a message to me. Xiaobian will reply to you in time. Thank you very much for your support!

If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!