Using Access Policies in Vertica to Hide Sensitive Data

Amaksh

[This information is provided by @Ravi ]

You can use access policies in Vertica to show only limited data to users. We took an example of Credit Card numbers and hid few characters to protect sensitive data.

Let us create a table with two database users and provide necessary permissions to those users.

$ vsql -U dbadmin -w password 
dbadmin=> DROP TABLE customers_table CASCADE;
dbadmin=> CREATE TABLE customers_table  ( 
            ID      INT, 
            Name    VARCHAR(20), 
            CARDNUM VARCHAR(20)
          );
INSERT INTO customers_table VALUES ( 1, 'Tom','1234567809094321');
INSERT INTO customers_table VALUES ( 2, 'Jerry','4321567809096789');
COMMIT;

-- create users 
dbadmin=> CREATE user mgr identified by 'password';
dbadmin=> CREATE user opt identified by 'password';
dbadmin=> GRANT USAGE ON SCHEMA public to mgr,opt;
dbadmin=> GRANT SELECT on public.customers_table to mgr,opt;
-- create Roles and grant to users
dbadmin=> CREATE ROLE manager;
dbadmin=> CREATE ROLE operator;
dbadmin=> grant manager to mgr;
dbadmin=> grant operator to opt;
-- give default role operator to opt user only -- 
dbadmin=> alter user opt default role operator;

-- create Colum level Access Policy 

dbadmin=> CREATE ACCESS POLICY ON customers_table FOR COLUMN CARDNUM
    CASE
      WHEN ENABLED_ROLE('manager') THEN CARDNUM
      WHEN ENABLED_ROLE('operator') THEN 
          ( SUBSTR(CARDNUM, 1, 4)||'-XXXX-XXXX-'||SUBSTR(CARDNUM, 13, 4) )
      ELSE NULL
END ENABLE;

dbadmin=> \q

-- Login again and see what dbadmin user can see. 
$ vsql -U dbadmin -w password 
dbadmin=> SELECT * FROM  public.customers_table;
 ID | Name  | CARDNUM
----+-------+---------
  1 | Tom   |
  2 | Jerry |
(2 rows)
dbadmin=> \q

Even the admin user dbadmin cannot see the full credit card number. Now, let us see what values are visible to user mgr:

$ vsql -U mgr -w password
mgr=>
mgr=> SELECT * FROM  public.customers_table;
 ID | Name  | CARDNUM
----+-------+---------
  1 | Tom   |
  2 | Jerry |
(2 rows)
mgr=> set role manager;
SET
mgr=> SELECT * FROM  public.customers_table;
 ID | Name  |     CARDNUM
----+-------+------------------
  1 | Tom   | 1234567809094321
  2 | Jerry | 4321567809096789
(2 rows)

mgr=> \q

Let us see what values are visible to user opt:

$ Vsql -U opt -w password
opt=>
opt=> SELECT * FROM  public.customers_table;
 ID | Name  |       CARDNUM
----+-------+---------------------
  1 | Tom   | 1234-XXXX-XXXX-4321
  2 | Jerry | 4321-XXXX-XXXX-6789
(2 rows)

opt=> \q

Depending on the roles, users can either see the full credit card number or masked data.