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:
SET j = 2;
But the user failed on the following statement with error 229 (the SELECT permission was denied on the object):
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:
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.
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.