All Products
Search

This Product

    MaxCompute:ENHANCED_SYM_ENCRYPT

    Document Center

    MaxCompute:ENHANCED_SYM_ENCRYPT

    Last Updated:Nov 15, 2023

    This topic describes how to use the ENHANCED_SYM_ENCRYPT function to encrypt data by using a specified keyset.

    Background information and prerequisites

    MaxCompute allows you to use the ENHANCED_SYM_ENCRYPT function to encrypt data by using a specified basic keyset or wrapped keyset. You can create a wrapped keyset by encrypting an existing keyset based on a Key Management Service (KMS) key. Compared with basic keysets, you can use wrapped keysets with KMS to manage keys in a more secure manner.

    Before you use the ENHANCED_SYM_ENCRYPT function, make sure that the following prerequisites are met:

    • A basic keyset or wrapped keyset is generated by using the NEW_KEYSET or NEW_WRAPPED_KEYSET function. For more information, see NEW_KEYSET or NEW_WRAPPED_KEYSET

    • A basic keyset is obtained from a wrapped keyset by using the USE_WRAPPED_KEYSET function. This prerequisite must be met if you want to use a wrapped keyset to encrypt data. The basic keyset is used as a parameter in the ENHANCED_SYM_ENCRYPT function to encrypt data. In addition, your account is assigned the role that has permissions to use the wrapped keyset.

    Syntax

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

    Parameters

    • keyset: required. This parameter specifies a basic keyset of the BINARY type or a wrapped keyset of the STRUCT type.

    • plaintext: required. This parameter specifies the plaintext of the STRING or BINARY type that you want to encrypt.

    • additional_data: optional. This parameter specifies the verification information supported by the algorithm. The verification information is of the STRING type.

    Return value

    Ciphertext of the BINARY type is returned.

    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 data from the 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|
+------------+------+--------+------------+------------+

    Examples

    • Encrypt the id_card_no column in the mf_user_info table by using a basic keyset.

      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;

      The following sample statement queries the encryption result:

      select * from mf_user_info;

-- The following result is returned:
+------------+------+--------+------------+-----+
| 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 the tel column in the mf_user_info table by using a wrapped keyset.

      1. Generate a wrapped keyset and write it to a table.

        -- Create a table.
create table mf_keyset_kms (id string,ks binary);
-- Create 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');
-- Query data from the table.
select id,hex(ks) from mf_keyset_kms;

-- The following result is returned:
+----+-----+
| id | _c1 |
+----+-----+
| 1  | 613256354C576836656A59314D6D59344E7A6B7A624452754D6D3434627A49786443317A655859786358426F4E6A4D78447654524C4632635077766E74554654584579715242583953724167446D2F397131786F57456E6F5474516739633853766242674456773565736674714A4D5435524455382F6F6A2B4E61766D774344494C734B6A416B6B675A42496F5568656F566D38564C4F30506D4778767137646956517453447A5467395147775639533161305A464A6D6A45562B6742722F56386653444D6E424D2B71493779784668303866594E6D336578775744423949726B645A3469784F2B532B476E6750523854524A58326E5768666478347034473468687248684A514D615071332F526C342B67427652773D3D |
+----+-----+

      2. Encrypt the tel column by using the wrapped keyset.

        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 following result is returned:

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