Предоставление разрешений на коллекции схем XML

Статья
06/25/2013

На создание коллекции схем XML, а также на объекты коллекции схем XML можно предоставлять разрешения.

Предоставление разрешений на создание коллекции XML-схем

Для создания коллекции XML-схем требуются следующие разрешения.

Участнику требуется разрешение CREATE XML SCHEMA COLLECTION на уровне базы данных.
Поскольку областью действия коллекции XML-схем является реляционная схема, участнику требуется разрешение ALTER на реляционную схему.

Следующие разрешения позволяют участнику создавать коллекцию XML-схем в реляционной схеме в базе данных сервера:

разрешение CONTROL на сервере;
разрешение ALTER ANY DATABASE на сервере;
разрешение ALTER в базе данных;
разрешение CONTROL в базе данных;
разрешения ALTER ANY SCHEMA и CREATE XML SCHEMA COLLECTION в базе данных;
разрешение ALTER или CONTROL в реляционной схеме и разрешение CREATE XML SCHEMA COLLECTION в базе данных.

Этот метод разрешений используется в следующем примере.

Владелец реляционной схемы становится владельцем коллекции XML-схем, созданной в данной схеме. Этот владелец получает полный контроль над коллекцией XML-схем. Поэтому он может изменять коллекцию XML-схем, вводить XML-столбец и удалять коллекцию XML-схем.

Предоставление разрешений на объект коллекции XML-схем

В коллекции XML-схем допускаются следующие разрешения.

Разрешение ALTER требуется при изменении содержимого существующей коллекции XML-схем с помощью инструкции ALTER XML SCHEMA COLLECTION.
Разрешение CONTROL дает возможность пользователю выполнять любые операции в коллекции XML-схем.
Разрешение TAKE OWNERSHIP необходимо для передачи прав на обладание коллекцией схем XML от одного участника к другому.
Разрешение REFERENCES дает участнику право на использование коллекции схем XML для типизации или ограничения типа столбцов xml в таблицах, представлениях и параметрах. Кроме того, разрешение REFERENCES необходимо, если одна коллекция схем XML ссылается на другую.
Разрешение VIEW DEFINITION позволяет участнику выполнять запрос к содержимому коллекции XML-схем через XML_SCHEMA_NAMESPACE или представления каталогов при условии, что этот участник также имеет разрешение ALTER, REFERENCES или CONTROL на эту коллекцию.
Разрешение EXECUTE необходимо для проверки значений, вставленных или обновленных участником, по коллекции схем XML, которая типизирует или ограничивает столбцы, переменные и параметры типа xml. Это разрешение требуется также для запросов к XML-данным, хранящимся в этих столбцах и переменных.

Примеры

Представленные в следующих примерах сценарии отображают организацию разрешений на XML-схемы. В каждом примере создается соответствующая тестовая база данных, реляционные схемы и имена входа. Этим именам входа предоставляются необходимые разрешения на коллекции XML-схем. После завершения работы в каждом примере выполняются действия по очистке.

А.Предоставление разрешений на создание коллекции схем XML

В следующем примере показано предоставление разрешений на создание участником коллекции схем XML. В примере создается образец базы данных и пользователь TestLogin1. Пользователю TestLogin1 предоставляется разрешение ALTER в реляционной схеме и разрешение CREATE XML SCHEMA COLLECTION в базе данных. С этими разрешениями пользователь TestLogin1 успешно создает образец коллекции схем XML.

SETUSER
GO
USE master
GO
CREATE LOGIN TestLogin1 WITH password='SQLSvrPwd1'
GO
CREATE DATABASE SampleDBForSchemaPermissions
GO
USE SampleDBForSchemaPermissions
GO
CREATE USER TestLogin1
GO
-- User must have ALTER permission on the relational schema in the database.
GRANT ALTER ON SCHEMA::dbo TO TestLogin1
GO
-- User also must have permission to create XML schema collections in the database.
GRANT CREATE XML SCHEMA COLLECTION 
TO TestLogin1
GO
-- 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

