Row-Level Security for SingleStoreDB

Having fine-grained access to data in the database is critical. As we think about data security in databases, SingleStoreDB supports fine grained access control that can help customers to set the right level of access to the data.

This is achieved through a fully functional Role based Access Control (RBAC) mechanism  which ensures that only the right level of privileges are given to each user to access the right database objects. However, how can users bring in additional security to data within a table itself — thereby ensuring more granularity of control to users?

One key capability SingleStoreDB supports is Row-Level Security (RLS), a feature that’s been included since  SingleStoreDB 7.3. This provides the ability to ensure that the specific users/groups are able to see only specific segments of the tables based on the roles they assume.  By default, any user/group who has access privilege to a table essentially has access to all rows in it based on the SQL privilege system and all rows are available for manipulation.

With Row-Level Security we can prevent the exact rows that users can manipulate. It is a very strong capability to implement data security — especially for applications where data from multiple tenants are stored in a common table definition.

Use Cases

Here are some examples where Row-Level Security is very critical and can be used:

• A sales representative should only see the rows which are related to customers that they are working with
• Isolating data across different users’ segments in a multi-tenant application. This could be due to multiple customers' data being stored in a single table, but isolated from each other from application perspective

Row-Level Security is a role-based access control mechanism for granular level objects within a table. It is  flexible, centralized and scalable. The security is based on access permissions defined for each role-row pair in the table as the administrator sets it up. Based on the role-row pairing criteria, the right level of access to the data is maintained.

Benefits

(RLS) benefits an organization by balancing security and governance at scale using a RBAC model. The scalable aspect of the model refers to the fact that it can be dynamically changed at any time to meet the corporate policy of the organization.

Additional benefits include:

• Ease of use
• Change management— we can easily change the roles and users tied to that role, without having to reapply the change the roles accessing rows of the tables

Now that we know what Row-Level Security (RLS) is and the benefits of using it, let’s see the functionality in action. In this example we have two scenarios:

1. A global user is the owner of and able to access all sales data, but we want to restrict regional sales people to only view sales data pertaining to their country
2. We have two additional salespeople — one in  Canada and one in the U.S. The Canadian user shouldn't access U.S. sales data, and the U.S user shouldn't access Canada sales data.

Schema

We will first  create a database, a table that has an access_roles column and then insert the data:

DROP DATABASE if EXISTS row_level_security;
CREATE DATABASE row_level_security;
USE row_level_security;

DROP TABLE IF EXISTS sales_data;
CREATE TABLE sales_data (
  tx_date DATETIME,
  country VARCHAR(20),
  amt REAL,
  access_roles VARBINARY(50) DEFAULT "," NOT NULL
);
INSERT INTO sales_data VALUES (now(),'US', 100.11, ',us_sales_role,');
INSERT INTO sales_data VALUES (now(),'CAN',200.22,',canada_sales_role,');
SELECT * FROM sales_data;

See the screenshot here to see it in action:

Note:

• The table needs to have the column Access_Roles with data type VARBINARY
• During insert operation, the rolenames need to have trailing and leading commas

User Management

Users can access a database and execute their functions and responsibilities through the creation of users, roles and groups, and granting of correct permissions (privileges):

• A role can have multiple privileges
• A group can have multiple roles
• A group can have multiple users
• A user can have multiple roles
• A user can be assigned to multiple groups
• Users inherit the permissions, roles of the groups they are assigned to

Roles

We will create two roles, one for each country, updating access to be country specific for the appropriate role

CREATE ROLE 'us_sales_role';
CREATE ROLE 'canada_sales_role';
SHOW ROLES;

UPDATE sales_data SET ACCESS_ROLES=CONCAT(ACCESS_ROLES, "us_sales_role,")
WHERE country = 'US';
UPDATE sales_data SET ACCESS_ROLES=CONCAT(ACCESS_ROLES, "can_sales_role,")
WHERE country = 'CAN';

View creation

We need to create a view to restrict access on the table that was created earlier.

CREATE VIEW sales_data_view AS SELECT tx_date, country, amt
FROM sales_data WHERE SECURITY_LISTS_INTERSECT(CURRENT_SECURITY_ROLES(),
ACCESS_ROLES);

Groups

We need to  create two groups, one for each region.

CREATE GROUP 'us_sales_group';
CREATE GROUP 'canada_sales_group';
SHOW GROUPS;

Now, assign the role to the appropriate group and grant access.

GRANT ROLE 'us_sales_role' to 'us_sales_group';
GRANT ROLE 'canada_sales_role' to 'canada_sales_group';
GRANT SELECT ON row_level_security.sales_data_view to ROLE 
'canada_sales_role';
GRANT SELECT ON row_level_security.sales_data_view to ROLE 'us_sales_role';

Connect to SingleStoreDB using CLI/Dbeaver/MySQLWorkbench and verify access.

SELECT * FROM sales_data_view ;

Now, our U.S-based user can log in, and won’t see Canada-specific data.

Similarly, our Canada-based user will log in and not see U.S. data. 

Conclusion

Row-Level Security extends our RBAC model to give fine-grained access to users, and helps build solutions for real-world business applications. You can read more about the functionality in our documentation guide. If you are ready to hit the ‘easy’ button, get started with SingleStoreDB today and take advantage of the free product credits.

Keep up with the latest tech updates from SingleStoreDB. Follow us on Twitter @SingleStoreDevs.