MaxCompute:SYM_ENCRYPT

Usage notes

SYM_ENCRYPT returns a BINARY value. If the data you want to encrypt is of the BINARY type, enable the MaxCompute V2.0 data type edition first. The MaxCompute V2.0 data type edition introduces the following new types: TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.

• Session level: Add set odps.sql.type.system.odps2=true; before the SQL statement, then commit and run both together.

• Project level: Run the following command as the project owner. The change takes effect after 10 to 15 minutes.

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

  For details, see Project operations and Data type editions.

Syntax

binary sym_encrypt(string|binary <value_to_encrypt>,
                   binary <key>
                   [,string <encryption_method> ,
                      [ string <additional_authenticated_data> ]
                    ]
                  )

Parameters

Required:

• value_to_encrypt: The data to encrypt. Only STRING and BINARY types are supported.

• key: The encryption key of the BINARY type. Must be 256 bits in length.

Optional:

• encryption_method: The encryption method. Valid values: AES-GCM-256. Default value: AES-GCM-256.

• additional_authenticated_data: The additional authenticated data (AAD), used to verify the authenticity and integrity of the data. Only authenticated encryption with associated data (AEAD) algorithms, such as AES-GCM, support AAD.

Return value

Returns ciphertext of the BINARY type. The return value contains the following components in sequence: a randomly generated Initialization Vector (IV), the ciphertext, and the authentication tag (AEAD tag).

Because the IV is generated randomly for each call, the return value differs every time, even when you use the same key to encrypt the same plaintext.

Sample data

The following examples use the mf_user_info table. Run the statements below to create and populate the table.

-- Create the table.
create table mf_user_info(id bigint,
                          name string,
                          gender string,
                          id_card_no string,
                          tel string);

-- Insert sample data.
insert overwrite table mf_user_info values(1,"bob","male","0001","13900001234"),
                                           (2,"allen","male","0011","13900001111"),
                                           (3,"kate","female","0111","13900002222"),
                                           (4,"annie","female","1111","13900003333");

-- Query the table.
select * from mf_user_info;

Result:

+------------+------+--------+------------+------------+
| 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|
+------------+------+--------+------------+------------+

Example 1: Encrypt data with a plaintext key

These examples encrypt the id_card_no column in mf_user_info using a key passed inline as a BINARY literal. The base64() wrapper converts the binary ciphertext to a printable string for storage.

Encrypt with AES-GCM-256 (no AAD)

-- Encrypt 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;

Result:

+------------+------+--------+------------+-----+
| 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|
+------------+------+--------+------------+-----+

Encrypt with AES-GCM-256 and AAD

Pass an AAD value to bind the ciphertext to a specific context. Decryption fails if the AAD does not match.

-- Encrypt the id_card_no column with AAD.
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;

Result:

+------------+------+--------+------------+-----+
| id         | name | gender | id_card_no | tel |
+------------+------+--------+------------+-----+
| 1          | bob  | male   | gJ0QaAEAoGGWVw90H/zETg... | 13900001234|
| 2          | allen| male   | gJ0QaAIAoGGWVw90TpNFC0... | 13900001111|
| 3          | kate | female | gJ0QaAMAoGGWVw90KaQ8Vm... | 13900002222|
| 4          | annie| female | gJ0QaAQAoGGWVw90nYCAS1... | 13900003333|
+------------+------+--------+------------+-----+

Example 2: Encrypt data with a key table

To avoid passing plaintext keys in SQL, store keys in a MaxCompute table and join it with the target table at encryption time.

Key management notes:

• Keys are your responsibility to create and manage. MaxCompute does not store keys or any mapping between keys and ciphertext. If a key is lost, the encrypted data cannot be recovered.

• To prevent key exposure, do not grant direct access to the key table. Instead, create a secure view that calls the decryption function—users access data through the view without ever seeing the plaintext key.

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

-- Create a key table.
create table mf_id_key(id bigint, key binary);

-- Insert a key.
insert overwrite table mf_id_key
  values (1, cast('b75585cf321cdcad42451690cdb7bfc4' as binary));

-- Verify the key table.
select * from mf_id_key;

Result:

+------------+----------------------------------+
| id         | key                              |
+------------+----------------------------------+
| 1          | b75585cf321cdcad42451690cdb7bfc4 |
+------------+----------------------------------+

-- Encrypt the id_card_no column using the key 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 result.
select * from mf_user_info;

Result:

+------------+------+--------+------------+-----+
| 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|
+------------+------+--------+------------+-----+

Related functions

SYM_ENCRYPT is part of the encryption and decryption function group. For the full list, see Encryption and decryption functions.