Б.Предоставление разрешений на использование существующей коллекции схем XML

Следующий пример далее иллюстрирует модель разрешений для коллекции схем XML. В нем показаны различные разрешения, необходимые для создания коллекции схем XML и работы с ней.

В примере создается образец базы данных и имя входа TestLogin1. TestLogin1 создает коллекцию схем XML в базе данных. Имя входа создает таблицу и с помощью коллекции схем XML создает типизированный XML-столбец. Затем пользователь вставляет данные и выполняет к ним запрос. Для всех этих шагов необходимы соответствующие разрешения схемы, что отражается в программном коде.

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 must have ALTER permission on the relational schema in the database.
GRANT ALTER ON SCHEMA::dbo TO TestLogin1
GO
-- User also must have permission to create XML schema collections in the database.
GRANT CREATE XML SCHEMA COLLECTION 
TO TestLogin1
GO
-- Now user can execute the previous CREATE XML SCHEMA COLLECTION statement.
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

-- Create a table by using the collection to type an XML column. 
--TestLogin1 must have permission to create a table.
SETUSER
GO
GRANT CREATE TABLE TO TestLogin1
GO
-- The user also must have REFERENCES permission to use the XML schema collection
-- to create a typed XML column (REFERENCES permission on schema 
-- collection is not needed).
GRANT REFERENCES ON XML SCHEMA COLLECTION::myTestSchemaCollection 
TO TestLogin1
GO
-- Now user can create a 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, the 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 must have INSERT permission.
GRANT INSERT TO TestLogin1
GO
-- Now the user can insert data into 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 must have 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 element namespace "http://schemas.adventure-works.com/Additional/ContactInfo" /telephone[1]')
FROM MyTestTable
GO
-- To show that the user must have EXECUTE permission to query, revoke the
-- 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 element 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

В.Предоставление разрешения ALTER на коллекцию схем XML

Для изменения существующей коллекции схем XML в базе данных пользователю необходимо разрешение ALTER. В следующем примере показано предоставление разрешения ALTER.

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 must have ALTER permission on the relational schema in the database.
GRANT ALTER ON SCHEMA::dbo TO TestLogin1
GO
-- User also must have permission to create XML schema collections in the database.
GRANT CREATE XML SCHEMA COLLECTION 
TO TestLogin1
GO
-- Now user can execute the previous CREATE XML SCHEMA COLLECTION statement.
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 ALTER permission to TestLogin1.
setuser
GO
GRANT ALTER ON XML SCHEMA COLLECTION::myTestSchemaCollection TO TestLogin1
GO
-- 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

Г.Предоставление разрешения TAKE OWNERSHIP на коллекцию схем XML

В следующем примере показано, как права владельца коллекции схем XML могут передаваться от одного пользователя другому. Чтобы пример был более интересным, пользователи работают в разных реляционных схемах.

Код в примере выполняет следующие действия.

Создается база данных с двумя реляционными схемами — dbo и myOtherDBSchema.
Создаются два пользователя, TestLogin1 и TestLogin2. Пользователь TestLogin2 назначается владельцем реляционной схемы myOtherDBSchema.
Пользователь TestLogin1 создает коллекцию схем XML в реляционной схеме dbo.
Затем пользователь TestLogin1 предоставляет пользователю TestLogin2 разрешение TAKE OWNERSHIP на коллекцию схем XML.
Пользователь TestLogin2 становится владельцем коллекции схем XML в схеме myOtherDBSchema, реляционная схема коллекции схем XML не изменяется.

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 database.
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, the following
-- permission is required.
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 (therefore, 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

Д.Предоставление разрешения VIEW DEFINITION на коллекцию схем XML

В примере показано, каким образом предоставляются разрешения VIEW DEFINITION на коллекцию схем XML.

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.
setuser 'schemaUser'
GO
SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')
GO