Data security and privacy are critical challenges in today’s digital age. In particular, for engineers and database administrators handling massive databases, protecting data is extremely important.
This article explains effective data masking methods using SQL.

Contents
  1. What Is Data Masking?
  2. Basic Masking Process
  3. Using Hash Functions
  4. Using UUID
  5. Generating Custom IDs Based on Existing Data
  6. Important Notes
  7. Applications of Data Masking
  8. Conclusion

What Is Data Masking?

Data masking is the process of concealing original data and transforming it into another format that is difficult to read. This preserves data confidentiality while maintaining its usability. For example, it is used when specific information such as customer personal information or confidential corporate data needs to be protected.

Basic Masking Process

Below is an example of basic data masking in SQL.

UPDATE company_tbl
SET company_name = 'Confidential';

　
In this simple example, all company_name values in the company_tbl table are replaced with the string “Confidential”.

Using Hash Functions

Generate hash values from existing data and use them as the new company_name. By using hash functions (e.g., MD5, SHA1), you can generate a unique string for each record.

UPDATE company_tbl
SET company_name = CONCAT('Company_', MD5(company_name));

　
With this method, each company_name is converted into an MD5 hash and prefixed with ‘Company_’.

Using UUID

Generate and use a UUID (Universally Unique Identifier) as a unique identifier. Many database systems provide functions to generate UUIDs.

UPDATE company_tbl
SET company_name = CONCAT('Company_', UUID());

　
With this method, a unique UUID is generated for each record and prefixed with ‘Company_’.

A UUID (Universally Unique Identifier) is a 128-bit identifier considered to be unique worldwide. Because UUIDs are unique with an extremely high probability, they are widely used to uniquely identify objects or entities across various systems and processes.

Characteristics of UUID

  1. Uniqueness: UUIDs are unique worldwide with an extremely high probability. Due to their generation algorithm, the chance of generating the same UUID twice is extremely low.
  2. Standardized Format: A UUID is typically represented as a 36-character string consisting of hexadecimal digits and hyphens (e.g., 123e4567-e89b-12d3-a456-426614174000).
  3. Versions and Variants: UUIDs have multiple versions and variants depending on how they are generated. The most common is the randomly generated Version 4 UUID.
  4. Versatility: UUIDs are used for various purposes such as database record identifiers, component identifiers, transaction IDs, and website session IDs.

Generating UUIDs

There are several methods for generating UUIDs, but the two most common are:

  • Random generation (Version 4): Generated from random values. This method is easy to implement and widely used.
  • Time-based generation (Version 1): Generated by combining the current time, the hardware MAC address, and random elements.

Using UUIDs

UUIDs are widely supported in programming languages and database systems, and they can usually be generated easily using built-in functions or libraries. Due to their unique nature, they are extremely useful when unique identifiers are required in system design.

Generating Custom IDs Based on Existing Data

You can also perform masking using unique identifiers based on specific rules.
For example, using existing data from a unique ID (column name: id), you can mask data as follows:

UPDATE company_tbl
SET company_name = CONCAT('DummyCompany', id);

　
With this method, each record’s company_name is replaced with “DummyCompany” followed by the existing unique id value of that row.

Important Notes

  • With these methods, the original company_name data will be lost. Ensure that the change is irreversible and take backups if necessary.
  • The guarantee of uniqueness depends on the method used. Especially when using hash functions, be aware of the theoretical possibility of collisions.
  • Depending on the database system, the above functions may not be available. In such cases, refer to the database documentation or use system-specific features.

Applications of Data Masking

Data masking can be applied in various scenarios. For example, in a customer database, customer names and addresses can be masked to protect personally identifiable information.

Conclusion

Data masking is essential for protecting data security and privacy. By using SQL, you can protect data flexibly and efficiently. When handling data, always prioritize security and apply appropriate masking processes.

　
*Please use this information at your own discretion.*

Data Masking