Vertica加密存储(基于Vertica9.1.0)

       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

至此，权限简单验证完毕。

参考：Vertica的这些事<八>—— vertica加密数据