本文为您介绍如何使用ENHANCED_SYM_ENCRYPT函数
指定密钥集进行数据加密。
背景与前提
MaxCompute支持使用ENHANCED_SYM_ENCRYPT
函数指定生成的基础密钥集或封装密钥集对数据进行加密。封装密钥集是通过KMS密钥对生成的密钥集(KEYSET)再次进行加密,结合密钥管理服务做密钥管理,相比基础密钥集会更加安全。
使用ENHANCED_SYM_ENCRYPT
函数前需要完成以下操作:
已有通过
NEW_KEYSET
或NEW_WRAPPED_KEYSET
函数生成的基础密钥集或封装密钥集,详情请参见NEW_KEYSET、NEW_WRAPPED_KEYSET。如果使用封装密钥集加密数据,需要先使用
USE_WRAPPED_KEYSET
函数获取基础密钥集,基础密钥集作为ENHANCED_SYM_ENCRYPT
函数的参数进行数据加密。同时需要有使用封装密钥集对应角色的权限,操作详情请参见开通KMS并完成配置。
命令格式
binary ENHANCED_SYM_ENCRYPT(binary <keyset> , string|binary <plaintext> [,string <additional_data>])
参数说明
keyset:必填,用户密钥集, 类型为BINARY或者封装密钥集的STRUCT类型。
plaintext:必填,待加密的STRING或BINARY类型的明文。
additional_data: 可选,算法支持的STRING类型验证信息。
返回值说明
返回BINARY类型的密文。
示例数据
--创建表
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|
+------------+------+--------+------------+------------+
使用示例
使用基础密钥集对
mf_user_info
表的id_card_no
列加密: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;
查询加密结果示例如下:
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 | +------------+------+--------+------------+-----+
使用封装密钥集对
mf_user_info
表的tel
列加密:生成封装密钥集并写入表中:
--创建表并把加密后的keyset写入 create table mf_keyset_kms (id string,ks binary); --生产keyset加密后写入表 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'); --查询表 select id,hex(ks) from mf_keyset_kms; --返回结果示例 +----+-----+ | id | _c1 | +----+-----+ | 1 | 613256354C576836656A59314D6D59344E7A6B7A624452754D6D3434627A49786443317A655859786358426F4E6A4D78447654524C4632635077766E74554654584579715242583953724167446D2F397131786F57456E6F5474516739633853766242674456773565736674714A4D5435524455382F6F6A2B4E61766D774344494C734B6A416B6B675A42496F5568656F566D38564C4F30506D4778767137646956517453447A5467395147775639533161305A464A6D6A45562B6742722F56386653444D6E424D2B71493779784668303866594E6D336578775744423949726B645A3469784F2B532B476E6750523854524A58326E5768666478347034473468687248684A514D615071332F526C342B67427652773D3D | +----+-----+
使用封装密钥集对
tel
列加密: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;
返回结果示例如下:
+------------+------+--------+------------+------+ | 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 | +------------+------+--------+------------+------+
文档内容是否对您有帮助?