As relational databases became the standard in which we store tabular data, the need to impose restrictions on how the data is accessed by different users started to arise. For example, different SQL flavors have developed ways of securing the data from being accessed by non-authorized users. Among these methods, we can name two of the most applied, which are Row Level Security and Column Level Security. In this guide, we will focus on the first, while we will use the latter as a means for comparison.
What is Row Level Security
When we discuss database security, most of the time we are referring to specific policies or rules being put in place to restrict the access to certain parts of the information stored in the database to certain users. While we always have administrators who have access to the complete set of data stored in the database, certain users might be restricted to just certain rows of the data.
This might be the case when we have some sensitive financial data that requires it to be accessed by different users, who require access only to certain parts of the data. Other examples would be to restrict the view of records to specific departments that are relevant only to specific teams or to restrict a user from seeing only their records if they access a payroll table that contains information of all the employees of the company.
Row-level security can be applied in certain levels, with restrictions according to the risks we want to prevent:
- SELECT: We restrict users from seeing a certain portion of the data. This is the most common type of row-level security.
- INSERT: We restrict users from inserting new rows with specific values into a table. For example, we may want to prevent a sales manager from inserting deals for regions they’re not in charge of.
- UPDATE: We restrict users from making modifications to the data stored according to certain filters (for example: prevent a team from deleting records of other teams).
- DELETE: We restrict users from deleting records according to certain filters (for example: prevent a team from deleting records of other teams).
- REFERENCES: We restrict users from creating foreign keys which references a table to which the user is not authorized to see.
In this case, all of these actions can have as an objective a table stored in a database, but we can always create security policies that cover all securable elements, which are databases, tables, schemas, columns, and rows.
Without the incorporation of row-level security, these restrictions are enforced by the use of complex stored procedures that check the identity of the users running the query and add filtering accordingly, or by the use of temporary views which had in place security measures.
In some other scenarios, an additional table may be used to limit the results of the master table by joining it with the master table and presenting the results as a temporary view. This method could be used to restrict a certain user to see, for example, only the information of the employees in its department, by having a temporary view that joins a master table with the specific information of the users in each department of the company.
The problem with these methods of securing the data was that the use of temporary views makes the administration of the security measures a cumbersome task, having the need of managing and maintaining several temporary views, each one with their own security restrictions. Concerning the use of stored procedures it has been seen that these methods suffer from security concerns such as SQL injection, which makes them a solution far from ideal on production applications subjected to open internet traffic.
How Does Row-Level Security Work?
With the incorporation of Row Level Security as a feature in databases, users are now able to enforce fine-grained restrictions based on the user running the query, allowing them to see specific rows of the data without having to restrict the overall access to a certain table, or having to create temporary views or stored procedures for it.
The way in which these policies are implemented follows the next generic flow, though the exact details vary for different data platforms:
The first step is to grant select permissions to the user for which we want to restrict access to the data.
We need to create a filtering function that contains the predicate which will be used to select just specific rows that will be shown to that user. This filtering function can make use of WHERE statements that are applied to a certain column (for example, a UserID column within our table) and use a RETURN statement to provide the results back to the user.
Finally, we can create a specific security policy, which is an object that is used to bind the filtering function that we just created, to a specific table in a database for example. Security policies can be of two types; filter and block. The first is a method to be applied in read operations to silently filter the results shown to a user, while the latter is used to stop write operations that violate the predicate of the filtering function used. Queries that violate a filtering condition will return a null set, while queries that violate a block condition will fail with an error.
After these operations have been successfully applied, the final and most reasonable step would be to test that the security policy, along with the filtering function has been correctly placed and that for each user, we have different restricted views.
Best Practices of Row Level Security
One of the benefits of the Row Level Security restriction is that it is a method that is enforced by the database itself, rather than by an external application. This means that it is the database itself that applies the restrictions every time that a query is run, which results in a robust security measure that has few points of failure.
Row Level Security is an effective security measure when we need a method to effectively restrict access at the user level to certain portions of the data, but as it was mentioned before, the fact that this makes any query run by a specific user a surrogate of the filtering function, the performance of the query will be impacted by the complexity of the filtering function. This is a very important point of consideration if our filtering function depends on complex joins or lookup tables, while some other simpler approaches based on, for example, WHERE clauses acting on index columns shouldn’t cause any performance issues.
Row-Level Security vs Column-Level Security
Another option for applying a specific security policy is to use a Column Level Security, which is a method that allows masking or encryption of certain columns of a database. This masking or encryption can be applied to an entire column or portions of it, even with the possibility of using different encryption keys for different columns. Therefore, a user trying to access a portion of a table that contains a column that the user is not allowed to see will only see the hashed or encrypted text, while a user with required permissions will see the plain text.
Column-Level Security By Encryption
In order to create an encrypted column the user needs to have a master key to the database, therefore requiring being an administrator. First, the user needs to create a self-assigned certificate which is later going can be used to decrypt the data. After that, a key that will be used to encrypt the data is created with a designated algorithm which is usually an AES algorithm This key is a symmetric key stored in the database protected by an asymmetric key, which itself is protected by the master key. After that, the user can select the created key to encrypt a column in a database which can then only be seen in plain text while using this key.
Column Level Security can also be used in combination with Row Level Security. The difference between them lies in the fact that the first requires it to be programmed in the database while being set up, while the latter can be enforced through security policies. The difference between the applications can also lie in business logic like it would be to have a table containing sales data, for which we want a certain department to see columns with information about prices for all stores, and other departments to see only data from some stores and not see financial columns. Another difference is that Column Level Security is granular access control for certain parts of the data stored in a database, while Row Level Security can be applied more generally through policies.
The current needs in privacy and security concerning information require companies to keep up with changes not only within their own organization but also to be able to effectively comply with new standards and regulations. In order to do so, Row Level Security can be applied as a means to effectively control how users access and manage data with the use of security policies, and even in combination with Column Level Security by leveraging their advantages.