Odbieranie „Uprawnienie SELECT zostało odrzucone na obiekcie”, nawet jeśli zostało udzielone

Jestem programistą, a nie dba ... Wiem wystarczająco dużo, aby być niebezpiecznym.

Dziedziczyłem bazę danych ze starszym użytkownikiem, który jest właścicielem bazy danych db_owner. Nie możemy dostosowywać uprawnień tego użytkownika do istniejących tabel, schematów itp. Ze względów biznesowych, ale tworzone są nowe tabele i chcę, aby ten użytkownik miał do nich dostęp WYBIERZ.

Uprawnienia dla tych użytkowników zostały ustawione dla tych tabel, dzięki czemu wszystko jest ZABLOKOWANE, z wyjątkiem WYBIERZ, dla którego ustawiono GRANT.

Jednak gdy ten użytkownik (dbadmin) próbuje wykonać WYBÓR w jednej z tych tabel (AccountingAudit), występuje ten błąd:

The SELECT permission was denied on the object 'AccountingAudit', database 'billing', schema 'dbo'.

Uruchomiłem ten SQL, aby sprawdzić, jakie uprawnienia są ustawione dla tej tabeli / użytkownika:

select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
 permission_name,
 state_desc
from sys.database_permissions

I to właśnie otrzymuję:

AccountingAudit dbadmin dbo ALTER   DENY
AccountingAudit dbadmin dbo CONTROL DENY
AccountingAudit dbadmin dbo DELETE  DENY
AccountingAudit dbadmin dbo INSERT  DENY
AccountingAudit dbadmin dbo REFERENCES  DENY
AccountingAudit dbadmin dbo SELECT  GRANT
AccountingAudit dbadmin dbo TAKE OWNERSHIP  DENY
AccountingAudit dbadmin dbo UPDATE  DENY
AccountingAudit dbadmin dbo VIEW DEFINITION DENY
AccountingAudit dbadmin dbo VIEW CHANGE TRACKING    DENY

Wygląda na to, że powinno działać poprawnie?

Wywoływane przeze mnie WYBÓR to bardzo podstawowy WYBÓR * z AccountingAudit z poziomu SSMS. Nie robię żadnego specjalnego sp_executesql ani nic takiego.

Próbowałem jawnie udzielić pozwolenia:

GRANT SELECT ON [dbo].AccountingAudit TO dbadmin

Nie ma to żadnego efektu (dlaczego miałoby to być, powyższe zapytanie pokazuje, że zostało przyznane! ;-)

Szukałem przez stackoverflow.com i gdzie indziej i nie mogę znaleźć niczego, czego jeszcze nie próbowałem. Zastanawiam się, czy ma to związek z konfiguracją schematów. (W tym momencie niewiele wiem o schematach.)

Jakieś pomysły? Dzięki!

Nie jestem tu pewien, ale zamierzam wyjść na kończynę. Myślę, że twój problem może dotyczyć twojej DENY CONTROLdokumentacji. Zobacz tutaj mniej więcej w połowie strony:

Odmowa uprawnienia CONTROL do bazy danych domyślnie odmawia uprawnienia CONNECT do bazy danych. Główny zobowiązany, któremu odmówiono uprawnienia CONTROL do bazy danych, nie będzie mógł połączyć się z tą bazą danych.

Zdaję sobie sprawę, że ten przykład dotyczy bazy danych, ale postaw ją jeszcze jeden poziom granulacji. A DENY CONTROLna stole odmówi wszelkich przywilejów, jak sądzę. Zrób to, REVOKE CONTROLaby się tego pozbyć i sprawdź, czy to rozwiąże problem.

Jeśli tak, musisz umieścić użytkownika w roli bazy danych lub odmówić mu jawnych uprawnień w stosunku do tabeli.

1. Użyj sp_DBPermissionsprocedury przechowywanej Kena Fishera, aby sprawdzić uprawnienia.

   1. Upewnij się, że DENY CONTROLnie stosuje się do stołu, w uzupełnieniu do wspólnej DENY SELECT, DENY INSERT, DENY UPDATE, DENY DELETEi DENY REFERENCES.
   2. Jeśli SELECTinstrukcja zawiera funkcje o wartościach przechowywanych w tabeli, upewnij się, że istnieje albo EXECUTE AS OWNERna funkcji o wartościach przechowywanych w tabeli, albo GRANT EXECUTEna niej (i nie DENY EXECUTE!). W takim przypadku przeczytaj uważniej komunikat o błędzie, ponieważ prawdopodobnie nie powie on, że uprawnienie SELECT zostało odrzucone w tabeli, ale coś o odmowie wykonania.

2. Jeśli użytkownik jest użytkownikiem lub grupą AD, użyj następującego skryptu, aby określić użytkownika login_token( użytkowników ):

EXECUTE AS LOGIN = 'EXAMPLEDOMAIN\JOHN.DOE';
SELECT * FROM sys.login_token;
REVERT;

3. Spójrz na aktualny plan wykonania. Jeśli błąd znajduje się w procedurze przechowywanej za pomocą SET NOCOUNT ON;, wówczas rzeczywisty plan wykonania da ci wgląd, na który możesz nie zwracać uwagi, po prostu patrząc na kartę Wiadomości w SSMS, ponieważ „Wpływ na wiersze” może być poza twoją kontrolą.

   1. Poszukaj wyzwalaczy lub tabel tymczasowych.

4. Można skompilować instrukcję jako procedurę przechowywaną i SSMS „Zobacz zależności obiektów”, a także sztuczki przedstawione przez Svetlana Golovko w różnych sposobach, aby znaleźć zależności obiektów SQL Server

5. Za pomocą zdarzenia SQL Server Profiler Security „Audit Schema Object Access Event” oraz kolumn „TextData” i „Success” można śledzić, na których obiektach SQL Server ocenia uprawnienia. - Widziałem sytuacje, w których dla tego zdarzenia są emitowane dwa wiersze, a jedna wartość mówi Sukces = 1, a druga Sukces = 0. W tym scenariuszu jedynym rozwiązaniem, które znalazłem, jest ponowne uruchomienie serwera. Nawet uruchomienie repadmin /syncallnie rozwiązało problemu, nie uruchomiło się i nie zatrzymało aplikacji (a zatem puli połączeń).

6. Określ efektywne uprawnienia do logowania:

-- '<domain>\<username>' is a domain user in the group you wish to test
EXECUTE AS LOGIN = '<domain>\<username>';
SELECT * FROM fn_my_permissions('Database.Schema.Table', 'OBJECT');
REVERT;

8. Jeśli użytkownik jest powiązany z użytkownikiem lub grupą AD, rozważ uruchomienie, repadmin /syncallaby wymusić synchronizację zmian wprowadzonych w usłudze Active Directory na kontrolerach domeny. - Jeśli ktoś zna dobry sposób na porównanie bieżących wartości dwóch kontrolerów domeny, daj mi znać.

9. Przed rozważeniem twardego ponownego uruchomienia całego systemu, spróbuj zabić wszystkie aktywne połączenia dla tego użytkownika. Powodem jest to, że użytkownik otrzymuje token systemu Windows od kontrolera domeny, który obejmuje ich grupy. Token nie zostanie zaktualizowany, dopóki użytkownik nie otrzyma nowego tokena - zwykle przez wylogowanie, a następnie ponowne zalogowanie.

10. Ponownie uruchom system ponownie. To zadziałało dla mnie. Nadal nie jestem w 100% pewien, dlaczego jeszcze. TYLKO ZRÓB TO, JEŚLI MOŻESZ PRZEWRÓCIĆ CZAS! UWAŻAJCIE NA WYKONYWANIU TEGO, GDY MASZ DUŻE WYJĄTKOWE TRANSAKCJE!