Use ENHANCED_SYM_ENCRYPT to encrypt data - MaxCompute

ENHANCED_SYM_ENCRYPTFor more information, see the "Activate KMS and grant permissions on KMS" section in Use keysets. encrypts a column or value using a specified keyset. Pass a basic keyset directly, or unwrap a wrapped keyset with USE_WRAPPED_KEYSET before passing it to this function.

Prerequisites

Before you begin, ensure that you have:

A basic keyset generated by NEW_KEYSET, or a wrapped keyset generated by NEW_WRAPPED_KEYSET. For details, see NEW_KEYSET or NEW_WRAPPED_KEYSET
If using a wrapped keyset: a RAM role with permissions to use that keyset, and the basic keyset retrieved from it via USE_WRAPPED_KEYSET

Syntax

binary ENHANCED_SYM_ENCRYPT(binary <keyset>, string|binary <plaintext> [, string <additional_data>])

Parameters

Parameter	Required	Type	Description
`keyset`	Yes	BINARY (basic keyset) or STRUCT (wrapped keyset)	The keyset to encrypt with. Pass a basic keyset of the BINARY type directly. To use a wrapped keyset, first call `USE_WRAPPED_KEYSET` to retrieve the basic keyset, then pass that result here.
`plaintext`	Yes	STRING or BINARY	The data to encrypt.
`additional_data`	No	STRING	Verification information supported by the algorithm.

Return value

Returns the ciphertext as BINARY.

Usage notes

Wrapped keysets require unwrapping first. Call USE_WRAPPED_KEYSET first to retrieve the basic keyset, then pass that result to ENHANCED_SYM_ENCRYPT. Your account must have the role with permissions to use the wrapped keyset.
The ciphertext is returned as BINARY. To store or display it as a readable string, wrap the result with base64().

Examples

Sample data

The following examples use the mf_user_info table. Create the table and insert sample data:

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

-- Insert data into the table.
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 sample data:

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 a column using a basic keyset

Encrypt the id_card_no column using a basic keyset. The result is wrapped with base64() to display the binary ciphertext as a readable string:

insert overwrite table mf_user_info
select id,
       name,
       gender,
       base64(ENHANCED_SYM_ENCRYPT(
           unhex('0A1072384D715A414541385044643351534C12580A330A0B4145532D47434D2D323536122026A8FB1126DF4F5B5DD03C180E6919565D7716CBB291815EFB5BBF30F8BEF9AF1801200210011A1072384D715A414541385044643351534C20022A0B68656C6C6F20776F726C64'),
           id_card_no
       )) as id_card_no,
       tel
from mf_user_info;

Query the encrypted result:

select * from mf_user_info;

+------------+-------+--------+-------------------------------------------------------------------------------------------+-------------+
| id         | name  | gender | id_card_no                                                                                | tel         |
+------------+-------+--------+-------------------------------------------------------------------------------------------+-------------+
| 1          | bob   | male   | nLcdDFdjO2T4aATtirvDMVeBD8oSuu4BfM3t+Y8ny0kwQjJlAQAwkVhYOocPQll8LmdzSwkRf3v2iTow+TAmnQ== | 13900001234 |
| 2          | allen | male   | nLcdDFdjO2T4aATtirvDMVeBD8oSuu4BfM3t+Y8ny0kwQjJlAQBgj1hYOodIPdnyZ0ijZ9RmT+50xbxXh5cwcg== | 13900001111 |
| 3          | kate  | female | nLcdDFdjO2T4aATtirvDMVeBD8oSuu4BfM3t+Y8ny0kwQjJlAQCwp1hYOoentQgkfUqctPbmX96k9eD018xg9Q== | 13900002222 |
| 4          | annie | female | nLcdDFdjO2T4aATtirvDMVeBD8oSuu4BfM3t+Y8ny0kwQjJlAQDQqFhYOodexhRmfh6VieEwePZscC4nUVTJXQ== | 13900003333 |
+------------+-------+--------+-------------------------------------------------------------------------------------------+-------------+

Encrypt a column using a wrapped keyset

Wrapped keysets are stored in a table and unwrapped at query time via USE_WRAPPED_KEYSET. This approach delegates key material management to Key Management Service (KMS).

Step 1: Create a wrapped keyset and store it in a table.

-- Create a table to hold the wrapped keyset.
create table mf_keyset_kms (id string, ks binary);

-- Generate a wrapped keyset and write it to the table.
insert into mf_keyset_kms
select '1',
       NEW_WRAPPED_KEYSET(
           'acs:kms:cn-hangzhou:1**************7:key/key-hzz****************1t',
           'acs:ram::1**************7:role/kms',
           'AES-GCM-256',
           'description'
       );

-- Verify the keyset was written.
select id, hex(ks) from mf_keyset_kms;

+----+-----+
| id | _c1 |
+----+-----+
| 1  | 613256354C576836656A59314D6D59344E7A6B7A624452754D6D3434627A49786443317A655859786358426F4E6A4D78447654524C4632635077766E74554654584579715242583953724167446D2F397131786F57456E6F5474516739633853766242674456773565736674714A4D5435524455382F6F6A2B4E61766D774344494C734B6A416B6B675A42496F5568656F566D38564C4F30506D4778767137646956517453447A5467395147775639533161305A464A6D6A45562B6742722F56386653444D6E424D2B71493779784668303866594E6D336578775744423949726B645A3469784F2B532B476E6750523854524A58326E5768666478347034473468687248684A514D615071332F526C342B67427652773D3D |
+----+-----+

Step 2: Encrypt the `tel` column using the stored wrapped keyset.

USE_WRAPPED_KEYSET unwraps the keyset by calling KMS at query time. The resulting basic keyset is passed directly to ENHANCED_SYM_ENCRYPT:

select /*+ MAPJOIN(a) */
       id,
       name,
       gender,
       id_card_no,
       ENHANCED_SYM_ENCRYPT(
           USE_WRAPPED_KEYSET(
               'acs:kms:cn-hangzhou:1**************7:key/key-hzz****************1t',
               'acs:ram::1**************7:role/kms',
               unhex('613256354C576836656A59314D6D59344E7A6B7A624452754D6D3434627A49786443317A655859786358426F4E6A4D78447654524C4632635077766E74554654584579715242583953724167446D2F397131786F57456E6F5474516739633853766242674456773565736674714A4D5435524455382F6F6A2B4E61766D774344494C734B6A416B6B675A42496F5568656F566D38564C4F30506D4778767137646956517453447A5467395147775639533161305A464A6D6A45562B6742722F56386653444D6E424D2B71493779784668303866594E6D336578775744423949726B645A3469784F2B532B476E6750523854524A58326E5768666478347034473468687248684A514D615071332F526C342B67427652773D3D')
           ),
           tel
       ) as tel
from mf_user_info;

The encrypted tel values are returned as BINARY:

+------------+------+--------+------------+------+
| id         | name | gender | id_card_no | tel  |
+------------+------+--------+------------+------+
| 1          | bob  | male   | 0001       | =F1=EEa=13V9=CCsB=90=E7=F3fl=D2=CB=F31=D8=3D=88=B7=F7=0CnG=E3\R=FC)=F2=10=3D2e=01=00=90=86=05=94z;=18=A6j=1CN=E5=9F=AC)=8D=D6=D8=0D=A2Y{kq=EE=F4~=C4=A7=9BS=A1w |
| 2          | allen | male   | 0011       | =F1=EEa=13V9=CCsB=90=E7=F3fl=D2=CB=F31=D8=3D=88=B7=F7=0CnG=E3\R=FC)=F2=10=3D2e=01=00=20=AA=05=94z;=85=D8=08a=A2]=02d=20=B1=C3=AE=AF=1C{=EB=EA=C4=81=B5A=15=1BR=F7g=9B |
| 3          | kate | female | 0111       | =F1=EEa=13V9=CCsB=90=E7=F3fl=D2=CB=F31=D8=3D=88=B7=F7=0CnG=E3\R=FC)=F2=10=3D2e=01=00=20=B6=05=94z;[C=12=81=8B<=C1=9D=E2=CF=CE=BC=AE=A7=84=0F[=7CI=B9=B7=9D=DD=89=A8=FD! |
| 4          | annie | female | 1111       | =F1=EEa=13V9=CCsB=90=E7=F3fl=D2=CB=F31=D8=3D=88=B7=F7=0CnG=E3\R=FC)=F2=10=3D2e=01=00=00=A2=05=94z;E=03A=BC=7C=88=CFJ=14=B9=BD=A1=BF=ED=20=11=A3=A6/+%=0Fe=DD=C7=C8=0A |
+------------+------+--------+------------+------+

Related functions

To generate a basic keyset, see NEW_KEYSET.
To generate a wrapped keyset backed by KMS, see NEW_WRAPPED_KEYSET.

文档	说明
密钥集（KEYSET）使用说明	Keyset加密函数的使用说明。
NEW_KEYSET	根据指定的算法类型，创建对应的密钥Keyset。
Syntax	在Keyset中新增密钥，并将新增的密钥设置为主密钥。
KEYSET_TO_JSON	将BINARY类型的密钥Keyset转化为可读的JSON格式，以便查看密钥Keyset详情。
KEYSET_FROM_JSON	将JSON类型的keyset转化为BINARY类型。
ROTATE_KEYSET	系统自动生产一个新密钥并将新密钥设置为主密钥。
NEW_WRAPPED_KEYSET	将有权使用KMS用户主密钥资源名称（kms_cmk_arn）角色的`role_arn`授权给MaxCompute，用来新建一个封装密钥集（KEYSET），也可通过角色链（`role_chain`）方式授权给其他阿里云账号解密KEYSET的权限。
REWRAP_KEYSET	通过指定的KMS密钥重新加密封装密钥集（KEYSET）。
ROTATE_WRAPPED_KEYSET	解密已有的加密密钥集（KEYSET），并进行密钥轮转，然后通过新密钥加密。
USE_WRAPPED_KEYSET	将封装密钥集转换为基础密钥集，作为加解密函数的参数使用，您也可通过此函数，获得封装密钥集相关的信息，并保存起来，便于维护。
ENHANCED_SYM_DECRYPT	指定密钥集进行数据解密。

MaxCompute:ENHANCED_SYM_ENCRYPT