3

Row-level security is often an industry requirement in secure environments, such as those dealing with payment cards.

It's supported by most major relational databases, including PostgreSQL, Microsoft SQL Server and Oracle. It works by introducing additional WHERE clauses in SQL statements attributing the query to a particular user / tenant / domain.

The PostgreSQL wiki describes the rationale of row-level security as such:

According to the definition of ISO/IEC27001 (information security management) the design target of information security feature is to ensure confidentiality, integrity and availability of information asset. In short, these are often called C.I.A.

Access control contributes towards both confidentiality and integrity. Access controls prevent unprivileged users from reading or writing information assets based on the rules which are configured in the access control system. Information or data itself does not have a particularly tangible form and therefore it must be stored in an object. Usually, access control features allow or prohibit users access to the object that contains the information or data. The intent of RS is to allow a more fine-grained control over the information inside of the objects.

For example, regular GRANT/REVOKE mechanisms control access on the specified database object according to the access control list, but they do not allow anything more granular. This coarse access control can be a problem for multi-tenanted, hosting, and highly security sensitive environments.

Use Cases

PCI Compliant implementations
Classified Environments
Shared hosting / multi-tenant applications

Considering that the SQL queries are constructed and sent by the application, which is external to the database and free to construct arbitrary queries, the application is of course free to misattribute the user.

What kind of attacks does row-level security prevent?

How does row-level security "contribute towards both confidentiality and integrity"?

Note:

This question relates to row-level security, NOT row-level encryption. The distinction being that a numeric user ID is passed in the SQL clause, instead of an encryption key or encrypted data.

2
  • I've never known row level permissions to be required at the database level by PCI-DSS. Do you have a link by any chance?
    – John Wu
    Commented Feb 15, 2022 at 7:37
  • @JohnWu Unfortunately there's very little public information about PCI-DSS. No, I don't have a public reference, and I'm not a expert in the area, sorry. The use of the term "often" was deliberate
    – watchowl
    Commented Feb 16, 2022 at 16:59

1 Answer 1

3

It works by introducing additional WHERE clauses in SQL statements attributing the query to a particular user / tenant / domain.
... the application is of course free to misattribute the user.

This is only true when the concept of different users is fully controlled by the business logic of the calling application and thus outside the database. This setup is common for web applications, where a single application "tenant" owns and accesses the database. The database itself has in this case no idea of what a user in the context of the application actually means, it only sees tables accessed by the same tenant. Authentication of users is done fully inside the web application and the database is only involved in this to get the stored password hashes or similar - the authentication check itself is then done by the application based on these retrieved information.

But, commonly databases themselves have their own concept of users build in. When using such concepts different users will maintain different connections to the database. Authentication of the users is done against the database engine and access control is enforced by the database itself with various granularity, like which user can access which database, maybe which table, maybe even which row. Also if the user can only read data or also write data etc. Check Managing PostgreSQL users and roles for some overview of how this works in PostgreSQL.

It works by introducing additional WHERE clauses in SQL statements attributing the query to a particular user / tenant / domain.

This is only true for the first case, where the concept of users only exist in the business logic of the application but is not reflected in different users of the database engine.

With different users at the database engine the access control is instead enforced by the database engine itself and is based on the user authenticated against the database, not some WHERE clause in a SQL statement. The application accessing the database cannot simply misattribute the user by specifying another one. It also needs to provide the authentication credentials for this user in order to get an authenticated database connection in the name of the user.

3
  • 2
    Just to add to this: A database user doesn't necessarily have to represent an actual person - it could just be that each application has it's own database user, which then limits what can be accessed using that application, regardless of how much or little permissions an actual user has within it, which in turn limits how much information could be leaked by a vulnerability with that application.
    – Bobson
    Commented Feb 13, 2022 at 3:22
  • Thank you for the thorough explanation! If I understood correctly, in order for RLS to be effective it requires that the SQL client authenticates as the end user/tenant? And this would typically happen in intranet desktop applications, but is not possible (or extremely unusual?) in web applications?
    – watchowl
    Commented Feb 15, 2022 at 1:26
  • @loopbackbee: Yes, row level security as implemented in the database requires that the client authenticates against the database - since otherwise the database cannot reliably distinguish clients. The typical setup for cheaper web hosting is a shared database between different clients, where each client gets a single database user - which means that RLS cannot be implemented at the database level within the web application. Commented Feb 15, 2022 at 4:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .