MaxCompute:SYM_ENCRYPT

MaxCompute V2.0 provides additional functions. If the functions that you use involve new data types that are supported in the MaxCompute V2.0 data type edition, you must run a SET command to enable the MaxCompute V2.0 data type edition. The new data types include TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.

• Session level: To enable the MaxCompute V2.0 data type edition, you must add set odps.sql.type.system.odps2=true; before the SQL statement that you want to execute, and commit and execute them together.

• Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. The configuration takes effect after 10 to 15 minutes. To enable the MaxCompute V2.0 data type edition at the project level, run the following command:

  setproject odps.sql.type.system.odps2=true;

  For more information about setproject, see Project operations. For more information about the precautions that you must take when you enable the MaxCompute V2.0 data type edition at the project level, see Data type editions.

Encrypt data in a specified column of the mf_user_info table based on the sample data. Sample statements:

• Use the AEAD algorithm to encrypt data.

  -- Encrypt data in the id_card_no column.
  insert overwrite table mf_user_info
  select id,
      name,
      gender,
         base64(sym_encrypt(id_card_no,
                     cast('b75585cf321cdcad42451690cdb7bfc4' as binary)
                    ))as id_card_no,
        tel
  from mf_user_info;
  
  select * from mf_user_info;

  The following result is returned:

  +------------+------+--------+------------+-----+
  | id         | name | gender | id_card_no | tel |
  +------------+------+--------+------------+-----+
  | 1          | bob  | male   | frgJZAEAQMeEuHqpS8lK9VxQhgPYpZ317V+oUla/xEc= | 13900001234|
  | 2          | allen| male   | frgJZAIAQMeEuHqpLeXQfETsFSLJxBwHhPx6tpzWUg4= | 13900001111|
  | 3          | kate | female | frgJZAMAQMeEuHqpdphXAU6iWelWenlDnVy+R0HMvAY= | 13900002222|
  | 4          | annie| female | frgJZAQAQMeEuHqpR5c8bj21dYCeM0C25bLRZIrP71c= | 13900003333|
  +------------+------+--------+------------+-----+

• Use the AAD algorithm to encrypt data.

  -- Encrypt data in the id_card_no column.
  insert overwrite table mf_user_info
  select id,
               name,
               gender,
         base64(sym_encrypt(id_card_no,
                cast('b75585cf321cdcad42451690cdb7bfc4' as binary),
                'AES-GCM-256',
                'test'
                ))as id_card_no,
        tel
  from mf_user_info;
  select * from mf_user_info;

  The following result is returned:

  +------------+------+--------+------------+-----+
  | id         | name | gender | id_card_no | tel |
  +------------+------+--------+------------+-----+
  | 1          | bob  | male   | frgJZAEAQMeEuHqpS8lK9VxQhgPYpZ317V+oUla/xEc= | 13900001234|
  | 2          | allen| male   | frgJZAIAQMeEuHqpLeXQfETsFSLJxBwHhPx6tpzWUg4= | 13900001111|
  | 3          | kate | female | frgJZAMAQMeEuHqpdphXAU6iWelWenlDnVy+R0HMvAY= | 13900002222|
  | 4          | annie| female | frgJZAQAQMeEuHqpR5c8bj21dYCeM0C25bLRZIrP71c= | 13900003333|
  +------------+------+--------+------------+-----+

Construct a key table based on the sample data to manage keys.

• Principle

  • For data encryption, you can store keys in a MaxCompute table and perform the JOIN operation on the MaxCompute key table and the table whose column data you want to encrypt. This prevents keys from being leaked when the keys are passed.

  • For data decryption, the project administrator does not grant the permission on the MaxCompute key table to users who want to use the key table. Instead, the project administrator creates a secure view and authorizes the users to access the key table and call the decryption function to decrypt data based on the view. Users can decrypt data only after they obtain the access permissions on the view. The plaintext of the key is not included in the view. This way, the key is not leaked.

• Precautions

  • Customers are responsible for the creation and management of keys. MaxCompute does not store keys or mappings between keys and ciphertext. If a key is lost, the data cannot be decrypted.

  • To ensure data security, special processing is required when you pass parameters that involve plaintext keys.

  • If the data that you want to encrypt is of the BINARY type, you must run the set odps.sql.type.system.odps2=true; command to enable the MaxCompute V2.0 data type edition.

Sample statements:

-- Create a key table.
create table mf_id_key(id bigint,key binary);
-- Insert a key into the key table.
insert overwrite table mf_id_key
  values (1,cast('b75585cf321cdcad42451690cdb7bfc4' as binary));
-- Query the key.
select * from mf_id_key;
+------------+------+
| id         | key  |
+------------+------+
| 1          | b75585cf321cdcad42451690cdb7bfc4 |
+------------+------+

-- Query data from the mf_user_info table.
select * from mf_user_info;
+------------+------+--------+------------+------------+
| id         | name | gender | id_card_no |    tel     |
+------------+------+--------+------------+------------+
| 1          | bob  | male   | 0001       | 13900001234|
| 2          | allen| male   | 0011       | 13900001111|
| 3          | kate | female | 0111       | 13900002222|
| 4          | annie| female | 1111       | 13900003333|
+------------+------+--------+------------+------------+

-- Encrypt data in the specified column of the mf_user_info table.
insert overwrite table mf_user_info
select /*+mapjoin(b)*/
      a.id,
      a.name,
      a.gender,
      base64(
        (sym_encrypt(a.id_card_no, b.key))
      ) as id_card_no,
      a.tel
 from mf_user_info as a join mf_id_key as b on a.id>=b.id;

-- Query the encrypted data.
select * from mf_user_info;

The following result is returned:

+------------+------+--------+------------+-----+
| id         | name | gender | id_card_no | tel |
+------------+------+--------+------------+-----+
| 1          | bob  | male   | 9esKZAEAoBquXVJo3ZptvoI09XuM4bSFTqF1mXH1BO4= | 13900001234|
| 2          | allen| male   | 9esKZAIAoBquXVJoJYqnXieAANih7FR59luePvdHB9U= | 13900001111|
| 3          | kate | female | 9esKZAMAoBquXVJoppwxgVwPYBnvjIMklWLmJ/sU0Y8= | 13900002222|
| 4          | annie| female | 9esKZAQAoBquXVJoB85RUFCLMbdyEBSz7LdS4M3Guvk= | 13900003333|
+------------+------+--------+------------+-----+