r06 05 (42)


Rozdział 6.
Ustawianie uprawnień bazy danych

Wcześniejszy rozdział omawiał model zabezpieczeń SQL Servera 2000. SQL Server ma dwa różne modele zabezpieczeń: Windows Authentication Mode (domyślnie) i Mixed Mode Authentication. Aby uzyskać połączenie z SQL Serverem można używać konta użytkownika/grupy Windows NT/2000 lub trybu bezpiecznego logowania do SQL Servera. Pomimo tego, że jest to sposób na logowanie do SQL Servera; w celu połączenia się i używania bazy danych wymagane jest konto użytkownika w tej bazie. Funkcję konta użytkownika w bazie może pełnić grupa, użytkownik Windows lub użytkownik SQL Servera lub wszystkie z tych kont zgrupowane w rolę SQL Servera. Role aplikacji, które są również dostępne, dostarczają bardzo dobrego mechanizmu, pomocnego w zabezpieczaniu aplikacji bazy danych.

Jednak, w bazie danych potrzebne są uprawnienia aby móc wykonywać jakiekolwiek operacje. SQL Server jest prawidłowo zabezpieczonym systemem. Jeżeli użytkownik chce wykonać jakąś operację, musi mieć do tego uprawnienie. W niniejszym rozdziale zostanie omówione działanie uprawnień, różnice pomiędzy uprawnieniami polecenia i obiektu oraz powiązania pomiędzy uprawnieniami a rolami i kontami użytkownika. Zostaną również omówione łańcuchy własności, ich znaczenie i ich działanie.

Potrzeba używania uprawnień

Do tej pory, wszystkie operacje przedstawione w tej książce były wykonywane poprzez konto sa (administrator systemu) lub poprzez konto użytkownika Windows, które przynależy do grupy administratorów lokalnych i dlatego jest członkiem stałej roli serwera sysadmin, w instalacji domyślnej. Konto sa również należy do tej roli, co zostało omówione w rozdziale 5. Dlatego przedstawione informacje na temat możliwości roli serwera sysadmin dotyczyły także możliwości konta sa. Członkowie roli sysadmin nie mają ograniczeń dotyczących operacji wykonywanych na SQL Serverze, co jest nie tylko wygodne ale również niezbędne w wielu zadaniach administracyjnych, jakie trzeba wykonywać na SQL Serverze. Zwyczajni użytkownicy nie powinni jednak łączyć się z SQL Serverem jako sa lub jako członkowie ustalonej roli serwera sysadmin, ponieważ mieliby w tym przypadku wszelkie uprawnienia dotyczące SQL Servera — wystarczające aby usunąć wszystkie bazy danych i wyłączyć serwer.

Poprzez stworzenie i implementację dobrego planu zabezpieczeń dla SQL Servera, można wyeliminować wiele problemów zanim wystąpią. Jest to lepsze niż spędzanie czasu na próbie odgadnięcia co spowodowało uszkodzenie danych (lub SQL Servera). Można ściśle określić jakie modyfikacje danych są dozwolone, jak również to jakie dane użytkownik może przeglądać. Można również określić, czy użytkownik może tworzyć kopie bezpieczeństwa bazy danych, kopie dziennika transakcji lub tworzyć i manipulować obiektami w bazie danych.

Kolejną zaletą udostępniania wielu kont logowania, użytkowników i uprawnień jest to, że można śledzić operacje, jakie są dozwolone dla użytkowników oraz całą działalności użytkowników. Możliwość ta jest bardzo istotna, szczególnie po to, aby mieć szansę określenia co się wydarzyło, gdy niespodziewanie dany element „zniknie” z bazy danych. Śledzenie w SQL Server 2000 zostanie omówione w rozdziale 20.

Implementacja uprawnień w bazie danych

Jedna istotna kwestia musi być wyjaśniona na wstępie: wszystkie uprawnienia w SQL Serverze są przyznawane użytkownikom bazy danych. Dlatego, omawiając uprawnienia, zawsze odnosi się to do uprawnień użytkownika w bazie danych, a nie uprawnień dotyczących logowania. Oznacza to, że uprawnienia (przywileje) są specyficzne dla bazy danych.

Od każdej reguły istnieje wyjątek. Ustalone role serwera są przyznawane dla kont logowania, a nie dla użytkowników bazy danych. Przyznanie tych ról kontom logowania jest sensowne, jednak, członkostwo w rolach serwera daje uprawnienia dotyczące całego serwera.

Przykładowo, Sue ma uprawnienia do bazy danych sales. (Jest ona regionalnym koordynatorem sprzedaży). Sue może mieć przywilej SELECT (odczyt) dotyczący tablic w bazie danych zakupów. (Użytkownik Sue może przeglądać zamówione elementy, ale jedynie departament zakupów może kupić nową rzecz). Sue może również nie mieć uprawnień dotyczących finansowej bazy danych. (Jedynie sprzedawcy mają uprawnienia do tej bazy danych).

W tym przykładzie, Sue musi łączyć się z SQL Serverem przy pomocy logowania SQL Server Authentication Mode lub jej konta w systemie Windows. W każdej z baz danych, ma ona odrębne konto użytkownika (może używać też własnego konta Windows lub korzystać z członkostwa w grupie), które umożliwia dostęp. Jak zostało wspomniane w poprzednim rozdziale, użytkownik może również skorzystać z uprawnień konta guest, przykładowo, dla bazy finansowej (jeżeli istnieje w niej konto guest). Należy pamiętać, że każdy użytkownik, w każdej z baz danych ma odrębne uprawnienia.

Tabela systemowa syspermissions śledzi zabezpieczenia w bazie danych. Dlatego też, zabezpieczenia są specyficzne dla każdej bazy danych. Istnieje również widok, zwany sysprotects, istniejący dla zachowania zgodności z wersją SQL Server 6.5. Widok ten i tabelę można znaleźć w każdej bazie danych w SQL Serverze, włączając w to bazę master.

Typy uprawnień

SQL Server 2000 korzysta z trzech pojęć, aby wskazać jakie może wykonywać użytkownik w związku z uprawnieniami. Są to polecenia: GRANT, DENY i REVOKE. Szczegóły tych poleceń zostaną omówione później, ale dobrze jest zacząć od przykładu użycia terminologii.

Aby pozwolić użytkownikowi wykonać odpowiednią czynność, należy przyznać (grant). Aby powstrzymać użytkownika od wykonania danej czynności należy zabronić(deny) tego użytkownikowi. Aby usunąć (cofnąć) przyznane wcześniej uprawnienie, należy je odwołać (revoke).

Można przyznać dwa typy uprawnień: na poziomie polecenia i na poziomie obiektu. Uprawnienia na poziomie polecenia umożliwiają użytkownikowi uruchamianie pojedynczych poleceń Transact-SQL, a uprawnienia na poziomie obiektu pozwalają na wykonanie pewnych operacji na danych: SELECT (odczyt), INSERT, UPDATE lub DELETE.

Pierwszeństwo uprawnień

Poznanie sposobu stosowania uprawnień umożliwia zrozumienie, kiedy poszczególne uprawnienia przynoszą efekt. Wszystkie uprawnienia SQL Servera sumują się, z wyjątkiem DENY, które zastępuje inne uprawnienia.

Jeżeli użytkownik posiada uprawnienie SELECT przyznane mu z roli Role1 oraz uprawnienie INSERT z roli Role2, oznacza to, że posiada on obydwa te uprawnienia. Również w przypadku, gdy uprawnienie SELECT zostanie wstrzymane (deny) w którejkolwiek z tych ról lub w obrębie indywidualnego konta użytkownika, oznacza to, że użytkownik nie posiada uprawnienia SELECT. DENY zawsze „przesłania” wszelkie inne uprawnienia.

Specjalne uprawnienia SQL Servera

SQL Server 2000 ma kilka różnych poziomów uprawnień. Większość z tych uprawnień jest specyficznych dla bazy danych. Jednak, jak zostało wcześniej wspomniane, stałe role serwera są związane z kontami logowania, a nie użytkownikami bazy danych. W rozdziale 5 powiedziano, że każda rola zawiera w sobie określony zbiór uprawnień. Również członkostwo w roli sysadmin oznacza posiadanie własnego, szczególnego zbioru uprawnień.

W każdej bazie danych są ustalone role bazy danych, z których każda wiąże się z określonym zbiorem uprawnień. Każda baza danych posiada również specjalnego użytkownika zwanego dbo (właściciel bazy danych). Chociaż nigdzie nie ma podanych o tym informacji bezpośrednio, istnieje pojęcie właściciela obiektu bazy danych. Specjalne uprawnienia są przypisane do każdego, kto przynależy to tej roli pojęciowej.

W dalszej części tego rozdziału, zostanie omówiona rola public i wynikające z niej przywileje. Kolejna sekcja poszerza temat stałych ról serwera i ich uprawnień.

Uprawnienia stałych ról serwera

Każda rola ma związane z nią ukryte uprawnienia, można je zobaczyć uruchamiając procedurę systemową sp_srvrolepermission:

sp_srvrolepermission [[@srvrolename =] 'role']

W tej składni, role jest nazwą ustalonej roli serwera, której uprawnienia mają być przeglądane.

Wynikiem działania tej procedury jest wydruk przedstawiony poniżej. Przykładowo, uruchamiając:

EXEC sp_srvrolepermission 'dbcreator'

otrzyma się informacje:

ServerRole Permission

....................................................

dbcreator Add member to dbcreator

dbcreator ALTER DATABASE

dbcreator CREATE DATABASE

dbcreator DROP DATABASE

dbcreator Extend database

dbcreator RESTORE DATABASE

dbcreator RESTORE LOG

dbcreator sp_renamedb

(8 row(s) affected)

Wszystkie uprawnienia i role serwera zostały wyjaśnione w kolejnych sekcjach.

sysadmin

Członkowie roli serwera sysadmin mogą wykonać wszelkie możliwe operacje na SQL Serverze. Mają oni przyznany bardzo efektywny zbiór uprawnień dlatego należy rozważnie przydzielać użytkowników do tej roli. Konto sa zawsze należy do tej roli i nie może być usunięte z roli sysadmin. Członkowie ustalonej roli systemowej sysadmin są zawsze pojmowani jako właściciele każdej bazy danych, której używają. Nie można zabronić użytkownikom tej roli dostępu do żadnej z baz danych na SQL Serverze.

Interfejs użytkownika dostarcza listy praw dla członków roli sysadmin; jest to jednak lekko mylące, ponieważ członek roli sysadmin może naprawdę wykonać wszelkie operacje. Należy o tym pamiętać, decydując się na przydzielenie kogoś do tej roli.

serveradmin

