Row-level security (RLS) is a database access control mechanism that dynamically restricts users' access to specific data rows through policies. It is often used in multi-tenant systems and permission isolation scenarios. Unlike view or column permissions, RLS automatically adds a WHERE condition when the query runs, preventing users from seeing rows of data that do not belong to them. The steps to implement RLS in SQL Server include: 1. Create an inline table value function to return access conditions; 2. Create a security policy and bind the function to the target table; 3. Determine access permissions based on the user's identity. For example, in the Sales table, salespeople can only view their own data. Notes include: the function must be inline form, the performance needs to be optimized in combination with indexes, and the debugging can be simulated by EXECUTE AS, and it is suitable for user information in database management scenarios. In addition, PostgreSQL and Azure SQL also support RLS, but have slight syntax differences; while MySQL or Oracle needs to be implemented with the help of view and application logic simulation. Overall, RLS improves security and reduces the workload of business-level processing permissions.

To put it directly, the key is to achieve row-level security in SQL, the key is to control which rows of data users can see through policies, rather than the entire table or column. This function is particularly suitable for multi-tenant systems, permission isolation and other scenarios.

What is Row-Level Security (RLS)

RLS is a database-level access control mechanism that allows you to dynamically restrict access to certain rows in a table based on the current user identity or execution context. For example, people in the sales department can only see data in their own area and cannot see records in other areas.

It does not just encapsulate the query like a view, nor does it restrict field access like column permissions. Instead, it automatically adds the WHERE condition when the query runs , and users cannot see data that does not belong to them.

How to implement RLS in SQL Server

SQL Server has supported RLS since 2016. Here are the basic steps to implement it:

• Create an Inline Table-Valued Function that returns the data conditions allowed to be accessed
• Create a Security Policy and bind the function to the target table
• Determine whether to allow access to the corresponding row based on logged-in user information (such as SUSER_SNAME or custom context)

For example: Suppose there is a Sales table, and each salesperson can only view his own data.

 -- Step 1: Create filter function CREATE FUNCTION dbo.fn_SalesAccessPredicate(@SalesPersonId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_accessResult
WHERE @SalesPersonId = USER_ID() OR IS_MEMBER('db_owner') = 1;

 -- Step 2: Create a security policy CREATE SECURITY POLICY SalesAccessPolicy
ADD FILTER PREDICATE dbo.fn_SalesAccessPredicate(SalesPersonId)
ON dbo.Sales
WITH (STATE = ON);

After setting this way, ordinary users can only see the sales records corresponding to their ID.

Notes and FAQs

• The function must be inline : not an ordinary scalar function or multi-statement function, otherwise the binding will fail.
• The performance impact is not great but requires testing : RLS filtering is added during the query optimization stage and will not significantly slow down the query speed, but it is recommended to cooperate with the index under large data volume.
• Debugging is a bit troublesome : by default you cannot see the filtered data, you can use EXECUTE AS to simulate different users to test the effect.
• Applicable to specific user models : If user information is not managed in the database, it may be necessary to pass in context in conjunction with the application layer, such as using SESSION_CONTEXT to store user information.

Support for other databases

In addition to SQL Server, PostgreSQL and Azure SQL also support RLS, but the syntax is slightly different:

• PostgreSQL Using CREATE POLICY USING Expressions
• Azure SQL is basically consistent with SQL Server

If you are using MySQL or Oracle, this type of function must be implemented through view application logic simulation, and it does not natively support true row-level security.

In general, RLS is a very practical function that can help you save a lot of trouble in the business layer of permission filtering. Although there is a little threshold for configuration, once it is installed, the subsequent maintenance cost is low and the safety is high. Basically that's it.

The above is the detailed content of SQL Row-Level Security Implementation. For more information, please follow other related articles on the PHP Chinese website!