0

A question about SQL in general:

If two users execute the same sql statement, is it possible that user1 gets different results than user2?

Imagine user1 has more permissions than user2.

With other words: Is it possible that user1 receives 100 rows, and user2 only 80?

My question is about the SQL standard.

But feel free to speak about implementations of specific DB systems in comments.

  • 1
    "BTW: Which tag fits to this question?" - the tag kind of changes the question. The short answer is: "yes, it is possible", but it super depends on the specifics of what is being asked – Peter Vandivier Nov 15 '19 at 10:48
  • I would expect user 2 with less permissions to receive error messages about inaccessible objects as opposed to just receive less data than user 1. Though this heavily depends on the actual database management system in use - but this leaves your theoretical approach. – eagle275 Nov 15 '19 at 10:51
6

If two users execute the same sql statement, is it possible that user1 gets different results than user2?

Yes, absolutely.

Row-Level Access Control allows the Database Administrator to put "contextual" filters on a table so that different users really do see different data.

For example, a Users table might be protected such that users in the Personnel department can see everyone but anyone in any other department can only see people in the same department.

  • 4
    This is database specific, and not SQL in general. – Luuk Nov 15 '19 at 11:44
  • 1
    Question1: Is "Row-Level Access Control" part of the SQL standard? Question2: Which DB system are you talking about? – guettli Nov 15 '19 at 11:52
  • @Luuk: Granted, but that's the level I work at; I let purists worry about the Theory of it. Also, I know of at least three DBMSs that currently support this capability natively so I can only assume that either there really is some Relational Theory behind it, on which these implementations are based, or these companies are just really good at copying one anothers' ideas. – Phill W. Nov 15 '19 at 11:54
  • 1
    @guettli: pretty much every modern DBMS supports row level access control (sometimes named differently though) – a_horse_with_no_name Nov 15 '19 at 12:02
  • 2
    @guettli: a quick look didn't turn up anything. As far as I can tell privileges in SQL are only managed on "object" level (tables, views, schemas, etc) not on row level. The only thing that I can find is permissions on columns so the two users from your example might not see the same columns, but still the same rows. – a_horse_with_no_name Nov 15 '19 at 12:09
1

The SQL standard does not, and cannot, prescribe what data are returned from or updated in a SQL database; it is about syntax and semantic rules of the language. A compliant DBMS could return different results to the same user in response to the same query, and still remain compliant. In fact, this happens all the time -- see transaction isolation levels.

The opposite (same result in response to different queries) is also true -- the BLACKHOLE storage engine doesn't make MySQL any less standard-compliant (which isn't much, but still).

In other words, your question:

Is it possible that user1 receives 100 rows, and user2 only 80?

is entirely outside the scope of the SQL standard and is subject to individual RDBMS implementations, which, as others have mentioned, may have specific mechanisms to allow that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.