plik

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 2/20

Informacje o module

Opis modułu

Język  SQL  został  opracowany  w  1987  roku  z  myślą  o  relacyjnych  bazach
danych.  Składa  się  on  z  trzech  składowych:  języka  definiowania  danych
(DDL),  języka  sterowania  danymi  (DCL)  oraz  języka  operowania  na  danych
(DML).  W  module  tym  zostaną  wprowadzone,  a  następnie  przedstawione
na przykładach podstawowe instrukcje języka definiowania danych – języka
SQL DDL (z ang. Data Definition Language) oraz języka sterowania danymi –
języka SQL DCL (z ang. Data Control Language).

Cel modułu

Celem modułu jest zaprezentowanie podstaw języka SQL DCL i DDL. W
module tym pokazano składnię częściową instrukcji oraz przykładowe ich
użycie.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:



potrafił użyd podstawowych instrukcji języka T-SQL DDC



potrafił użyd podstawowych instrukcji języka T-SQL DDL



rozumiał mechanizm zarządzania uprawnieniami dostępu do
obiektów bazy danych



rozumiał mechanizm manipulowania bazą danych i jej obiektami

Wymagania wstępne

Przed przystąpieniem do pracy z tym modułem powinieneś:



wiedzied w jaki sposób stworzyd bazę danych wraz z jej
podstawowymi obiektami.



wiedzied, w jaki sposób poruszad się po Microsoft SQL Server
Menagement Studio.



wiedzied, w jaki sposób należy zakładad użytkowników i przypisywad
ich do bazy danych.

Mapa zależności modułu

Zgodnie z mapą zależności przedstawioną na Rys. 1, przed przystąpieniem
do realizacji tego modułu należy zapoznad się z materiałem zawartym
w Module 3 i Module 10.

Moduł 5

Dodatek

Moduł 1

Moduł 2

Moduł 3

Moduł 4

Moduł 6

Moduł 7

Moduł 8

Moduł 9

Moduł 10

Moduł 11

Moduł 12

Moduł 13

Rys. 1 Mapa zależności modułu

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 3/20

Przygotowanie teoretyczne

Przykładowy problem

Jak  zapewne wiesz z  bazy  danych  może korzystad  wielu  użytkowników, którzy  mogą  dysponowad
różnymi  prawami  dostępu  do  różnych  obiektów  w  bazie  danych.  Najgorszym  rozwiązaniem  jaki
może istnied to pełne zaufanie do osób korzystających z bazy i przydzielenie im wszystkich pełnych
praw.  Ze  względu  na  bezpieczeostwo  danych  zawartych  w  bazie  dobrze  jest  stosowad  zasadę
ograniczonego  zaufania  do  użytkownika.  Zatem  należy  zastanowid  się  jakiemu  użytkownikowi  lub
jakiej  grupie  użytkowników  jakie  prawa  należy  przydzielid.  Dobrze  jest  wiedzied  w  jaki  sposób
można  nadawad,  odmawiad  i  usuwad  prawa  dostępu.  W  kolejnym  kroku  należy  rozważyd,  czy
użytkownik taki powinien mied dostęp do całego obiektu typu tabela lub widok, czy może wystarczy
mu  dostęp  do  poszczególnych  kolumn.  Ze  względu  na  bezpieczeostwo  administrator  powinien
zastanowid  się  czy  lepszym  rozwiązaniem  nie  byłoby  umożliwid  użytkownikowi  lub  grupie
użytkowników  dostępu  do  obiektów  programowalnych  typu  procedury  składowane,  które
wprowadzają kolejny stopieo bezpieczeostwa i maskują fizyczna struktur bazy danych.

Podstawy teoretyczne

W części tej poznasz podstawy języka T-SQL DCL i T-SQL DDL. Dowiesz się w jaki sposób nadawad,
odmawiad i usuwad użytkownikowi prawo do wykonywania operacji na poszczególnych obiektach
baz danych, typu tabele i widoki, w jaki sposób zarządzad dostępem do programowalnych
obiektów. Nauczysz się tworzyd, modyfikowad i usuwad podstawowe obiekty.

Polecenia DCL
Instrukcje języka DCL służą do zarządzania uprawnieniami dostępu do obiektów bazy.
Najważniejszymi poleceniami języka DCL są instrukcje:



GRANT

– pozwala użytkownikowi lub roli na wykonywanie operacji określonej przez nadane

uprawnienie



DENY

– odmawia użytkownikowi lub roli określonego uprawnienia i zapobiega odziedziczeniu

go po innych rolach.



REVOKE

– usuwa uprzednio nadane lub odmówione uprawnienie.

Modyfikowanie dostępu do tabel
Dostęp do tabeli należy do efektywnych uprawnieo, jakimi dysponuje użytkownik. Dostępem tym
można sterowad poprzez zarządzanie uprawnieniami na poziomie tabel. Uprawnienia, którymi
można zarządzad, zostały przedstawione w Tab. 1.

Tab. 1 Uprawnienia, którymi można zarządzad na poziomie tabel

Usprawnienie

Opis

ALTER

Umożliwia modyfikowanie właściwości tabeli

CONTROL

Zapewnia uprawnienia właściciela

DELETE

Umożliwia usuwanie wierszy tabeli

INSERT

Umożliwia wstawienia wierszy do tabeli

REFERENCES

Umożliwia odwoływanie się do tabel z obcego
klucza

SELECT

Umożliwia wybieranie wierszy tabeli

TAKE OWNERSHIP

Umożliwia przejęcie prawa własności do tabeli

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 4/20

UPDATE

Umożliwia aktualizowanie wierszy tabeli

VIEW DEFINITION

Umożliwia dostęp do meta danych tabeli

Przyznawanie praw dostępu do tabeli
Za pomocą instrukcji

GRANT

można przyznawad prawa dostępu dla użytkownika oraz roli bazy

danych. W poniższym przykładzie użytkownik otrzyma uprawnienia

SELECT

INSERT

oraz

UPDATE

pewnej tabeli:

GRANT SELECT, INSERT, UPDATE
ON Nazwa_Tabeli
TO Nazwa_Uzytkownika

Ograniczanie praw dostępu do tabel
W celu zabronienia użytkownikowi praw dostępu można zetknąd się z dwojaką sytuacją.

1. Jeśli użytkownik zostały przyznane uprawnienia do tabeli, aby je zdjąd należy użyd instrukcji

REVOKE

REVOKE SELECT
ON Nazwa_Tabeli
TO Nazwa_Użytkownika

2. Może zdarzyd się sytuacja, że mimo zdjęcia uprawnieo, użytkownik nadal dysponuje tymi

uprawnieniami, jeżeli należy do roli, której to uprawnienie przyznano. W takim przypadku
należy użyd instrukcji

DENY

DENY DELETE
ON Nazwa_Tabeli
TO Nazwa_Użytkownika

Zapewnienie dostępu do pojedynczych kolumn
Istnieje również możliwośd przyznawania lub odmawiania praw dostępu nie tylko do tabel, ale
również do poszczególnych kolumn. W tabeli 2 pokazano uprawnienia dotyczące zarządzaniem
kolumnami tabeli.

Tab. 2 Uprawnienia dotyczące zarządzaniem kolumn tabeli

Uprawnienia

Opis

SELECT

Umożliwia wykonywanie selekcji na kolumnie

UPDATE

Umożliwia aktualizowanie kolumny

REFERENCE

Umożliwia odwoływanie się do kolumny z obcego klucza

Nadawanie praw dostępu do kolumn
Prawa dostępu do pojedynczej kolumny nadajemy poleceniem

GRANT.

W poniższym przykładzie

użytkownikowi zostanie nadane uprawnienie

SELECT

oraz

UPDATE

dotyczące kolumn

kolumna_1

Kolumna_2,…,kolumna_n w tabeli:

GRANT SELECT, UPDATE(kolumna_1, kolumna_2,…,kolumna_n)
ON Nazwa_Tabeli
TO Nazwa_Użytkownika

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 5/20

Odmawianie praw dostępu do kolumn
Podobnie jak w przypadku tabel, tak również w przypadku poszczególnych kolumn przy
odmawianiu praw dostępu użytkownikowi można zetknąd się z dwojaką sytuacją:

1. Jeśli użytkownik zostały przyznane uprawnienia do poszczególnych kolumn tabeli, aby je

zdjąd należy użyd instrukcji

REVOKE

REVOKE UPDATE
ON Nazwa_Tabeli
TO Nazwa_Użytkownika

2. Może zdarzyd się sytuacja, że mimo zdjęcia uprawnieo, użytkownik nadal dysponuje tymi

uprawnieniami, jeżeli należy do roli, której to uprawnienie przyznano. W takim przypadku
należy użyd instrukcji

DENY

DENY DELETE
ON Nazwa_Tabeli
TO Nazwa_Użytkownika

Zarządzanie dostępem do obiektów programowalnych
Obiekty programowalne, takie jak procedury składowane, mają swój własny kontekst zabezpieczeo.
Zatem  po  to,  żeby  użytkownik  mógł  wykonad  procedurę  składowaną,  potrzebuje  odpowiednich
uprawnieo.  Kiedy  aparat  bazy  danych  sprawdzi  uprawnienia  do  wykonywania  procedury
składowanej  i  są  one  właściwe,  wówczas  sprawdza,  czy  użytkownik  posiada  odpowiednie
uprawnienia do wykonywania operacji wewnątrz obiektów.

Tak  jak  i  inne  obiekty,  procedury  składowane  muszą  byd  w  odpowiedni  sposób  zabezpieczone.
Aspekt  zabezpieczania  procedur  składowanych  można  potraktowad  dwojako.  Z  jednej  strony
potrzebne  są  uprawnienia  na  przykład  do  tworzenia  procedur  składowanych,  a  z  drugiej  strony
użytkownicy  muszą  mied  odpowiednie  uprawnienia  do  wywołania  tej  procedury.  Tabela  3
przedstawia uprawnienia dotyczące procedur składowanych.

Tab. 3. Uprawnienia dotyczące procedur składowanych
Uprawnienia

Opis

ALTER

Umożliwia modyfikację właściwości procedury składowanej

CONTROL

Zapewnia uprawnienia właściciela

EXECUTE

Umożliwia wykonywanie procedury składowanej

TAKE OWNERSHIP

Umożliwia przejęcie prawa własności do procedury składowanej

VIEW DEFINITION

Umożliwia przeglądanie meta danych procedury składowanej

W momencie kiedy aplikacja żąda wywołania procedury składowanej, SQL Server musi sprawdzid,
czy użytkownik posiada uprawnienia

EXECUTE

