Ćwiczenia > Język T-SQL > Polecenia języka DCL

4.2 Polecenia języka DCL



Aby móc wykonywać operacje na danych, użytkownik musi mieć nadane odpowiednie uprawnienia. Do zarządzania uprawnieniami użytkowników służą polecenia języla DDL (ang. Data Control Language): GRANT, REVOKE oraz DENY.

Krok 1 - Dodaj nowych użytkowników bazy danych i sprawdź ich domyślne uprawnienia

1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_4_2_1.sql.
5. Zaznacz i uruchom (klawisz F5) fragmenty kodu oznaczone w komentarzach jako (1) oraz (2).


-- (1) ustawmy Biblioteke jako baze robocza 
USE Biblioteka
GO

-- (2) dodajmy dwoch nowych uzytkownikow serwera i bazy danych,
-- Czytelnik1 i Czytelnik2, umiescmy ich w grupie Czytelnicy
EXEC sp_addlogin Czytelnik1
EXEC sp_addlogin Czytelnik2
EXEC sp_grantdbaccess Czytelnik1
EXEC sp_grantdbaccess Czytelnik2
EXEC sp_addrole Czytelnicy
EXEC sp_addrolemember Czytelnicy, Czytelnik1
EXEC sp_addrolemember Czytelnicy, Czytelnik2
GO


Powyższy kod dodaje dwóch użytkowników serwera - Czytelnik1 oraz Czytelnik2 (loginy serwera SQL, nie zaś logowanie w systemie Windows). Dodatkowo nadajemy użytkownikom dostęp do bazy (czyli inaczej mówiąc mapujemy loginy do bazy pod tymi samymi nazwami). Wreszcie tworzymy grupę w bazie danych i dodajemy do niej uprzednio utworzonych użytkowników.

Nie przejmuj się, że nie bardzo rozumiesz na razie mechanizmy bezpieczeństwa (loginy, grupy itd.). Dokładniej zapoznamy Cię z tymi zagadnieniami w rozdziale 9 ćwiczeń). Na razie musisz tylko wiedzieć, jakie operacje wykonaliśmy.


6. Uruchom drugą kopię programu Query Analyzer (z menu Start z grupy programów Microsoft SQL Server).
7. Zaloguj się używając uwierzytelniania SQL Server jako Czytelnik1 (hasło pozostaw puste).
8. W menu głównym programu wybierz File - Open.
9. W oknie Open Query File wybierz plik demo_4_2_2.sql.
10. Zaznacz i uruchom (klawisz F5) fragmenty kodu oznaczone w komentarzach jako (1) oraz (2).


-- (1) ustawmy Biblioteke jako baze robocza
USE Biblioteka
GO

-- (2) sprawdzmy, czy mamy uprawnienia do wykonania
-- polecenia SELECT na tabeli Wydawnictwa
SELECT * FROM Wydawnictwa
GO


Powyższy kod ustawi bazę danych Biblioteka jako bazę roboczą i spróbuje wykonać polecenie SELECT na tabeli Wydawnictwa w kontekście użytkownika Czytelnik1.

Wynikiem działania powinien być komunikat:

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Wydawnictwa', database 'Biblioteka', owner 'dbo'.


Domyślne uprawnienia nowego użytkownika nie pozwalają na wykonanie żadnego polecenia w bazie danych.

11. Skoro nie mamy uprawnień do wykonywania poleceń, tym bardziej nie możemy ich nadawać. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) sprobujmy nadac uprawnienia innemu uzytkownikowi
GRANT SELECT ON Wydawnictwa TO Czytelnik2
GO


Podjąłeś nieudaną próbę nadania innemu użytkownikowi (Czytelnik2) uprawnień do wykonywania polecenia SELECT w tabeli Wydawnictwa. Wynikiem próby jest komunikat:

Server: Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.

Krok 2 - Nadaj uprawnienia używając polecenia GRANT

1. Przełącz się do okna programu Query Analyzer uruchomionego z użyciem logowania systemu Windows (to okno, w którym masz otwarty skrypt demo_4_2_1.sql).
2. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) nadaj uprawnienia do wykonywania polecenia SELECT
-- w tabeli Wydawnictwa uzytkownikowi Czytelnik1
GRANT SELECT ON Wydawnictwa TO Czytelnik1
GO


Powyższy kod powoduje nadanie użytkownikowi Czytelnik1 uprawnień do wykonywania polecenia SELECT w tabeli Wydawnictwa.

3. Przełącz się do okna programu Query Analyzer uruchomionego w kontekście użytkownika Czytelnik1 (to okno, w którym masz otwarty skrypt demo_4_2_2.sql). Możesz poznać, jaki użytkownik jest zalogowany patrząc w oknie programu Query Analyzer pasek tytułowy okna lub pasek stanu (patrz rysunek 4.2.1).



Rys. 4.2.1 Query Analyzer - informacje o bieżącym użytkowniku


4. Zaznacz i uruchom (F5) ponownie fragmenty kodu oznaczone w komentarzach jako (1) i (2).

Tym razem powinno wynikiem działania kodu powinno być wyświetlenie zawartości tabeli Wydawnictwa.

5. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (3).

I tym razem nie udaje się nadać praw innemu użytkownikowi. Czyli nadanie uprawnień do wykonywania polecenia nie jest równoznaczne z umożliwieniem nadawania uprawnień innym użytkownikom.

Nie musisz nadawać uprawnień do wykonywania każdego polecenia SQL osobno. Jeśli na przykład chcesz nadać użytkownikowi Czytelnik1 uprawnienia do wykonywania wszystkich poleceń SQL w tabeli Wydawnictwa, użyj składni:

GRANT ALL ON Wydawnictwa TO Czytelnik1


Krok 3 - Cofnij uprawnienia używając polecenia REVOKE

1. Przełącz się do okna programu Query Analyzer uruchomionego z użyciem logowania systemu Windows (to okno, w którym masz otwarty skrypt demo_4_2_1.sql).
2. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (4).


-- (4) cofnij uprawnienia do wykonywania polecenia SELECT
-- w tabeli Wydawnictwa uzytkownikowi Czytelnik1
REVOKE SELECT ON Wydawnictwa FROM Czytelnik1
GO


Powyższy kod spowoduje cofnięcie uprzednio nadanych użytkownikowi Czytelnik1 uprawnień do wykonywania polecenia SELECT w tabeli Wydawnictwo.

3. Przełącz się do okna programu Query Analyzer uruchomionego w kontekście użytkownika Czytelnik1 (to okno, w którym masz otwarty skrypt demo_4_2_2.sql).
4. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (3).

Po cofnięciu uprawnień do wykonania polecenia SELECT uruchomienie tego fragmentu skryptu zwraca informację o błędzie.

Krok 4 - Odbierz uprawnienia używając polecenia DENY

1. Przełącz się do okna programu Query Analyzer uruchomionego z użyciem logowania systemu Windows (to okno, w którym masz otwarty skrypt demo_4_2_1.sql).
2. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (3) (polecenie GRANT).
3. Przełącz się do okna programu Query Analyzer uruchomionego w kontekście użytkownika Czytelnik1 (to okno, w którym masz otwarty skrypt demo_4_2_2.sql).
4. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (2) (polecenie SELECT).

Ponieważ już tą operację wykonywaliśmy, wiesz doskonale, że uruchomienie kodu z poleceniem SELECT spowoduje wyświetlenie zawartości tabeli Wydawnictwa (użytkownik ma nadane uprawnienia).

5. Przełącz się ponownie do okna programu Query Analyzer uruchomionego z użyciem logowania systemu Windows (to okno, w którym masz otwarty skrypt demo_4_2_1.sql).
6. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (5).


-- (5) zabron wykonywania polecenia SELECT
-- w tabeli Wydawnictwa grupie Czytelnicy
DENY SELECT ON Wydawnictwa TO Czytelnicy
GO


Powyższy kod spowoduje zabronienie wszystkim użytkownikom w grupie Czytelnicy (czyli u nas konkretnie użytkownikom Czytelnik1 i Czytelnik2) wykonywania polecenia SELECT w tabeli Wydawnictwa.

7. Przełącz się ponownie do okna programu Query Analyzer uruchomionego w kontekście użytkownika Czytelnik1 (to okno, w którym masz otwarty skrypt demo_4_2_2.sql).
8. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (2) (polecenie SELECT).

Tym razem okaże się, że nie możemy wykonać polecenia SELECT. Dzieje się tak dlatego, że przed momentem zabroniliśmy grupie Czytelnicy, do której należy użytkownik, w którego kontekście pracujemy, wykonywania polecenia SELECT.

Zapamiętaj, że uprawnienia do wykonywania poleceń SQL mogą mieć trzy ustawienia:
- brak nadanych i odebranych uprawnień (nie wykonaliśmy żadnego polecenia DDL, albo polecenie REVOKE),
- nadane uprawnienia (wykonaliśmy polecenie GRANT),
- odebrane uprawnienie (wykonaliśmy polecenie DENY).

Polecenie REVOKE służy do wycofywania uprawnień pozytywnych (nadanych poleceniem GRANT) i negatywnych ("odebranych" poleceniem DENY).

Pamiętaj, że uprawnienia użytkownika sumują się z uprawnieniami grupy. Przy czym uprawnienia negatywne biorą górę nad pozytywnymi.

Stąd jeśli odbierzemy grupie uprawnienia do wykonywania polecenia, to użytkownicy mimo indywidualnie nadanych uprawnień nie będą mogli tego polecenia wykonywać.


Krok 5 - Nadaj uprawnienia z opcją nadawania uprawnień

1. Przełącz się do okna programu Query Analyzer uruchomionego z użyciem logowania systemu Windows (to okno, w którym masz otwarty skrypt demo_4_2_1.sql).
2. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (6).


-- (6) nadaj uprawnienia do wykonywania polecenia SELECT
-- w tabeli Wydawnictwa uzytkownikowi Czytelnik1
-- z opcja nadawania przez uzytkownika uprawnien
-- do wykonywania polecenia SELECT w tej tabeli
GRANT SELECT ON Wydawnictwa TO Czytelnik1 WITH GRANT OPTION
GO


Powyższy kod nadaje uprawnienia do wykonywania polecenia SELECT w tabeli Wydawnictwa użytkownikowi Czytelnik1, ale to nie wszystko. Poza tym umożliwia użytkownikowi Czytelnik1 nadawanie uprawnień do wykonywania polecenia SELECT w tabeli Wydawnictwa (wyszczególniony fragment kodu - WITH GRANT OPTION).

3. Przełącz się ponownie do okna programu Query Analyzer uruchomionego w kontekście użytkownika Czytelnik1 (to okno, w którym masz otwarty skrypt demo_4_2_2.sql).
4. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (2).

Ponieważ grupa Czytelnicy, do której należy użytkownik Czytelnik1 nadal ma zabronione wykonywanie polecenia SELECT w tabeli Wydawnictwa, zostanie zwrócona informacja o błędzie.

5. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (3).

Tym razem okazuje się, że możemy nadać uprawnienia innemu użytkownikowi (a sami przecież nie posiadamy takich uprawnień!).

Jeśli kiedykolwiek zagubisz się w uprawnieniach, zawsze możesz zajrzeć do programu Enterprise Manager. We właściwościach każdej tabeli oraz użytkownika bazy danych (prawy klawisz myszy na tabeli lub nazwie użytkownika bazy danych i opcja Properties) znajdziesz przycisk Permissions..., który prowadzi do okna prezentującego aktualny stan uprawnień.


W bazie danych Biblioteka nadaj uprawnienia do wykonywania wszystkich poleceń (GRANT ALL) użytkownikom Czytelnik1 oraz Czytelnik2 we wszystkich tabelach. Będziesz musiał zmienić uprawnienia grupy Czytelnicy (użyj polecenia REVOKE do cofnięcia odebranych uprawnień).

Poeksperymentuj trochę z uprawnieniami w projekcie Prace dyplomowe. Stwórz skrypt, za pomocą którego utworzysz grupę w bazie danych, dodasz dwóch użytkowników (dodaj ich do grupy) oraz nadasz grupie uprawnienia do wykonywania kilku poleceń na wybranych tabelach. Skrypt zapisz pod nazwą jezyk_dcl.sql.


Przejdź dalej



Ćwiczenia > Język T-SQL > Polecenia języka DCL