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.
Here are some examples where Row-Level Security is very critical and can be used:
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.
(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:
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:
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:
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):
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';
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);
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_group';
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.
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.