Jaa

Granting Permissions on an XML Schema Collection

  • Artikkeli

You can grant permissions to create an XML schema collection and also grant permissions on an XML schema collection object.

Granting Permission to Create an XML Schema Collection

To create an XML schema collection, the following permissions are required:

  • The principal requires CREATE XML SCHEMA COLLECTION permission at the database-level.
  • Because the XML schema collections are relational schema-scoped, the principal must also have ALTER permission on the relational schema.

The following permissions let a principal create an XML schema collection in a relational schema in a database on a server:

  • CONTROL permission on the server
  • ALTER ANY DATABASE permission on the server
  • ALTER permission on the database
  • CONTROL permission in the database
  • ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database
  • ALTER or CONTROL permission on the relational schema and CREATE XML SCHEMA COLLECTION permission in the database

This last method of permissions is used in the following example.

The owner of the relational schema becomes the owner of the XML schema collection created in that schema. This owner then has full control over the XML schema collection. Therefore, this owner can modify the XML schema collection, type an xml column, or drop the XML schema collection.

Granting Permissions on an XML Schema Collection Object

The following permissions are allowed on the XML schema collection:

  • The ALTER permission is required when modifying contents of an existing XML schema collection by using the ALTER XML SCHEMA COLLECTION statement.
  • The CONTROL permission lets a user perform any operation on the XML schema collection.
  • The TAKE OWNERSHIP permission is required to transfer ownership of the XML schema collection from one principal to another.
  • The REFERENCE permission authorizes the principal to use the XML schema collection to type or constrain xml type columns, in tables and views and parameters. The REFERENCE permission is also required when one XML schema collection refers to another.
  • The VIEW DEFINITION permission allows the principal to query the contents of an XML schema collection either through XML_SCHEMA_NAMESPACE or through the catalog views, provided this principal also has one of the ALTER, REFERENCES, or CONTROL permissions on the collection.
  • The EXECUTE permission is required to validate values inserted or updated by the principal against the XML schema collection that is typing or constraining the xml type columns, variables, and parameters. You also need this permission when you are querying the XML stored in these columns and variables.

Examples

The scenarios in the following examples illustrate how XML schema permissions work. Each example creates the necessary test database, relational schemas, and logins. These logins are granted the necessary XML schema collection permissions. Each example does the necessary clean up at the end.

A. Granting permissions to create an XML schema collection

The following example illustrates how permissions are granted so that a principal can create an XML schema collection. The example creates a sample database and a test user, TestLogin1. TestLogin1 is then given ALTER permission on the relational schema and given CREATE XML SCHEMA COLLECTION permission on the database. With these permissions, TestLogin1 succeeds in creating a sample XML schema collection.

SETUSER
go
USE master
go
CREATE LOGIN TestLogin1 WITH password='SQLSvrPwd1'
go
CREATE DATABASE SampleDBForSchemaPermissions
go
USE SampleDBForSchemaPermissions
go
CREATE USER TestLogin1
go
-- User needs ALTER permission on the relational schema in the database
GRANT ALTER ON SCHEMA::dbo TO TestLogin1
go
-- User also needs permission to create xml schema collections in the database
GRANT CREATE XML SCHEMA COLLECTION 
TO TestLogin1
go
-- now execute create xml schema  collection
SETUSER 'TestLogin1'
go
CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS '<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema targetNamespace="http://schemas.adventure-works.com/Additional/ContactInfo" 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
elementFormDefault="qualified">
<xsd:element name="AdditionalContactInfo" >
  <xsd:complexType mixed="true" >
    <xsd:sequence>
      <xsd:any processContents="strict"  
               namespace="http://schemas.adventure-works.com/Contact/Record 
                          http://schemas.adventure-works.com/AdditionalContactTypes"
               minOccurs="0" maxOccurs="unbounded" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>
<xsd:element name="root" type="xsd:byte"/>
</xsd:schema>'
go
-- final cleanup
SETUSER
go
USE master
go
DROP DATABASE SampleDBForSchemaPermissions
go
DROP LOGIN TestLogin1
go

B. Granting permission to use an existing XML schema collection

The following example further illustrates the permission model for the XML schema collection. It illustrates how different permissions are required to create and use the XML schema collection.

The example creates a test database and a login, TestLogin1. TestLogin1 creates an XML schema collection in the database. The login then creates a table and uses the XML schema collection to create a typed xml column. The user then inserts data and queries it. All these steps require the necessary schema permissions as shown in the code.

SETUSER
go
USE master
go
CREATE LOGIN TestLogin1 WITH password='SQLSvrPwd1'
go
CREATE DATABASE SampleDBForSchemaPermissions
go
USE SampleDBForSchemaPermissions
go
CREATE USER TestLogin1
go
-- grant permission to the user
SETUSER
go
-- User needs ALTER permission on the relational schema in the database
GRANT ALTER ON SCHEMA::dbo TO TestLogin1
go
-- User also needs permission to create xml schema collections in the database
GRANT CREATE XML SCHEMA COLLECTION 
TO TestLogin1
go
-- Now user can execute previous CREATE XML SCHEMA COLLECTION
SETUSER 'TestLogin1'
go
CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS '<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema targetNamespace="http://schemas.adventure-works.com/Additional/ContactInfo" 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
elementFormDefault="qualified">

<xsd:element name="AdditionalContactInfo" >
  <xsd:complexType mixed="true" >
    <xsd:sequence>
      <xsd:any processContents="strict"  
               namespace="http://schemas.adventure-works.com/Contact/Record 
                          http://schemas.adventure-works.com/AdditionalContactTypes"
               minOccurs="0" maxOccurs="unbounded" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>
<xsd:element name="telephone" type="xsd:string" />
</xsd:schema>'
go

-- Let us create a table using the collection to type an xml column. 
--TestLogin1 needs permission to create table
setuser
go
GRANT CREATE TABLE TO TestLogin1
go
-- The user also needs REFERENCE permission to use the XML schema collection
-- to create a typed XML column (REFERENCES permission on schema 
-- collection not needed).
GRANT REFERENCES ON XML SCHEMA COLLECTION::myTestSchemaCollection 
TO TestLogin1
go
-- Now user can create table and use the XML schema collection to create 
-- a typed XML column
setuser 'TestLogin1'
go
CREATE TABLE MyTestTable (xmlCol xml (dbo.myTestSchemaCollection))
go
-- To insert data in the table user needs EXECUTE permission on the XML schema collection
-- GRANT EXECUTE permission to TestLogin2 on the xml schema collection
setuser
go
GRANT EXECUTE ON XML SCHEMA COLLECTION::myTestSchemaCollection 
TO TestLogin1
go
-- TestLogin1 does not own the dbo schema. This user needs insert permission
GRANT INSERT TO TestLogin1
go
-- Now user can insert data in the table
setuser 'TestLogin1'
go
INSERT INTO MyTestTable VALUES('
<telephone xmlns="http://schemas.adventure-works.com/Additional/ContactInfo">111-1111</telephone>
')
go
-- To query the table,TestLogin1 needs permissions (SELECT on the table and EXECUTE on the XML schema collection).
setuser
go
GRANT SELECT TO TestLogin1
go
-- TestLogin1 already has EXECUTE permission on the schema (granted before inserting a record in the table).
SELECT xmlCol.query('declare default namespace="http://schemas.adventure-works.com/Additional/ContactInfo" /telephone[1]')
FROM MyTestTable
go
-- To illustrate the user needs EXECUTE permission to query, let us REVOKE
-- previously granted permission and return the query
SETUSER
go
REVOKE EXECUTE ON XML SCHEMA COLLECTION::myTestSchemaCollection to TestLogin1
Go
-- Now TestLogin1 cannot execute the query
SETUSER 'TestLogin1'
go
SELECT xmlCol.query('declare default namespace="http://schemas.adventure-works.com/Additional/ContactInfo" /telephone[1]')
FROM MyTestTable
go
-- Final cleanup 
SETUSER
go
USE master
go
DROP DATABASE SampleDBForSchemaPermissions
go
DROP LOGIN TestLogin1
go

C. Granting ALTER permission on an XML schema collection

A user needs ALTER permission to modify an existing XML schema collection in the database. The following example illustrates how ALTER permission is granted:

SETUSER
go
USE master
go
CREATE LOGIN TestLogin1 WITH password='SQLSvrPwd1'
go
CREATE DATABASE SampleDBForSchemaPermissions
go
USE SampleDBForSchemaPermissions
go
CREATE USER TestLogin1
go
-- grant permission to the user
SETUSER
go
-- User needs ALTER permission on the relational schema in the database
GRANT ALTER ON SCHEMA::dbo TO TestLogin1
go
-- User also needs permission to create xml schema collections in the database
GRANT CREATE XML SCHEMA COLLECTION 
TO TestLogin1
go
-- Now user can execute previous CREATE XML SCHEMA COLLECTION
SETUSER 'TestLogin1'
go
CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS '<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema targetNamespace="http://schemas.adventure-works.com/Additional/ContactInfo" 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
elementFormDefault="qualified">

<xsd:element name="AdditionalContactInfo" >
  <xsd:complexType mixed="true" >
    <xsd:sequence>
      <xsd:any processContents="strict"  
               namespace="http://schemas.adventure-works.com/Contact/Record 
                          http://schemas.adventure-works.com/AdditionalContactTypes"
               minOccurs="0" maxOccurs="unbounded" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>
<xsd:element name="telephone" type="xsd:string" />
</xsd:schema>'
go
-- now grant ALTER permission to TestLogin1
setuser
go
GRANT ALTER ON XML SCHEMA COLLECTION::myTestSchemaCollection TO TestLogin1
go
-- now TestLogin1 should be able to add components to the collection
setuser 'TestLogin1'
go
ALTER XML SCHEMA COLLECTION myTestSchemaCollection ADD '
<xsd:schema targetNamespace="http://schemas.adventure-works.com/Additional/ContactInfo" 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns="http://schemas.adventure-works.com/Additional/ContactInfo" 
elementFormDefault="qualified">
 <xsd:element name="pager" type="xsd:string"/>
</xsd:schema>
'
Go
-- Final cleanup 
SETUSER
go
USE master
go
DROP DATABASE SampleDBForSchemaPermissions
go
DROP LOGIN TestLogin1
go

D. Granting TAKE OWNERSHIP permission on an XML schema collection

This example illustrates how XML schema ownership can be transferred from one user to another. To make the example more interesting, the users in this example work in different default relational schemas.

This example does the following:

  • Creates a database with two relational schemas, dbo and myOtherDBSchema).
  • Creates two users, TestLogin1 and TestLogin2. TestLogin2 is made the owner of the myOtherDBSchema relational schema.
  • TestLogin1 creates an XML schema collection in the dbo relational schema.
  • TestLogin1 then gives TAKE OWNERSHIP permission on the XML schema collection to TestLogin2.
  • TestLogin2 becomes the owner of the XML schema collection in the myOtherDBSchema, without changing the relational schema of the XML schema collection.
