Vertica加密存储,有不少小坑,官方文档的介绍都是基于和Voltage对接的基础上的,Voltage是micro focus的另一款产品,既不开源也不提供试用......micro soft在前两年左右的时间和慧与的软件部做了合并,现在的Vertica也属于micro soft的产品。对Vertica加密存储做一个简单记录,另,Vertica的官文提供了TLS/SSL的加密传输,感兴趣的可以去https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/Security/SSL/ImplementingSSL.htm%3FTocPath%3DSecurity%2520and%2520Authentication%7CTLS%252FSSL%2520Server%2520Authentication%7C_____0看看,本文不表。
Vertica数据的加密都是由Voltage SecureData来实现的,access policy只是调用了此功能来实现针对部分用户加密或解密,Voltage SecureData使用的加密算法是FPE。
Vertica对数据的加密需要与Voltage SecureData集成,集成后可以有两种方式实现:
方式一: 在加载数据的时候对数据加密即数据库里存的就是加密数据。
如下例,在copy语句中调用VoltageSecureProtect函数,对某列加密。
CREATE TABLE customers (id INTEGER, first_name VARCHAR, last_name VARCHAR,
ssn VARCHAR(11), cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
CREATE TABLE
=> COPY customers (id, first_name, last_name, ssn_raw FILLER VARCHAR(11),
cc_num_raw FILLER VARCHAR(25), cvv, dob,
ssn AS VoltageSecureProtect(ssn_raw USING PARAMETERS format='ssn',
config_dfs_path='voltage.conf'),
cc_num AS VoltageSecureProtect(cc_num_raw USING PARAMETERS format='cc',
config_dfs_path='voltage.conf'))
FROM '/home/dbadmin/customer_data.csv' DELIMITER ',';
方式二:在create access policy时调用VoltageSecureProtect函数可对数据即时加密,即数据库里存的是未加密的数据。
当用户查看数据并匹配到access policy时,看到的数据会被加密,如下列,
CREATE ACCESS POLICY ON customers FOR COLUMN ssn
CASE
WHEN enabled_role('see_ssn') THEN VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
config_dfs_path='/voltagesecure/conf')
ELSE ssn
END ENABLE;
方式二的加密算法仍然是和Voltage进行对接,可以明显的看到使用了Voltage对应的conf文件。
方式二的修改:继续对方式二进行尝试,即不用Voltage的配置文件进行配置
Customers的数据对应的文本文件
[root@V01 opt]# cat /opt/customer_data.csv
5345,Thane,Ross,559-32-0670,376765616314013,618,05-09-1996
5346,Talon,Wilkins,540-48-0784,4716511603424923,111,09-17-1941
5347,Daquan,Phelps,785-34-0092,342226134491834,294,05-08-1963
5348,Basia,Lopez,011-85-0705,4595818418314603,503,04-29-1940
5349,Kaseem,Hendrix,672-57-0309,4556078737944,693,03-11-1942
5350,Omar,Lott,825-45-0131,6462054107996261,555,02-17-1956
5351,Nell,Cooke,637-50-0105,6465975630903530,818,02-14-1995
5352,Illana,Middleton,831-47-0929,6482364086684267,883,12-29-1949
5353,Garrett,Williamson,408-73-0207,5334270213608370,869,11-06-1955
5354,Hanna,Ware,694-97-0394,5433849419219254,586,08-08-1967
CREATE TABLE customers (id INTEGER, first_name VARCHAR, last_name VARCHAR, ssn VARCHAR(11), cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
MyVertica=> CopY customers from '/opt/customer_data.csv' DELIMITER ',' DIRECT;
MyVertica=> DROP ACCess policy on customers For column ssn;
MyVertica=> create ACCESS POLICY ON customers
MyVertica-> for column ssn
MyVertica-> case
MyVertica-> when ENABLED_ROLE('manager') THEN ssn
MyVertica-> when ENABLED_ROLE('operator') THEN SUBSTR(ssn,0,4)
MyVertica-> ELSE NULL
MyVertica-> END
MyVertica-> ENABLE;
CREATE ACCESS POLICY
MyVertica=> create rolE manager;
CREATE ROLE
MyVertica=> create rolE operator;
CREATE ROLE
MyVertica=> create usER testa;
CREATE USER
MyVertica=> create usER testb;
CREATE USER
MyVertica=> create usER testc;
CREATE USER
MyVertica=> graNT manager to testa;
GRANT ROLE
MyVertica=> graNT operator to testb;
GRANT ROLE
MyVertica=> granT all on customers to tes
testa testb
MyVertica=> granT all on customers to testa;
GRANT PRIVILEGE
MyVertica=> granT all on customers to testb;
GRANT PRIVILEGE
MyVertica=> grANT all on customers to testc;
GRANT PRIVILEGE
正常数据查看显示如下:
查看创建Access Policy的语句可知,对于赋予manager角色权限的用户testa,可以看到ssn该字段原本的内容,对于赋予operator角色权限的用户testb,可以看到ssn字段的前三个字符,验证如下:
testa用户查看为原本的信息内容
testb查看为前三个字符
testc查看为null
至此,权限简单验证完毕。