Członkami tej roli powinni być administratorzy serwera, którzy nie będą raczej administrować bazami danych lub innymi obiektami. Użytkownicy, korzystający z tej roli mogą wykonać następujące operacje:

setupadmin

Członkowie roli setupadmin są typowymi administratorami, którzy konfigurują zdalne serwery. Mogą oni wykonywać następujące operacje:

securityadmin

Członkowie roli securityadmin mogą wykonywać w SQL Server wszelkie operacje związane z bezpieczeństwem całego serwera. Dobrymi kandydatami do tej roli są pracownicy pomocy technicznej (tworzący nowe konta). Mogą oni wykonywać następujące operacje:

Przynależność do roli securityadmin nie daje dostępu do wszystkich baz danych. Dlatego, niektóre procedury składowe, takie jak sp_helpdb lub sp_droplogin, mogą zgłaszać błędy przy próbie dostępu do bazy danych, do której użytkownik nie posiada prawa przeglądania danych. Informacje, które użytkownik może przeglądać, nadal będą wyświetlane i w przypadku sp_droplogin, konta zostaną usunięte.

processadmin

Członkowie roli processadmin mogą kontrolować procesy działające na serwerze baz danych. Rola ta na ogół dotyczy kasowania działających zapytań; może być przydatna dla pracowników pomocy technicznej. Członkowie tej roli mogą wykonywać następujące operacje:

dbcreator

Członkowie ustalonej roli serwera dbcreator, do której należą na ogół doświadczeni administratorzy baz danych, mogą wykonywać operacje związane z tworzeniem i modyfikacją bazy danych. Uprawnienia tej roli zawierają możliwość wykonywania następujących operacji:

diskadmin

Członkowie roli serwera diskadmin mogą zarządzać plikami. Rola ta istnieje dla zachowania zgodności z wcześniejszą wersją SQL Server 6.5. Generalnie, większość administratorów baz danych korzysta z roli dbcreator. Członkowie roli serwera diskadmin mogą wykonywać następujące operacje:

sa

Konto logowania sa jest warte osobnego omówienia. We wcześniejszych wersjach przed SQL Serverem 7.0 wszystkie uprawnienia do administracji całym serwerem były skojarzone z kontem sa SQL Server Authenticated. Żadne odrębne role nie łamały tych uprawnień. Konto sa jest nadal stosowane dla zachowania zgodności z wcześniejszymi wersjami i konto to ma nadal wszelkie uprawnienia jakie miało wcześniej. Jednak, teraz uprawnienia te pochodzą z przynależności sa do ustalonej roli systemowej sysadmin. Konto sa nie może być usunięte z tej roli. Nie można również zmienić nazwy konta sa.

Konto to (i wszystkie konta należące do roli sysadmin) zawsze „występują” jako właściciel (dbo) w każdej z baz danych. Nie można zmienić tego zachowania. Naśladowanie tego użytkownika nie jest tym samym co przynależność do ustalonej roli bazy danych db_owner.

Wraz z SQL Serverem 2000 i przejściem do zabezpieczeń Windows Authentication Mode, konto sa przestało być tak istotne jak we wcześniejszych wersjach serwera.

Ustalone role bazy danych

Członkowie ustalonych ról bazy danych mają specjalne uprawnienia w każdej z baz danych. Inaczej niż w przypadku ról serwera, ale jednak są one specyficzne dla każdej bazy danych. Przynależność do ustalonej roli bazy danych w jednej bazie nie ma wpływu na uprawnienia w żadnej innej bazie danych. W poniższych sekcjach zostanie omówiona każda z dziewięciu ustalonych ról bazy danych. Można zobaczyć te role uruchamiając składową procedurę systemową sp_dbfixedrolepermission:

sp_dbfixedrolepermission [[@rolename =] 'role']

W tej składni role jest nazwą ustalonej roli bazy danych, której uprawnienia chce zobaczyć użytkownik.

Następne sekcje opisują wyniki uruchomienia tej procedury systemowej.

db_owner

Członkowie ustalonej roli bazy danych są „właścicielami” bazy danych. Mają w bazie danych wiele uprawnień i mogą wykonywać prawie wszystkie operacje, jakie może wykonać właściciel bazy. Członkowie ustalonej roli bazy danych db_owner mogą wykonywać następujące operacje w bazie danych:

Uprawnienia ustalonej roli bazy danej db_owner określają, że użytkownik należący do tej roli może uruchamiać dowolne polecenia data definition language (DDL), z wyjątkiem GRANT, REVOKE i DENY”. Wymaga to objaśnienia. Domyślnie, członkowie roli db_owner mają uprawnienia grant, revoke i deny do każdego z obiektów w bazie danych. Jednak, właściciel obiektu bazy danych (dboo), omówiony później, może odwołać uprawnienia dbo i członków roli db_owner dotyczące ich obiektów.

--> Nadal istnieje [Author:A.K.] różnica pomiędzy występowaniem jako jedyny właściciel bazy danych (dbo) a członkostwem w roli bazy danych db_owner. Pierwsza różnica dotyczy tworzenia obiektów: właścicielem obiektów dbo jest użytkownik dbo, podczas gdy członkowie roli db_owner występują z własnymi nazwami jako właściciele obiektów. Inną różnicą jest to, że jeśli baza danych zostanie uszkodzona, lub z innych powodów musi zostać odtworzona, ale nadal istnieje, SQL Server skorzysta z użytkownika dbo jako zarejestrowanego w tabeli systemowej sysdatabases w bazie danych master, aby sprawdzić prawa do odtwarzania bazy danych. Wykonuje to, ponieważ nie może się dostać do bazy danych aby stwierdzić, kto jest członkiem roli db_owner. W rozdziale 8 zostaną omówione odtwarzania z uwzględnieniem wymagań zabezpieczeń.

