Partager via

Attribution d'autorisations sur une collection de schémas XML

  • Article

Vous pouvez attribuer des autorisations de création d'une collection de schémas XML et accorder des autorisations sur un objet collection de schémas XML.

Attribution d'autorisations de création d'une collection de schémas XML

Pour créer une collection de schémas XML, les autorisations d'accès suivantes sont requises :

  • L'entité de sécurité requiert l'autorisation CREATE XML SCHEMA COLLECTION au niveau base de données.
  • Les collections de schéma XML étant étendues aux schémas relationnels, l'entité de sécurité doit également disposer de l'autorisation ALTER sur le schéma relationnel.

Les autorisations suivantes permettent à une entité de sécurité de créer une collection de schémas XML dans un schéma relationnel d'une base de données hébergée sur un serveur :

  • Autorisation CONTROL sur le serveur
  • Autorisation ALTER ANY DATABASE sur le serveur
  • Autorisation ALTER sur la base de données
  • Autorisation CONTROL sur la base de données
  • Autorisations ALTER ANY SCHEMA et CREATE XML SCHEMA COLLECTION sur la base de données
  • Autorisation ALTER ou CONTROL sur le schéma relationnel et autorisation CREATE XML SCHEMA COLLECTION sur la base de données

Cette dernière méthode d'autorisations est utilisée dans l'exemple qui suit.

Le propriétaire du schéma relationnel devient propriétaire de la collection de schémas XML créée dans ce schéma. Il bénéficie d'un contrôle complet sur la collection en question. Il peut ainsi modifier la collection de schémas XML, typer une colonne xml ou supprimer la collection.

Attribution d'autorisations sur l'objet collection de schémas XML

Les autorisations suivantes peuvent s'attribuer sur la collection de schémas XML :

  • L'autorisation ALTER est nécessaire pour modifier le contenu d'une collection de schémas XML existante par le biais de l'instruction ALTER XML SCHEMA COLLECTION.
  • L'autorisation CONTROL permet à un utilisateur d'effectuer n'importe quelle opération sur la collection de schémas XML.
  • L'autorisation TAKE OWNERSHIP est nécessaire pour transmettre la propriété de la collection de schémas XML d'une entité de sécurité à une autre.
  • L'autorisation REFERENCE permet à l'entité de sécurité d'utiliser la collection de schémas XML pour typer ou contraindre les colonnes de type xml des tables, des vues et des paramètres. L'autorisation REFERENCE est également nécessaire si une collection de schémas XML fait référence à une autre.
  • L'autorisation VIEW DEFINITION permet à l'entité de sécurité d'interroger le contenu de la collection de schémas XML par le biais de XML_SCHEMA_NAMESPACE ou des affichages catalogue, à condition que cette entité de sécurité dispose également de l'une des autorisations ALTER, REFERENCES ou CONTROL sur la collection.
  • L'autorisation EXECUTE est nécessaire pour valider les valeurs insérées ou mises à jour par l'entité de sécurité par rapport à la collection de schémas XML qui type ou contraint les colonnes, les variables et les paramètres de type xml. Vous devez également disposer de cette autorisation pour interroger le contenu XML stocké dans ces colonnes et ces variables.

Exemples

Les scénarios proposés dans les exemples suivants illustrent le fonctionnement des autorisations sur les schémas XML. Chaque exemple crée la base de données de test, les schémas relationnels et les connexions nécessaires. Ces connexions reçoivent les autorisations nécessaires sur la collection de schémas XML. Chaque exemple effectue le nettoyage nécessaire à la fin.

A. Attribution d'autorisations de création d'une collection de schémas XML

L'exemple suivant illustre comment les autorisations sont accordées pour qu'une entité de sécurité puisse créer une collection de schémas XML. Il crée un exemple de base de données et un utilisateur de test nommé TestLogin1. L'autorisation ALTER est alors attribuée à TestLogin1 sur le schéma relationnel, ainsi que l'autorisation CREATE XML SCHEMA COLLECTION sur la base de données. Avec ces autorisations, TestLogin1 réussit à créer un exemple de collection de schémas 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 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. Attribution d'autorisations pour utiliser une collection de schémas XML existante

L'exemple suivant illustre plus en détail le modèle d'autorisations pour la collection de schémas XML. Il démontre les différentes combinaisons d'autorisations nécessaires à la création et à l'utilisation de la collection de schémas XML.

Cet exemple crée une de base de données de test et un utilisateur nommé TestLogin1. TestLogin1 crée une collection de schémas XML dans la base de données. La connexion crée ensuite une table et utilise la collection de schémas XML pour créer une colonne xml typée. L'utilisateur insère ensuite les données et les interroge. Toutes ces étapes nécessitent les autorisations de schéma nécessaires comme illustré dans le 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. Attribution de l'autorisation ALTER sur une collection de schémas XML

Un utilisateur nécessite l'autorisation ALTER pour modifier une collection de schémas XML existante dans la base de données. L'exemple suivant illustre le mode d'attribution de l'autorisation 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 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. Attribution de l'autorisation TAKE OWNERSHIP sur une collection de schémas XML

Cet exemple illustre la méthode à employer pour transférer la propriété des schémas XML d'un utilisateur à un autre. Pour rendre cet exemple plus intéressant, les utilisateurs manipulent différents schémas relationnels par défaut.

L'exemple réalise les actions suivantes :

  • Il crée une base de données contenant deux schémas relationnels dbo et myOtherDBSchema.
  • Il crée deux utilisateurs nommés TestLogin1 et TestLogin2. Le propriétaire du schéma relationnel myOtherDBSchema est TestLogin2.
  • TestLogin1 crée une collection de schémas XML dans le schéma relationnel dbo.
  • TestLogin1 accorde ensuite l'autorisation TAKE OWNERSHIP sur la collection de schémas XML à TestLogin2.
  • TestLogin2 devient le propriétaire de la collection de schémas XML dans le schéma relationnel myOtherDBSchema, sans modifier le schéma relationnel de la collection de schémas 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 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. Attribution de l'autorisation VIEW DEFINITION sur une collection de schémas XML

Cet exemple illustre le mode d'attribution des autorisations VIEW DEFINITION pour collection de schémas 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 anymore
setuser 'schemaUser'
go
SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')
go

Voir aussi

Référence

Gestion des collections de schémas XML sur le serveur
Utilisation de collections de schémas XML sur le serveur — Recommandations et limitations
Autorisations sur une collection de schémas XML

Concepts

Type de données xml
XML typé et non typé

Aide et Informations

Assistance sur SQL Server 2005