Does "row-level security" actually serve a security purpose?

Question

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.

Answer 1

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.