Implementing Row Level Security in SAS SpeedyStore
Implementing Row Level Security in SAS SpeedyStore
A Practical Guide to Secure Data Access in Analytics Workflows and Visualizations
Overview of SAS SpeedyStore
SAS SpeedyStore seamlessly integrates SAS Viya’s advanced analytical, machine learning, and AI capabilities with SingleStore’s high-performance database. By combining these platforms, SAS SpeedyStore empowers organisations to analyse large-scale data with speed, flexibility, and robust security. Data scientists and business users benefit from direct access to SingleStore’s scalable data storage within SAS Viya’s intuitive user interfaces, enabling secure, real-time analytics while maintaining fine-grained control over data access and compliance. This integrated bundle delivers a unified experience for efficient, secure, and scalable analytics workflows.
Introduction
In this Guide we will use the table Insight Toys (~800 000 rows and 59 columns), which contains sales, customer and product related information. It also contains regional/geographical information in the columns FacilityContinent and FacilityCountry, which will be used to restrict Managers’ and Users’ access to rows.
We will create 4 users with different profiles and access restrictions.
Row Level Security Concept in SingleStore
In SingleStore there are Users, Groups and Roles. Row Level Security is Role based (RBAC), which means that we grant permissions to each Role on which rows it has access to. Roles are then granted to one or more Groups, and a User will inherit the Roles and permissions of the group they are assigned to.
In this guide we will have a 1:1 relationship between Groups and Roles, where each Group will only be granted 1 Role.
For a table to be used with Row Level Security, it must have a VARBINARY column where a row entry in the column contains a comma separated list of roles which have access to that row. We will add a column called ACCESS_ROLES to our table Insight Toys.
And finally, we need a view on the table with the ACCESS_ROLES column, to enforce the Row Level Security.
Step-by-Step Guide: Setting Up Row Level Security in SAS SpeedyStore
During this Guide we will use SAS Studio Engineer and explicit pass-through to execute our SQL code inside SingleStore.
1. Define Security Requirements
Start by identifying the data domains and user groups that require restricted access. Make sure to have a strategy in place before implementing Row Level Security.
2. Create 2 Databases
To add an extra layer of security, we will create 2 different databases. One database will contain the Base data (physical table), and one contains the View. Users will only have access to the database containing Views, and no access to the underlying physical tables.
/*Create the database row_level_security_base which will hold the
physical base table*/
proc fedsql libs=(SASDP);
execute(CREATE DATABASE IF NOT EXISTS row_level_security_base) by SASDP;
quit;
/*Create the database row_level_security_view which will hold the
view*/
proc fedsql libs=(SASDP);
execute(CREATE DATABASE IF NOT EXISTS row_level_security_view) by SASDP;
quit;
3. Create Base Table and View
Create our base table which is placed in the row_level_security_base database, our end users won’t have access to this database.
/*Create the physical base table INSIGHT_TOY5_DEMO_VIYA_S2_RLS in the row_level_security_base database*/
proc fedsql libs=(base_rls);
execute(DROP TABLE IF EXISTS INSIGHT_TOY5_DEMO_VIYA_S2_RLS ) by base_rls;
execute(CREATE TABLE INSIGHT_TOY5_DEMO_VIYA_S2_RLS as
select * from SASDP.INSIGHT_TOY5_DEMO_VIYA_S2) by base_rls;
quit;
Add the ACCESS_ROLES column to the base table. This column is populated with values from FacilityContinent and FacilityCountry by using string concatenation.
/*Adds the ACCESS_ROLES column to the table and populate it with ROLES according to FacilityContinent and
FacilityCountry column.
Row level security will be based off these roles, only groups who have these roles granted will be able to see
the row.*/
proc fedsql libs=(base_rls);
execute(ALTER TABLE INSIGHT_TOY5_DEMO_VIYA_S2_RLS ADD COLUMN ACCESS_ROLES VARBINARY(50) DEFAULT ",") by base_rls;
execute(UPDATE INSIGHT_TOY5_DEMO_VIYA_S2_RLS SET ACCESS_ROLES=CONCAT(UPPER(REPLACE(facilitycontinent,' ','')), '_CONTINENT_ROLE', ', ', UPPER(REPLACE(facilitycountry,' ','')), '_COUNTRY_ROLE')) by base_rls;
quit;
The UPDATE statement will add the correct Continent and Country Role to the ACCESS_ROLES column, based on the FACILITYCONTINENT and FACILITYCOUNTRY Column.
We use the statement “SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES(), ACCESS_ROLES))” to force the view to use the ACCESS_ROLES column.
/*Create the View in the View database. The view is used to enforce row level security
Users will only have access to the view and not the physical table or database where
the physical table is stored.*/
proc fedsql libs=(row_lev);
execute(DROP VIEW IF EXISTS INSIGHT_TOY5_RLS_VIEW) by row_lev;
execute(CREATE VIEW INSIGHT_TOY5_RLS_VIEW AS SELECT *
FROM row_level_security_base.INSIGHT_TOY5_DEMO_VIYA_S2_RLS
WHERE SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES(), ACCESS_ROLES)) by row_lev;
quit;4. Create Groups and Roles
To create a Role or a Group in SingleStore we use the “CREATE ROLE” and CREATE GROUP” statements. In this guide we will have a 1:1 relationship between Groups and Roles and there will be one Group and Role for each Continent and Country.
SingleStore statements to create ROLE and GROUP for Europe Continent and Sweden Country:
CREATE ROLE 'EUROPE_CONTINENT_ROLE’
CREATE GROUP 'EUROPE_CONTINENT_GROUP'
CREATE ROLE 'SWEDEN_COUNTRY_ROLE'
CREATE GROUP 'SWEDEN_COUNTRY_GROUP'
To create the rest of the statements, we use proc sql and string concatenation in SAS Studio.
/*Generate execute command to create all groups and roles for Continent*/
proc sql;
create table create_facilitycontinent_R_G as
select distinct facilitycontinent,
cat('execute(CREATE ROLE ',"'", upper(strip(compress(facilitycontinent))), '_CONTINENT_ROLE',"')", ' by base_rls;') as create_continent_role,
cat('execute(CREATE GROUP ',"'", upper(strip(compress(facilitycontinent))), '_CONTINENT_GROUP',"')", ' by base_rls;') as create_continent_group
from base_rls.INSIGHT_TOY5_DEMO_VIYA_S2_RLS
order by facilitycontinent;
quit;
/*Generate execute command to create all groups and roles for Country*/
proc sql;
create table create_facilitycountry_R_G as
select distinct facilitycountry,
cat('execute(CREATE ROLE ',"'", upper(strip(compress(facilitycountry))), '_COUNTRY_ROLE',"')", ' by base_rls;') as create_country_role,
cat('execute(CREATE GROUP ',"'", upper(strip(compress(facilitycountry))), '_COUNTRY_GROUP',"')", ' by base_rls;') as create_country_group
from base_rls.INSIGHT_TOY5_DEMO_VIYA_S2_RLS
order by facilitycountry;
quit;
This will create a table with the execute statements needed to create all groups and roles in Singlestore
To execute the statements in the table, we run the below code. It will read the statements into 4 different macro variables and then execute them.
/*Generate macrovariable that holds the execute statements to create groups and roles for continent and country */
proc sql;
select create_continent_role into :create_continent_roles separated by ' ' from work.create_facilitycontinent_R_G;
select create_continent_group into :create_continent_group separated by ' ' from work.create_facilitycontinent_R_G;
select create_country_role into :create_country_roles separated by ' ' from work.create_facilitycountry_R_G;
select create_country_group into :create_country_group separated by ' ' from work.create_facilitycountry_R_G;
quit;
/*Executes the create group and role statements.*/
proc fedsql libs=(base_rls);
&create_continent_roles.;
&create_continent_group.;
&create_country_roles.;
&create_country_group.;
quit;
5. Grant ROLES to GROUPS
Permissions will only be granted to ROLES. For Users to inherit these permissions based on their assigned GROUP, a ROLE need to be Granted to a GROUP.
In SingleStore we can use the statement GRANT ROLE, to grant a ROLE to a GROUP.
GRANT ROLE 'EUROPE_CONTINENT_ROLE' to 'EUROPE_CONTINENT_GROUP'
GRANT ROLE 'SWEDEN_COUNTRY_ROLE' to 'SWEDEN_COUNTRY_GROUP'
We will use the same approach with proc sql and string concatenation in SAS Studio, as we did when creating ROLES and GROUPS.
/*Generate execute command to GRANT ROLES to GROUPS on continent level*/
proc sql;
create table GRANT_facilitycontinent_R_G as
select distinct facilitycontinent,
cat('execute(GRANT ROLE ',"'", upper(strip(compress(facilitycontinent))), '_CONTINENT_ROLE',"' ", 'to ',"'", upper(strip(compress(facilitycontinent))), '_CONTINENT_GROUP',"')", ' by base_rls;') as grant_continent_role
from base_rls.INSIGHT_TOY5_DEMO_VIYA_S2_RLS
order by facilitycontinent;
quit;
/*Generate execute command to GRANT ROLES to GROUPS on country level*/
proc sql;
create table GRANT_facilitycountry_R_G as
select distinct facilitycountry,
cat('execute(GRANT ROLE ',"'", upper(strip(compress(facilitycountry))), '_COUNTRY_ROLE',"' ", 'to ',"'", upper(strip(compress(facilitycountry))), '_COUNTRY_GROUP',"')", ' by base_rls;') as grant_country_role
from base_rls.INSIGHT_TOY5_DEMO_VIYA_S2_RLS
order by facilitycountry;
quit;
This will create a table with the execute statements needed to Grant ROLES to GROUPS in Singlestore
To execute the statements in the table, we run the below code. It will read the statements into 2 different macro variables and then execute them.
/*Create macrovariables for the execute commands */
proc sql;
select grant_continent_role into :grant_continent_roles separated by ' ' from work.GRANT_facilitycontinent_R_G;
select grant_country_role into :grant_country_roles separated by ' ' from work.GRANT_facilitycountry_R_G;
quit;
/*Execute the Grant code in Singlestore */
proc fedsql libs=(base_rls);
&grant_continent_roles.;
&grant_country_roles.;
quit;
To see if a ROLE was granted to a GROUP we can use the statement SHOW ROLES FOR GROUP in SingleStore. This can also be executed with pass-through from SAS Studio:
/*Show ROLES FOR GROUPS – SWEDEN_COUNTRY_GROUP */
proc sql;
connect to s2 as dbcon
(user=&_S2user password=&_S2pass host=&myhost
database=row_level_security port=3306);
select * from connection to dbcon(SHOW ROLES FOR GROUP 'SWEDEN_COUNTRY_GROUP');
disconnect from dbcon;
quit;
6. Grant permissions to ROLES
We grant the same permissions to all ROLES, except for the Row Level Security permissions, which are handled by the ACCESS_ROLES column.
- Permissions on the Embedded Process database in SingleStore, which is needed for in-database processing, model deployment/scoring as well as pushdown from Visual Analytics
- Read permissions on the view INSIGHT_TOY5_RLS_VIEW which enforce Row Level Security.
- SHOW METADATA permission.
Using the same procedure as last time, we will create a control table with one row per ROLE and then generate the execute statements using proc sql and string concatenation in SAS Studio.
/*Fetch all ROLES from SingleStore */
proc sql;
connect to s2 as dbcon
(user=&_S2user password=&_S2pass host=&myhost
database=row_level_security port=3306);
create table ROLE_CONTROL_TABLE as
select * from connection to dbcon(SHOW ROLES);
quit;
/*Generate execute command to GRANT access to ROLES*/
proc sql;
create table GRANT_EP_TO_ROLES as
select distinct Roles,
cat('execute(GRANT SELECT, INSERT, UPDATE, CREATE, DROP, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `EP`.* TO ROLE ', "'", strip(Roles), "')", ' by base_rls;') as grant_perm_to_roles,
cat('execute(GRANT SELECT ON row_level_security.INSIGHT_TOY5_RLS_VIEW TO ROLE ', "'", strip(Roles), "')", ' by base_rls;') as grant_select_to_roles,
cat('execute(GRANT SHOW METADATA ON *.* TO ROLE ', "'", strip(Roles), "')", ' by base_rls;') as grant_metadata_to_roles
from WORK.ROLE_CONTROL_TABLE
order by Roles;
quit;
/*Create macrovariables containing the GRANT commands*/
proc sql;
select grant_perm_to_roles into :grant_perm_to_roles separated by ' ' from WORK.GRANT_EP_TO_ROLES;
select grant_select_to_roles into :grant_select_to_roles separated by ' ' from WORK.GRANT_EP_TO_ROLES;
select grant_metadata_to_roles into :grant_metadata_to_roles separated by ' ' from WORK.GRANT_EP_TO_ROLES;
quit;
/*Execute the Grant code in Singlestore */
proc fedsql libs=(base_rls);
&grant_perm_to_roles.;
&grant_select_to_roles.;
&grant_metadata_to_roles.;
quit;
To see if we granted the correct permissions to a ROLE and that a GROUP inherited those permissions, we use the following sql statement:
/*Fetch GRANT permissions for GROUP "EUROPE_CONTINENT_GROUP" */
proc sql;
connect to s2 as dbcon
(user=&_S2user password=&_S2pass host=&myhost
database=row_level_security port=3306);
select * from connection to dbcon(SHOW GRANTS FOR GROUP 'EUROPE_CONTINENT_GROUP');
disconnect from dbcon;
quit;
7. Create Users and Grant GROUPS
Now that Databases, tables, views, groups and roles are created, it’s time to create the users and grant groups to them.
We will create 4 different users
- Mathias – Company CEO with access to all Continents and Countries
- Kalle – Europe Lead/Manager with access to Europe Continent and Contries
- Patric – Asia/Oceania Lead/Manager with access to Asia and Oceania Continents and Countries
- Hans – Germany Lead/Manager with access to Country Germany
/*Create users */
proc fedsql libs=(row_lev);
execute(CREATE USER 'Patric'@'%' IDENTIFIED BY 'Password') by row_lev;
execute(CREATE USER 'Mathias'@'%' IDENTIFIED BY ' Password ') by row_lev;
execute(CREATE USER 'Kalle'@'%' IDENTIFIED BY ' Password ') by row_lev;
execute(CREATE USER 'Hans'@'%' IDENTIFIED BY ' Password ') by row_lev;
quit;
Once our users are created, we can grant GROUPS to them. The users will inherit the access permission passed down from the ROLE.
/*Grant GROUPS to users base on their position in the company */
proc fedsql libs=(row_lev);
/*Grant GROUPS FOR Patric – ASIA and OCEANIA continent groups */
execute(GRANT GROUP 'ASIA_CONTINENT_GROUP' TO 'Patric'@'%') by row_lev;
execute(GRANT GROUP 'OCEANIA_CONTINENT_GROUP' TO 'Patric'@'%') by row_lev;
/*GRANT GROUPS FOR Kalle – EUROPE continent group*/
execute(GRANT GROUP 'EUROPE_CONTINENT_GROUP' TO 'Kalle'@'%') by row_lev;
/*GRANT GROUPS FOR Mathias – All Continent Groups*/
execute(GRANT GROUP 'AFRICA_CONTINENT_GROUP' TO 'Mathias'@'%') by row_lev;
execute(GRANT GROUP 'ASIA_CONTINENT_GROUP' TO 'Mathias'@'%') by row_lev;
execute(GRANT GROUP 'EUROPE_CONTINENT_GROUP' TO 'Mathias'@'%') by row_lev;
execute(GRANT GROUP 'OCEANIA_CONTINENT_GROUP' TO 'Mathias'@'%') by row_lev;
execute(GRANT GROUP 'SOUTHAMERICA_CONTINENT_GROUP' TO 'Mathias'@'%') by row_lev;
/*GRANT GROUPS FOR Hans – GERMANY Country group */
execute(GRANT GROUP 'GERMANY_COUNTRY_GROUP' TO 'Hans'@'%') by row_lev;
quit;
To see if a user has the correct permissions, we can execute the following sql statement:
/*Fetch GRANT permissions for user Patric */
proc sql;
connect to s2 as dbcon
(user=&_S2user password=&_S2pass host=&myhost
database=row_level_security port=3306);
select * from connection to dbcon(SHOW GRANTS FOR 'Patric'@'%');
disconnect from dbcon;
quit;
We can see that all permissions are inherited via ROLES and GROUPS.
Now our data is secured and it’s time to run some tests in Visual Analytics
8. Visual analytics report with Row Level Security
Using the INSIGHT_TOY5_RLS_VIEW in Visual Analytics requires a CAS table pointing to the View. To keep it simple we will do this in SAS Data Explorer (Manage Data in the menu).
Simply right click the View table and select “Load into memory”. No data is loaded into memory, instead a CAS table pointing to the view is be created.
(To prevent caching in CAS and Visual Analytics, a Row-Level filter on the View can be added to users with access. In this example the filter is “FacilityContinent <> 'SUB::SAS.Userid'”).
When building and consuming the report based on the RLS View, users will only see the rows they have access to.
Conclusion
By implementing Row Level Security in SingleStore and integrating with SAS Viya, you can ensure that sensitive data is protected and accessible only to authorised users. This approach strengthens compliance, reduces risk, and empowers your analytics teams to work confidently with enterprise data.
