Concedendo permissões em uma coleção de esquema XML
É possível conceder permissões para criar uma coleção de esquema XML e também permissões em um objeto da coleção de esquema XML.
Concedendo permissão para criar uma coleção de esquema XML
Para criar uma coleção de esquema XML, são necessárias as seguintes permissões:
A entidade de segurança requer permissão CREATE XML SCHEMA COLLECTION no nível de banco de dados.
Como as coleções de esquema XML estão no escopo do esquema relacional, a entidade de segurança deve ter permissão ALTER no esquema relacional.
As seguintes permissões permitem que uma entidade de segurança crie uma coleção de esquema XML em um esquema relacional em um banco de dados em um servidor:
Permissão CONTROL no servidor
Permissão ALTER ANY DATABASE no servidor
Permissão ALTER no banco de dados
Permissão CONTROL no banco de dados
Permissões ALTER ANY SCHEMA e CREATE XML SCHEMA COLLECTION no banco de dados
Permissões ALTER ou CONTROL no esquema relacional e CREATE XML SCHEMA COLLECTION no banco de dados
Esse último método de permissões é usado no exemplo seguinte.
O proprietário do esquema relacional se torna o proprietário da coleção de esquema XML criada naquele esquema. Esse proprietário tem controle total sobre a coleção de esquema XML. Portanto esse proprietário pode modificar a coleção de esquema XML, digitar uma coluna xml ou descartar a coleção e esquema XML.
Concedendo permissões em um objeto da coleção de esquema XML
As seguintes permissões são permitidas na coleção de esquema XML:
A permissão ALTER é necessária ao modificar o conteúdo de uma coleção de esquema XML existente usando uma instrução ALTER XML SCHEMA COLLECTION.
A permissão CONTROL permite que um usuário execute qualquer operação na coleção de esquema XML.
A permissão TAKE OWNERSHIP é necessária para transferir a propriedade da coleção de esquemas XML de uma entidade de segurança para outra.
A permissão REFERENCES autoriza a entidade de segurança a usar a coleção de esquema XML para digitar ou restringir colunas de tipo xml em tabelas, exibições e parâmetros. A permissão REFERENCES também é necessária quando uma coleção de esquema XML faz referência a outra.
A permissão VIEW DEFINITION permite que a entidade de segurança consulte o conteúdo de uma coleção de esquema XML por meio de XML_SCHEMA_NAMESPACE ou de exibições de catálogo, desde que a entidade de segurança tenha uma das permissões ALTER, REFERENCES ou CONTROL na coleção.
A permissão EXECUTE é necessária para validar valores inseridos ou atualizados pela entidade de segurança na coleção de esquemas XML que está digitando ou restringindo as variáveis, os parâmetros e as colunas de tipo xml. Essa permissão também é necessária para consultar o XML armazenado nessas colunas e variáveis.
Exemplos
Os cenários nos exemplos seguintes ilustram como as permissões de esquema XML funcionam. Cada exemplo cria o banco de dados, os esquemas relacionais e os logons de teste necessários. Esses logons recebem as permissões necessárias na coleção de esquema XML. Cada exemplo faz a limpeza necessária no final.
A. Concedendo permissões para criar uma coleção de esquema XML
O exemplo a seguir ilustra como são concedidas permissões para que uma entidade de segurança possa criar uma coleção de esquema XML. O exemplo cria um banco de dados de exemplo e um usuário de teste, TestLogin1. Em seguida, TestLogin1 recebe permissão ALTER no esquema relacional e CREATE XML SCHEMA COLLECTION no banco de dados. Com essas permissões, TestLogin1 pode criar uma coleção de esquema XML de exemplo.
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. Concedendo permissão para usar uma coleção de esquema XML existente
O exemplo seguinte ilustra ainda mais o modelo de permissão para a coleção de esquema XML. Ele ilustra como permissões diferentes são necessárias para criar e usar a coleção de esquema XML.
O exemplo cria um banco de dados de teste e um logon, TestLogin1. TestLogin1 cria uma coleção de esquema XML no banco de dados. Em seguida, o logon cria uma tabela e usa a coleção de esquemas XML para criar uma coluna xml tipada. Em seguida, o usuário insere dados e os consulta. Todas essas etapas requerem as permissões de esquema necessárias conforme mostrado no código.
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
-- 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 REFERENCES 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. Concedendo permissão ALTER em uma coleção de esquema XML
Um usuário precisa da permissão ALTER para modificar uma coleção de esquema XML existente no banco de dados. O exemplo a seguir ilustra como a permissão ALTER é concedida.
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. Concedendo a permissão TAKE OWNERSHIP em uma coleção de esquema XML
O exemplo a seguir ilustra como a propriedade do esquema XML pode ser transferida de um usuário para outro. Para tornar o exemplo mais interessante, os usuários neste trabalho de exemplo trabalham em diferentes esquemas relacionais padrão.
Este exemplo faz o seguinte:
Cria um banco de dados com dois esquemas relacionais, dbo e myOtherDBSchema.
Cria dois usuários, TestLogin1 e TestLogin2. TestLogin2 torna-se proprietário do esquema relacional myOtherDBSchema.
TestLogin1 cria uma coleção de esquema XML no esquema relacional dbo.
TestLogin1 concede a permissão TAKE OWNERSHIP então na coleção de esquemas XML para TestLogin2.
TestLogin2 se torna o proprietário da coleção de esquemas XML nomyOtherDBSchema, sem alterar o esquema relacional da coleção de esquemas 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, 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. Concedendo permissão VIEW DEFINITION em uma coleção de esquema XML
Este exemplo ilustra como são concedidas permissões VIEW DEFINITION para uma coleção de esquema 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