All Products
Search

This Product

    MaxCompute:Use keysets

    Document Center

    MaxCompute:Use keysets

    Last Updated:Dec 21, 2023

    MaxCompute uses keysets to manage keys. Each keyset stores one or more keys. You can add keys to a keyset to implement key rotation and view the original key. This topic describes how to use keysets.

    Usage notes

    Keep your keyset and decryption parameters confidential. If the keyset and decryption parameters are lost, the data that is encrypted by using the keyset cannot be decrypted. As a result, the original data cannot be retrieved.

    Prepare data

    Create a table named mf_test_data and insert data into the table. 

    -- Create a table.
create table mf_test_data(id string,name string,tel string);
-- Insert data into the table.
insert into mf_test_data values(1,'kyle','13900001234'),(2,'tom','13900001111');
-- Query data from the table.
select * from mf_test_data;
-- The following result is returned:
+----+------+-----+
| id | name | tel |
+----+------+-----+
| 1  | kyle | 13900001234 |
| 2  | tom  | 13900001111 |
+----+------+-----+

    Directly use keysets

    • Example 1: Create a keyset. 

      select NEW_KEYSET('AES-GCM-256', 'my first keyset');

      The following result is returned: 

      +------+
| _c0  |
+------+
| =0A=10260nZQEAMAsSF7mB=12\=0A3=0A=0BAES-GCM-256=12=20=C4t=13+=8E=DD=9D=E8=A0=AA=B4=ED~1`=B7=C6=D0K=D3=FC=D4n=DF=DF=D4=C3)=E8=96=0E=17=18=01=20=02=10=01=1A=10260nZQEAMAsSF7mB=20=02*=0Fmy=20first=20keyset |
+------+

    • Example 2: Convert a keyset into plaintext for queries. 

      select KEYSET_TO_JSON(NEW_KEYSET('AES-GCM-256', 'my first keyset'));

      The following result is returned: 

      +-----+
| _c0 |
+-----+
| {
    "key": [{
            "description": "my first keyset",
            "key_id": "Ra4nZQEAoBuiGbmB",
            "key_meta_data": {
                "key_material_origin": "Origin_ALIYUN_MAXCOMPUTE",
                "key_material_type": "SYMMETRIC",
                "type": "AES-GCM-256",
                "value": "/LFKWhw18hz+OBO490YKmjQQDNVWJLOueaUAKKiem/k="},
            "output_prefix_type": "PREFIX_ALIYUN_MAXCOMPUTE",
            "status": "ENABLED"}],
    "primary_key_id": "Ra4nZQEAoBuiGbmB"} |

    • Example 3: Add a key to a keyset and configure the new key as the master key.

      • Add a key whose algorithm type is AES-SIV-CMAC-128 to a keyset whose algorithm type is AES-GCM-256. 

        select  KEYSET_TO_JSON(
  					ADD_KEY_TO_KEYSET(
              NEW_KEYSET('AES-GCM-256', 'my first keyset'), 
              'AES-SIV-CMAC-128', 
              unhex('b75585cf321cdcad42451690cdb7bfc49c26092f60f854e72d43244c55620a3d'),
              ''
            )
				);

        The following result is returned: 

        +-----+
| _c0 |
+-----+
| {
    "key": [{
            "description": "my first keyset",
            "key_id": "+q8nZQEAgAMtJLmB",
            "key_meta_data": {
                "key_material_origin": "Origin_ALIYUN_MAXCOMPUTE",
                "key_material_type": "SYMMETRIC",
                "type": "AES-GCM-256",
                "value": "Hj//ZKxLE/t0Uq7XRJQoe2OYNwlauDdGmkaQbMfnZ80="},
            "output_prefix_type": "PREFIX_ALIYUN_MAXCOMPUTE",
            "status": "ENABLED"},
        {
            "description": "",
            "key_id": "+q8nZQEAML2VArmB",
            "key_meta_data": {
                "key_material_origin": "Origin_RAW",
                "key_material_type": "SYMMETRIC",
                "type": "AES-SIV-CMAC-128",
                "value": "t1WFzzIc3K1CRRaQzbe/xJwmCS9g+FTnLUMkTFViCj0="},
            "output_prefix_type": "PREFIX_ALIYUN_MAXCOMPUTE",
            "status": "ENABLED"}],
    "primary_key_id": "+q8nZQEAML2VArmB"} |
+-----+

      • Allow the system to automatically produce a new key and configure the new key as the master key. 

        select  KEYSET_TO_JSON(
  				ROTATE_KEYSET(
  					NEW_KEYSET('AES-GCM-256', 'my first keyset'), 
  			 		'AES-GCM-256')
  			);

        The following result is returned: 

        +-----+
| _c0 |
+-----+
| {
    "key": [{
            "description": "my first keyset",
            "key_id": "TbEnZQEAUIEJC7mB",
            "key_meta_data": {
                "key_material_origin": "Origin_ALIYUN_MAXCOMPUTE",
                "key_material_type": "SYMMETRIC",
                "type": "AES-GCM-256",
                "value": "TLAKX8y0/aUbMAtElI+oicEw1fWSTJhZs1D2i3AAf40="},
            "output_prefix_type": "PREFIX_ALIYUN_MAXCOMPUTE",
            "status": "ENABLED"},
        {
            "key_id": "TbEnZQEAAIy0IrmB",
            "key_meta_data": {
                "key_material_origin": "Origin_ALIYUN_MAXCOMPUTE",
                "key_material_type": "SYMMETRIC",
                "type": "AES-GCM-256",
                "value": "jPewQsmbsajzM/gLNX9QFtENs2n9uvhgrgcrcGgl0A0="},
            "output_prefix_type": "PREFIX_ALIYUN_MAXCOMPUTE",
            "status": "ENABLED"}],
    "primary_key_id": "TbEnZQEAAIy0IrmB"} |
+-----+

    • Example 4: Encrypt and decrypt data.

      1. Create a table named mf_keyset and write a keyset to the table. 

          --- Create a table.
create table mf_keyset (id string,ks binary);
--- Create a keyset and store the keyset in the mf_keyset table.
insert into mf_keyset select '1',NEW_KEYSET('AES-GCM-256', 'my first keyset');

      2. Encrypt and decrypt data in the tel column of the mf_test_data table.

        • Encrypt data in the tel column of the mf_test_data table. 

          select id,
       name,
       ENHANCED_SYM_ENCRYPT(ks,tel) as tel 
  from (SELECT id,name,tel FROM mf_test_data) JOIN 
       (SELECT ks FROM mf_keyset WHERE  id = '1') a ;

          The following result is returned: 

          +----+------+------+
| id | name | tel  |
+----+------+------+
| 1  | kyle | =0B=88=A5=0Ak=AD=E0=9A=B4=EC=CC=1F=F9=DEk\=16=0F=BD=F1=03=8B=95=F0@=88s=EE=1E=8A=D2=05=83=B5'e=01=00=A0=C5=0BXu=A5Z&<=01=F8=C5Q=89=A9=A6=80=E2=0F=1A)=02fa=CF=07'=1B'=EB=FD=CF=E9 |
| 2  | tom  | =0B=88=A5=0Ak=AD=E0=9A=B4=EC=CC=1F=F9=DEk\=16=0F=BD=F1=03=8B=95=F0@=88s=EE=1E=8A=D2=05=83=B5'e=01=00=10=FC=0BXu=A5+3=D5=1Fb=C0=88=AC=90=AA=FE!C=F0=99y&=9C=89=0E=9B=8FD=16=E0=96, |
+----+------+------+

        • Decrypt data in the tel column of the mf_test_data table. 

          select id,
       name,
       ENHANCED_SYM_DECRYPT(ks,
                            ENHANCED_SYM_ENCRYPT(ks,tel),
                            '')as tel 
  from (SELECT id,name,tel FROM mf_test_data) JOIN 
       (SELECT ks FROM mf_keyset WHERE  id = '1') a ;

          The following result is returned: 

          +----+------+------+
| id | name | tel  |
+----+------+------+
| 1  | kyle | 13900001234 |
| 2  | tom  | 13900001111 |
+----+------+------+

    Use keysets together with KMS

    MaxCompute allows you to use keysets together with Key Management Service (KMS). The system automatically generates a basic keyset for data encryption and decryption. You can use the generated basic keyset together with a KMS key to generate a wrapped keyset. You must store the wrapped keyset for subsequent use. When you need to encrypt or decrypt data, you can use the KMS key to convert the wrapped keyset into the basic keyset, and use the basic keyset to encrypt or decrypt data.

    The following figure shows how to use keysets together with KMS to encrypt or decrypt data.

    keyset.jpg

    Activate KMS and grant permissions on KMS

    1. Activate KMS and purchase an instance of the software key management type. For more information, see Purchase and enable a KMS instance.

    2. Create a key and obtain the Alibaba Cloud Resource Name (ARN) of the key on the Key Details page. For more information, see the "Software-protected key" section in Getting started with Key Management.

      In this example, the ARN of the key is acs:kms:cn-beijing:189273228874****:key/key-bjj6527b6c6465hsf****.

    3. Grant permissions on KMS to MaxCompute. This allows MaxCompute to use KMS to encrypt and decrypt data.

      Note

      After you use a KMS key to encrypt a keyset in MaxCompute, you must grant permissions on KMS-based data encryption and decryption to MaxCompute. This ensures that subsequent commands for data encryption and decryption can be successfully run in MaxCompute.

      Main operation principles:

      1. Create a RAM role. Select Alibaba Cloud Service for Select Trusted Entity, and select MaxCompute from the Select Trusted Service drop-down list. For more information, see Create a RAM role for a trusted Alibaba Cloud service.

      2. Attach the AliyunKMSCryptoUserAccess policy to the new RAM role. This allows MaxCompute to encrypt or decrypt data by using KMS. For more information, see Grant permissions to a RAM role.

        • In this example, you complete the preceding operation by using the Alibaba Cloud account USER_A. After the policy is attached, you can use the Alibaba Cloud account USER_A to encrypt a keyset as expected.

        • In this example, a role named mf-secr is created, and the ARN of the mf-secr role is acs:ram::189273228874****:role/mf-secr.

    4. Optional. If you want to use a RAM user of USER_A or another account such as USER_B or a RAM user of USER_B to encrypt and decrypt data, you must perform the following authorization operations:

      Authorize a RAM user of USER_A to encrypt and decrypt data

      Create a policy that includes the PassRole permission, attach the policy to the RAM role mf-secr, and then assign the RAM role to the RAM user. This way, the RAM user can assume the RAM role mf-secr. For more information, see Grant permissions to a RAM user. The policy document contains the following content: 

      {
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "ram:PassRole",
            "Resource": "acs:ram:*:*:role/mf-secr"
        }
    ],
    "Version": "1"
}

      Authorize USER_B or a RAM user of USER_B to encrypt and decrypt data

      Important

      A wrapped keyset is used to encrypt and decrypt data in cross-account scenarios. In these scenarios, the role_chain parameter in relevant functions is required. Example: 'acs:ram::188538605451****:role/b-secr,acs:ram::189273228874****:role/mf-secr2b'.

      Operation principles:

      1. Create a RAM role named mf-secr2b for the Alibaba Cloud account USER_A. Select Alibaba Cloud Account for Select Trusted Entity, select Other Alibaba Cloud Account for Select Trusted Alibaba Cloud Account, and then enter USER_B in the field that appears. For more information, see Create a RAM role for a trusted Alibaba Cloud account.

      2. Grant the PassRole permission of the mf-secr role to the mf-secr2b role. For the sample policy document, see Authorize a RAM user of USER_A to encrypt and decrypt data in this topic.

      3. Create a RAM role named b-secr for the Alibaba Cloud account USER_B. Select Alibaba Cloud Service for Select Trusted Entity, and select MaxCompute from the Select Trusted Service drop-down list. For more information, see Create a RAM role for a trusted Alibaba Cloud service.

      4. Grant the AssumeRole permission of the mf-secr2b role to the b-secr role. The policy document contains the following content: 

        {
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "acs:ram:*:189273228874****:role/mf-secr2b"
        }
    ]
}

      5. Optional. If you use the wrapped keyset created by USER_A to encrypt and decrypt data as the RAM user of USER_B, grant the PassRole permission of the b-secr role to the RAM user of USER_B. The policy document contains the following content: 

        {
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "ram:PassRole",
            "Resource": "acs:ram:*:*:role/b-secr"
        }
    ],
    "Version": "1"

    Use keysets together with KMS to encrypt and decrypt data

    • Example 1: Create a wrapped keyset. 

      select hex(NEW_WRAPPED_KEYSET('acs:kms:cn-beijing:189273228874****:key/key-bjj6527b6c6465hsf****', 
                              'acs:ram::189273228874****:role/mf-secr', 'AES-GCM-256', 'hello'));

      The following result is returned: 

      +-----+
| _c0 |
+-----+
| 613256354C574A71616A59314D6A64694E6D4D324E44593161484E6D65576732595331315A586435646D4678596A6C36555437693738632B647070524B46334E5263497567384A6B36716C4E54354133734F6E4776376258453132524E6C4B6E5163703859787132536C6D794F6E4A6975797541745448374F6B5132412B4B6947502F4A7530756477694B736B503033576643726E45646D666F6F324D61774F7A452F4F55314A51643268397941384F6B425644665A59746D3968775576536E543744684276723773464A714A47394D7770765064484B6A457A6F4A3533716E57766F4339634639413348556E433641434A6438444170583275326B2B4177776941487668575279304941445031373461794A2F50773D3D |
+-----+

    • Example 2: Encrypt a wrapped keyset. 

      select REWRAP_KEYSET('acs:kms:cn-beijing:189273228874****:key/key-bjj6527b6c6465hsf****', 
                'acs:ram::189273228874****:role/mf-secr',  
                     unhex('613256354C574A71616A59314D6A64694E6D4D324E44593161484E6D65576732595331315A586435646D4678596A6C36555437693738632B647070524B46334E5263497567384A6B36716C4E54354133734F6E4776376258453132524E6C4B6E5163703859787132536C6D794F6E4A6975797541745448374F6B5132412B4B6947502F4A7530756477694B736B503033576643726E45646D666F6F324D61774F7A452F4F55314A51643268397941384F6B425644665A59746D3968775576536E543744684276723773464A714A47394D7770765064484B6A457A6F4A3533716E57766F4339634639413348556E433641434A6438444170583275326B2B4177776941487668575279304941445031373461794A2F50773D3D'));

      The following result is returned: 

      +------+
| _c0  |
+------+
| a2V5LWJqajY1MjdiNmM2NDY1aHNmeWg2YS11ZXd5dmFxYjl60Kftgx5o1sQH4kkwRboFSYUmcVKjF1GKF+JU5gKSp3xOF1xjKdb6fGZyNuD4YSAzqNTD7x7j5fzTLW2+9a+8BmS1z3ZP1RjNL6Lp93FAC4NWg/jtggh6WOTXrVoG67/CfzdWro65YDTPZpe52K416gpfW18GXSOzu9q4swMti0UrScl/fTg6eOIMYgoPCfBh9qvXhNSR72J+qXU1vHcyYNQL6UewsBE2suVRFQ=3D=3D |

    • Example 3: Implement key rotation. 

      select  ROTATE_WRAPPED_KEYSET('acs:kms:cn-beijing:189273228874****:key/key-bjj6527b6c6465hsf****', 
                'acs:ram::189273228874****:role/mf-secr',  
                     unhex('613256354C574A71616A59314D6A64694E6D4D324E44593161484E6D65576732595331315A586435646D4678596A6C36555437693738632B647070524B46334E5263497567384A6B36716C4E54354133734F6E4776376258453132524E6C4B6E5163703859787132536C6D794F6E4A6975797541745448374F6B5132412B4B6947502F4A7530756477694B736B503033576643726E45646D666F6F324D61774F7A452F4F55314A51643268397941384F6B425644665A59746D3968775576536E543744684276723773464A714A47394D7770765064484B6A457A6F4A3533716E57766F4339634639413348556E433641434A6438444170583275326B2B4177776941487668575279304941445031373461794A2F50773D3D'), 
                              'AES-GCM-256', 
                              'descrption');

      The following result is returned: 

      +------+
| _c0  |
+------+
| a2V5LWJqajY1MjdiNmM2NDY1aHNmeWg2YS11ZXd5dmFxYjl67ZxGgfbQhsOududp3FuxLFW1qWt7fF2fT2mAqFekH3D/SoooVf1Jgj0dS/3kxHLQImthef+fCca5vRbVYbOeSsjhGI841WhJvYE1KzRuTpV04SpzVimCovlPPiYCm1649Vhkua1/zUu2W0ioCPnXzHIANhoOIXM2mAV+EfuRCjLUtcJhMdCnu+whHwkGXMYugtXmLxZIBHaJNvO9I3tntplTzxElVmj/LpDrAkg0mKahLJa7FhcJ8cn/JHjp9sk0MhHQc/5X14vHBJuulkYkukcF/kZ+AFVfWes5pZOMs8Og3pYEjCESMiiMONy/CpIrYepapgsKqRAmCGxRv/7aDOZyaAV5Jdz31NotMCBi/hrYBwyU0QdAq5pvsOEdXVIJyazViQ=3D=3D |
+------+

    • Example 4: Encrypt and decrypt data.

      1. Create a table named mf_keyset_kms and write a wrapped keyset to the table. 

        ----- Create a table.
create table mf_keyset_kms (id string,ks binary);
----- Create a wrapped keyset and write the wrapped keyset to the table.
insert into mf_keyset_kms 
      select '1',
             NEW_WRAPPED_KEYSET(
                'acs:kms:cn-beijing:189273228874****:key/key-bjj6527b6c6465hsf****', 
                'acs:ram::189273228874****:role/mf-secr', 
                'AES-GCM-256', 
               'description');

        Confirm the write result. 

        ---- Query data from the table.
select id,hex(ks) from mf_keyset_kms;
---- The following result is returned:
+----+------+
| id | ks   |
+----+------+
| 1  | 613256354C574A71616A59314D6A64694E6D4D324E44593161484E6D65576732595331315A586435646D4678596A6C36555437693738632B647070524B46334E5263497567384A6B36716C4E54354133734F6E4776376258453132524E6C4B6E5163703859787132536C6D794F6E4A6975797541745448374F6B5132412B4B6947502F4A7530756477694B736B503033576643726E45646D666F6F324D61774F7A452F4F55314A51643268397941384F6B425644665A59746D3968775576536E543744684276723773464A714A47394D7770765064484B6A457A6F4A3533716E57766F4339634639413348556E433641434A6438444170583275326B2B4177776941487668575279304941445031373461794A2F50773D3D |

      2. Encrypt and decrypt data in the tel column of the mf_test_data table.

        • Encrypt data in the tel column of the mf_test_data table. 

          -- Encrypt data in the tel column. The keyset supports only constants.
select id,
       name,
       ENHANCED_SYM_ENCRYPT(
           USE_WRAPPED_KEYSET('acs:kms:cn-beijing:189273228874****:key/key-bjj6527b6c6465hsf****', 
                              'acs:ram::189273228874****:role/mf-secr', 
                              unhex('613256354C574A71616A59314D6A64694E6D4D324E44593161484E6D65576732595331315A586435646D4678596A6C36555437693738632B647070524B46334E5263497567384A6B36716C4E54354133734F6E4776376258453132524E6C4B6E5163703859787132536C6D794F6E4A6975797541745448374F6B5132412B4B6947502F4A7530756477694B736B503033576643726E45646D666F6F324D61774F7A452F4F55314A51643268397941384F6B425644665A59746D3968775576536E543744684276723773464A714A47394D7770765064484B6A457A6F4A3533716E57766F4339634639413348556E433641434A6438444170583275326B2B4177776941487668575279304941445031373461794A2F50773D3D')
                             ),
           tel
       ) as tel 
 FROM mf_test_data;

          The following result is returned: 

          +----+------+------+
| id | name | tel  |
+----+------+------+
| 1  | kyle | =1A=B88=F7=C5A=DF=DC=91=A6R=F8{=EB=A5_x=AD=AF=FF=9A=14=D81<=8BO=EB=F5=D3Jn=E8=0ESe=01=00=A0=AC=0D=C8=0De=B9=E4=84=AB=F0f=AE~Dt=C5W=FAx=A5=11=01t=95=DF=FA/-=BC7=C5G |
| 2  | tom  | =1A=B88=F7=C5A=DF=DC=91=A6R=F8{=EB=A5_x=AD=AF=FF=9A=14=D81<=8BO=EB=F5=D3Jn=E8=0ESe=01=00=B0=BF=0D=C8=0DejL{;.w0=80,=E6=86V@b=F3=AB=DEY=A6=02=07=001=E0[=E7V |
+----+------+------+

        • Decrypt data in the tel column of the mf_test_data table. 

          select id,
       name,
       ENHANCED_SYM_DECRYPT (
         USE_WRAPPED_KEYSET('acs:kms:cn-beijing:189273228874****:key/key-bjj6527b6c6465hsf****', 
                              'acs:ram::189273228874****:role/mf-secr', 
                              unhex('613256354C574A71616A59314D6A64694E6D4D324E44593161484E6D65576732595331315A586435646D4678596A6C36555437693738632B647070524B46334E5263497567384A6B36716C4E54354133734F6E4776376258453132524E6C4B6E5163703859787132536C6D794F6E4A6975797541745448374F6B5132412B4B6947502F4A7530756477694B736B503033576643726E45646D666F6F324D61774F7A452F4F55314A51643268397941384F6B425644665A59746D3968775576536E543744684276723773464A714A47394D7770765064484B6A457A6F4A3533716E57766F4339634639413348556E433641434A6438444170583275326B2B4177776941487668575279304941445031373461794A2F50773D3D')
                             ),
       	 ENHANCED_SYM_ENCRYPT(
           USE_WRAPPED_KEYSET('acs:kms:cn-beijing:189273228874****):key/key-bjj6527b6c6465hsf****', 
                              'acs:ram::189273228874****:role/mf-secr', 
                              unhex('613256354C574A71616A59314D6A64694E6D4D324E44593161484E6D65576732595331315A586435646D4678596A6C36555437693738632B647070524B46334E5263497567384A6B36716C4E54354133734F6E4776376258453132524E6C4B6E5163703859787132536C6D794F6E4A6975797541745448374F6B5132412B4B6947502F4A7530756477694B736B503033576643726E45646D666F6F324D61774F7A452F4F55314A51643268397941384F6B425644665A59746D3968775576536E543744684276723773464A714A47394D7770765064484B6A457A6F4A3533716E57766F4339634639413348556E433641434A6438444170583275326B2B4177776941487668575279304941445031373461794A2F50773D3D')
                             ),
           tel
       ),
       ''
      )
       as tel 
 FROM mf_test_data;

          The following result is returned: 

          +----+------+------+
| id | name | tel  |
+----+------+------+
| 1  | kyle | 13900001234 |
| 2  | tom  | 13900001111 |
+----+------+------+