![Row-Level Security for SingleStoreDB]()
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 CasesHere 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 withIsolating 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 perspectiveRow-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 useChange 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 tablesNow 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: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 countryWe 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.SchemaWe 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:
