Use SYM_DECRYPT to decrypt data that is encrypted by using a random key in specified columns of a table-MaxCompute(MaxCompute)-阿里云帮助中心

Decrypts data that was encrypted using SYM_ENCRYPT and returns the plaintext as a BINARY value.

Usage notes

SYM_DECRYPT returns a BINARY type. To use the result as a string, cast it explicitly:

CAST(sym_decrypt(...) AS STRING)

SYM_DECRYPT uses new data types introduced in the MaxCompute V2.0 data type edition (BINARY, TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP). Enable the V2.0 data type edition before running queries that use SYM_DECRYPT:

Session level: Add the following statement before your SQL statement and execute them together.
```
set odps.sql.type.system.odps2=true;
```
Project level: Run the following command as the project owner. The setting 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_decrypt(binary <value_to_decrypt>,
                   binary <key>
                   [,string <encryption_method> ,
                    [ string <additional_authenticated_data> ]
                   ]
                  )

Parameters

Parameter	Required	Type	Description
`value_to_decrypt`	Yes	BINARY	The ciphertext to decrypt. Only BINARY type data is accepted.
`key`	Yes	BINARY	The decryption key. Must be BINARY type and 256 bits in length.
`encryption_method`	No	STRING	The algorithm used for decryption. Must match the algorithm used during encryption.
`additional_authenticated_data`	No	STRING	The additional authenticated data (AAD) used to verify the authenticity and integrity of the ciphertext. If AAD was specified during encryption, provide the same AAD for decryption.

Return value

Returns the decrypted plaintext as a BINARY value. To convert the result to STRING, use the CAST function:

CAST(sym_decrypt(<value_to_decrypt>, <key>) AS STRING)

Examples

Example 1: Decrypt using a plaintext key

The id_card_no column contains data encrypted using SYM_ENCRYPT Example 1. The ciphertext is Base64-encoded, so unbase64() is applied before decryption.

All examples operate on the mf_user_info table, which has columns: id, name, gender, id_card_no, and tel.

Using the default encryption method (AES-GCM-256):

Warning

This example hardcodes the key in the SQL statement for demonstration purposes. In production, never store the decryption key as plaintext in the same location as the encrypted data.

-- Decrypt 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 data.
SELECT * FROM mf_user_info;

Using AES-GCM-256 with additional authenticated data (AAD):

-- Decrypt the id_card_no column with AAD verification.
INSERT OVERWRITE TABLE mf_user_info
SELECT id,
       name,
       gender,
       CAST(sym_decrypt(unbase64(id_card_no),
                        CAST('b75585cf321cdcad42451690cdb7bfc4' AS BINARY),
                        'AES-GCM-256',
                        'test'
                       ) AS STRING) AS id_card_no,
       tel
FROM mf_user_info;

-- Query the decrypted data.
SELECT * FROM mf_user_info;

Both queries return:

+------------+------+--------+------------+-------------+
| 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 using a key table

The id_card_no column contains data encrypted using SYM_ENCRYPT Example 2. Keys are stored in the mf_id_key table and joined at query time using a MAPJOIN hint.

-- Decrypt the id_card_no column using per-row keys from mf_id_key.
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 query returns:

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