CREATE LOGIN TestLogin1 with password='SQLSvrPwd1'
go
CREATE LOGIN TestLogin2 with password='SQLSvrPwd2'
go
CREATE DATABASE SampleDBForSchemaPermissions
go
USE SampleDBForSchemaPermissions
go
-- Create another relational schema in the db
CREATE SCHEMA myOtherDBSchema
go
-- Create users in the database. Note TestLogin2's default schema is
-- myOtherDBSchema
CREATE USER TestLogin1
go
CREATE USER TestLogin2 WITH DEFAULT_SCHEMA=myOtherDBSchema
go
-- TestLogin2 will own myOtherDBSchema relational schema
ALTER AUTHORIZATION ON SCHEMA::myOtherDBSchema TO TestLogin2
go

-- For TestLogin1 to create XML schema collection, following
-- permission needed.
GRANT CREATE XML SCHEMA COLLECTION 
TO TestLogin1
go
GRANT ALTER ON SCHEMA::dbo TO TestLogin1
go
-- Now TestLogin1 can create an XML schema collection.
setuser 'TestLogin1'
go
CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS '<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema targetNamespace="http://schemas.adventure-works.com/Additional/ContactInfo" 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
elementFormDefault="qualified">

<xsd:element name="AdditionalContactInfo" >
 <xsd:complexType mixed="true" >
    <xsd:sequence>
      <xsd:any processContents="strict" 
               namespace="http://schemas.adventure-works.com/Contact/Record 
                          http://schemas.adventure-works.com/AdditionalContactTypes"
               minOccurs="0" maxOccurs="unbounded" />
    </xsd:sequence>
 </xsd:complexType>
</xsd:element>
<xsd:element name="telephone" type="xsd:string" />
</xsd:schema>'
go

