What does EXECUTE on an XML SCHEMA COLLECTION allow?

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.

2 thoughts on “What does EXECUTE on an XML SCHEMA COLLECTION allow?

  1. Bob,

    Some companies consider XML schemas their intellectual property since the schemas contain constraints meaningful within the company domain. They may allow users to read the XML data but not the constraints enforced on the data.

    The EXECUTE permission is required to validate data against the XML schema collection. It prevents users from trying to validate arbitrary XML data and thereby discovering the rules within the XML schema.

    The BOL sentence you highlighted is correct but not well formulated. You can retrieve the full value in the XML column (e.g. in SELECT * or SELECT @x) – this is "retrieval". However, you cannot execute any XML data type method on the XML column or variable. In the XQuery within the XML data type method, you can cast a value to a type defined in the XML schema collection. This would leak type definitions in the XML schema. Hence, it is denied. Read "query" in the highlighted sentence to mean the query(), value(), exist() and nodes() methods. This is overkill – the XQuery compiler could detect whether or not a cast occurs, but that is much harder to implement without significant benefit.

    Hope this provides some clarification.

    Thank you,

    Shankar Pal
    Program Manager
    Microsoft SQL Server

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.