Wprowadzenie autoryzacji do bazy która jest już eksploatowana - best practice

0

Witam ponowie,

koledzy proszę podpowiedzcie jak podejść do tematu by z jednej strony nie reorganizować wszystkiego od nowa, a z drugiej jednak dojść do zamierzonego efektu.

Problem polega na tym, że jest baza z masą tabel (powiedzmy, że coś w rodzaju bardzo prymitywnego MagazynuDanych) z których korzysta masa użytkowników na zasadzie łączenia się z tabelami i budowy swoich raportów czy jakichś tam aplikacji pomocniczych w Accessie czy Excelu. Problem polega, że ten kto to tworzył na początku w ogóle nie zajął się sprawą autoryzacji i tak w bazie jest tylko domyślny schema = dbo do którego należą wszystkie tabele, a do bazy łączenie jest za pomocą jednego wspólnego loginu w SQL.

Oczywiście jest to jakaś masakra która w końcu muszę ruszyć i wyprostować. Pytanie brzmi jak to zrobić żeby nie przebudowywać wszystkiego od nowa.
Generalnie zakładam, że chciałbym zrobić grupy użytkowników w AD i te używać w SQL Server z dostępem do wyznaczonych tabel czyli coś w rodzaju:
grupa SQLReaderSales --> dostęp do tablic dbo.SalesOrders ; dbo.SalesOrderLines; dbo.Customers ..... etc.

Niestety nie jestem jakoś mega znawcą tego aspektu w SQL server jakim są autoryzacje i teraz próbując tu coś naprostować nie chce za bardzo namieszać i przekombinować. No a z drugiej strony chciałbym to zrobić jakoś w miarę możliwości "zgodnie ze sztuką".

Wydaje mi się,że najbardziej logicznie by było zrobić schematy dla każdej z grup i każdą grupę podpiąć pod nią ... ale to nie wchodzi w grę (a raczej jest ostatecznością) bo wszystko co korzysta z tej bazy musiało by być poprawione (nazwy tabel się zmienią). W sytuacji jakiej się znajduje to jest raczej nie do zaakceptowania.

Dla tego myślałem sobie, żeby po stworzeniu grupy użytkowników np ten SQLReaderSales z jednej strony zablokować jej od górnie dostęp do schema ="dbo", a później tylko nadać dostępy do konkretnych obiektów z "dbo". Problem w tym, że nie wychodzi mi to ... jak zablokuje "select" dla całego dbo, to pomimo, że później nadam uprawnienia dla konkretnej tablicy np. dbo.SalesOrders - to nic z tego nie widzi tej tablicy w ODBC.
Oczywiście mogę iść od drugiej strony i blokować tylko tablice do których grupa nie powinna mieć dostęp ... ale wiecie rozumiecie ...zawsze jest więcej tablic wtedy do zablokowania niz do nadania dostępu, a przy prawie 200 tablicach i kilku grupach uzytkowników zaczyna się lekka masakra w zarządzaniu i klikaniu ;) Chyba... że jest jakiś myk o którym nie wiem.

Jakbyście mogli mi coś poradzić jak podejść do tematu jak najlepiej uzyskać wynik, a nie ingerować za bardzo w aktuyalna strukturę bazy - byłbym wielce zobowiązany.

Baza na SQL Server 2014.

Z góry dzięki,
pozdrawiam BB

1

Jeżeli schematy odpadają, bo zmienia się nazwy (coprawda można posiłkować się synonimami, ale jak coś od początku nie było projektowane pod użycie schematów to jest to rzeźbienie w...), to ja bym poszedł w stronę ról

Powiedzmy że masz w AD grupę SQLReaderSales, a baza nazywa się "baza" to:

--wybierz odpowiednią bazę
USE [baza]
GO
--stwórz rolę
CREATE ROLE [ReaderSales]
GO
/*nadaj uprawnienia do obiektów wg. potrzeb:
GRANT DELETE do usuwania
GRANT INSERT do dodawania
GRANT SELECT do pobierania
GRANT UPDATE do modyfikacji
*/

