全部产品
Search
文档中心

云原生大数据计算服务 MaxCompute:SYM_ENCRYPT

更新时间:Jun 15, 2023

对表里的指定列做随机性加密,并返回加密后的BINARY类型密文。

注意事项

升级到MaxCompute 2.0后,产品扩展了部分函数。如果您用到的函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展函数时,需要执行如下语句开启新数据类型开关:

  • Session级别:如果使用新数据类型,您需要在SQL语句前加上语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。

  • Project级别:Project Owner可根据需要对Project进行设置,等待10~15分钟后才会生效。命令如下。

    setproject odps.sql.type.system.odps2=true;

    setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项,请参见数据类型版本说明

命令格式

binary sym_encrypt(string|binary <value_to_encrypt>,
                   binary <key>
                   [,string <encryption_method> ,
                      [ string <additional_authenticated_data> ]
                    ]
                  )

参数说明

  • value_to_encrypt:必填。待加密数据。目前只支持STRING和BINARY类型的数据进行加密。

  • key:必填。加密使用的密钥。支持的数据类型为BINARY,长度为256bits。

  • encryption_method:可选。加密模式选择。目前只提供了一种加密模式,即AES-GCM-256加密算法,默认使用AES-GCM-256。

  • additional_authenticated_data:可选。附加身份验证数据AAD,可以用来验证数据的真实性和完整性。目前只支持AEAD的加密算法,比如AES GCM才支持AAD。

返回值说明

返回BINARY类型的密文。

  • 返回值中按顺序包含初始向量(IV),密文,带附加数据的加密和验证算法标签(AEAD Tag)。

  • 相同的明文和密码,每次生成的密文也是随机的不同的。

示例数据

--创建表
create table mf_user_info(id bigint,
                          name string,
                          gender string,
                          id_card_no string,
                          tel string);
--插入数据
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");
--查询数据
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|
+------------+------+--------+------------+------------+

示例1:基于明文密钥加密

基于示例数据,对表mf_user_info中的列进行加密。命令如下:

  • 使用AEAD加密算法进行加密。

    --对id_card_no加密
    insert overwrite table mf_user_info
    select id,
        name,
        gender,
           base64(sym_encrypt(id_card_no,
                       cast('b75585cf321cdcad42451690cdb7bfc4' as binary)
                      ))as id_card_no,
          tel
    from mf_user_info;
    
    select * from mf_user_info;

    返回结果:

    +------------+------+--------+------------+-----+
    | id         | name | gender | id_card_no | tel |
    +------------+------+--------+------------+-----+
    | 1          | bob  | male   | frgJZAEAQMeEuHqpS8lK9VxQhgPYpZ317V+oUla/xEc= | 13900001234|
    | 2          | allen| male   | frgJZAIAQMeEuHqpLeXQfETsFSLJxBwHhPx6tpzWUg4= | 13900001111|
    | 3          | kate | female | frgJZAMAQMeEuHqpdphXAU6iWelWenlDnVy+R0HMvAY= | 13900002222|
    | 4          | annie| female | frgJZAQAQMeEuHqpR5c8bj21dYCeM0C25bLRZIrP71c= | 13900003333|
    +------------+------+--------+------------+-----+
  • 使用AAD加密算法进行加密。

    --对id_card_no加密
    insert overwrite table mf_user_info
    select id,
                 name,
                 gender,
           base64(sym_encrypt(id_card_no,
                  cast('b75585cf321cdcad42451690cdb7bfc4' as binary),
                  'AES-GCM-256',
                  'test'
                  ))as id_card_no,
          tel
    from mf_user_info;
    select * from mf_user_info;

    返回结果:

    +------------+------+--------+------------+-----+
    | id         | name | gender | id_card_no | tel |
    +------------+------+--------+------------+-----+
    | 1          | bob  | male   | frgJZAEAQMeEuHqpS8lK9VxQhgPYpZ317V+oUla/xEc= | 13900001234|
    | 2          | allen| male   | frgJZAIAQMeEuHqpLeXQfETsFSLJxBwHhPx6tpzWUg4= | 13900001111|
    | 3          | kate | female | frgJZAMAQMeEuHqpdphXAU6iWelWenlDnVy+R0HMvAY= | 13900002222|
    | 4          | annie| female | frgJZAQAQMeEuHqpR5c8bj21dYCeM0C25bLRZIrP71c= | 13900003333|
    +------------+------+--------+------------+-----+

示例2:基于密钥表加密

基于示例数据,借助构造密钥表,实现密钥管理。

  • 原理。

    • 对于数据加密场景,您可以将密钥存储在MaxCompute表中,在进行数据加密时,通过JOIN加密表的方式,完成数据加密。这样可以避免由于在查询中直接传递密钥导致的泄漏问题。

    • 在数据解密时,项目管理员不要直接将密钥(表)授权给数据使用方,而是应该创建一个(secure)view,在view内部通过访问解密表并调用解密函数,完成数据解密。通过控制view权限来控制数据使用方对数据明文的访问。同时在view中不包含密钥明文,避免密钥泄漏。

  • 注意事项。

    • 整套方案密钥的创建管理都来自客户,需要您自行保存关键密钥,平台不保存密钥以及密钥与密文的直接对应关系。密钥一旦丢失,会导致无法解密数据。

    • 明文密钥参数传递存在泄漏风险,需要您在使用过程中进行特殊处理。

    • 涉及BINARY类型,可能需要使用MaxCompute 2.0,请使用set odps.sql.type.system.odps2=true;开启2.0数据类型开关。

命令如下:

--创建密钥表
create table mf_id_key(id bigint,key binary);
--插入一个密钥
insert overwrite table mf_id_key
  values (1,cast('b75585cf321cdcad42451690cdb7bfc4' as binary));
--查询密钥
select * from mf_id_key;
+------------+------+
| id         | key  |
+------------+------+
| 1          | b75585cf321cdcad42451690cdb7bfc4 |
+------------+------+

--查询需要加密的表数据
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|
+------------+------+--------+------------+------------+

--对目标表的列进行加密
insert overwrite table mf_user_info
select /*+mapjoin(b)*/
      a.id,
      a.name,
      a.gender,
      base64(
        (sym_encrypt(a.id_card_no, b.key))
      ) as id_card_no,
      a.tel
 from mf_user_info as a join mf_id_key as b on a.id>=b.id;

--查询加密后的数据
select * from mf_user_info;

返回结果:

+------------+------+--------+------------+-----+
| id         | name | gender | id_card_no | tel |
+------------+------+--------+------------+-----+
| 1          | bob  | male   | 9esKZAEAoBquXVJo3ZptvoI09XuM4bSFTqF1mXH1BO4= | 13900001234|
| 2          | allen| male   | 9esKZAIAoBquXVJoJYqnXieAANih7FR59luePvdHB9U= | 13900001111|
| 3          | kate | female | 9esKZAMAoBquXVJoppwxgVwPYBnvjIMklWLmJ/sU0Y8= | 13900002222|
| 4          | annie| female | 9esKZAQAoBquXVJoB85RUFCLMbdyEBSz7LdS4M3Guvk= | 13900003333|
+------------+------+--------+------------+-----+

相关函数

SYM_ENCRYPT函数属于加密函数,更多加密、解密的相关函数请参见加密函数