db_accessadmin

Członkowie roli bazy danych db_accessadmin ustalają, które konta logowania mają prawa dostępu do bazy danych. Podobnie jak w przypadku roli securityadmin, pracownicy działu pomocy technicznej (helpdesk) są najlepszymi kandydatami, do przyznania im tej roli. Mogą oni wykonywać następujące operacje:

db_securityadmin

Członkowie tej roli bazy danych mogą administrować zabezpieczeniami w bazie oraz mogą wykonywać następujące operacje:

db_ddladmin

Członkowie roli bazy danych db_ddladmin mogą wykonywać następujące operacje:

db_backupoperator

Członkowie tej stałej roli bazy danych mogą wykonywać wszystkie operacje związane z tworzeniem kopii bezpieczeństwa bazy danych. Mogą wykonywać następujące operacje:

db_datareader

Członkowie roli db_datareader mają uprawnienie SELECT na wszelkich tabelach lub widokach w bazie danych. Nie mogą oni jednak przyznawać (grant) lub zabierać (revoke) tego prawa innym.

db_datawriter

Członkowie roli db_datawriter mają uprawnienia INSERT, UPDATE i DELETE do wszystkich tabel i widoków bazy danych. Nie mogą oni jednak przyznawać (grant) lub zabierać (revoke) tych praw innym.

db_denydatareader

Członkowie roli db_denydatareader nie mogą uruchamiać polecenia SELECT na żadnej z tabel i widoków bazy danych. Opcja ta jest przydatna, gdy administrator bazy danych (DBA) ma za zadanie skonfigurować obiekty (korzystając z roli db_ddladmin) ale nie powinien mieć dostępu do istotnych danych w bazie.

db_denydatawriter

Członkowie ustalonej roli bazy danych db_denywriter nie mogą uruchamiać poleceń INSERT, UPDATE lub DELETE na żadnej z tabel lub widoków w bazie danych.

Właściciel bazy danych (dbo)

Właściciel bazy danych ma wszystkie prawa, jakie posiadają członkowie roli db_owner. Każda baza danych może posiadać tylko jednego właściciela (dbo).

Jeżeli użytkownik jest właścicielem bazy (dbo), gdy tworzy on obiekt, nazwą właściciela obiektu staje się nazwa dbo i użytkownik dbo staje się właścicielem tego obiektu bazy danych (dboo). Nie jest to prawdą dla członków roli bazy danych db_owner (ani innych użytkowników bazy danych). Dopóki użytkownik nie skojarzy nazwy swojego obiektu z nazwą dbo, jako właściciel będzie występować nazwa użytkownika.

Po wykonaniu operacji create table, właścicielem tabeli jest dbo. Jeżeli użytkownik jest zalogowany poprzez zabezpieczenia Windows i należy do roli db_owner, tabela będzie pokazana z nazwą konta logowania Windows jako nazwą właściciela. Dlatego tabela może występować jako: dbo.mytable — jeśli właścicielem jest dbo lub [rhome\Richard].mytable jeśli właściciel należy do roli db_owner. Jest to bardzo istotny punkt ponieważ domyślnie wszelkie polecenie SQL, które nie określają właściciela obiektu, zawsze sprawdzają obiekty w nazwie bieżącego użytkownika a następnie w tych, których właścicielem jest dbo. Przykładowo, użytkownik jest zalogowany jako [rhome\Joe] i uruchamia zapytanie select * from mytable, SQL Server nie ma określone z której tabeli korzystać, więc na wstępie szuka tabeli [rhome\Joe].mytable a następnie dbo.mytable.

--> Wniosek[Author:A.K.] : Zawsze lepiej jest określać właściciela przy wykonywaniu wszelkich poleceń SQL, gdy odnoszą się one do obiektów.

--> Należy[Author:A.K.] zastąpić RHOME nazwą własnego komputera we wszelkich przykładach kodu.

Użytkownik jest rozpoznawany w bazie danych jako dbo w następujących czterech sytuacjach:

Uprawnienia właściciela obiektów bazy danych (dboo)

Użytkownik, który tworzy obiekt bazy danych jest dla tego obiektu użytkownikiem dboo. Domyślnie, użytkownik, który tworzy obiekt jest jego właścicielem. Członkowie ustalonych ról bazy danych db_owner i db_ddladmin mogą tworzyć obiekty występując pod własną nazwą lub mogą określać nazwę obiektu, jako przynależną do dbo. Dlatego, używając polecenia create, będąc zalogowanym jako Joe, z nazwą użytkownika w bazie danych Joe, właścicielem obiektu będzie Joe:

Use pubs

Create table mytable (c1 int NOT NULL)

GO

Aby sprawdzić, kto jest właścicielem tego obiektu, należy uruchomić następujący kod:

EXEC sp_help mytable

Pojawi się następująca informacja:

Name Owner Type Created_datetime

......... .......... ................ .......................

mytable joe user table 2000-04-26 22:05:11.593

The object does not have any indexes.

No constraints have been defined for this object.

No foreign keys references this table.

No views with schemabinding reference this table.

Warto zauważyć, że właścicielem jest Joe.

Jednak, jeśli posiada się uprawnienia wynikające z roli db_owner lub db_ddladmin można uruchomić następujący kod:

