January 24, 2020 at 3:56 pm
Did you miss a big point? There is actually a very good reason to name a specific column!
If you don't have access to every single column in the table then any value except a specific column that you do have access to will cause a security failure.
All generic values such as *, 1 or null cause all columns to be checked. If you name a specific column, that's the only column that will be checked.
This is an interesting point. When using EXISTS the engine only checks for the existence of rows. But what happens if your EXISTS uses * or a column that you don't have access to though column level security.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2020 at 5:28 pm
There is a good set of stories about the exists () predicate. The first implementations in Oracle actually worked out the SELECT statement without any optimizations. This meant that a "SELECT *" would return all of the columns, clearly a waste of time and space. However, if you used "SELECT <comstant>" the SQL engine did not have to go to the table. This style became part of the folklore long after the optimizers improved.
Today, we prefer to use "SELECT *" because this is never used as part of the select statement in good programming practice; we really want to see just the columns that we need in the select list. The Asterix tells us we are dealing with the existence of a row as a whole and not a particular set of columns.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 24, 2020 at 6:09 pm
Richard.Bielawski wrote:Did you miss a big point? There is actually a very good reason to name a specific column!If you don't have access to every single column in the table then any value except a specific column that you do have access to will cause a security failure.All generic values such as *, 1 or null cause all columns to be checked. If you name a specific column, that's the only column that will be checked.
In an WHERE EXISTS, the columns do not get checked. That's kind of the point of this article.
This is an interesting point. When using EXISTS the engine only checks for the existence of rows. But what happens if your EXISTS uses * or a column that you don't have access to though column level security.
Michael and Sean (and others): Richard is correct. I am about to post a proof of this along with a slightly different approach to proving that, outside of the permission issue, it doesn't matter what you specify in the SELECT list.
I will post a link here as soon as I publish it.
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
January 24, 2020 at 6:35 pm
OK - I'm missing something or misunderstanding the point, because SELECT 1 or SELECT * works fine for a user that is denied permission on a column within the table used in the EXISTS clause:
IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = 'LowPriv')
BEGIN;
CREATE USER LowPriv WITHOUT LOGIN;
END;
GO
DROP TABLE IF EXISTS dbo.TestExists;
GO
CREATE TABLE dbo.TestExists(
i int NOT NULL PRIMARY KEY,
j int NOT NULL
);
GO
INSERT INTO dbo.TestExists(i, j)
VALUES
(1, 100),
(2, 200)
;
GO
GRANT ALL ON dbo.TestExists TO LowPriv;
DENY SELECT ON dbo.TestExists(j) TO LowPriv;
GO
SELECT 'ABC'
WHERE EXISTS(
SELECT i
FROM dbo.TestExists
WHERE i = 2
);
GO
-- Works
EXECUTE AS USER = 'LowPriv';
SELECT 'ABC'
WHERE EXISTS(
SELECT 1
FROM dbo.TestExists
WHERE i = 2
);
GO
REVERT;
GO
-- Error
EXECUTE AS USER = 'LowPriv';
SELECT 'ABC'
WHERE EXISTS(
SELECT j
FROM dbo.TestExists
WHERE i = 2
);
GO
REVERT;
GO
Looking forward to elaboration.
January 24, 2020 at 6:55 pm
OK - I'm missing something or misunderstanding the point, because SELECT 1 or SELECT * works fine for a user that is denied permission on a column within the table used in the EXISTS clause:
-- Works
EXECUTE AS USER = 'LowPriv';
SELECT 'ABC'
WHERE EXISTS(
SELECT 1
FROM dbo.TestExists
WHERE i = 2
);
GO
REVERT;
GO
Hello. The reason that you see it working is due to the reference of a permitted column in the subquery. In your case, you reference the i column in the WHERE clause. If you remove the WHERE clause and re-execute the query as the LowPriv user, you will get the error. Likewise, if you then add the i column to the SELECT list (e.g. SELECT 1, i ... ) it will again succeed.
I am including this nuance in my post.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
January 24, 2020 at 7:11 pm
Thanks for the additional clarification, Solomon. Very interesting behavior. I don't know, though, that I've constructed many (any) EXISTS predicates that weren't correlated in some way, hence always referencing at least one column from the inner table. I can imagine a scenario, though, perhaps leveraging a "control" table of some kind. I'll definitely keep this issue in mind going forward. Thanks!
January 24, 2020 at 7:42 pm
Michael L John wrote:Richard.Bielawski wrote:Did you miss a big point? There is actually a very good reason to name a specific column!If you don't have access to every single column in the table then any value except a specific column that you do have access to will cause a security failure.All generic values such as *, 1 or null cause all columns to be checked. If you name a specific column, that's the only column that will be checked.
In an WHERE EXISTS, the columns do not get checked. That's kind of the point of this article.
Sean Lange wrote:This is an interesting point. When using EXISTS the engine only checks for the existence of rows. But what happens if your EXISTS uses * or a column that you don't have access to though column level security.
Michael and Sean (and others): Richard is correct. I am about to post a proof of this along with a slightly different approach to proving that, outside of the permission issue, it doesn't matter what you specify in the
SELECTlist.I will post a link here as soon as I publish it.
Take care, Solomon..
I know it doesn't matter because it is checking for the existence of rows. I often use the 1/0 example myself as proof for other people. But curious if a row is returned if the only referenced column is one you don't have access to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2020 at 10:21 pm
Here is my post on this topic:
Actual Difference Between EXISTS(SELECT 1 …), EXISTS(SELECT * …), and EXISTS(SELECT column …)
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
January 27, 2020 at 4:02 pm
WHERE EXISTS (SELECT NULL ...) is the semantically correct answer, as we really don't care what we are SELECTing, it is just a mechanism to check for existence.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply