I've always been slightly puzzled by the permission "EXECUTE" on an XML SCHEMA COLLECTION. Say I have an XML SCHEMA COLLECTION named MySchemas and a table that uses it:
CREATE TABLE MyDocuments ( id INT PRIMARY KEY IDENTITY, thexml XML (MySchemas))
And suppose I have a user named FRED that I grant access to the TABLE:
GRANT SELECT, INSERT, UPDATE, DELETE on MyDocuments to FRED
Now FRED can DELETE and SELECT from the table, but because he does not have EXECUTE permission on the XML SCHEMA COLLECTION, he can't INSERT or UPDATE the column that uses it with a schema-valid document. He also can't use typed parameters or variables. UNLESS the value of the XML column, variable, or parameter is NULL. So this works for FRED...
INSERT MyDocuments VALUES(NULL) -- INSERTing a non-NULL would fail.
So does this:
DECLARE @x XML (MySchemas)
But assignment of a document that corresponds to the XML SCHEMA COLLECTION to the variable @x fails unless FRED has EXECUTE on the XML SCHEMA COLLECTION. You grant the permission like this:
GRANT EXECUTE ON XML SCHEMA COLLECTION::MySchemas TO FRED
I can only guess that the reason for this privilege is to keep folks from "probing" the XML SCHEMA COLLECTION (perhaps there is sensitive information in the restrictions) by seeing what works on a column insert or variable assignment and what doesn't. And following the validation error messages. Perhaps.
BTW, the Books Online (I have the December update) gets it right except for SELECT. It says:
Denying the EXECUTE permission denies the principal the ability to insert or update the values in columns, variables, and parameters that are typed or constrained by the XML schema collection. It also denies the principal the ability to query the values in those same xml type columns and variables.
Theme design by Jelle Druyts
Pick a theme: BlogXP sqlx BlogXP sqlx
Powered by: newtelligence dasBlog 2.0.7226.0
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2008, Bob Beauchemin
E-mail