8.5.2.4 MySQL Enterprise Data Masking and De-Identification Component Function Descriptions (original) (raw)

Data Masking Component Functions

Each component function in this section performs a masking operation on its string argument and returns the masked result.

• mask_canada_sin(str [, mask_char])
  Masks a Canada Social Insurance Number (SIN) and returns the number with all meaningful digits replaced by'X' characters. An optional masking character can be specified.
  Arguments:
  • str: The string to mask. The accepted formats are:
    * Nine non-separated digits.
    * Nine digits grouped in pattern:xxx-xxx-xxx ('-' is any separator character).

  This argument is converted to theutf8mb4 character set.

  • maskchar: (Optional) The single character to use for masking. The default is'X' if_maskchar_ is not given.
    Return value:
    The masked Canada SIN as a string encoded in theutf8mb4 character set, an error if the argument is not the correct length, orNULL if str is in incorrect format or contains a multibyte character.
    Example:

mysql> SELECT mask_canada_sin('046-454-286'), mask_canada_sin('abcdefijk');  
+--------------------------------+------------------------------+  
| mask_canada_sin('046-454-286') | mask_canada_sin('abcdefijk') |  
+--------------------------------+------------------------------+  
| XXX-XXX-XXX                    | XXXXXXXXX                    |  
+--------------------------------+------------------------------+  
mysql> SELECT mask_canada_sin('909');  
ERROR 1123 (HY000): Can't initialize function 'mask_canada_sin'; Argument 0 is too short.  
mysql> SELECT mask_canada_sin('046-454-286-909');  
ERROR 1123 (HY000): Can't initialize function 'mask_canada_sin'; Argument 0 is too long.  

• mask_iban(str [, mask_char])
  Masks an International Bank Account Number (IBAN) and returns the number with all but the first two letters (denoting the country) replaced by '*' characters. An optional masking character can be specified.
  Arguments:
  • str: The string to mask. Each country can have a different national routing or account numbering system, with a minimum of 13 and a maximum of 34 alphanumeric ASCII characters. The accepted formats are:
    * Non-separated characters.
    * Character grouped by four, except the last group, and separated by space or any other separator character (for example:xxxx-xxxx-xxxx-xx).

  This argument is converted to theutf8mb4 character set.

  • maskchar: (Optional) The single character to use for masking. The default is'*' if_maskchar_ is not given.
    Return value:
    The masked International Bank Account Number as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, orNULL if str is in incorrect format or contains a multibyte character.
    Example:

mysql> SELECT mask_iban('IE12 BOFI 9000 0112 3456 78'), mask_iban('abcdefghijk');  
+------------------------------------------+--------------------------+  
| mask_iban('IE12 BOFI 9000 0112 3456 78') | mask_iban('abcdefghijk') |  
+------------------------------------------+--------------------------+  
| IE** **** **** **** **** **              | ab*********              |  
+------------------------------------------+--------------------------+  
mysql> SELECT mask_iban('909');  
ERROR 1123 (HY000): Can't initialize function 'mask_iban'; Argument 0 is too short.  
mysql> SELECT mask_iban('IE12 BOFI 9000 0112 3456 78 IE12 BOFI 9000 0112 3456 78');  
ERROR 1123 (HY000): Can't initialize function 'mask_iban'; Argument 0 is too long.  

• mask_inner(str,margin1,margin2 [,mask_char])
  Masks the interior part of a string, leaving the ends untouched, and returns the result. An optional masking character can be specified.
  mask_inner supports all character sets.
  Arguments:
  • str: The string to mask. This argument is converted to theutf8mb4 character set.
  • margin1: A nonnegative integer that specifies the number of characters on the left end of the string to remain unmasked. If the value is 0, no left end characters remain unmasked.
  • margin2: A nonnegative integer that specifies the number of characters on the right end of the string to remain unmasked. If the value is 0, no right end characters remain unmasked.
  • maskchar: (Optional) The single character to use for masking. The default is'X' if_maskchar_ is not given.
    Return value:
    The masked string encoded in the same character set used for str, or an error if either margin is negative.
    If the sum of the margin values is larger than the argument length, no masking occurs and the argument is returned unchanged.
    Note
    The function is optimized to work faster for single byte strings (having equal byte length and character length). For example, the utf8mb4 character set uses only one byte for ASCII characters, so the function processes strings containing only ASCII characters as single-byte character strings.
    Example:

mysql> SELECT mask_inner('abcdef', 1, 2), mask_inner('abcdef',0, 5);  
+----------------------------+---------------------------+  
| mask_inner('abcdef', 1, 2) | mask_inner('abcdef',0, 5) |  
+----------------------------+---------------------------+  
| aXXXef                     | Xbcdef                    |  
+----------------------------+---------------------------+  
mysql> SELECT mask_inner('abcdef', 1, 2, '*'), mask_inner('abcdef',0, 5, '#');  
+---------------------------------+--------------------------------+  
| mask_inner('abcdef', 1, 2, '*') | mask_inner('abcdef',0, 5, '#') |  
+---------------------------------+--------------------------------+  
| a***ef                          | #bcdef                         |  
+---------------------------------+--------------------------------+  

• mask_outer(str,margin1,margin2 [,mask_char])
  Masks the left and right ends of a string, leaving the interior unmasked, and returns the result. An optional masking character can be specified.
  mask_outer supports all character sets.
  Arguments:
  • str: The string to mask. This argument is converted to theutf8mb4 character set.
  • margin1: A nonnegative integer that specifies the number of characters on the left end of the string to mask. If the value is 0, no left end characters are masked.
  • margin2: A nonnegative integer that specifies the number of characters on the right end of the string to mask. If the value is 0, no right end characters are masked.
  • maskchar: (Optional) The single character to use for masking. The default is'X' if_maskchar_ is not given.
    Return value:
    The masked string encoded in the same character set used for str, or an error if either margin is negative.
    If the sum of the margin values is larger than the argument length, the entire argument is masked.
    Note
    The function is optimized to work faster for single byte strings (having equal byte length and character length). For example, the utf8mb4 character set uses only one byte for ASCII characters, so the function processes strings containing only ASCII characters as single-byte character strings.
    Example:

mysql> SELECT mask_outer('abcdef', 1, 2), mask_outer('abcdef',0, 5);  
+----------------------------+---------------------------+  
| mask_outer('abcdef', 1, 2) | mask_outer('abcdef',0, 5) |  
+----------------------------+---------------------------+  
| XbcdXX                     | aXXXXX                    |  
+----------------------------+---------------------------+  
mysql> SELECT mask_outer('abcdef', 1, 2, '*'), mask_outer('abcdef',0, 5, '#');  
+---------------------------------+--------------------------------+  
| mask_outer('abcdef', 1, 2, '*') | mask_outer('abcdef',0, 5, '#') |  
+---------------------------------+--------------------------------+  
| *bcd**                          | a#####                         |  
+---------------------------------+--------------------------------+  

• mask_pan(str [, mask_char])
  Masks a payment card Primary Account Number (PAN) and returns the number with all but the last four digits replaced by 'X' characters. An optional masking character can be specified.
  Arguments:
  • str: The string to mask. The string must contain a minimum of 14 and a maximum of 19 alphanumeric characters. This argument is converted to the utf8mb4 character set.
  • maskchar: (Optional) The single character to use for masking. The default is'X' if_maskchar_ is not given.
    Return value:
    The masked payment number as a string encoded in theutf8mb4 character set, an error if the argument is not the correct length, orNULL if str is in incorrect format or contains a multibyte character.
    Example:

mysql> SELECT mask_pan(gen_rnd_pan());  
+-------------------------+  
| mask_pan(gen_rnd_pan()) |  
+-------------------------+  
| XXXXXXXXXXXX9102        |  
+-------------------------+  
mysql> SELECT mask_pan(gen_rnd_pan(19));  
+---------------------------+  
| mask_pan(gen_rnd_pan(19)) |  
+---------------------------+  
| XXXXXXXXXXXXXXX8268       |  
+---------------------------+  
mysql> SELECT mask_pan('a*Z');  
ERROR 1123 (HY000): Can't initialize function 'mask_pan'; Argument 0 is too short.  

• mask_pan_relaxed(str)
  Masks a payment card Primary Account Number and returns the number with all but the first six and last four digits replaced by 'X' characters. The first six digits indicate the payment card issuer. An optional masking character can be specified.
  Arguments:
  • str: The string to mask. The string must be a suitable length for the Primary Account Number, but is not otherwise checked. This argument is converted to theutf8mb4 character set.
  • maskchar: (Optional) The single character to use for masking. The default is'X' if_maskchar_ is not given.
    Return value:
    The masked payment number as a string encoded in theutf8mb4 character set, an error if the argument is not the correct length, orNULL if str is in incorrect format or contains a multibyte character.
    Example:

mysql> SELECT mask_pan_relaxed(gen_rnd_pan());  
+---------------------------------+  
| mask_pan_relaxed(gen_rnd_pan()) |  
+---------------------------------+  
| 551279XXXXXX3108                |  
+---------------------------------+  
mysql> SELECT mask_pan_relaxed(gen_rnd_pan(19));  
+-----------------------------------+  
| mask_pan_relaxed(gen_rnd_pan(19)) |  
+-----------------------------------+  
| 462634XXXXXXXXX6739               |  
+-----------------------------------+  
mysql> SELECT mask_pan_relaxed('a*Z');  
ERROR 1123 (HY000): Can't initialize function 'mask_pan_relaxed'; Argument 0 is too short.  

• mask_ssn(str [, mask_char])
  Masks a US Social Security Number (SSN) and returns the number with all but the last four digits replaced by'*' characters. An optional masking character can be specified.
  Arguments:
  • str: The string to mask. The accepted formats are:
    * Nine non-separated digits.
    * Nine digits grouped in pattern:xxx-xx-xxxx ('-' is any separator character).

  This argument is converted to theutf8mb4 character set.

  • maskchar: (Optional) The single character to use for masking. The default is'*' if_maskchar_ is not given.
    Return value:
    The masked Social Security Number as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, orNULL if str is in incorrect format or contains a multibyte character.
    Example:

mysql> SELECT mask_ssn('909-63-6922'), mask_ssn('cdefghijk');  
+-------------------------+-------------------------+  
| mask_ssn('909-63-6922') | mask_ssn('cdefghijk')   |  
+-------------------------+-------------------------+  
| ***-**-6922             | *******hijk             |  
+-------------------------+-------------------------+  
mysql> SELECT mask_ssn('909');  
ERROR 1123 (HY000): Can't initialize function 'mask_ssn'; Argument 0 is too short.  
mysql> SELECT mask_ssn('123456789123456789');  
ERROR 1123 (HY000): Can't initialize function 'mask_ssn'; Argument 0 is too long.  

• mask_uk_nin(str [, mask_char])
  Masks a United Kingdom National Insurance Number (UK NIN) and returns the number with all but the first two digits replaced by '*' characters. An optional masking character can be specified.
  Arguments:
  • str: The string to mask. The accepted formats are:
    * Nine non-separated digits.
    * Nine digits grouped in pattern:xxx-xx-xxxx ('-' is any separator character).
    * Nine digits grouped in pattern:xx-xxxxxx-x ('-' is any separator character).

  This argument is converted to theutf8mb4 character set.

  • maskchar: (Optional) The single character to use for masking. The default is'*' if_maskchar_ is not given.
    Return value:
    The masked UK NIN as a string encoded in theutf8mb4 character set, an error if the argument is not the correct length, orNULL if str is in incorrect format or contains a multibyte character.
    Example:

mysql> SELECT mask_uk_nin('QQ 12 34 56 C'), mask_uk_nin('abcdefghi');  
+------------------------------+--------------------------+  
| mask_uk_nin('QQ 12 34 56 C') | mask_uk_nin('abcdefghi') |  
+------------------------------+--------------------------+  
| QQ ** ** ** *                | ab*******                |  
+------------------------------+--------------------------+  
mysql> SELECT mask_uk_nin('909');  
ERROR 1123 (HY000): Can't initialize function 'mask_uk_nin'; Argument 0 is too short.  
mysql> SELECT mask_uk_nin('abcdefghijk');  
ERROR 1123 (HY000): Can't initialize function 'mask_uk_nin'; Argument 0 is too long.  

• mask_uuid(str [, mask_char])
  Masks a Universally Unique Identifier (UUID) and returns the number with all meaningful characters replaced by'*' characters. An optional masking character can be specified.
  Arguments:
  • str: The string to mask. The accepted format isxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx in which 'X' is any digit and '-' is any separator character This argument is converted to theutf8mb4 character set.
  • maskchar: (Optional) The single character to use for masking. The default is'*' if_maskchar_ is not given.
    Return value:
    The masked UUID as a string encoded in theutf8mb4 character set, an error if the argument is not the correct length, orNULL if str is in incorrect format or contains a multibyte character.
    Example:

mysql> SELECT mask_uuid(gen_rnd_uuid());  
+--------------------------------------+  
| mask_uuid(gen_rnd_uuid())            |  
+--------------------------------------+  
| ********-****-****-****-************ |  
+--------------------------------------+  
mysql> SELECT mask_uuid('909');  
ERROR 1123 (HY000): Can't initialize function 'mask_uuid'; Argument 0 is too short.  
mysql> SELECT mask_uuid('123e4567-e89b-12d3-a456-426614174000-123e4567-e89b-12d3');  
ERROR 1123 (HY000): Can't initialize function 'mask_uuid'; Argument 0 is too long.