Drop table mytable

GO

Create table dbo.mytable (c1 int)

GO

EXEC sp_help mytable

Pojawi się następująca informacja:

Name Owner Type Created_datetime

......... .......... ................ .......................

mytable dbo user table 2000-04-26 22:07:54.970

The object does not have any indexes.

No constraints have been defined for this object.

No foreign keys references this table.

No views with schemabinding reference this table.

W tym przypadku, jak widać z uruchomienia polecenia sp_help mytable właścicielem obiektu nie jest już Joe ale dbo.

Dobrym pomysłem jest używanie obiektów jedynie w produkcyjnych bazach danych, których właścicielem jest użytkownik dbo. Przyczyny zostaną omówione w dalszej części tego rozdziału, podczas omawiania łańcuchów własności. Jeżeli właściciel jest określony jako dbo, właścicielem obiektu jest użytkownik dbo, a nie użytkownik, który faktycznie stworzył ten obiekt. Dlatego też, użytkownik, który stworzył obiekt posiadając kwalifikacje właściciela nie jest użytkownikiem dboo tego obiektu.

Użytkownik, który posiada obiekt w bazie danych ma automatycznie przyznawane wszystkie uprawnienia dla tego obiektu. Odpowiednie uprawnienia dla każdego obiektu są przyznawane jego właścicielowi. Przykładowo, jeżeli użytkownik stworzy tabele, ma on przydzielone uprawnienia SELECT, INSERT, UPDATE, DELETE, REFERENCES i BACKUP do tej tabeli. Można zmienić właściciela danego obiektu przy pomocy procedury systemowej sp_changeobjectowner:

sp_changeobjectowner [@objname =] 'object', [@newowner =] 'owner'

Znaczenie składni:

Aby zmienić właściciela obiektu mytable z dbo na Joe, należy uruchomić następujący kod:

EXEC sp_changeobjectowner 'dbo.mytable', 'Joe'

Uprawnienia użytkownika

Większość osób korzystających z bazy danych to zwykli użytkownicy. Użytkownik bazy danych, nie posiada przysługujących z góry praw i uprawnień (inaczej, niż użytkownicy przydzieleni do roli public, omówionej w kolejnej sekcji). Wszystkie prawa muszą być przyznane bezpośrednio użytkownikowi, rolom użytkownika lub roli public.

Uprawnienia przydzielane użytkownikom mogą być podzielone na uprawnienia obiektu i polecenia:

Szczegóły obydwu typów uprawnień, jak również sposób ich przyznawania zostaną omówione później.

Rola public

Rola public istnieje w każdej bazie danych i nie może zostać usunięta. Zostanie omówiona, ponieważ jest bardzo potrzebna i należy wiedzieć jak z niej korzystać.

Każdy użytkownik bazy danych, rola, grupa Windows i użytkownik Windows w bazie danych należy do roli public i nie może zostać z niej usunięty. Dlatego, rola public jest bardzo przydatna, w przypadku, gdy dane uprawnienie ma być przyznane wszystkim.

Przykład skorzystania z tej roli występuje w zainstalowanym systemie SQL Server. Uprawnienie SELECT jak również uprawnienie EXECUTE w wielu systemowych procedurach składowych jest przyznane roli public do wszystkich tabel systemowych w każdej z baz danych. Uprawnienia te są wymagane do prawidłowego działania SQL Servera i nie powinny być modyfikowane.

Należy być ostrożnym przy przyznawaniu uprawnień roli public. Należy pamiętać, że oznacza to, że wszyscy, włączając w to użytkowników, którzy zostali dodani do bazy danych później otrzymają to uprawnienie.

Instrukcje uprawnień

Instrukcje uprawnień pozwalają użytkownikowi bazy danych, roli bazy danych lub grupie/użytkownikowi Windows na wykonywanie różnych zadań takich jak tworzenie baz danych, tworzenie obiektów lub tworzenie kopii zapasowych bazy danych. Uprawnienia wykonywania pozwalają użytkownikowi raczej na uruchamianie poszczególnych poleceń (lub zbioru poleceń) niż na manipulowanie pojedynczym obiektem.

Należy starannie rozważyć przyznawanie użytkownikom lub rolom uprawnień do tworzenia obiektów. Jeżeli użytkownik utworzy obiekt, staje się jego właścicielem (chyba, że twórca, podczas tworzenia obiektu, określił właściciela jako dbo) i posiada wszystkie uprawnienia związane z tym obiektem bazy danych. W późniejszej części zostanie pokazane, że posiadanie obiektów, mających różnych właścicieli może stworzyć trudne sytuacje związane z uprawnieniami.

Instrukcje uprawnień powinny być przyznawane jedynie gdy są one bezpośrednio potrzebne. Przypadkowe przyznawanie uprawnień polecenia może powodować powstanie w bazie danych niepotrzebnych i często bezużytecznych obiektów.

Można przyznać uprawnienia polecenia indywidualnym użytkownikom bazy danych, użytkownikom/grupom Windows lub rolom bazy danych, włączając w to rolę public. Uprawnienia wykonywania mogą być przyznawane, odwoływane lub wstrzymywane (deny) włączając w to CREATE DATABASE, CREATE TABLE, CREATE PROCEDURE, CREATE DEFAULT, CREATE RULE, CREATE VIEW, CREATE FUNCTION, BACKUP DATABASE i BACKUP LOG.

