15. Nowości w "Data Control Language", czyli parę słów o schematach

Schematy

Schematy w MS SQL Server 2005 nie są tak naprawdę nowością. Zalążek koncepcji schematów pojawił się już w MS SQL Server 2000, ale dopiero teraz doczekał się on pełnej implementacji. Ale zamiast rozwodzić się nad historią schematów przejdźmy do wyjaśnienia co to tak naprawdę jest schemat.
We wcześniejszej wersji serwera aby użytkownik mógł wykonywać operacje na bazie danych takie jak SELECT, INSERT, DELETE czy EXECUTE musiał mieć przydzielone prawa do wykonywania takiej operacji na tym obiekcie. Prawa mogły mu zostać indywidualnie przydzielone lub mógł je odziedziczyć od grupy do której należał. Czyli wystarczyło sobie stworzyć grupę STUDENTS nadać jej prawo do operacji SELECT na tabeli w której znajdował się ich plan zajęć i wszystko było ok. Teraz wystarczy tylko utworzyć użytkownika w SCHEMA STUDENTS (w której również znajduje się tabela z przedmiotami) i nie trzeba się bawić z nadawaniem dostępu. Innym bardzo dużym udogodnieniem jest możliwość łatwego kasowania użytkowników. W SQL Server 2000 jeśli użytkownik był właścicielem obiektu nie można było go usunąć dopóki nie został zmieniony właściciel obiektu. Jeśli więc administrator bazy danych zmieniał pracę trzeba było się sporo namęczyć aby usunąć jego konto na serwerze. Wymagało to przepięcia wszystkich procedur, funkcji, tabel itd. które utworzył. Teraz obiekty bazy danych są przyporządkowane do schematu. Zaś użytkownik może mieć przyporządkowany domyślny schemat bez potrzeby jego posiadania.

Przejdźmy więc do utworzenia pierwszego schematu. Przyporządkowanie obiektu do schematu może nastąpić na dwa różne sposoby: jawny i niejawny. Pierwszy polega na bezpośredni wypisaniu przed obiektem nazwy schematu tak jak to ma miejsce w skrypcie znajdującym się poniżej.

CREATE TABLE Person.MyTable
(
            MyTableID INT
            , Note NVARCHAR(32)
            , ModificationDate DATETIME

)

Większość z Nas jest teraz zalogowanym do SQL Servera w trybie Windows Authentication co spowoduje, że gdy odpalimy podobny skrypt bez jawnie określonego schematu domyślnie doda go do schematu w którym się znajdujemy, a to oznacza DBO. Jeśli jednak byłbym zalogowany jak użytkownik grzegorzch. Zaś Grzegorz Ch. byłby domyślnie w schemacie Person to tabela również została by dodana do schematu Person. Wystarczyło by więc wpisać:

CREATE TABLE MyTable
(
            MyTableID INT
            , Note NVARCHAR(32)
            , ModificationDate DATETIME
)

Takie niejawne określanie schematu jest jednak zalecane. Trzeba być bardzo ostrożnym jeśli chcielibyśmy używać tej formy. Można w pewnym momencie zapomnieć o schemacie, co z pewnością nie doprowadzi do niczego dobrego.

Dodawanie użytkowników

Skoro jesteśmy zalogowani do serwera MS SQL Server 2005 (wcześniejsza wersja nie oferowała poniższej procedury) jako administrator możemy spróbować nowego użytkownika. W tym celu należy wykonać dwie operacje:

1. Dodać login do serwera baz danych
2. Dodać użytkownika do konkretnej bazy danych

Login jest dodawany dla każdej z osób która chce mieć dostęp do serwera. Użytkownik dodawany jest w ramach każdej z baz danych. Ponieważ Pan Jan Kowalski może mieć dostęp do dwóch różnych baz danych to w ramach jednego możemy udostępnić mu taką opcje.

CREATE LOGIN grzegorzch
            WITH PASSWORD = 'P@ssw0rd1'
            , DEFAULT_DATABASE = AdventureWorks

Domyślnie serwer nie przyjmuje haseł mało skomplikowanych. Aby udało się dodać login hasło musi składać się z małych i duży liter oraz znaków specjalnych.
Jeśli mamy już utworzony login przejdźmy do stworzenia użytkownika.

USE AdventureWorks
GO
CREATE USER grzegorzch
            FOR LOGIN grzegorzch
            WITH DEFAULT_SCHEMA = dbo

Jeśli wszystko bez problemów i udało się dodać login i użytkownika to możemy teraz przejść do zalogowania się w trybie SQL Server Authentication. Aby się za dużo nie rozpisywać jak to zrobić po prostu restartujmy SQL Management Studio. A kiedy pojawi się okienko logowania wybierzmy SQL Server Authentication, login = grzegorzch, password = P@ssw0rd1 i wciskamy Connect.

Teraz już jako grzegorzch możemy przeprowadzać operacje na bazie danych. W identyczny sposób można dodawać użytkowników do różnych schematów. Ponieważ grzegorzch został dodany do schematu dbo (Database Owner) ma możliwość do przeprowadzania praktycznie wszystkich operacji na bazie danych. Jeśli jednaka chcielibyśmy stworzyć użytkownika z możliwością tylko odczytywania danych bez sposobności do zarządzania obiektami bazodanowymi lub zmianą danych to nie ma z tym najmniejszego problemu. Wystarczy przydzielić użytkownika do konkretnego schematu lub roli.

Podsumowanie

W ciągu ostatnich paru lat Microsoft postawił sobie za zadanie znaczne poprawienie bezpieczeństwa swoich produktów. Jak sprawdzi się w tej sferze SQL Server 2005 czas pokaże. Z perspektywy programisty oraz administratora serwera bazodanowego wprowadzenie schematów oraz nowych procedur umożliwiających łatwiejszy pracę z kontami użytkowników znacznie usprawniło pracę.