Zezwól użytkownikowi na zrobienie czegokolwiek w ramach własnego schematu, ale nie tworzenie ani upuszczanie samego schematu

Utworzyłem schemat w SQL Azure i przyznałem następujące uprawnienia roli bazy danych:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW 
DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;

Dzięki wyżej zdefiniowanym uprawnieniom myusermożna tworzyć / upuszczać własny schemat, więc aby rozwiązać problem, wypróbowałem pozwolenie ALTER ANY SCHEMA. Ale to uprawnienie również odmawia użytkownikowi tworzenia / upuszczania tabel.

Jakie uprawnienia są wymagane, aby użytkownik mógł wykonać cokolwiek w ramach własnego schematu, ale nie mógł tworzyć ani upuszczać samego schematu?

Nie ma potrzeby przyznawania CONTROLschematu.
Wymagane uprawnienie DROP SCHEMAjest albo CONTROLna schemacie, albo ALTER ANY SCHEMAna poziomie bazy danych, i dlatego użytkownik mógł upuścić schemat. Usunięcie tych dwóch uprawnień zapobiegnie tworzeniu i upuszczaniu schematu przez użytkowników powiązanych z rolą (chyba że mają oczywiście uprawnienia wyższego poziomu).

Wymagane uprawnienia do CREATE ALTERi DROPinnych obiektów to CREATEuprawnienie dla typu obiektu (tabela \ procedura \ funkcja \ widok) w połączeniu z ALTERuprawnieniem do schematu.
Masz już te uprawnienia w skrypcie, więc wszystko, co musisz zrobić, to usunąć je CONTROL. W celach informacyjnych znajduje się lista instrukcji BOL, w DDLktórej można znaleźć wymagane uprawnienia dla wszystkich typów obiektów.

Dla leniwych oto kod po usunięciu niepotrzebnego pozwolenia:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;

Należy pamiętać, że ponieważ nowy schemat ma autoryzację „dbo”, użytkownik będzie mógł uzyskać pośredni dostęp do wszystkich obiektów bazy danych, w których schemat jest własnością dbo.

Przykład:

select * from dbo.test; --fails

create view myschema.test
as 
select * 
from dbo.test; --view is created

select * from myschema.test;  --contents of dbo.test now revealed.

Jest to poprawne działanie silnika SQL Server; uprawnienia przenikają do innych schematów z tą samą autoryzacją. Aby ograniczyć taki dostęp, oto opcja tworzenia schematu:

CREATE SCHEMA myschema AUTHORIZATION myrole;