Możliwości schematów

SQL Server 2005 dostarcza zupełnie nowych możliwości organizacji obiektów w bazach danych. Jest to nowa jakość w porównaniu do poprzedniej wersji tego produktu. Zgodnie z zasadą, że jeden przykład wart jest tysiąca słów - postaram się pokazać za pośrednictwem prostego zadania, jakie funkcjonalności można osiągnąć stosując schematy.

Zadanie jest proste - chodzi o to, by następujące zapytanie:

SELECT kolumna FROM tabela;

zwróciło różne wyniki dla różnych zalogowanych użytkowników. Prawdopodobnie taka funkcjonalność nie przyda się w zastosowaniach produkcyjnych (chociaż kto wie) - trzeba ją traktować bardziej w kategoriach eksperymentów z językiem T-SQL.

Zaczniemy od stworzenia bazy danych do testów (jako sysadmin):

CREATE DATABASE testowa;

Następnie przełączymy się do kontekstu utworzonej bazy i stworzymy 3 schematy (s1, s2, s3):

USE testowa;
GO
CREATE SCHEMA s1;
GO
CREATE SCHEMA s2;
GO
CREATE SCHEMA s3;
GO

Utworzymy też 3 tabele o nazwie tabela w poszczególnych schematach:

CREATE TABLE s1.tabela
(
kolumna varchar(50)
);
GO

CREATE TABLE s2.tabela
(
kolumna varchar(50)
);
GO

CREATE TABLE s3.tabela
(
kolumna varchar(50)
);
GO

Następnie dodamy po jednym rekordzie do poszczególnych tabel - w każdym rekordzie w kolumnie kolumna umieszczona będzie inna wartość:

INSERT INTO s1.tabela(kolumna) VALUES('niebieski');
INSERT INTO s2.tabela(kolumna) VALUES('zielony');
INSERT INTO s3.tabela(kolumna) VALUES('czerwony');
GO

Stworzymy teraz 3 loginy, baza testowa stanie się ponadto ich domyślną bazą danych:

CREATE LOGIN aplikacja1 WITH PASSWORD = 'aplikacja1_1234567', DEFAULT_DATABASE = testowa;
GO
CREATE LOGIN aplikacja2 WITH PASSWORD = 'aplikacja2_1234567', DEFAULT_DATABASE = testowa;
GO
CREATE LOGIN aplikacja3 WITH PASSWORD = 'aplikacja3_1234567', DEFAULT_DATABASE = testowa;
GO

Aby powyższe loginy miały dostęp do bazy testowa, konieczne jest - zgodnie z architekturą uprawnień SQL Server 2005 - utworzenie użytkowników bazodanowych powiązanych z tymi loginami (dla uproszczenia nazwy użytkowników i loginów będą takie same). Utworzonym użytkownikom określimy ponadto domyślny schemat:

CREATE USER aplikacja1 FOR LOGIN aplikacja1 WITH DEFAULT_SCHEMA = s1;
GO
CREATE USER aplikacja2 FOR LOGIN aplikacja2 WITH DEFAULT_SCHEMA = s2;
GO
CREATE USER aplikacja3 FOR LOGIN aplikacja3 WITH DEFAULT_SCHEMA = s3;
GO

Należy pamiętać o nadaniu użytkownikom uprawień do ich domyślnych schematów - nie dzieje się to automatycznie. Dla potrzeb przykładu nadamy wyłącznie prawa do odczytu danych z obiektów schematów:

GRANT SELECT ON SCHEMA :: s1 TO aplikacja1;
GO
GRANT SELECT ON SCHEMA :: s2 TO aplikacja2;
GO
GRANT SELECT ON SCHEMA :: s3 TO aplikacja3;
GO

W tym momencie wszystko jest już gotowe do przeprowadzenia testu. Logując się na poszczególne loginy i wydając komendę:

SELECT kolumna FROM tabela;

każdy z użytkowników uzyska w wynikowym resultsecie inną wartość koloru. Żeby to sprawdzić, nie jest konieczne trzykrotne logowanie się na poszczególne konta. Można skorzystać ze specjalnego wyrażenia EXECUTE AS.., które ustawia kontekst sesji na innego użytkownika. Wykonanie takiej operacji wymaga stosownych uprawnień, skoro jednak jesteśmy zalogowani jako sysadmin i wszystko nam wolno:

EXECUTE AS USER = 'aplikacja1';
SELECT kolumna FROM tabela;
REVERT;
-- niebieski

EXECUTE AS USER = 'aplikacja2';
SELECT kolumna FROM tabela;
REVERT;
-- zielony

EXECUTE AS USER = 'aplikacja3';
SELECT kolumna FROM tabela;
REVERT;
-- czerwony

Polecenie REVERT służy do przywracania poprzedniego kontekstu sesji.

Po zakończeniu testów pamiętajmy o usunięciu bazy testowa. Plik z gotowym do odpalenia kodem T-SQL zastosowanym w powyższym przykładzie można pobrać stąd.