Как я могу управлять правами хранимой процедуры роли в SQL Server

Question

Я хочу создать три роли базы данных SQL Server.

1. Это может СОЗДАТЬ, ИЗМЕНИТЬ и ВЫПОЛНИТЬ все хранимые процедуры в базе данных.
2. Это может только ВЫПОЛНИТЬ все хранимые процедуры в базе данных
3. У которых нет доступа к хранимым процедурам в базе данных

Я создал роли, но при ОТМЕНЕ их разрешений у меня возникают проблемы.

Я казнил

REVOKE CREATE PROCEDURE TO [ROLE NAME]

чтобы отозвать разрешения на создание процедуры, и она успешно выполнилась.

Но при выполнении этого оператора я получил ошибку:

Ошибка: неправильный синтаксис рядом с "ALTER".

Я новичок в правах на роль SQL-сервера, поэтому могу полностью ошибиться в своем подходе.

Пожалуйста, направьте меня к правильному достижению моей цели.

Благодарность

Answer 1

Из документации Создайте хранимую процедуру:

Разрешения

Требуется разрешение CREATE PROCEDURE в базе данных и разрешение ALTER для схемы, в которой создается процедура.

Поэтому просто давая CREATE PROCEDUREсам по себе не позволит вам создать процедуру. Фактически, даваяROLE то CREATE PROCEDURE разрешение, а не ALTER на схеме приведет к следующей ошибке:

Указанное имя схемы "dbo" либо не существует, либо у вас нет разрешения на его использование.

Здесь нет ALTER PROCEDURE разрешения, следовательно, для члена ROLE иметь возможность как CREATE а также ALTER а PROCEDURE вам нужно будет сделать:

GRANT CREATE PROCEDURE TO YourRole;
GRANT ALTER ON SCHEMA::dbo TO YourRole; --Replace with appropriate schema name

Это, однако, также позволит пользователю ALTERлюбые процедуры по указанной схеме. Ut также позволяет тем, кто играет эту роль,ALTER а также другие объекты схемы (например, таблицы).

Если твой ROLE имеет разрешения на ALTER процедуры, и вы хотите удалить это, вам нужно будет выполнить следующее:

REVOKE ALTER ON SCHEMA::dbo TO YourRole;

Это, как уже упоминалось, лишит их возможности ALTER любые другие объекты в указанной схеме.

Помнить, REVOKE не DENY, это просто означает, что USER не унаследует это разрешение от этого ROLEбольше нет. ЕслиUSER имеет разрешение от другого ROLE, или у них есть разрешение, они смогут продолжать использовать разрешение. Если вы должны остановитьUSER от выполнения действия, независимо от любых других разрешений, они должны иметь DENY разрешение.

Answer 2

1) Это может СОЗДАТЬ, ИЗМЕНИТЬ и ВЫПОЛНИТЬ все хранимые процедуры в базе данных

Это роль db_owner или разрешение CONTROL в базе данных. Любой, у кого есть все эти разрешения, может повысить свои привилегии до администратора уровня базы данных. Так что не пытайся.

2) Это может только ВЫПОЛНИТЬ все хранимые процедуры в базе данных

ПРЕДОСТАВИТЬ ВЫПОЛНЕНИЕ [SomeRole]

3) У которых нет доступа к хранимым процедурам в базе данных

Пользователь не имеет доступа к хранимой процедуре, если вы не предоставите разрешения или не добавите их к роли, имеющей разрешения.