Blog Post

"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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating