sql, schema, login, user, role

Dostałem do zrobienia bazę danych w sql. Aktualnie pracuję nad stworzeniem mechanizmów uprawnień i zabezpieczeń połączonych z logowaniem.
Mam zrobioną aplikację c# która loguje użytkownika i łączy się z bazą wykonując na niej pewne tam operacje.
Tabel w bazie jest załóżmy 4.
Z programu będą korzystać trzy rodzaje użytkowników (choć ich będzie bardzo wielu ale każdy jeden z nich będzie jednym z trzech rodzaji):

• magazynier
• administrator
• księgowy

Chodziłoby mi o to aby magazynier miał możliwość: odczytu i zapisu do pierwszych dwóch tabel i aby nie miał dostępu do odczytu i zapisu do pozostałych dwóch tabel;
administrator mógł robić wszystko z bazą i tabelami;
księgowy aby miał dostęp tylko do odczytu pierwszych dwóch tabel;

Mam strategiczne pytanie jak taki prosty przykład osiągnąć realizując to wszystko o takie rzeczy jak: logins, users, roles i schemas...

Help please!

A dajesz tym grupom BEZPOŚREDNI dostęp do bazy?
Czy poprzez aplikację?

Chciałbym aby mieli dostęp do bazy tylko poprzez aplikację ale sam za bardzo nie wiem jak to osiągnąć bo przecież jeśli ktoś z nich zainstaluje u siebie np.sql studio to będzie mieć dostęp do bazy nie tylko poprzez aplikację w c# ale również poprzez sql studio, a może jeszcze jakoś czego nie wiem... jak jeszcze może mieć ? jak zrobić żeby tylko poprzez aplikację mieli ? proszę o max. dużo wskazówek, jestem leszcz.

Robisz jednego użytkownika SQL i nim logujesz się aplikacją do bazy.
Do aplikacji logujesz się WŁASNYMI użytkownikami i ich rolami.

Proste. Dzięki za podsunięcie rozwiązania.
Czyli przy takim podejściu cały mechanizm autentyfikacji oraz nadawania/używania uprawnień przerzucam na aplikację która loguje się do bazy na maksymalnych prawach administratorskich, czy tak? Tylko że wówczas w ogóle pomijam możliwości które w tym zakresie daje mi sql serwer, czy tak?

Nie na prawach administratorskich.
W zupełności wystarczy SELECT / UPDATE/ DELETE / INSERt + ew. EXECute wybranych funkcji/ procedur. Koniec. Nic więcej.

No dobrze, ale przecież to samo można osiągnąć z wykorzystaniem schema, users, logins i innych mechanizmów wbudowanych w sql.
Tylko najgorsze że nie wiem jak to zrobić. Ale jestem prawie pewien że można. Jeśli mam rację ominąłbym konieczność oprogramowywania tego wszystkiego w aplikacji.
Chciałbym osiągnąć coś takiego że:

Tabel w bazie jest załóżmy 4.
Z programu będą korzystać trzy rodzaje użytkowników (choć ich będzie bardzo wielu ale każdy jeden z nich będzie jednym z trzech rodzaji):

magazynier
administrator
księgowy

Chodziłoby mi o to aby magazynier miał możliwość: odczytu i zapisu do pierwszych dwóch tabel i aby nie miał dostępu do odczytu i zapisu do pozostałych dwóch tabel;
administrator mógł robić wszystko z bazą i tabelami;
księgowy aby miał dostęp tylko do odczytu pierwszych dwóch tabel;

Jak taki prosty przykład osiągnąć realizując to wszystko o takie rzeczy jak: logins, users, roles i schemas...

Potrzebowałbym podpowiedzi jak w logicznej strukturze wykorzystującej schemy, userów i loginy to zrobić (najlepiej z poziomu sql studio) ?

A Ci użytkownicy są w jakiejś domenie ? Jest tu AD i grupa użytkowników ?

Generalnie poszedł bym takim tropem:

Stworzył grupy w AD: magazynierzy, ksiegowi, admini (jesli jest 1 czy 2 adminów to może bez sensu)
Takie grupy podpiął bym pod login w SQL server np: SqlReadermagazynierzy, SQLReaderKsiegowi... etc.
Na podstawie loginu bym zrobił usera.

Dla grup SqlReadermagazynierzy, SQLReaderKsiegowi bym zrobił role: ReadMagazyny, ReadKsiegowsc (czy jak tam chcesz).

W konkretnej roli dał bym dostep tylko do tych tabel co chesz (w tym wypadku tylko Select).

I taką rolę i tylko tą przypisał bym do konkretnego usera tylko. I to wsio. dla 4 czy kilku table nie baw się w schema w ogóle.

Mniej więcej tak bym poszedł choć uprzedzam, że nie jestem wielkim znawcą w temacie autoryzacji :| to przykład jak ja rozwiązałem sprawe w 1 bazie na podstwie porad z tego forum btw. (gdzies tam jest mój topic co porusza podobny temat).

Co to jest "AD" ?
Jak stworzyć grupy userów ?

Kompletnie nie rozumiem pytania cytuję "A Ci użytkownicy są w jakiejś domenie ? Jest tu AD i grupa użytkowników ? " , jestem trochę starej daty i z informatyką mam generalnie mało do czynienia, proszę bardziej łopatologicznie.

AD = Active Directory

Z pytania, zakładam że go nie masz w takim razie :)
Czyli rozumiem, że u Ciebie w pełni autoryzacja ma się odbywać na poziomie SQL, a nie Windows ?

Tak, dokładnie. Autoryzacja na poziomie sql serwera.
Mam pytania dot. rozwiązania które zaproponowałeś:

1. Chciałbym uzyskać możliwość że jeśli z poziomu aplikacji logować się będzie dany user podając login i hasło (które chciałbym aby de facto były loginami zapisanymi jako loginy sql serwera) to jeśli będzie coś do tabeli zapisywał to ma również w ostatniej jej kolumnie zapisywać swój login (najlepiej kodowany jako liczba jakaś). Chciałbym to realizować tak że w sql serwerze definiuję tyle loginów ile będzie rzeczywistych ludzi wykorzystujących moją aplikację, a następnie jeśli z poziomu aplikacji dana osoba na swój login się zaloguje to aplikacja łącząc się do serwera pierwsze co zrobi to sprawdzi czy w tabeli np. o nazwie Użytkownicy (posiadającej dwie kolumny : login (varchar), imie (varchar), nazwisko (varchar), numer (int)) widnieje już ten login przy użyciu którego połączono się z sql serverem. Jeśli tak to nic. Jeśli nie - login zostaje zapisany do tabeli z kolejnym numerem a wraz z nim imię i nazwisko delikwenta.
   Po co mi to ? Ano po to że program ma działać tak że jeśli monter coś zmontuje to trafia ta informacja do bazy , ale również trafiać ma (inna kolumna tego rekordu) także numer skojarzony z tym loginem, aby później było wiadomo kto to montował.
   Podział na jedynie kilka grup (w związku z tym na tyle właśnie loginów) nie sprawdzi się moim zdaniem gdyż dwie różne osoby będą mogły się zalogować na ten sam login i de facto nie będzie później wiadomo która z nich montowała produkt.
   Proszę abyś z łaski swojej coś innego zaproponował.

Proszę również abyś wyjaśnił jak zrealizować coś takiego jak pisałeś cytuję:
"W konkretnej roli dałbym dostep tylko do tych tabel co chesz (w tym wypadku tylko Select)." - ja używając sql studia widzę że jak się tworzy nową rolę bazodanową to nie idzie tam wybrać których tabel dana rola ma dotyczyć i nie bardzo da się ustawić uprawnienia, jak to się robi?

A więc łopatologicznie: tego się tak nie robi. Aplikacja jest użytkownikiem bazy danych. Użytkownicy aplikacji to użytkownicy aplikacji, a role użytkowników aplikacji to role użytkowników aplikacji.
Pamiętaj, że do bazy danych łączysz się określonym connection stringiem, który zawiera w sobie nazwę użytkownika bazy. Masz zamiar podmieniać connection string po logowaniu?

Proszę również abyś wyjaśnił jak zrealizować coś takiego jak pisałeś cytuję:
"W konkretnej roli dałbym dostep tylko do tych tabel co chesz (w tym wypadku tylko Select)." - ja używając sql studia widzę że jak się tworzy nową rolę bazodanową to nie idzie tam wybrać których tabel dana rola ma dotyczyć i nie bardzo da się ustawić uprawnienia, jak to się robi?

Tu masz wszystko opisane dzięki uprzejmości @Panczo W moim wypadku było to dokładnie czego potrzebowałem.

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

Co do reszty pytania .. to nie bardzo mam czas się w głębić teraz .. no i @somekind pewno będzie miał lepszy pomysł ;)

