Simple data hiding demo using RBAC in SingleStore

!SingleStore Data Hiding Demo.md

This is an example of how you can use RBAC and the CURRENT_SECURITY_GROUPS function to implement data hiding in SingleStore.

1. Get a SingleStore free license key from https://portal.singlestore.com
2. Set your SingleStore license key as an environment variable

export LICENSE_KEY="PUT YOUR LICENSE KEY HERE"

3. Run SingleStore cluster in a box

docker run -it \
  --name memsql-ciab \
  -e LICENSE_KEY=$LICENSE_KEY \
  -e ROOT_PASSWORD='p' \
  -p 3306:3306 -p 8080:8080 \
  memsql/cluster-in-a-box

4. Download and run hiding.sql

mysql -u root -h 127.0.0.1 -pp < hiding.sql
This is BOB, the operator.  He can only see the last 4 of the social security number
+-------------+-------+---------+---------------------+-------------+
| customer_id | fname | lname   | dob                 | ssnumber    |
+-------------+-------+---------+---------------------+-------------+
|           2 | dale  | deloy   | 2000-10-01 00:00:00 | xxx-xx-1234 |
|           1 | matt  | demarco | 2000-01-31 00:00:00 | xxx-xx-6789 |
+-------------+-------+---------+---------------------+-------------+
---
This is ALICE, the manager.  She can the ENTIRE social security number
+-------------+-------+---------+---------------------+-------------+
| customer_id | fname | lname   | dob                 | ssnumber    |
+-------------+-------+---------+---------------------+-------------+
|           2 | dale  | deloy   | 2000-10-01 00:00:00 | 198-12-1234 |
|           1 | matt  | demarco | 2000-01-31 00:00:00 | 123-45-6789 |
+-------------+-------+---------+---------------------+-------------+

hiding.sql

create database if not exists demo;
use demo;

drop view if exists data_table;
drop table if exists base_table;

create table if not exists base_table
(
    customer_id     bigint primary key,
    fname           longtext,
    lname           longtext,
    dob             datetime,
    ss_number       longtext
);

create view data_table as select
    customer_id, fname, lname, dob,
    case
        when CURRENT_SECURITY_GROUPS() = 'operator' then
            concat('xxx-xx-',right(ss_number,4))
        when CURRENT_SECURITY_GROUPS() = 'manager' then
            concat_ws('-',substring(ss_number,1,3),substring(ss_number,4,2),substring(ss_number,6,4))
        else 'Access Denied'
    end ssnumber
from base_table;

/* create roles and groups */
drop role if exists 'manager_role';
drop role if exists 'operator_role';

create role 'manager_role';
create role 'operator_role';

grant select on demo.data_table to role 'manager_role';
grant select on demo.data_table to role 'operator_role';

drop group if exists 'manager';
drop group if exists 'operator';

create group 'manager';
create group 'operator';

grant role 'manager_role' to 'manager';
grant role 'operator_role' to 'operator';

/* create users and grant groups */
drop user if exists 'alice';
create user 'alice';
grant group 'manager' to 'alice';

drop user if exists 'bob';
create user 'bob';
grant group 'operator' to 'bob';

/* insert example data */
insert into base_table (customer_id, fname, lname, dob, ss_number)
values
    (1,'matt','demarco','2000-01-31','123456789'),
    (2,'dale','deloy','2000-10-01','198121234');

\! echo "This is BOB, the operator.  He can only see the last 4 of the social security number"
\! mysql demo -h 127.0.0.1 -ubob -e 'select * from data_table' --table

\! echo "---"

\! echo "This is ALICE, the manager.  She can the ENTIRE social security number"
\! mysql demo -h 127.0.0.1 -ualice -e 'select * from data_table' --table