Relational databases like MySQL are the mainstay of many of our favorite apps and websites today, from client-relationship management software to social media sites. These deep reservoirs of data require exceptional security policies with special attention to authorization. In other words, with so much data, knowing who has access to what and for what reasons is important to the health and safety of your databases.
Row-level security (or RLS) allows organizations to keep data organized and visible only to those who need it, within the same tables. This is accomplished by granting user authorization access only to relevant data by restricting what they can see based on row filters.
An example of this is when salespeople in your organization have access to clients in their region, but not to clients in other regions. If based on a client_location column, the sales team in charge of California will only be able to access data where the client_location column is ‘California’.
Read more about row-level security in our dedicated guide to row-level security.
This guide will help you understand the importance, benefits, and limitations of MySQL row-level security in addition to how to implement it.
In this article, you will learn about:
What is Row-Level Security?
With so much information that can be housed in a single database, it’s important that all the different kinds of users that have access to it only have access to what they need. Row-level security is one of a few methods to limit user access to data. As the name implies, this security mechanism filters relevant rows of data to specific users.
Row-level security is a fine-grained access control method, allows you to authorize users to access only the data they need, even within tables. By using row-level security, organizations often reduce risks of access to sensitive data without creating unnecessary complexity in their database.
When applying row-level security at the database level (rather than the application level), no matter how a user accesses the data, row-level security remains in effect consistently. Moreover, instead of needing to change access restrictions multiple times across various plugins and applications that use your database, row-level security coding is centralized in one place.
Row-level security is valuable in the situations where certain tables hold information you want to restrict based on the user accessing the data. However, row-level security is only used in certain situations, and definitely not in all database architectures.
Why Use Row-Level Security with MySQL?
Row-level security, in cases where you want to limit access to specific rows based on the user’s identity, is a useful capability. It can be an additional layer of security when an application is already filtering data, or used as an access control when users are accessing the database directly or using BI tools.
Though row-level security has plenty of benefits, it isn’t a perfect solution. To make up for its shortcomings, however, it should still be a part of a comprehensive data access policy that addresses its disadvantages.
If you’re thinking about implementing row-level security on your MySQL database, consider the following advantages, disadvantages, and use cases.
Advantages of Row-Level Security
- Access control inheritance–Because RLS is applied directly to the database, any applications or plugins that use the database will inherit these access and authorization controls. Alternatively, row-level security will be used as an additional layer of defense even if an application is applying the same logic.
- Centralized code–RLS is used on the database itself and therefore needs to be coded only in one place, a marked benefit from needing to adjust access controls across multiple applications each time a user is added or policies change.
- Less risk of security gaps–The centralized nature of RLS makes it easier to identify and address security loopholes compared to auditing multiple complex end-user applications or plugins.
- Code complexity reduction–All of these advantages add up to a simpler database security solution that doesn’t lose strength due to that simplicity. RLS can also be used with application-layer security with little extra effort if you need additional protection.
Disadvantages of Row-Level Security
- Difficult to scale–As the database grows and your organization’s needs on it become more complex, managing the database and all its users and user groups through RLS will increase time and risk of errors.
- Single point of failure–RLS’s biggest advantage can also be its largest pitfall if you’re not careful. Because RLS operates at such a core level within the database, one mistake can cause access issues that ripple throughout the database and its applications. With that in mind, be sure to use layers of different security measures to mitigate this issue.
Use Cases of Row-Level Security
So when do you apply row-level security to your MySQL database? Here are a few examples:
- An investment company can create an access policy that allows its analysts to only see deals, transactions, and customer information relevant to them.
- A company can create a policy for the HR department to view employee information only per departments.
- A company that uses MySQL can apply regional based row-level security over its customers’ data. For example, only European employees will be able to access European customers’ data.
How MySQL Row-Level Security Works
When creating your row-level security policy, be sure to ask the following questions to determine how to restrict access:
- What user groups exist in the database and what do they use the database for?
- What tables and rows do they need access to in order to do their jobs?
- What is the least amount of access users can be given to do their work?
- What other security methods should be included to alleviate the risks associated with RLS by itself.
In general, row-level security in MySQL is usually implemented by adding an abstraction layer (a view) between the user and the table they’re querying. The view contains a filtering over the data accessed from the table, to server different users with different results.
Quick Tutorial: Granting MySQL Row-Level Security Using Views
You can implement row-level access control in MySQL by creating an abstraction of views (and optionally functions), to limit users’ access by a specific row filtering. For example, the following simple example uses a view which dynamically filters the rows you retrieve from the employees table per the owner column (which should correspond to the user).
CREATE OR REPLACE VIEW V_EMPLOYEES AS
SELECT * FROM EMPLOYEES WHERE owner = CURRENT_USER();
By granting users access only to the V_EMPLOYEES view, and not to the underlying EMPLOYEES table, their results are dynamically filtered.
For more elaborate situations, you will probably want to either create an entitlements map to join by, or use a product like Satori to simplify access control.
Implementing MySQL Row-Level Security with Satori
Satori helps organizations streamline access to sensitive data stored on MySQL, as well as provision MySQL production server access. In addition, Satori continuously discovers and maps sensitive data on your MySQL servers, and allows you to enable policies such as row-level security and data masking in a fast and simple way.