Panowie dzięki za wyjaśnienia, to po pierwsze.
Po drugie @somekind pytałeś czy do bazy łączę się określonym connection stringiem który już zawiera jakiegoś "Usera", odpowiadam:
owszem tak, z tym że ten User i jego Password jest zależny od tego co na początku w polu textbox aplikacji wpisze faktyczny użytkownik który tą aplikację dostanie do siebie na komputer.
Dlatego wybacz ale nie bardzo rozumiem Twoją sugestię odnośnie tego żeby uprawnienia i cały mechanizm przerzucać na aplikację, po co w takim razie wymyślono ten mechanizm (role, users, login, schema) na serwerach sql ?

Dzięki Waszym podpowiedziom udało mi się jednak zrobić to co sobie założyłem, aktualnie wszystkie uprawnienia mam zdefiniowane w rolach, mam utworzone loginy i powiązanych z nimi userów w stosunku jeden do jednego. Każdy user dostaje jakąś tam rolę czego efektem jest to że coś może robić a coś nie. Role udało mi się utworzyć tak jak chciałem, czyli dla niektórych tabel może np.robić select i insert, a dla niektórych tylko select a dla niektórych nic. Inna rola - podobnie ale oczywiście inaczej. Nie korzystam w ogóle z schema bo mam tylko kilka powiązanych relacjami tabel. Dodatkowo całość aplikacji tak zorganizowałem że z jej poziomu odczytywane jest do jakiej role jest przypisany bieżący jej użytkownik i na podstawie tego te niedozwolone rzeczy są wyszarzane (np. przyciski do dodawania czegoś tam...).

po co w takim razie wymyślono ten mechanizm (role, users, login, schema) na serwerach sql ?

Załóżmy, że masz w jednej bazie dwa schematy - np. aplikacja1 oraz aplikacja2, w każdej z nich są jacyś użytkownicy, różni.
W jaki sposób chcesz w tym przypadku przerzucić autoryzację w pełni na bazę danych, tak aby użytkownicy się nie duplikowali?

Oprócz tego załóżmy, że z poziomu aplikacji można tworzyć użytkowników - w Twoim przypadku aplikacja musiałaby mieć niemalże dostęp rootowski do bazy danych (by móc tworzyć nowych użytkowników oraz grantować im prawa), co mogłoby się źle skończyć w niewłaściwych rękach.

Mechanizm logowania do baz danych wymyślono w celu logowania do baz danych, a nie aplikacji. Idąc dalej można by przecież stwierdzić: po co w bazie osobni użytkownicy, skoro już system operacyjny zawiera taką funkcjonalność ;-)

Ja nigdy nie mówiłem że założyłbym że z poziomu aplikacji będziemy mogli tworzyć użytkowników. Wychodzę z założenia że użytkowników będę sobie tworzył w sql studio logując się na superadmina (sa) - i tylko ja będę miał do niego hasło.
Pozostali użytkownicy zawsze dostają tylko ograniczone prawa z których prawa maksymalne to select i insert i tylko tyle.
Przy odpaleniu mojej aplikacji jest tak że wyskakuje okienko w którym wprowadza się login i hasło. Na podstawie tego aplikacja robi connection stringa i próbuje się połączyć do bazy.

I nie bardzo rozumiem co znaczy że w bazie mam dwa schematy aplikacja1 i aplikacja2. Co to jest ten schemat ?

PS. Doskonale rozumiem to że mechanizm logowania do baz danych wymyślono w celu logowania do baz danych, a nie aplikacji. Jednak w moim przypadku jeśli aplikacja na starcie pyta o login i hasło które w dalszej części jest poprzez connection stringa użyte do łączenia się z bazą to przecież de facto to nie jest login i hasło do aplikacji tylko do bazy i dokładnie tak jest używany - jako login i hasło do zalogowania się do bazy (tyle tylko iż to logowanie przeprowadza moja aplikacja).

A już żeby było tak naprawdę "po bożemu " - martiwleś się power userem z Sql server management, to tak naprawdę aplikacja kliencka w ogólnie nie powinna się łączyć z bazą. Nad bazą powinno być API które pyta się bazy, a klient łączy się do API. Dawanie klientowi bezpośredniego dostępu do bazy to niezbyt dobry pomysł.

Ja nigdy nie mówiłem że założyłbym że z poziomu aplikacji będziemy mogli tworzyć użytkowników. Wychodzę z założenia że użytkowników będę sobie tworzył w sql studio logując się na superadmina (sa) - i tylko ja będę miał do niego hasło.

Okay, Ty nie napisałeś, ale rozmawiamy o przypadku ogólnym ;-)
Teraz wyobraź sobie, że piszesz forum - nadal Ty chciałbyś każdemu użytkownikowi ręcznie tworzyć konto?

inb4 ale nie tworzę forum - okay, nie tworzysz forum, tym niemniej: rozmawiamy o dobrych praktykach programistycznych.

I nie bardzo rozumiem co znaczy że w bazie mam dwa schematy aplikacja1 i aplikacja2. Co to jest ten schemat ?

Masz serwer bazy danych, a w nim utworzone jakieś bazy danych (schematy).

Jednak w moim przypadku jeśli aplikacja na starcie pyta o login i hasło które w dalszej części jest poprzez connection stringa użyte do łączenia się z bazą to przecież de facto to nie jest login i hasło do aplikacji tylko do bazy i dokładnie tak jest używany - jako login i hasło do zalogowania się do bazy

Tak, to nie jest login i hasło do aplikacji, bo tak ją stworzyłeś, że to jest akurat login do bazy danych, zatem to zdanie nie ma sensu :-P

Przepraszam, mój błąd, czasem po prostu jak mam trudne zadanie do realizacji to bardziej skupiam się na sobie niż na potrzebach także innych forumowiczów. Chyba już rozumiem większość co zostało poruszone w tej dyskusji, udało mi się uzyskać sporo ważnych porad jeśli chodzi o właśnie dobre praktyki programistyczne. Dziękuję!

Mam tylko jeszcze takie pytanie co do tego API pośredniczącego pomiędzy aplikacją a bazą. Jak i w czym takie API się zazwyczaj tworzy? To jakieś skrypty serwera sql? Czy jakoś inaczej?

Jak i w czym takie API się zazwyczaj tworzy? To jakieś skrypty serwera sql? Czy jakoś inaczej?

Może być Java, Python, Ruby, PHP - praktycznie wszystko, co działa po stronie serwera. Wpisz np. java api tutorial w Google, a niejeden artykuł znajdziesz :-)

API to usługa(aplikacja działająca w tle) która nie ma interfejsu uzytkownika. Jej zadaniem jest przyjmowanie żądań za pomocą protokołu, przetwarzaniem ich (np pobranie listy towarów, wstawienie nowego zamówienia itd) i zwrócenie wynikow do. Takie API może się łączyć bezpośrednio z bazą , kilkoma bazami na innych serwerach , z innymi API td.
API jako takie nie ma nic wspólnego z sewerem SQL , jest po prostu aplikacją która jest klientem bazy.

W twoim wypadku byłoby to cos w stylu : Twoja aplikacja <------> API <-------> Baza

Ma to tą zaletę, że korzystając z API możesz w przyszłości dodać np. aplikacje na telefony , aplikacje webową itd, a wszytsko nadal będzie kompatybilne - np. zamówienie złożone na telefonie traktowane tak samo jak za pomocą aplikacji na PC.

Są też kwestie bezpieczeństwa i rozpowszechniania. Masz swoją apkę na 3 kompach i musisz wproadzić nową funkcjonalność, która wymaga np. dodania nowych kolumn. Co się teraz stanie jeśli 2 aplikacje zaktualizujesz a 3 u pani Krysi z handlowego nie ? Będziez potrafił ogarnąc burdel jeśli ta stara aplikacja wykona serię nieprawidłowych insertów ? Api częściowo chroni przed tym - apliacja się wywali bo api nie zwróci wyników ale dane będą bezpieczne.

Co do technologii to materiał na flame-war. Praktycznie każda platforma oferuje coś w czym mozna stworzyć API , wybór należy do Ciebie : Java, .NET, Python, Ruby, GO itd itp..

W2K napisał(a):

Są też kwestie bezpieczeństwa i rozpowszechniania. Masz swoją apkę na 3 kompach i musisz wproadzić nową funkcjonalność, która wymaga np. dodania nowych kolumn. Co się teraz stanie jeśli 2 aplikacje zaktualizujesz a 3 u pani Krysi z handlowego nie ? Będziez potrafił ogarnąc burdel jeśli ta stara aplikacja wykona serię nieprawidłowych insertów ? Api częściowo chroni przed tym - apliacja się wywali bo api nie zwróci wyników ale dane będą bezpieczne.

Coraz więcej amatorów pcha się do zabawy. O spójność danych można i powinno się zadbać w samej bazie danych, a nie w jakimś API.

A czy napisalem ,że to jedyna mozliowść ? Czy to co napisałem wyklucza sprawdzanie spójności na poziomie bazy ? Czy słowo "częściowo" coś Ci mówi ?

Dziękuję za odpowiedź. Rozumiem teraz w czym rzecz.