SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

"Predicate Evaluation Order"

In writing an article for SQL Server Standard's January issue, I received a tech edit comment from Adam Machanic about a weakness in the typical row-level security implementation for SQL Server that I wasn't aware of. Doing a little research (especially using Adam's citations as MVPs Steve Kass and Sharon Dooley as sources), I found a few references, especially in the newsgroups. There was also this little gem from Microsoft titled Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005.

Using the traditional view-based security mechanisms allow for data to be revealed when it shouldn't be. The big problem is "Predicative Evalution Order" due to the way the Query Optimizer operates (and it's the title of the section describing the issue in the Microsoft article). The Query Optimizer has free reign to order the tables in any way it needs to in order to present a reasonably optimized query plan. An error in the query, such as a casting error, can cause things to drop out and the error message itself will reveal some of the data. This would potentially allow a savvy user to gain access to information he or she would normally not be able to see.

All of it has been interesting to research and look into and a SQL Server Central article should follow from it shortly. It's been about six months since I've had time to write consistently but I'm hoping to get back into a groove and write more often. Hopefully this subject will be the one that kickstarts things.

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


No comments.

Leave a Comment

Please register or log in to leave a comment.