SYM_DECRYPT - MaxCompute - Alibaba Cloud Documentation Center

Decrypts data that is encrypted by using a random key in specified columns of a table.

Usage notes

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.

Syntax

binary sym_decrypt(binary <value_to_decrypt>,
                   binary <key>
                   [,string <encryption_method> ,
                    [ string <additional_authenticated_data> ]
                   ]
                  )

Parameters

value_to_decrypt: required. The data that you want to decrypt. Only data of the BINARY type can be decrypted.
key: required. The key that is used to decrypt data. The key must be of the BINARY type and must be 256 bits in length.
encryption_method: optional. The mode that is used to decrypt data, which must be the same as the encryption mode.
additional_authenticated_data: optional. The additional authenticated data (AAD), which is used to verify the authenticity and integrity of the data. This parameter is required if AAD is used during data encryption.

Return value

Plaintext of the BINARY type is returned. You can use the CAST function to convert the BINARY type to the STRING type based on your business requirements.

Examples

The following examples illustrate how to decrypt data that is encrypted by using SYM_ENCRYPT.

Example 1: Decrypt data that is encrypted by using a plaintext key.

Decrypt data in the specified column of the mf_user_info table. The data is encrypted based on Example 1: Encrypt data by using a plaintext key. Sample statements:

Use the authenticated encryption with associated data (AEAD) encryption algorithm to decrypt data.

-- Decrypt data in the id_card_no column.
insert overwrite table mf_user_info
select id,
    name,
    gender,
       cast(sym_decrypt(unbase64(id_card_no),
                   cast('b75585cf321cdcad42451690cdb7bfc4' as binary)
                  ) as string) as id_card_no,
       tel
from mf_user_info;

-- Query the decrypted plaintext data.
select * from mf_user_info;

The following result is returned:

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

Use the AAD encryption algorithm to decrypt data.

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

-- Query the decrypted plaintext data.
select * from mf_user_info;

The following result is returned:

+------------+------+--------+------------+------------+
| 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 2: Decrypt data that is encrypted by using a key table.

Decrypt data in the specified column of the mf_user_info table. The data is encrypted based on Example 2: Encrypt data by using a key table. Sample statement:

-- Decrypt 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,
      cast(sym_decrypt(unbase64(a.id_card_no), b.key) as string) 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 decrypted data.
select * from mf_user_info;

The following result is returned:

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

Related functions

SYM_DECRYPT is a decryption function. For more information about encryption and decryption functions, see Encryption and decryption functions.