THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Is the SELECT permission checked in an UPDATE statement?

I was at PASS Summit 2011 in Seattle and had a chat with Jack Richins of Microsoft on the SQL Server audit feature after his presentation. A question came up with regards to whether the SELECT permission should be checked when an UPDATE statement is being executed, and whether the behavior (i.e. you may get UPDATE even if you only want to audit SELECT and the UPDATE statement does not have an explicit SELECT subquery) that I discussed in my previous blog post was a bug or expected.

I don’t know the answer to the ‘should’ part of the question because I don’t know the specifications for this feature. But I became curious about how it actually behaves – is the SELECT permission checked in an UPDATE statement?

Here are some tests to see how it actually behaves in SQL Server 2008.

I granted login test_user permission to update table TestDB.dbo.TestTable, but did not grant the SELECT permission on the table.

The test_user was able to execute:

UPDATE TestTable

   SET j = 2;

 

But the user failed on the following statement with error 229 (the SELECT permission was denied on the object):

UPDATE TestTable

   SET j = 2

 WHERE i = 1;

 

If SELECT was audited in the database audit specification, the audit log had a record for the second statement, but had no record for the first one (i.e. the one without the WHERE clause). No apparently no SELECT permission was checked if an UPDATE statement does not have a WHERE clause. Well, it’s not just the mere presence of the WHERE clause.

The following UPDATEs would trigger a SELECT permission check and cause a record to be logged in the audit log:

UPDATE TestTable

   SET j=2

 WHERE i=i;

 

UPDATE TestTable

   SET j = 2

 WHERE exists (SELECT * FROM TestTable);

 

The following UPDATE statements do not trigger any SELECT permission check on TestTable and won’t leave any audit trail if SELECT on TestTable is the only audited action type.

UPDATE TestTable

   SET j=2

 WHERE 2=2;

 

UPDATE TestTable

   SET j = 2

 WHERE exists (SELECT * FROM sysobjects);

 

By the way, if we determine whether the SELECT permission is checked by the presence of an audit record for the SELECT audit action type, then the SELECT permission is always checked even for a sysadmin. The difference is that even if you explicitly deny the SELECT permission to a sysadmin, the check will always succeed, but an audit trail is left behind in the audit log.

Published Monday, October 17, 2011 4:44 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement