czwartek, 12 marca 2015

Schemat bazy danych

Wszystkie obiekty w bazie danych są pogrupowane w zbiorze zwanym schematem. Schemat to kontener zawierający obiekty i do którego można w prosty sposób nadac uparwnienia. Zamiast nadawać uprawnienia na poszczególne obiekty, można to zrobić na wyższym czyli na schemacie.
Nazwa schematu jest częścią nazwy obiektu. Pełna kwalifikowana nazwa obiektu składa się z 4 części. Są to
  • Nazwa serwera
  • Nazwa bazy danych
  • Nazwa schematu
  • Nazwa obiektu
W momencie kiedy obiekt jest tworzony bez podania nazwy schematu, to jest on tworzony w schemacie domyślnym użytkownika tworzącego obiekt.

Zastosowanie a także konsekwensje używania uproszczonej nazy obiektów, bez podania nazwy schematu zaprezentuje na krótkim przykładzie.

Poniższy skrypt tworzy testową bazę danych, loginy, użytkowników, schemat oraz nadaje odpowiednie uprawnienia.
USE [master]

GO

 

--Create Database

CREATE DATABASE [SchemaTest]

GO

 

USE [SchemaTest]

GO

 

--Create logins, users and schema

CREATE LOGIN [MySchemaLogin] WITH PASSWORD='T0PSecret', DEFAULT_DATABASE=[SchemaTest], DEFAULT_LANGUAGE=[us_english]

GO

 

CREATE USER [MySchemaUser] FOR LOGIN [MySchemaLogin]

GO

 

CREATE SCHEMA [MySchema] AUTHORIZATION [MySchemaUser]

GO

ALTER USER [MySchemaUser] WITH DEFAULT_SCHEMA=[MySchema]

GO

 

CREATE LOGIN [dboSchemaLogin] WITH PASSWORD='T0PSecret', DEFAULT_DATABASE=[SchemaTest], DEFAULT_LANGUAGE=[us_english]

GO

 

CREATE USER [dboSchemaUser] FOR LOGIN [dboSchemaLogin]

GO

 

ALTER USER [dboSchemaUser] WITH DEFAULT_SCHEMA=[dbo]

GO

 

--Grant Permissions

GRANT CREATE TABLE TO [MySchemaUser]

GO

GRANT INSERT TO [MySchemaUser]

GO

GRANT SELECT TO [MySchemaUser]

GO

 

GRANT ALTER ON SCHEMA::[dbo] TO [MySchemaUser]

GO

GRANT INSERT ON SCHEMA::[dbo] TO [MySchemaUser]

GO

GRANT SELECT ON SCHEMA::[dbo] TO [MySchemaUser]

GO

 

GRANT ALTER ON SCHEMA::[dbo] TO [dboSchemaUser]

GO

GRANT INSERT ON SCHEMA::[dbo] TO [dboSchemaUser]

GO

GRANT SELECT ON SCHEMA::[dbo] TO [dboSchemaUser]

GO


Teraz loguje się do serwera korzystając z loginu MySchemaLogin.

Poniższy skrypt tworzy dwie tabele MyTable w dwóch różnych schematach a także dodaje po jednym wierszu do każdej z tabel.


CREATE TABLE MyTable (

    Greeting VARCHAR(100)

)

GO

 

INSERT INTO MyTable (Greeting) VALUES ('Hello World from MyTable in MySchema schema')

GO

 

CREATE TABLE dbo.MyTable (

    Greeting VARCHAR(100)

)

GO

 

INSERT INTO dbo.MyTable (Greeting) VALUES ('Hello World from MyTable in dbo schema')

GO


Pomimo tego, że tabele ozornie nazywają się tak samo, zapytanie poprawnie się wykonało.

Poniższa kwerenda weryfikuje istnienie dwóch tabel o nazwie MyTable w bazie danych.


SELECT SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables WHERE name ='MyTable'


SchemaNameTableName
dboMyTable
MySchemaMyTable


Teraz czas sprawdzić co się stanie w przypadku gdy będąc zalogowanym na loginie MySchemaLogin odpytana zostanie tabela MyTable.


SELECT 1 AS QueryID, Greeting FROM MyTable UNION ALL

SELECT 2 AS QueryID, Greeting FROM dbo.MyTable 



Pierwsze z zapytań odwołuje się do tabeli bez jawnie podanego schematu. Jak już wcześniej wspomniałem w przypadku kiedy schemat nie zostanie uwzględniony w zapytaniu, SQL Server będzie próbował znaleść obiekt MyTable w domyślnym schemacie użytkownika.

Wynik zapytanie jest następujący

QueryIDGreeting
1Hello World from MyTable in MySchema schema
2Hello World from MyTable in dbo schema


W przypadku drugiego zapytania nazwa obiektu została podana razem ze schematem.

No to jeszcze zobaczmy co się stanie w przypadku kiedy do zerwera zalogujemy się na loginie dboSchemaLogin i wywołamy te same zapytanie co powyżej.
QueryIDGreeting
1Hello World from MyTable in dbo schema
2Hello World from MyTable in dbo schema


Tym razem oba zapytanie zwrócily ten sam wynik. Stało się tak dlatego, że domyślnym schematem dla użytkownika powiązanego z loginem dboSchemaLogin jest dbo.

Podsumowując, dobrą praktyką jest używanie nazw dwuczłonowych. Pozwala to uniknąć niespodzianek zwiazanych z tym, gdy użytkownik tworzący obiekty, nie ma domyślnego schematu ustawionego na taki na którym większośc zapytań do bazy dancyh jest wykonywanych. W niektórych sytuacjach użycie nazw dwuczłonowych jest wręcz wymagane. Dwuczłonowe nazwy są jednym z wymagań podczas tworzenia materializowanych widoków.

Na koniec jeszcze skrypt czyszczący obiekty wykorzystane w powyższych przykładach.


USE [master]

GO

 

DECLARE @SQL VARCHAR(MAX)

DECLARE @SESSION_ID INT

DECLARE SESSIONDROP CURSOR FAST_FORWARD FOR SELECT session_id FROM sys.dm_exec_sessions WHERE login_name IN ('MySchemaLogin', 'dboSchemaLogin')

OPEN SESSIONDROP

FETCH NEXT FROM SESSIONDROP INTO @SESSION_ID

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @SQL = 'KILL ' + CONVERT(VARCHAR(10), @SESSION_ID)

    EXEC (@SQL)

 

    FETCH NEXT FROM SESSIONDROP INTO @SESSION_ID    

END

 

CLOSE SESSIONDROP

DEALLOCATE SESSIONDROP

 

DROP LOGIN MySchemaLogin 

GO

 

DROP LOGIN dboSchemaLogin 

GO

 

ALTER DATABASE [SchemaTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

 

DROP DATABASE [SchemaTest]

GO

Prześlij komentarz