Można przyznawać te uprawnienia indywidualnie lub wszystkim naraz (przy użyciu słowa kluczowego ALL). Z każdego polecenia wynikają konsekwencje, które muszą być rozważone przed użyciem danego polecenia.

Uprawnienie CREATE DATABASE

Uprawnienie CREATE DATABASE umożliwia użytkownikom tworzenie ich własnych baz danych, stają się oni właścicielami — dbo tych baz. Właściciel danej bazy może być później zmieniony przy pomocy systemowej procedury składowej sp_changeowner. Jedynie członkowie ustalonych ról serwera sysadmin lub dbcreator mogą przyznawać użytkownikom uprawnienie CREATE DATABASE. Ponieważ uprawnienia są zawsze przyznawane użytkownikom (nigdy kontom logowania), należy przyznać to uprawnienie jedynie dla bazy danych master. To uprawnienie polecenia nie występuje w innej bazie danych. Uprawnienie CREATE DATABASE przyznaje również prawo do używania polecenie ALTER DATABASE. Inaczej mówiąc, nie można używać polecenie ALTER DATABASE nie mając uprawnienia CREATE DATABASE.

Używanie ustalonej roli serwera dbcreator jest znacznie lepsze niż przyznawanie uprawnienia CREATE DATABASE. Zwykle potrzebne są również inne prawa przydzielane przez rolę serwera dbcreator, a szukanie kto ma jakie prawa jest łatwiejsze jeżeli korzysta się z zalet ról SQL Servera.

Uprawnienia CREATE TABLE, VIEW FUNCTION, PROCEDURE, DEFAULT i RULE

Uprawnienia CREATE TABLE, VIEW FUNCTION, PROCEDURE, DEFAULT i RULE umożliwiają użytkownikom uruchamianie odpowiednich wyrażeń do tworzenia obiektów w bazie danych, gdzie przydzielone są uprawnienia. Na ogół programiści mają przyznane te uprawnienia, aby umożliwić im tworzenie w bazie danych zasobów, jakich potrzebują podczas rozwoju produktu.

Wszystkie uprawnienia CREATE uwzględniają prawo do zmiany lub usunięcia dowolnego obiektu utworzonego przez użytkownika. Przydzielanie tego uprawnienia może powodować poważne problemy w bazie danych ponieważ użytkownik może usunąć obiekty, których już nie używa, jedynie po to aby sprawdzić czy inni korzystali z tych obiektów. Użytkownik może również zmieniać obiekt, co może powodować, że obiekt jest bezużyteczny dla innych użytkowników w bazie danych. Aby sprawdzić istniejące typy zależności, należy uruchomić procedurę systemową sp_depends.

Uprawnienie polecenia BACKUP DATABASE i BACKUP LOG

Uprawnienia BACKUP DATABASE i BACKUP LOG mogą być przydzielone również pojedynczym użytkownikom, grupom/użytkownikom Windows i rolom. Pomimo tego, że tworzenie kopii bezpieczeństwa i dzienników transakcji jest zwykle procesem automatycznym sterowanym przez zaplanowane zadania tworzone przez administratora systemu, to jednak niektóre środowiska wymagają, aby indywidualni użytkownicy mieli przyznaną możliwość wykonywania kopii bezpieczeństwa.

Ponowne, używanie ustalonej roli bazy danych db_backupoperator jest znacznie lepsze niż przyznawanie uprawnień polecenia BACKUP DATABASE i BACKUP LOG. Zwykle potrzebne są również inne prawa przydzielane przez przynależność do danej roli, a szukanie kto ma jakie prawa jest łatwiejsze jeżeli korzysta się z zalet ról SQL Servera.

Przydzielanie uprawnień polecenia

Można używać Transact-SQL lub SQL Server Enterprise Management aby przyznawać, odbierać i wstrzymywać uprawnienia polecenia.

Polecenie GRANT

Polecenie GRANT przyznaje użytkownikowi uprawnienia polecenia:

GRANT {ALL | statement_list} TO {account}

Składnia:

Polecenie REVOKE

Polecenie REVOKE zabiera uprawnienia, które zostały wcześniej przyznane:

REVOKE {ALL | statement_list} TO {account}

Składnia:

Polecenie DENY

Inaczej niż w przypadku polecenia REVOKE, polecenie DENY bezpośrednio zabiera uprawnienie polecenia. Przykładowo, jeżeli Joe jest członkiem roli bazy danych, a rola ta ma uprawnienie CREATE TABLE, Joe może również tworzyć tabele. Jednak, jeżeli Joe powinien mieć zabronione tworzenie tabel, mimo tego, że jako członek roli posiada to uprawnienie, można zabronić Joe wykonywania tego polecenia. Dlatego, Joe nie będzie mógł uruchomić wyrażenia CREATE TABLE, pomimo tego, że normalnie rola przydzieliła mu to prawo.

DENY {ALL | statement_list} TO {account}

Składnia:

Przykłady uprawnień nadawanych z poziomu Transact-SQL

Przegląd kilku przykładów jest najlepszym sposobem na zrozumienie działania omówionych poleceń:

GRANT CREATE VIEW TO [Rhome\Joe]

REVOKE CREATE TABLE, CREATE VIEW FROM [Rhome\Mary], [Rhome\Joe]

GRANT ALL TO [Rhome\Joe]