dotyczące tej procedury.

GRANT EXECUTE On Nazwa_Procedury_Składowanej
TO Nazwa_Użytkownika

W taki sam sposób możemy odwoład lub odmówid użytkownikowi uprawnienia EXECUTE.

Polecenia DDL
Instrukcje języka DDL służą do manipulowania bazą danych i jej obiektami. Pozwalają one na:



tworzenie nowych obiektów



modyfikowanie obiektów już istniejących

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 6/20



usuwanie obiektów

Do podstawowych instrukcji języka DDL należą:



CREATE

– służy do tworzenie nowych obiektów



ALTER

– służy do modyfikacji obiektów już istniejących



DROP

– służy do usunięcia obiektu już istniejącego

Tworzenie nowych obiektów
Tworzenie nowych obiektów omówimy w kontekście tabel i widoków. W celu stworzenia nowej
tabeli wywołujemy instrukcję

CTEATE TABLE

. Składnia częściowa tej instrukcji została

przedstawiona poniżej:

CREATE TABLE nazwa_tabeli (

nazwa_kolumny_1 typ_danych [NULL | NOT NULL]

nazwa_kolumny_2 typ_danych [NULL | NOT NULL]

)

Przy definiowaniu tabeli należy podad jej nazwę, nazwy jej atrybutów oraz typ danych, które te
atrybuty mogą przyjmowad. Dodatkowo określamy, czy dany atrybut może przyjmowad wartośd

NULL

czy nie. Przykład tworzenia tabeli pokazano poniżej:

CREATE TABLE Osoba
(
    ID_Osoba    int        NOT NULL,
    Nazwisko    char(50)    NOT NULL,
    Imie       char(20)    NOT NULL,
    Telefon     int        NULL
)

W podobny sposób tworzymy widoki. W celu stworzenia nowego widoku wywołujemy instrukcję

CREATE VIEW.

Składnia częściowa tej instrukcji została przedstawiona poniżej:

CREATE VIEW nazwa_widoku[(kolumna_1, kolumna_2,…,kolumna_n)]
AS
SELECT [(kolumna_1, kolumn_2,…,kolumna_n)] FROM nazwa_tabeli

Przy definiowaniu widoku należy podad jego nazwę, nazwę kolumn, które będą używane w widoku,
oraz wyrażenie

SELECT

, które definiuje widok. Polecenie to może używad nie tylko jedna, ale

również wiele tabel, jak również innych widoków. Przykład tworzenia widoku pokazano poniżej:

CREATE VIEW Praca_dyplomowa(ID_Praca, Nazwisko, Imię, Tytuł)
AS
SELECT ID_Praca, Imię, Nazwisko, Tytuł FROM Praca

Modyfikowanie obiektów
Po stworzeniu obiektu powinniśmy móc go w razie jakiejkolwiek potrzeby zmodyfikowad. Do
modyfikacji obiektów (zarówno tabel jak i widoków) służy polecenie

ALTER

. W celu modyfikacji

istniejącej tabeli należy zastosowad polecenie

ALTER TABLE

, którego uproszczona składnię

częściową pokazano poniżej:

ALTER TABLE nazwa_tabeli
{|[ALTER COLUMN nazwa_kolumny]
  | { ADD
    {<definicja_kolumny> :=nazwa_kolumny_typ_danych
    {[NULL | NOT NULL]
    | DROP COLUMN nazwa_kolumny}

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 7/20

Najczęściej polecenie

ALTER TABLE

stosuje się do zmiany schematu relacji. Przykład zastosowania

tego polecenia do dodania lub usunięcia kolumny z tabeli pokazano poniżej:

ALTER TABLE Nazwa_Tabeli ADD nazwa_koluny typ_danych
ALTER TABLE Nazwa_Tabeli DROP COLUMN nazwa_kolumny

Podobnie jak tabelę możemy zmodyfikowad widok. W celu modyfikacji istniejącego widoku należy
zastosowad polecenie

ALTER VIEW

, którego uproszczona składnię częściową pokazano poniżej:

ALTER VIEW nazwa_widoku [(kolumna_1, kolumna_2,…,kolumna_n)]
AS
SELECT wyrażenie_select

Przy modyfikacji widoku należy podad nazwę modyfikowanego widoku, nazwę jednej lub wielu
kolumn, z których składad się ma nowy widok oraz wyrażenie

SELECT

, które ma definiowad nowy

widok. Przykład zastosowania instrukcji modyfikacji widoku pokazano poniżej:

ALTER VIEW Praca_dyplomowa(Nazwisko, Imię, Tytuł)
AS
SELECT Imię, Nazwisko, Tytuł FROM Praca

Usuwanie obiektów
Po stworzeniu obiektu powinniśmy móc go w razie jakiejkolwiek potrzeby usunąd. Do usuwania
obiektów (zarówno tabel jak i widoków) służy polecenie DROP. W celu usunięcia istniejącej tabeli
należy zastosowad polecenie DROP TABLE. Usuwa ono również schemat tabeli. Jego uproszczoną
składnię częściową pokazano poniżej:

DROP TABLE nazwa_tabeli

gdzie w nazwie tabeli podajemy nazwę obiektu, który chcemy usunąd. Przykładowo:

DROP TABLE Osoba

W celu usunięcia istniejącego widoku należy zastosowad polecenie

DROP VIEW

. Jego uproszczoną

składnię częściową pokazano poniżej:

DROP VIEW nazwa_widoku

gdzie w nazwie widoku podajemy nazwę obiektu, który chcemy usunąd. Przykładowo:

DROP VIEW Osoba PracaDyplomowa

Przykładowe rozwiązanie

Dodawanie użytkowników
Dodawad  użytkowników  można  na  dwa  sposoby.  Pierwszy  sposób  polega  na  tworzeniu
użytkowników  z poziomu języka T-SQL używając  procedur składowanych. Drugi sposób polega na
tworeiu użytkowników z poziomu SQL Server Menagement Studio. Poniżej pokażemy w jaki sposób
tworzyd użytkowników na dwa sposoby.

Sposób 1. Poziom języka T-SQL

W pierwszym kroku związanym z zarządzaniem użytkownikami jest stworzenie użytkowników
serwera i bazy danych. W tym celu stosujemy systemowej procedury składowanej

sp_addlogin

. W

naszym przykładzie stwórzmy dwóch użytkowników

Konsultant1

Konsultant2

EXEC sp_addlogin Konsultant1
EXEC sp_addlogin Konsultant2

Następnie pozwalamy nowo stworzonym użytkownikom na dostęp do bazy danych. W tym celu
używamy systemowej procedury składowanej

sp_grantdbaccess

. W naszym przykładzie mamy:

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 8/20

EXEC sp_grantdbaccess Konsultant1
EXEC sp_grantdbaccess Konsultant2

Po stworzeniu użytkowników i daniu im możliwości dostępu do bazy danych w kolejnym kroku
dodajemy ich do roli. W tym celu używamy systemowej procedury składowanej

sp_addrolemember

. W naszym przykładzie mamy:

EXEC sp_addrolemember Obsluga, Konsultant 1
EXEC sp_addrolemember Obsluga, Konsultant 2

W wyniku stworzyliśmy dwóch użytkowników,

Konsultant1

Konsultant2

, którym nadaliśmy

prawo dostępu do bazy danych a następnie przydzieliliśmy ich do roli

Obsluga

Poziom SQL Server Maenagement Studio

W celu stworzenia użytkowników poziomu SQL Server Menagemant Studio w pierwszym kroku
należy dodad użytkowników do serwera bazodanowego. W tym celu w obrębie

Object Explore

wybieramy katalog

Security

a następnie

. W obrębie zakładki

wybieramy prawym

przyciskiem myszy

New Login

a następnie dodajemy nazwę użytkownika, sposób autoryzacji,

ustawiamy hasło, które użytkownik zmieni przy pierwszym logowaniu, domyślną bazę danych oraz
język domyślny co pokazano na Rysunku 2.

Rys. 2 Zakładanie konta na SQL Server 2008 użytkownikowi

W analogiczny sposób dodajemy Konsulatanta2. W następnym etapie dodajemy użytkowników do
bazy danych. W tym celu w obrębie

Object Explore

wybieramy

Datatabase

a następnie bazę

danych do której chcemy przypisad użytkowników. W naszym przypadku będzie to baza

Obsluga

Następnie wybieramy zakładkę

Security

Users

. W obrębie zakładki

Users

wybieramy prawym

przyciskiem myszy

New User

a następnie dodajemy użytkownika, który został wcześniej stworzony

na poziomie serwera co pokazano na Rysunku 3. W analogiczny sposób postępujemy z
Konsultantem2.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 9/20

Rys. 3 Dodawanie użytkownika do bazy danych

Na koocu należy zmienid jeszcze tylko ustawienia serwera tak żeby nowi użytkownicy mogli się
zalogowad. W tym celu klikamy prawym przyciskiem myszy na instancji serwera i wybieramy

Propertis

a następnie

Security

i zaznaczamy opcję

SQL Server and Windows Authentication

mode

co pokazano na Rysunku 4.

Rys. 4 Ustawienie autoryzacji

Zarządzanie uprawnieniami dostępu do tabeli
Podobnie  jak  w  przypadku  dodawania  użytkowników  tak  i  teraz  podczas  nadawania  uprawnieo
dostępu  do  tabel  możemy  uczynid  to  na  dwa  sposoby.  Pierwszy  sposób  polega  na  nadawaniu
uprawnieo  użytkownikom  użytkowników  z  poziomu  języka  T-SQL.  Drugi  sposób  polega  na
nadawaniu  uprawnieo  użytkownikom  z  poziomu  SQL  Server  Menagement  Studio.  Poniżej
pokażemy w jaki sposób tworzyd użytkowników na dwa sposoby.

Poziom języka T-SQL

W celu przyznania prawa dostępu dla użytkownika stosujemy instrukcje

GRANT

. Przykładowo dla

stworzonego użytkownika

Konsultant1

nadajmy mu prawo przeglądania i wstawiania danych do

tabeli Klienci. Zatem:

GRANT SELECT, INSERT,
ON Klienci
TO Konsultant1

W podobny sposób możemy nadad prawo przeglądania I wstawiania danych do tabeli

Klienci

dla

roli

Obsluga

. Zatem:

GRANT SELECT, INSERT,
ON Klienci
TO Obsluga

W analogiczny sposób odmawiamy (

DENY

) lub usuwamy (

REVOKE

) użytkownikowi lub roli

uprawnienia.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 10/20

W wielu sytuacjach znacznie bezpieczniejszym rozwiązaniem jest przydzielenie użytkownikowi
prawa wykonania procedury składowanej. Załóżmy, że posiadamy procedurę składowaną

proc_klienci

która zwraca w wyniku zapytanie

SELECT

w postaci tabeli klientów. W naszym

przykładzie przydzielimy prawo wykonania procedury składowanej użytkownikowi

Konsultant2

Zatem będziemy mieli:

GRANT EXECUTE On proc_klienci
TO Konsultant2

W analogiczny sposób możemy przydzielid prawo wykonywania procedury składowanej dla roli

Obsluga

. Zatem będziemy miel:

GRANT EXECUTE On proc_klienci
TO Obsluga

Poziom SQL Server Maenagement Studio

W celu nadania uprawnieo użytkownikowi dostępu do obiektu typu tabela należy w obrębie
zakładki

Tables

wybrad tabelę

Uzytkownik

a następnie klikamy prawym przyciskiem myszy

wybieramy

Properties

. W obrębie

Proberties

wybieramy zakładkę

Permissions

. Mając

zdefiniowanych użytkowników na poziomie bazy danych

Obsluga

nadajemy mu odpowiednie

uprawnienia. W naszym przypadku dajemy mu możliwośd przeglądania tabeli co pokazano na
Rysunku 5.

Rys. 5 Nadanie uprawnieo na tabeli Uzytkownik

Operacje na obiektach
Załóżmy, że chcemy stworzyd nowy obiekt w postaci tabeli, która będzie przechowywała informacje
o klientach. W tym celu stosujemy polecenie

CREATE TABLE

co pokazano poniżej:

CREATE TABLE Klienci
(
      ID_Klient    int        NOT NULL,
      Firma       char(100)    NOT NULL,
      Nazwisko    char(50)    NOT NULL,
      Imie       char(20)    NOT NULL,
      Telefon     int        NULL,
      Fax        int        NULL,
      email       char(20)    NULL

)

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 11/20

Jeżeli użytkownikowi Konsultant2 chcemy nadad prawo przeglądania danych o klientach możemy
dla tabeli Klienci stworzyd widok z udostępnionymi danym a użytkownikowi dad prawo dostępu do
widoku. Zatem tworzymy widok jak poniżej i nadajemy prawo dostępu do widoku w analogiczny
sposób jak pokazano to wyżej dla obiektu typu tabela.

CREATE VIEW Klienci_Obsluga(Firma, Nazwisko, Imię, Telefon)
AS
SELECT Imię, Nazwisko, Firma, Telefon FROM Klienci

Załóżmy, że chcemy dodad kolumnę przechowująca informacje o stronie internetowej do tabeli
Klienci. Wówczas:

ALTER TABLE Klienci ADD www char(50) NULL

W analogiczny sposób możemy zmodyfikowad widok Klienci_Obsluga w taki sposób żeby
Konsultant2 widział adres strony internetowej klienta. Zatem:

ALTER VIEW Klienci_Obsluga (Firma, Nazwisko, Imię, Telefon, www)
AS
SELECT Imię, Nazwisko, Firma, Telefon, www FROM Klienc

Załóżmy, że chcemy usunąd kolumnę przechowująca informacje o numerze faksu z tabeli Klienci.
Wówczas:

ALTER TABLE Klienci DROP COLUMN fax

Porady praktyczne

Język T-SQL DCL



Przyznawanie lub odmawianie praw dostępu do poszczególnych kolumn zwiększa
elastycznośd w zarządzaniu dostępem na przykład do poufnych danych z niektórych kolumn.



W  zarządzaniu  dostępem  do  obiektów  programowalnych  występuje  zagadnienie  łaocucha
praw własności. Łaocuch praw własności jest sekwencją obiektów bazy danych uzyskujących
dostęp  do  siebie  nawzajem.  W  sytuacji,  kiedy  w  tabeli  są  wiersze  uzyskane  z  procedury
składowanej,  procedura  ta  jest  obiektem  wywołującym,  a  tabela  obiektem  wywoływanym.
Gdy SQL Server napotka na taki łaocuch, aparat bazy danych sprawdza uprawnienia inaczej,
niż w przypadku indywidualnego dostępu do obiektów.



Tworzenie użytkowników oraz nadawanie im uprawnieo może odbywad się na dwa sposoby.
Sposób pierwszy wymaga znajomości języka T-SQL. Drugi sposób polega odbywa się z
poziomu SQL Server Menagement Studio.



W przypadku tworzenia użytkowników z poziomu języka T-SQL musimy znad odpowiednie
procedury składowane, które należy w tym celu użyd. W procedurach składowanych zapisane
są ustawienia, które zostaną wprowadzone podczas ich użycia.



Możemy  nadawad  uprawnienia  do  różnych  obiektów  w  bazie  danych.  W  rozwiązaniu
problemu  pokazano  w  jaki  sposób  nadawad  uprawnienia  do  obiektu  typu  tabela.  W
analogiczny  sposób  możemy  nadawad  uprawnienia  do  obiekty  typu:  widok,  funkcja  czy
procedura składowana.



Użytkowników możemy grupowad według nadanych im uprawnieo. Wówczas możemy
założyd role i pogrupowad użytkowników według ról jakie pełną w bazie danych.
Konsekwencją tego jest to, że możemy przypisad dostęp do obiektów bazy danych nie tylko
dla pojedynczego użytkownika ale również dla grupy użytkowników zapisanych w roli.

Język T-SQL DDL



Poleceniem

CREATE

możemy utworzyd również obiekt programowalny w postaci procedury

składowanej. W tym celu wywołujemy instrukcję

CREATE PROCEDURE

. Przykładowa składnia

częściowa instrukcji tworzenia procedury bez parametru została pokazana poniżej:

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 12/20

CREATE PROCEDURE nazwa_procedury
AS
SELECT wyrażenie



Polecenie

ALTER

modyfikuje również obiekt programowalny w postaci procedury

składowanej. W tym celu wywołujemy instrukcje

ALTER PROCEDURE

. Przykładowa składnia

częściowa instrukcji modyfikowania procedury została pokazana poniżej:

ALTER PROCEDURE nazwa_procedury
WITH [RECOMPILE | ENCRYPTION]
AS
SELECT wyrażenie



W celu usunięcia istniejącej procedury składowanej należy zastosowad polecenie

DROP

PROCEDURE

. Jego uproszczoną składnię częściową pokazano poniżej:

DROP PROCEDURE nazwa_widoku



Tworzenie, modyfikacja i usuwanie może odbywad się na obiektach typu: baza danych,
funkcja, indeks, tabela, procedura składowana, widok, wyzwalacz.



Polecenie

CREATE

służy do tworzenia wszystkich obiektów baz danych: tabel (

CREATE

TABLE

), widoków (

CREATE VIEW

), procedur składowanych (

CREATE PROCEDURE

), indeksów

(

CREATE INDEX

), wyzwalaczy (

CREATE TRIGGER

). Tylko tymczasowe twory, takie jak zmienne

czy kursory, deklarujesz (nie tworzysz) za pomocą polecenia

DECLARE



Do istniejące tabeli możemy zawsze dodad ograniczenia na istniejąca kolumnę Przykładowo:

ALTER TABLE Osoba_1
ADD CONSTRAINT CK_Nr_Indeksu CHECK (Nr_Indeksu > 10000)

Ograniczenie to wymusza wstawianie w kolumnie

Nr_indeksu

liczb całkowitych większych

10000

Uwagi dla studenta

Jesteś przygotowany do realizacji laboratorium jeśli:



rozumiesz zasadę zarządzania uprawnieniami do obiektów bazy danych



rozumiesz mechanizm manipulowania bazą danych i jej obiektami



umiesz podad przykłady obiektów baz danych, do których można zastosowad składnię języka
T-SQL DDL oraz T-SQL DCL

Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że
rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego
w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.

Dodatkowe źródła informacji

1. Kalen Delaney, Microsoft SQL Server 2005: Rozwiązania praktyczne krok po kroku, Microsoft

Press, 2006

W książce autor w przystępny i zrozumiały sposób przedstawia między innymi w
jaki sposób nadawad prawa dostępu do bazy danych oraz w jaki sposób zarządzad
rolami. Następnie pokazuje jak prosto można nadawad uprawnienia do obiektów
bazy danych typu tabela i widok.

2. Edward Whalen, Microsoft SQL Server 2005 Administrator’s Companion, Microsoft Press, 2006

W książce autor pokazał w jaki sposób można zorganizowad nadawanie uprawnieo
do bazy danych oraz do obiektów bazy danych od strony administracyjnej. Przybliża
również jak należy planowad politykę bezpieczeostwa serwera i bazy danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 13/20

poprzez nadawanie odpowiednich uprawnieo. Pozycja szczególnie polecana osoba
chcącym poszerzyd wiedzę o tych elementach administracji serwera.

3. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo

Wiernik, SQL Server 2005 Implemantacja i obsuga, APN Promise, Warsyawa 2006

W książce przedstawiono obie składowe języka T-SQL: DCL i DDL. Pokazano w jaki
sposób tworzyd użytkowników, nadawad im uprawnienia oraz jak tworzyd
podstawowe obiekty bazy danych, jak je modyfikowad i usuwad. Książka szczególnie
polecana ze względu na dużą zawartośd dwiczeo laboratoryjnych.

4. Dusan Petkovic, Microsoft SQL Server 2008: A Beginner's Guide, McGraw-Hill, 2008

Pozycja napisana w sposób prosty. Wprowadza w SQL Server 2008 w sposób szybki
i łatwy. Osoba początkująca w SQL Server 2008 znajdzie w niej podstawy z każdego
tematu  dotyczącego  serwera  bazodanowego.  W  prosty  sposób  dowiesz  się  jak
należy  definiowad  użytkowników  i  nadawad  im  uprawnienia  oraz  jak  tworzyd,
modyfikowad i usuwad podstawowe obiekty bazy. Pozycja szczególnie polecana dla
osób początkujących.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 14/20

Laboratorium podstawowe

Problem 1 (czas realizacji 15 min)

Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że
firma National Insurance zarządzająca systemem prac dyplomowych na Twoim wydziale planuje
rozszerzenie systemu na skalę uczelniana. Pierwsze zadanie, jakie przed Tobą stoi, to zdefiniowanie
nowych użytkowników oraz modyfikacja użytkowników istniejących.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia z SQL
Server 2008



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.

2. Tworzenie
tabeli



Z menu głównego wybierz File -> Open -> File.



Odszukaj plik C:\Labs\Lab06\ddl.sql



Zaznacz i uruchom (F5) poniższy fragment kodu:

-- (1) Ustawiamy PraceDyplomowe jako baze robocza
USE PraceDyplomowe
GO

-- (2) Tworzymy w bazie danych tabele Osoba_1
CREATE TABLE Osoba_1(
ID_Osoby

smallint

NOT NULL,

Imie

varchar(10)

NOT NULL,

Nazwisko

varchar(10)

NOT NULL,

Nr_Indeksu

varchar(10)

NULL,

ID_Stopnien

smallint

NULL

)

3. Modyfiko
wanie tabeli



Mając otwarty skrypt ddl.sql w oknie Query zaznacz i uruchom poniższy
fragment kodu odpowiedzialny za zmianę definicji kolumny Nazwisko

-- (3) zmiana definicji kolumny Nazwisko
ALTER TABLE Osoba_1
ALTER COLUMN Nazwisko varchar(40) NOT NULL
ALTER COLUMN Nr_Indeksu int NULL
GO



Dodaj do tabeli Osoba kolumnę przechowującą informacje o numerze
telefonu. Zaznacz i uruchom (F5) poniższy fragment kodu.

-- (4) Dodanie kolumny Telefon
ALTER TABLE Osoba_1
ADD Telefon int
GO



Dodajmy ograniczenia na kolumnę indeks. Zaznacz i uruchom (F5)
poniższy fragment kodu.

-- (5) Dodajmy ograniczenie na kolumne Nr_Indeksu
ALTER TABLE Osoba_1

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 15/20

ADD CONSTRAINT CK_Nr_Indeksu CHECK (Nr_Indeksu > 10000)
GO



Usuomy ograniczenia nałożone w poprzednim kroku. Zaznacz i uruchom
(F5) poniższy fragment kodu.

-- (6) Usuwamy ograniczenie na kolumnie Nr_Indeksu
ALTER TABLE Osoba_1
DROP CONSTRAINT CK_Nr_Indeksu
GO



Usuomy kolumnę Telefon. Zaznacz i uruchom (F5) poniższy fragment
kodu.

-- (7) Usuwamy kolumne Telefon
ALTER TABLE Osoba_1
DROP COLUMN Telefon
GO

4. Usunięcie
tabeli



Usuniemy tabelę Osoba. Mając otwarty skrypt ddl.sql zaznacz i
uruchom (F5) poniższy fragment kodu.

-- (8) Usuwamy tabele Osoba
DROP TABLE Osoba_1
GO

Problem 2 (czas realizacji 30 min)

W wyniku rozszerzenia systemu zarządzania pracami dyplomowymi na skalę uczelni kolejne
zadanie jakie przed tobą stoi, to nowo zdefiniowanym użytkownikom przydzielenie odpowiednich
praw dostępu do obiektów w Twojej bazie danych.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia z SQL
Server 2008



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.

2. Dodawanie
użytkowników do
bazy danych



Z menu głównego wybierz File -> Open -> File.



Odszukaj pliku C:\Labs\Lab06\dcl_1.sql



Zaznacz i uruchom (F5) poniższy fragment kodu. Wynik pokazano na
Rys. 2.

-- (1) Ustawiamy PraceDyplomowe jako baze robocza
USE PraceDyplomowe
GO

-- (2) Dodajmy dwoch nowych uzytkownikow serwera i bazy danych,
  Sekretariat1 i Sekretariat2, umiescmy ich w grupie Dziekanat
  EXEC sp_addlogin Sekretariat1
  EXEC sp_addlogin Sekretariat2
  EXEC sp_grantdbaccess Sekretariat1
  EXEC sp_grantdbaccess Sekretariat2

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 16/20

  EXEC sp_addrole Dziekanat
  EXEC sp_addrolemember Dziekanat, Sekretariat1
  EXEC sp_addrolemember Dziekanat, Sekretariat2
GO

Rys. 2 Dodanie użytkowników do bazy danych



W oknie Object Explorer kliknij przycisk Connect -> Database Engine



W zakładce Authentication wybierz SQL Server Authentication



W polu Login wpisz użytkownika Sekretariat1. Hasło pozostaw puste



W wyniku powinieneś uzyskad połączenie do drugiej instancji serwera
co pokazano na Rys. 3.

Rys. 3 Podłączenie do drugiej instancji serwera



Odszukaj pliku C:\Labs\Lab06\dcl_2.sql



Zaznacz i uruchom (F5) poniższy fragment kodu. Wynik pokazano na
Rys. 4.

-- (1) Ustawmy PraceDyplomowe jako baze robocza
USE PraceDyplomowe
GO

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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 17/20

Pamiętaj, że powyższy fragment kodu zawarty w skrypcie dcl_2.sql
wykonujemy z zalogowanym użytkownikiem Sekretatiat1

Rys. 4 Sprawdzenie uprawnienia wykonania polecenia SELECY na tabeli Osoba

Wykonanie  poprzedniego  polecenia  nie  powiodło  się  ponieważ
domyślne  uprawnienia  nowego  użytkownika  nie  pozwalają  na
wykonanie  żadnego  polecenia  w  bazie  danych.  Skoro  nie  mamy
uprawnieo  do  wykonywania  poleceo,  tym  bardziej  nie  możemy  ich
nadawad.



Spróbujmy nadad uprawnienia innemu użytkownikowi będąc
zalogowani jako Sekretariat1. Zaznacz i uruchom (F5) poniższy fragment
kodu.

-- (3) Spróbujmy nadać uprawnienia innemu użytkownikowi
GRANT SELECT ON Osoba TO Sekretariat2
GO

Podjąłeś

nieudaną

próbę

nadania

innemu

użytkownikowi

(Sekretariat2) uprawnieo do wykonywania polecenia

SELECT

w tabeli

Osoba.

3. Nadaj
uprawnienia



Przełącz się do okna ze skryptem dcl_1.sql



Zaznacz i uruchom (F5) poniższy fragment kodu.

-- (3) Nadaj uprawnienia do wykonywania polecenia SELECT
-- w tabeli Osoba użytkownikowi Sekretariat1
GRANT SELECT ON Osoba TO Sekretariat1
GO



Przełącz się do okna ze skryptem dcl_2.sql uruchomionego w kontekście
użytkownika Sekretariat1



Zaznacz i uruchom (F5) poniższy fragment kodu. Wynik uruchomienia
pokazano na Rys. 5.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 18/20

Rys. 5 Sprawdzenie uprawnien do wykonania polecenia SELECT



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu.

-- (3) Nadaj uprawnienia do wykonywania polecenia SELECT
-- w tabeli Osoba użytkownikowi Sekretariat2
GRANT SELECT ON Osoba TO Sekretariat2
GO

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

4. Cofnięcie
uprawnieo



Przełącz się do okna, w którym masz uruchomiony skrypt dcl_1.sql



Zaznacz i uruchom (F5) poniższy fragment kodu.

-- (4) Cofnij uprawnienia do wykonywania polecenia SELECT
-- w tabeli Osoba użytkownikowi Sekretariat1
REVOKE SELECT ON Osoba FROM Sekretariat1
GO



Przełącz się do okna, w którym masz uruchomiony skrypt dcl_2.sql.



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu.

-- (2) sprawdźmy, czy mamy uprawnienia do wykonania
-- polecenia SELECT na tabeli Osoba
SELECT * FROM Osoba
GO

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

5. Odbiór
uprawnieo



Przełącz się do okna, w którym masz uruchomiony skrypt dcl_1.sql.



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu.

-- (3) Nadaj uprawnienia do wykonywania polecenia SELECT
-- w tabeli Osoba użytkownikowi Sekretariat1
GRANT SELECT ON Osoba TO Sekretariat1
GO



Przełącz się do okna, w którym masz uruchomiony skrypt dcl_2.sql.



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu.

-- (2) sprawdzmy, czy mamy uprawnienia do wykonania
-- polecenia SELECT na tabeli Osoba

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 19/20

SELECT * FROM Osoba
GO



Przełącz się do okna, w którym masz uruchomiony skrypt dcl_1.sql.



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu.

-- (5) Zabroń wykonywania polecenia SELECT
-- w tabeli Osoba grupie Dziekanat
DENY SELECT ON Osoba TO Dziekanat
GO

Powyższy kod spowoduje zabronienie wszystkim użytkownikom w
grupie Dziekanat (czyli u nas konkretnie użytkownikom Sekretariat1 i
Sekretariat2) wykonywania polecenia SELECT w tabeli Osoba.



Przełącz się do okna, w którym masz uruchomiony skrypt dcl_2.sql.



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu.

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

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

6. Przekazywanie
uprawnieo



Przełącz się do okna w którym masz uruchomiony skrypt dcl_1.sql.



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu.

-- (6) nadaj uprawnienia do wykonywania polecenia SELECT

-- w tabeli Osoba uzytkownikowi Sekretariat1

-- z opcja nadawania przez uzytkownika uprawnien

-- do wykonywania polecenia SELECT w tej tabeli

GRANT SELECT ON Osoba TO Sekretariat1 WITH GRANT OPTION



Przełącz się do okna, w którym masz uruchomiony skrypt dcl_2.sql.



Zaznacz i uruchom (F5) ponownie poniższy fragment kodu

-- (3) sprobujmy nadac uprawnienia innemu uzytkownikowi

GRANT SELECT ON Osoba TO Sekretariat2

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

ITA-101 Bazy Danych

Język SQL DCL, DDL

Strona 20/20

Laboratorium rozszerzone

Zadanie 1 (czas realizacji 90 min)

Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że
firma  zarządzająca  bazą  AdventureWorks  planuje  rozszerzenie  i  modernizację  systemu  w  celu
spełnienia  pewnych  standardów.  W  związku  z  modernizacją  systemu  bazodanowego
najprawdopodobniej  ulegną  zmianie  pewne  obiekty  w  bazie  danych  (częśd  najprawdopodobniej
zostanie  zmodyfikowanych,  częśd  stworzonych,  a  częśd  usuniętych)  oraz  uprawnienia
poszczególnych  użytkowników  oraz  grup  użytkowników  do  poszczególnych  obiektów  w  bazie
danych (tabel, widoków, procedur składowanych, funkcji, itp.).

Zadania, jakie przed Tobą stoją, to:

1. Podjęcie decyzji, które obiekty w bazie danych pozostaną bez zmian, a które zostaną

zmodyfikowane lub usunięte.

2. Zdefiniowanie nowych użytkowników oraz zmodyfikowanie użytkowników istniejących w

celu przydzielenia im praw do obiektów w zmodernizowanej bazie danych.

3. Przeorganizowanie grupy użytkowników i nadanie im uprawnieo do obiektów bazy danych.