Condividi tramite

Separazione fra schema e utente

  • Articolo

Data aggiornamento: 12 dicembre 2006

A partire da SQL Server 2005, ogni oggetto appartiene a uno schema di database. Per schema di database si intende uno spazio dei nomi distinto, separato dagli utenti del database. Può essere paragonato a un contenitore di oggetti. Gli schemi possono essere creati e modificati in un database. Agli utenti può essere consentito l'accesso a uno schema. Lo schema può essere di proprietà di qualsiasi utente. La proprietà degli schemi è trasferibile.

[!NOTA] Gli schemi di database sono diversi dagli schemi XML. Per ulteriori informazioni sugli schemi XML, vedere Gestione di insiemi di schemi XML sul server.

Per ulteriori informazioni sulla creazione di uno schema di database, vedere CREATE SCHEMA (Transact-SQL).

Nuovo comportamento

Nelle versioni precedenti di SQL Server gli utenti e gli schemi del database sono concettualmente lo stesso oggetto. A partire da SQL Server 2005, gli utenti e gli schemi sono separati. Gli schemi vengono utilizzati come contenitori di oggetti.

La separazione della proprietà dagli schemi ha implicazioni importanti. Gli schemi di database garantiscono maggiore controllo sulla protezione degli oggetti di database nei modi seguenti:

  • Le autorizzazioni per schemi ed entità a protezione diretta contenute negli schemi possono essere gestite con una maggiore precisione rispetto alle versioni precedenti. Per ulteriori informazioni, vedere GRANT (autorizzazioni per schemi) (Transact-SQL) e GRANT - autorizzazioni per oggetti (Transact-SQL).
  • La proprietà degli schemi e delle entità a protezione diretta definite a livello di ambito dello schema è trasferibile. Per ulteriori informazioni, vedere ALTER AUTHORIZATION (Transact-SQL).
  • È possibile spostare gli oggetti tra gli schemi. Per ulteriori informazioni, vedere ALTER SCHEMA (Transact-SQL).
  • Un singolo schema può contenere oggetti di proprietà di più utenti del database.
  • Più utenti del database possono condividere un singolo schema predefinito.
  • Uno schema può essere di proprietà di qualsiasi entità di database, inclusi i ruoli e i ruoli applicazione.
  • È possibile eliminare un utente del database senza rimuovere gli oggetti in uno schema corrispondente.

Con gli schemi di database vengono introdotte altre importanti modifiche alla protezione rispetto alle versioni precedenti:

  • Il codice scritto per le versioni precedenti di SQL Server può restituire risultati non corretti se in esso si presuppone che gli schemi siano equivalenti agli utenti del database.
  • Le viste del catalogo progettate per versioni precedenti di SQL Server, inclusa la vista sysobjects, possono restituire risultati non corretti.
  • Le catene di proprietà e il cambio di contesto utente possono comportarsi in modo diverso, perché gli utenti possono essere proprietari di più di uno schema. Per ulteriori informazioni sulle catene di proprietà, vedere Catene di proprietà and Gerarchia di autorizzazioni. Per ulteriori informazioni sul cambio di contesto, vedere Cambi di contesto.
  • In SQL Server 2000 gli oggetti di database sono di proprietà degli utenti. Il riferimento in quattro parti a un oggetto di database in SQL Server 2000 è [DatabaseServer].[DatabaseName].[ObjectOwner].[DatabaseObject]. A partire da SQL Server 2005, il riferimento a quattro parti a un oggetto di database è [DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject].

Modifiche alla proprietà degli oggetti

La proprietà Owner degli oggetti seguenti fa riferimento a uno schema e non a un utente:

  • CREATE TABLE
  • ALTER TABLE
  • CREATE VIEW
  • ALTER VIEW
  • CREATE INDEX
  • ALTER INDEX
  • CREATE FUNCTION
  • ALTER FUNCTION
  • DROP FUNCTION
  • VIEW_TABLE_USAGE
  • VIEW_COLUMN_USAGE
  • TABLE_CONSTRAINTS
  • REFERENTIAL_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • CONSTRAINT_COLUMN_USAGE
  • CHECK_CONSTRAINTS
  • COLUMN_DOMAIN_USAGE
  • COLUMNS
  • DOMAIN_CONSTRAINTS
  • ROUTINE_COLUMNS

Per ulteriori informazioni sulle colonne che restituiscono metadati degli utenti e le colonne che restituiscono metadati degli schemi, vedere la sezione "Viste e funzioni del catalogo per gli schemi" di seguito.

In SQL Server 2005 sono state introdotte oltre 250 nuove viste del catalogo. Alcune di esse sono associate agli utenti del database e agli oggetti dello schema che sostituiscono le tabella di sistema di SQL Server 2000. È consigliabile utilizzare le nuove viste del catalogo per accedere ai metadati. Per ulteriori informazioni, vedere Viste del catalogo (Transact-SQL).

Nella tabella seguente è indicato il mapping tra le tabelle di sistema di SQL Server 2000 e le viste del catalogo di SQL Server 2005 equivalenti:

Tabella di sistema di SQL Server 2000 Vista del catalogo di SQL Server 2005

Sysusers

sys.database_principals (Transact-SQL)

sys.schemas (Transact-SQL)

Syslogins

sys.server_principals (Transact-SQL)

Schemi predefiniti

Per risolvere i nomi delle entità a protezione diretta non completi, in SQL Server 2000 viene utilizzata la risoluzione dei nomi per controllare lo schema di proprietà dell'utente del database che ha eseguito la chiamata e lo schema di proprietà di dbo.

In SQL Server 2005 a ogni utente può essere assegnato uno schema predefinito che può essere impostato e modificato tramite l'opzione DEFAULT_SCHEMA di CREATE USER o ALTER USER. Se DEFAULT_SCHEMA non è definito, in SQL Server 2005 si presuppone che dbo sia lo schema predefinito.

[!NOTA] Agli utenti che si connettono utilizzando un gruppo con autenticazione di Windows non viene associato uno schema predefinito. Se un utente di questo tipo crea un oggetto non qualificato con uno schema, verrà creato un nuovo schema il cui nome verrà impostato sul nome dell'utente corrente. L'oggetto tabella verrà creato in questo nuovo spazio dei nomi con nome dell'utente.

Le nuove istruzioni DDL (Data Definition Language) possono introdurre complessità nei metadati di sistema che non vengono riflesse in modo accurato nelle tabelle di sistema precedenti, ad esempio sysobjects. In questo esempio l'ID utente e il nome dello schema restituiti da sysobjects non sono sincronizzati a causa della distinzione tra utente e schema introdotta con SQL Server 2005.

USE tempdb
GO
CREATE LOGIN u1 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'
CREATE USER u1 WITH DEFAULT_SCHEMA = u1
GO
GRANT CREATE TABLE TO u1 
GO
CREATE SCHEMA sch1
GO
CREATE SCHEMA u1 AUTHORIZATION u1
GO
EXECUTE AS USER = 'u1'
GO
CREATE TABLE t1(c1 int)
GO
REVERT
GO
SELECT user_name(uid) , * FROM sysobjects WHERE name = 't1'
GO
ms190387.Caution(it-it,SQL.90).gifAttenzione:
È necessario utilizzare le nuove viste del catalogo in tutti i database in cui sono state applicate in qualsiasi momento una o più delle istruzioni DDL seguenti: CREATE/ALTER/DROP SCHEMA, CREATE/ALTER/DROP USER, CREATE/ALTER/DROP ROLE, CREATE/ALTER/DROP APPROLE, ALTER AUTHORIZATION.

Viste e funzioni del catalogo per gli schemi

A partire da SQL Server 2005 gli schemi sono entità esplicite riflesse nei metadati, pertanto possono avere un solo proprietario, mentre un utente può essere proprietario di più schemi. Poiché questa relazione complessa non è riflessa nelle tabelle di sistema di SQL Server 2000, in SQL Server 2005 sono state introdotte nuove viste del catalogo, che rispecchiano i nuovi metadati in modo accurato.

Nella tabella seguente vengono illustrati le viste, i metadati e le funzioni del catalogo per gli schemi di SQL Server 2005:

Per informazioni su Vedere

Metadati generali dello schema

sys.schemas (Transact-SQL)

Viste degli schemi delle informazioni

Viste degli schemi delle informazioni (Transact-SQL)

Definizioni delle colonne restituite dalla vista INFORMATION_SCHEMA.SCHEMATA

SCHEMATA (Transact-SQL)

Esempi

A. Creazione di uno schema e assegnazione della proprietà a un utente

Nell'esempio seguente al database AdventureWorks vengono aggiunti un account di accesso e un utente di SQL Server denominato Marjorie, nonché un nuovo schema denominato Auditing. Marjorie viene assegnata come proprietaria dello schema Auditing.

CREATE LOGIN Marjorie
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';
USE AdventureWorks;
CREATE USER Marjorie FOR LOGIN Marjorie
GO
CREATE SCHEMA Auditing AUTHORIZATION Marjorie;
GO

B. Concessione all'utente di diritti su un altro schema

Nell'esempio seguente a un utente denominato Marjorie viene concessa l'autorizzazione SELECT sullo schema Purchasing del database AdventureWorks.

USE AdventureWorks;
GO
GRANT SELECT ON SCHEMA::Purchasing TO Marjorie;
GO

C. Modifica della proprietà di uno schema

Nell'esempio seguente nel database AdventureWorks viene creato un nuovo utente, Jon. A Jon viene concessa la proprietà dello schema Auditing del database AdventureWorks. L'utente denominato Marjorie viene quindi eliminato dal database AdventureWorks.

USE AdventureWorks;
GO
/* Create a new user in the database */
CREATE LOGIN Jon
    WITH PASSWORD = '1fdKJl3$nlNv3049jsBB';
USE AdventureWorks;
CREATE USER Jon FOR LOGIN Jon
GO
ALTER AUTHORIZATION ON SCHEMA::Auditing TO Jon;
GO
/* Removes the user from the system */
DROP LOGIN Marjorie;
GO
DROP USER Marjorie;
GO

D. Visualizzazione della proprietà di uno schema

Nell'esempio seguente viene visualizzato il proprietario dello schema Auditing del database AdventureWorks.

USE AdventureWorks;
GO
/* This method uses the INFORMATION_SCHEMA views */
SELECT * 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'Auditing';
GO

/* This method uses the sys.schemas catalog and links
   the names of the database users and server logins */
SELECT s.name AS 'Schema Name'
, db.name AS 'Database User Name'
, svr.name AS 'SQL Server Login Name'
FROM sys.schemas s
/* Obtains the name of the database user */
   INNER JOIN sys.database_principals db
      ON s.principal_id = db.principal_id
/* Obtains the name of the server login */
      INNER JOIN sys.server_principals svr
         ON db.sid = svr.sid
WHERE s.name = 'Auditing'
ORDER BY s.name

Vedere anche

Concetti

Gerarchia di autorizzazioni
Entità

Altre risorse

CREATE SCHEMA (Transact-SQL)
ALTER SCHEMA (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
DROP SCHEMA (Transact-SQL)
sys.schemas (Transact-SQL)
CREATE USER (Transact-SQL)
ALTER USER (Transact-SQL)
Modifiche allo schema nei database di pubblicazione

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione Cronologia

12 dicembre 2006
Contenuto modificato:
  • Aggiunta di nuovi esempi, ulteriori informazioni sull'utilizzo degli schemi e nuovi collegamenti.

17 luglio 2006
Contenuto modificato:
  • Sostanziale riorganizzazione del contenuto dell'argomento.