Jeżeli polecenie GRANT ALL jest uruchomione w bazie danych master, określony użytkownik ma wszystkie prawa w tej bazie danych. Jeżeli polecenie to jest wykonane w innej bazie danych, użytkownik otrzymuje wszystkie prawa z wyjątkiem CREATE DATABASE, ponieważ to szczególne polecenie jest przyznawane jedynie w bazie danych master.

Przyjmując, że użytkownik Bob jest członkiem ról Role1 i Role2, jakie uprawnienia będzie posiadał po wykonaniu następującego zbioru poleceń?

EXEC sp_addrole 'Role1'

EXEC sp_addrole 'Role2'

GO

GRANT CREATE TABLE TO Role1

GRANT CREATE VIEW TO Role2

GRANT CREATE DEFAULT TO [Rhome\Bob]

REVOKE ALL FROM Role1

DENY CREATE VIEW to [Rhome\Bob]

W tym momencie Bob może używać CREATE DEFAULT i to wszystko. Jego uprawnienia CREATE TABLE (przyznane roli Role2) zostały następnie zabrane poleceniem REVOKE ALL FROM Role1. Uprawnienia CREATE VIEW przyznane z roli Role2 zostały cofnięte dla użytkownika Bob. Dlatego, jedyne uprawnienie, które nadal jest aktualne to CREATE DEFAULT.

Administracja uprawnieniami polecenia przy pomocy SQL Server Enterprise Managera

SQL Server Enterprise Manager dostarcza interfejsu graficznego do implementacji uprawnień polecenia. Aby zobaczyć lub edytować uprawnienia polecenia w SQL Server Enterprise Manager, należy otworzyć folder Databases dla danego SQL Servera, Kliknąć prawym przyciskiem bazę danych, która ma być modyfikowana lub przeglądana i wybrać Properties. Następnie kliknąć zakładkę Permissions aby zobaczyć uprawnienia dla tej bazy danych. Powinno się ukazać okno podobne do przedstawionego na rysunku 6.1, które pokazuje uprawnienia bazy pubs.

Rysunek 6.1. Zakładka Permissions bazy danych pubs.

0x01 graphic

Podczas przyznawania i zabierania uprawnień, pola zawierają jeden z trzech znaczników:

Aby przyznać uprawnienia, wystarczy zaznaczyć odpowiednie pole dla danego konta. Aby wstrzymać (deny) uprawnienie, należy kliknąć pole dwukrotnie (pojawi się czerwony znak X). Jeżeli uprawnienie zostało wcześniej przyznane, jednokrotne kliknięcie spowoduje pojawienie się znaku X. Należy pamiętać, że jest to negacja uprawnienia. Należy kliknąć pole ponownie, aby wyczyścić to pole, co oznacza, że SQL Server Enterprise Manager wyśle do SQL Servera polecenie REVOKE. Należy kliknąć OK., aby zmiany odniosły skutek.

W przypadku każdej innej bazy danych (z wyjątkiem master) uprawnienie CREATE DATABASE nie będzie widoczne, ponieważ to prawo może być przydzielone jedynie z bazy danych master. Rysunek 6.2 pokazuje zakładkę Permissions dla bazy danych master.

Rysunek 6.2. Zakładka Permissions bazy danych master.

0x01 graphic

Możliwość tworzenie obiektów w bazie danych jest poważną sprawą. Nie należy przyznawać tego prawa, dopóki nie jest ono użytkownikowi niezbędne do wykonywania jego pracy.

Uprawnienia obiektu

Uprawnienia obiektu pozwalają użytkownikowi, roli lub grupie/użytkownikowi Windows na wykonywanie działań na poszczególnych obiektach bazy danych. Uprawnienia stosują się tylko do określonych obiektów nazwanych podczas przyznawania uprawnienia, nie do wszystkich obiektów znajdujących się w bazie danych. Uprawnienia obiektu pozwalają użytkownikowi na przypisanie praw do uruchamiania określonych poleceń Transact-SQL dotyczących danego obiektu do indywidualnych kont użytkowników. Uprawnienia obiektu są najczęściej przyznawanym typem uprawnień w bazie.

Dostępne są następujące typy uprawnień:

SELECT Przegląd danych w tabeli, widoku lub kolumnie.

INSERT Dodawanie danych do tabeli lub widoku.

UPDATE Modyfikacja istniejących danych w tabeli, widoku lub w kolumnie.

DELETE Usuwanie danych z tabeli lub widoku.

EXECUTE Uruchamianie procedury składowej.

REFERENCES Odwołanie do tabel z kluczami obcymi (zobacz rozdział 14) lub gdy tworzy się funkcję lub widok z opcją WITH SCHEMABINDING, jest to odwołanie do obiektu (zobacz rozdział 15).

Uprawnienie REFERENCES (skracane do DRI, co oznacza declarative referential integrity w SQL Server Enterprise Manager) pozwala użytkownikom (lub aplikacjom) na porównanie wartości z wartościami z innej tabeli bez potrzeby przeglądania danych w innej tabeli. Przykładowo, uprawnienie to może być używane aby pozwolić aplikacji na wyszukanie pasujących numerów Ubezpieczenia społecznego, bez przyznawania aplikacji uprawnień wystarczających do przeglądania innych numerów Ubezpieczeń społecznych w tabeli.

--> Nowa[Author:A.K.] własność w SQL Server 2000 to umożliwienie działania schema binding, który zostanie omówiony w rozdziale 15. Zasadniczo, chroni to obiekty, na których się opiera struktura, od wszelkich zmian, jeśli są używane poprzez widoki lub funkcje.

Przyznawanie uprawnień obiektu

Można skorzystać z Transact-SQL lub z SQL Server Enterprise Managera aby przyznać, odwołać lub wstrzymać uprawnienia obiektu.

Przyznawanie uprawnień obiektu korzystając z Transact-SQL

Polecenie GRANT przyznaje użytkownikowi jedno lub więcej uprawnień obiektu. Usuwa ona również uprawnienie DENY.

GRANT {ALL [PRIVILEGES] | permission_list [,...n]}

{

[(column[,...n])] ON {table | view}

| ON {table |view} [(column[,..n])]

| ON {stored_procedure |extended_stored_procedure}

| ON {user_defined_function}

}

TO account[,...n]

[WITH GRANT OPTION]

[AS {group | role}]

Znaczenie składni:

Polecenie REVOKE — Odwoływanie uprawnień obiektu

Polecenie REVOKE zabiera jedno lub więcej uprawnień obiektu, które zostały wcześniej przyznane. Nie zostanie wyświetlony komunikat informujący o błędzie, jeżeli zostanie odwołany przywilej, który nie był wcześniej nadany; polecenie nie przyniesie po prostu efektu.

REVOKE [GRANT OPTION FOR]

{ALL [PRIVILEGES] | [,...permission_list n]}

{

[(column[,...n])] ON {table | view}

| ON {table |view} [(column[,..n])]

| {stored_procedure |extended_stored_procedure}

| {user_defined_function}

}

FROM account[,...n]

[CASCADE]

[AS {group | role}]

Znaczenie składni:

Polecenie DENY — dotyczące uprawnienia obiektu

Inaczej niż w przypadku polecenie REVOKE, polecenie DENY bezpośrednio zabiera (neguje) uprawnienie dotyczące obiektu. Uprawnienie nie musi być wcześniej przyznane użytkownikowi. Przykładowo, jeżeli Joe należy do roli bazy danych, która ma uprawnienie SELECT dotyczące tabeli authors, to Joe może odczytywać dane z tej tabeli. Jednak, jeżeli założeniem jest, że Joe nie może mieć dostępu do danych z tabeli authors, nawet jeśli należy do roli, posiadającej tą możliwość, w tym przypadku można odebrać użytkownikowi Joe to uprawnienie (poleceniem DENY). Dlatego, Joe nie będzie miał możliwości odczytu danych z tabeli authors, nawet poprzez uprawnienia roli, które by na to normalnie pozwalały.

DENY

{ALL [PRIVILEGES] | [,...permission_list n]}

{

[(column[,...n])] ON {table | view}

| ON {table |view} [(column[,..n])]

| ON {stored_procedure |extended_stored_procedure}

| ON {user_defined_function}

}

TO account[,...n]

[CASCADE]

Znaczenie składni:

Przykłady uprawnień nadawanych z poziomu Transact-SQL

Przegląd kilku przykładów jest najlepszym sposobem na zrozumienie działania omówionych poleceń:

GRANT SELECT ON SALES TO [Rhome\Joe]

REVOKE SELECT, INSERT ON AUTHORS FROM [Rhome\Mary], [Rhome\Joe]

GRANT SELECT ON AUTHORS (AU_FNAME, AU_LNAME) TO [Rhome\Joe]

Warto zauważyć, że poprzednie wyrażenie może być przedstawione jako:

GRANT SELECT (AU_FNAME, AU_LNAME) on AUTHORS TO [Rhome\Joe]

Pomimo tego, że można przyznawać uprawnienia na poziomie kolumny, lepiej jest ograniczyć dostęp do całych tabel, poprzez tworzenie widoków, a następnie przyznawanie uprawnień do tych widoków. Polecenie view jest predefiniowanym poleceniem Transact-SQL lub grupą poleceń, które mogą zwracać dane. Dla poprzedniego przykładu można stworzyć widok zwany viewAuthorName, który pobierze dane z kolumn au_fname i au_lname z tabeli authors. Następnie można przydzielić Joe uprawnienia do tego widoku (rozdział 15 zawiera więcej informacji na temat widoków).

--> Podejście[Author:A.K.] to jest bardziej skuteczne ponieważ każdy użytkownik odnoszący się do tej tabeli musi zostać sprawdzony jeśli chodzi o jego uprawnienia na poziomie kolumny. Jeżeli tworzony jest widok, a nie są używane uprawnienia na poziomie kolumny, uprawnienia muszą zostać sprawdzone tylko raz na poziomie widoku, a nie na poziomie każdej z kolumn.

GRANT SELECT, INSERT ON PUBLISHERS TO [Rhome\Ann] WITH GRANT OPTION

REVOKE SELECT, INSERT ON PUBLISHERS FROM [Rhome\Ann] CASCADE

Przyznawanie uprawnień obiektu przy pomocy SQL Server Enterprise Managera

Uprawnienia obiektu są częścią administracji systemu. Przyznawanie i odwoływanie tych uprawnień to typowe zadania jakie są wykonywane codziennie.

SQL Server Enterprise Manager dostarcza szybkiego, łatwego i wizualnego sposobu kontroli uprawnień obiektu. Uprawnienia mogą być przeglądane w oparciu o obiekty lub użytkowników. Możliwość przeglądania informacji na dwa różne sposoby znacznie ułatwia wyśledzenie błędów.

Przegląd uprawnień dla obiektu Aby zobaczyć lub zmodyfikować uprawnienia obiektu w SQL Server Enterprise Manager należy postępować zgodnie ze wskazówkami: