ITA 101 Modul 05

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.

Wyszukiwarka

Podobne podstrony:
ITA 101 Modul 03
ITA 101 Modul 02
ITA 101 Modul 06
ITA 101 Modul 13
ITA 101 Modul 11
ITA 101 Modul 04
ITA 101 Modul 09
ITA 101 Modul Dodatek A
ITA 101 Modul 08
ITA 101 Modul 07
ITA 101 Modul 10
ITA 101 Modul 12
ITA 101 Modul 01
ITA 101 Modul 02 v2

więcej podobnych podstron