云小课｜使用SQL加密函数实现数据列的加解密
摘要：数据加密作为有效防止未授权访问和防护数据泄露的技术，在各种信息系统中广泛使用。作为信息系统的核心，GaussDB(DWS)数仓也提供数据加密功能，包括透明加密和使用SQL函数加密。
本文分享自华为云社区《看GaussDB(DWS)如何使用SQL加密函数实现数据列加解密》，作者：Hello EI。
数据加密作为有效防止未授权访问和防护数据泄露的技术，在各种信息系统中广泛使用。作为信息系统的核心，GaussDB(DWS)数仓也提供数据加密功能，包括透明加密和使用SQL函数加密。这里主要讨论SQL函数加密。
GaussDB(DWS)目前不支持从Oracle、Teradata和MySQL加密后到DWS解密。Oracle、Teradata和MySQL与DWS加解密有区别，需要非加密数据迁移到DWS后在DWS侧进行加解密。
技术背景
- 哈希函数
哈希函数又称为摘要算法，对于数据data，Hash函数会生成固定长度的数据，即Hash(data)=result。这个过程是不可逆的，即Hash函数不存在反函数，无法由result得到data。在不应保存明文场景（比如口令password属于敏感信息），系统管理员用户也不应该知道用户的明文口令，就应该使用哈希算法存储口令的单向哈希值。
实际使用中会加入盐值和迭代次数，避免相同口令生成相同的哈希值，以防止彩虹表攻击。
图1 哈希函数
- 对称密码算法
对称密码算法使用相同的密钥来加密和解密数据。对称密码算法分为分组密码算法和流密码算法。
分组密码算法将明文分成固定长度的分组，用密钥对每个分组加密。由于分组长度固定，当明文长度不是分组长度的整数倍时，会对明文做填充处理。由于填充的存在，分组密码算法得到的密文长度会大于明文长度。
流加密算法是指加密和解密双方使用相同伪随机加密数据流作为密钥，明文数据依次与密钥数据流顺次对应加密，得到密文数据流。实践中数据通常是一个位（bit）并用异或（xor）操作加密。流密码算法不需要填充，得到的密文长度等于明文长度。
图2 对称密码算法
技术实现
GaussDB(DWS)主要提供了哈希函数和对称密码算法来实现对数据列的加解密。哈希函数支持sha256，sha384，sha512和国密sm3。对称密码算法支持aes128，aes192，aes256和国密sm4。
哈希函数
- md5(string)
将string使用MD5加密，并以16进制数作为返回值。MD5的安全性较低，不建议使用。
- gs_hash(hashstr, hashmethod)
以hashmethod算法对hashstr字符串进行信息摘要，返回信息摘要字符串。支持的hashmethod：sha256, sha384, sha512, sm3。
SELECT gs_hash('GaussDB(DWS)', 'sha256');
gs_hash
--------------------------------------------------------------------------------------------------
e59069daa6541ae20af7c747662702c731b26b8abd7a788f4d15611aa0db608efdbb5587ba90789a983f85dd51766609
(1 row)对称密码算法
- gs_encrypt(encryptstr, keystr, cryptotype, cryptomode, hashmethod)
采用cryptotype和cryptomode组成的加密算法以及hashmethod指定的HMAC算法，以keystr为密钥对encryptstr字符串进行加密，返回加密后的字符串。
SELECT gs_encrypt('GaussDB(DWS)', '1234', 'aes128', 'cbc', 'sha256');
gs_encrypt
--------------------------------------------------------------------------------------------------------------------------
AAAAAAAAAACcFjDcCSbop7D87sOa2nxTFrkE9RJQGK34ypgrOPsFJIqggI8tl+eMDcQYT3po98wPCC7VBfhv7mdBy7IVnzdrp0rdMrD6/zTl8w0v9/s2OA==
(1 row)- gs_decrypt(decryptstr, keystr，cryptotype, cryptomode, hashmethod)
采用cryptotype和cryptomode组成的加密算法以及hashmethod指定的HMAC算法，以keystr为密钥对decryptstr字符串进行解密，返回解密后的字符串。解密使用的keystr必须保证与加密时使用的keystr一致才能正常解密。
SELECT gs_decrypt('AAAAAAAAAACcFjDcCSbop7D87sOa2nxTFrkE9RJQGK34ypgrOPsFJIqggI8tl+eMDcQYT3po98wPCC7VBfhv7mdBy7IVnzdrp0rdMrD6/zTl8w0v9/s2OA==', '1234', 'aes128', 'cbc', 'sha256');
gs_decrypt
--------------
GaussDB(DWS)
(1 row)- gs_encrypt_aes128(encryptstr,keystr)
以keystr为密钥对encryptstr字符串进行加密，返回加密后的字符串。keystr的长度范围为1~16字节。
SELECT gs_encrypt_aes128('MPPDB','1234');
gs_encrypt_aes128
-------------------------------------------------------------------------------------
gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=
(1 row)- gs_decrypt_aes128(decryptstr,keystr)
以keystr为密钥对decryptstr字符串进行解密，返回解密后的字符串。解密使用的keystr必须保证与加密时使用的keystr一致才能正常解密。keystr不得为空。
SELECT gs_decrypt_aes128('gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=','1234');
gs_decrypt_aes128
-------------------
MPPDB
(1 row)应用示例
创建表student，有id，name和score三个属性。使用哈希函数加密保存name，使用对称密码算法保存score。
CREATE TABLE student (id int, name text, score text, subject text);
CREATE TABLE
INSERT INTO student VALUES (1, gs_hash('alice', 'sha256'), gs_encrypt('95', '12345', 'aes128', 'cbc', 'sha256'),gs_encrypt_aes128('math', '1234'));
INSERT 0 1
INSERT INTO student VALUES (2, gs_hash('bob', 'sha256'), gs_encrypt('92', '12345', 'aes128', 'cbc', 'sha256'),gs_encrypt_aes128('english', '1234'));
INSERT 0 1
INSERT INTO student VALUES (3, gs_hash('peter', 'sha256'), gs_encrypt('98', '12345', 'aes128', 'cbc', 'sha256'),gs_encrypt_aes128('science', '1234'));
INSERT 0 1不使用密钥查询表student，通过查询结果可知：没有密钥的用户即使拥有了select权限也无法看到name和score这两列加密数据。
SELECT * FROM STUDENT;
id | name | score |
subject
----+------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+------------
----------------------------------------------------------------------------------
2 | 81b637d8fcd2c6da6359e6963113a1170de795e4b725b84d1e0b4cfd9ec58ce9 | AAAAAAAAAACCWNznqIVSGYgcuDz9jNKTHTd35+Jmhd/8j6zRLTfAa+Yl448SxNUsDTOBtW4w2ePmnqwf2FfbfsF3hYYlOlCQV/BSv2M3fQKKUwc0Ytunug== | Ti4Shb5N511
imwH8ugtiveRiSF6j7SC8OyUK/DQRPRRwwW9MFXPnGbG6jOMhMSMpKiz3NoEGOaT384aywVgI31MS3Z8=
3 | 026ad9b14a7453b7488daa0c6acbc258b1506f52c441c7c465474c1a564394ff | AAAAAAAAAAClDZIa1LfJuG+2dHpbnxn7VwHkCFuHChKErh069OHnMR+rhpWk0TxWlRfq8NIjX+590C3MqhPtha0ERbHbDpr5z8XVMkLgyAOQzJf6XtXvOA== | ijHXT/z94Zf
MKQ0k7fYDCUML7ZeU15tLXQreBwp0borh/pgB4ifh8j032v7IiENbHqnRdfRDgkrQDHswu5ZDb3Op3vE=
1 | 2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac4f93af71db186d6e90 | AAAAAAAAAACQF3OcOktEKkPEL6G/AKL7DVA9WeXiNOwPPcXZuk49GZq0mrtR1ebqIiZBCvmGJ4wqoa7WEo3w8PRw+CK1oFP8J3b51ZZTVf1HD3nS46uEeg== | Sq5Zi0Yhg6h
/hEcLD8bJqmpGKVkr0Ke4SKHqf7xBMqWflZjXeFvE9s7CUMvXzJ0uSg7P5Ta1CT4sm0vvB1fc+84o+7o=
(3 rows)使用密钥查询表student，通过查询结果可知：拥有密钥的用户通过使用gs_encrypt对应的解密函数gs_decrypt解密后，可以查看加密数据。
SELECT id, gs_decrypt(score, '12345', 'aes128', 'cbc', 'sha256'),gs_decrypt_aes128(subject, '1234') FROM STUDENT;
id | gs_decrypt | gs_decrypt_aes128
----+------------+-------------------
2 | 92 | english
3 | 98 | science
1 | 95 | math
(3 rows)