-- grant TAKE OWNERSHIP to TestLogin2
SETUSER
go
GRANT TAKE OWNERSHIP ON XML SCHEMA COLLECTION::dbo.myTestSchemaCollection 
TO TestLogin2
go
-- verify the owner. Note the UserName and Principal_id is null. 
SELECT user_name(sys.xml_schema_collections.principal_id) as UserName, 
       sys.schemas.name as RelSchemaName,* 
FROM   sys.xml_schema_collections 
      JOIN sys.schemas 
      ON sys.schemas.schema_id=sys.xml_schema_collections.schema_id
go
-- TestLogin2 can take ownership now
setuser 'TestLogin2'
go
ALTER AUTHORIZATION ON XML SCHEMA COLLECTION::dbo.myTestSchemaCollection 
TO TestLogin2
go
-- Note that although TestLogin2 is the owner,the XML schema collection 
-- is still in dbo
SELECT user_name(sys.xml_schema_collections.principal_id) as UserName, 
      sys.schemas.name as RelSchemaName,* 
FROM sys.xml_schema_collections JOIN sys.schemas 
     ON sys.schemas.schema_id=sys.xml_schema_collections.schema_id
go

-- TestLogin2 moves the collection from dbo to myOtherDBSchema relational schema
-- TestLogin2 already has all necessary permissions
-- 1) TestLogin2 owns the destination relational schema so he can ALTER it
-- 2) TestLogin2 owns the XML schema collection (thus has CONTROL permission)
ALTER SCHEMA myOtherDBSchema
TRANSFER XML SCHEMA COLLECTION::dbo.myTestSchemaCollection
go

SELECT user_name(sys.xml_schema_collections.principal_id) as UserName, 
       sys.schemas.name as RelSchemaName,* 
FROM   sys.xml_schema_collections JOIN sys.schemas 
       ON sys.schemas.schema_id=sys.xml_schema_collections.schema_id
go
-- final cleanup 
SETUSER
go
USE master
go
DROP DATABASE SampleDBForSchemaPermissions
go
DROP LOGIN TestLogin1
DROP LOGIN TestLogin2
go

E. Granting VIEW DEFINITION permission on an XML schema collection

This example illustrates how VIEW DEFINITION permissions are granted for an XML schema collection:

SETUSER
go
use master
go
if exists( select * from sysdatabases where name='permissionsDB' )
   drop database permissionsDB
go
if exists( select * from sys.sql_logins where name='schemaUser' )
   drop login schemaUser
go
CREATE DATABASE permissionsDB
go
CREATE LOGIN schemaUser WITH PASSWORD='Pass#123',DEFAULT_DATABASE=permissionsDB
go
GRANT CONNECT SQL TO schemaUser
go
USE permissionsDB
go
CREATE USER schemaUser WITH DEFAULT_SCHEMA=dbo
go
CREATE XML SCHEMA COLLECTION MySC AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns"
xmlns:ns="http://ns">

   <simpleType name="ListOfIntegers">
      <list itemType="integer"/>
   </simpleType>

   <element name="root" type="ns:ListOfIntegers"/>

   <element name="gRoot" type="gMonth"/>

</schema>
'
go
-- schemaUser cannot see the contents of the collection
setuser 'schemaUser'
go
SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')
go

-- grant schemaUser VIEW DEFINITION and REFERENCES permissions
-- on the xml schema collection
SETUSER
go
GRANT VIEW DEFINITION ON XML SCHEMA COLLECTION::dbo.MySC TO schemaUser
go
GRANT REFERENCES ON XML SCHEMA COLLECTION::dbo.MySC TO schemaUser
go
-- now schemaUser can see the content of the collection
setuser 'schemaUser'
go
SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')
go
-- revoke schemaUser VIEW DEFINITION permissions
-- on the xml schema collection
SETUSER
go
REVOKE VIEW DEFINITION ON XML SCHEMA COLLECTION::dbo.MySC FROM schemaUser
go
-- now schemaUser cannot see the contents of 
-- the collection anymore
setuser 'schemaUser'
go
SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')
go

See Also

Reference

Managing XML Schema Collections on the Server
Guidelines and Limitations in Using XML Schema Collections on the Server
Permissions on an XML Schema Collection

Concepts

xml Data Type
Typed vs. Untyped XML

Help and Information

Getting SQL Server 2005 Assistance