--dla tabeli  [dbo].[SalesOrders]
GRANT DELETE ON [dbo].[SalesOrders] TO [ReaderSales]
GO
GRANT INSERT ON [dbo].[SalesOrders] TO [ReaderSales]
GO
GRANT SELECT ON [dbo].[SalesOrders] TO [ReaderSales]
GO
GRANT UPDATE ON [dbo].[SalesOrders] TO [ReaderSales]
GO
--dla tabeli [dbo].[SalesOrdersLines]
GRANT DELETE ON [dbo].[SalesOrdersLines] TO [ReaderSales]
GO
GRANT INSERT ON [dbo].[SalesOrdersLines] TO [ReaderSales]
GO
GRANT SELECT ON [dbo].[SalesOrdersLines] TO [ReaderSales]
GO
GRANT UPDATE ON [dbo].[SalesOrdersLines] TO [ReaderSales]
GO
--tu dodac kolejne obiekty bazy które są potrzebne dla roli

W ten sposób jeszcze bez modyfikacji uprawnień na bazie możesz zrobić pierwszą przymiarkę do nadania uprawnień.
Jak wszystko gotowe to dodajmy grupę z AD i przypiszmy do roli w bazie:

--użyj bazy master
USE [master]
GO
--dodaj grupę z AD
CREATE LOGIN [AD\SQLReaderSales] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
--przejdź na bazę baza
USE [baza]
GO
--dodaj możliwość logowania do bazy
CREATE USER [AD\SQLReaderSales] FOR LOGIN [AD\SQLReaderSales]
GO
--dodaj grupę z ad do roli ReaderSales
ALTER ROLE [ReaderSales] ADD MEMBER [AD\SQLReaderSales]
GO

Zmieniasz hasło uzytkownika którym aktualnie się logują, czy tam odbierasz dostęp i zakopujesz się gdzieś w głuszy bez telefonu ;)

0

@Panczo: Właściwie to dokładnie o takie rozwiązanie mi chodziło ;) (i przy okazji chyba wiem czemu wcześniej mi coś nie grało ;] ) Także wielkie dzięki za szybką podpowiedź i rozwiązanie. Browarek dla Ciebie [_]b

Haa nawet ucieczka do głuszy, czy wywalenie telefonu nie pomoże ... jak im nie zabanglają "magiczne raporty" czy inne narzędzia użytkowników to prędzej czy później by mnie znaleźli :D ... zresztą tu i tak będzie "fight" bo na pewno skończona liczba aplikacji nie zadziała dla iluś tam użytkowników ale na to to ja już będę miał gotową ripostę (zwalę na key-userów że tak rozpisali dostępy dla grup i tabel) ... + przy błogosławieństwie najwyższego kierownictwa ;) to mi mogą. Po prawdzie nigdy nie powinna zaistnieć taka sytuacja ... no ale heh wiemy jak jest.

0
BlackBad napisał(a):

Dla tego myślałem sobie, żeby po stworzeniu grupy użytkowników np ten SQLReaderSales z jednej strony zablokować jej od górnie dostęp do schema ="dbo", a później tylko nadać dostępy do konkretnych obiektów z "dbo". Problem w tym, że nie wychodzi mi to ... jak zablokuje "select" dla całego dbo, to pomimo, że później nadam uprawnienia dla konkretnej tablicy np. dbo.SalesOrders - to nic z tego nie widzi tej tablicy w ODBC.
Oczywiście mogę iść od drugiej strony i blokować tylko tablice do których grupa nie powinna mieć dostęp ... ale wiecie rozumiecie ...zawsze jest więcej tablic wtedy do zablokowania niz do nadania dostępu, a przy prawie 200 tablicach i kilku grupach uzytkowników zaczyna się lekka masakra w zarządzaniu i klikaniu ;) Chyba... że jest jakiś myk o którym nie wiem.

Niczego nie blokuj za pomocą "Deny", wyczyść użytkownikowi przynależność do ról w "Membership", a potem wyczyść "Owned Schemas", to straci dostęp do tabel, i wtedy będziesz mógł oddać poszczególne tabele w "Securables" i dać im "Grant" na selecta.

1 użytkowników online, w tym zalogowanych: 0, gości: 1