Как предоставить разрешения db_owner другой роли?
Как можно предоставить все права и привилегии db_owner фиксированная роль базы данных для роли приложения?
Укороченная версия
Команда:
GRANT CONTROL ON [DatabaseName] TO [ApplicationRoleName];
было бы то, что я хочу, но это не с:
Msg 15151, Level 16, State 1, Line 23
Cannot find the object 'DatabaseName', because it does not exist or you do not have permission.
Исследования усилий
Я исследую использование ролей приложений SQL Server.
- это как пользователь (в том смысле, что у него есть имя пользователя и пароль)
- и это как роль
После подключения к серверу ваше приложение запускает хранимую процедуру, чтобы "войти" как приложение:
EXECUTE sp_SetAppRole @rolename='Contoso.exe', @password='Tm8gaSBkaWRuJ3QganVzdCBiYXNlNjQgZW5jb2RlIGEgcGFzc3dvcmQuIEl0J3Mgb25seSBhbiBleGFtcGxlIQ=='
Разрешения db_owner
Обычно приложение входит в систему как пользователь, который является членом предопределенной роли db_owner. db_owner Роль имеет разрешение:
- ко всему
- на каждом столе
- каждый взгляд
- каждая хранимая процедура, функция
- для всех существующих объектов
- и все объекты, которые будут существовать в будущем
И пока:
- Вы можете поместить пользователя в роль базы данных
- Вы можете поместить пользователя в роль приложения
Вы не можете поместить роль приложения в роль базы данных.
Таким образом, вопрос в том, как предоставить моему приложению роль, все разрешения, чтобы сделать все.
Разрешения для роли
Поэтому сейчас самое время предоставить разрешения на роль. Следуя советам на этой странице:] 2
GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO "Contoso.exe";
Это интересно и все, но это не дает всех привилегий - это только дает SELECT, INSERT, UPDATE, а также DELETE, Я хочу предоставить все - особенно, когда я не знаю, каковы все привилегии (или могут быть).
Я слепо пытаюсь:
GRANT ALL ON Users to "Contoso.exe";
The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity.
Хорошо, поэтому предоставление ALL не дает ALL. Это... страшно.
Итак, я вернулся к:
GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO "Contoso.exe";
За исключением того, что не все. Например, я знаю, что есть возможность затем предоставлять привилегии другим (привилегия, которая db_owner есть). Поэтому я должен изменить свою вещь на:
GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO "Contoso.exe" WITH GRANT OPTION;
Итак, это ближе, но это относится только к одной таблице.
Мне нужно что-то, что относится ко всем таблицам:
EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE ON ? TO "Contoso.exe" WITH GRANT OPTION;'
Хотя, оказывается, я упустил некоторые привилегии:
- ВЫБРАТЬ ✔️
- ВСТАВИТЬ ✔️
- ОБНОВЛЕНИЕ ✔️
- УДАЛИТЬ ✔️
- ССЫЛКИ ❌
- ALTER ❌
Конечно, я могу обновить свой скрипт:
EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER ON ? TO "Contoso.exe" WITH GRANT OPTION;'
Но вместо этой игры в кошки-мышки: я хочу предоставить ВСЕ разрешения.
ВСЕ это почти все
В приведенном выше предупреждении SQL Server отмечает, что ВСЕ не предоставляют все. Но они документируют все, что дает:
| Permission | Table | View | SP | Scalar UDF | Table UDF |
|------------|-------|------|----|------------|-----------|
| SELECT | ✔️ | ✔️ | | | ✔️ |
| INSERT | ✔️ | ✔️ | | | ✔️ |
| UPDATE | ✔️ | ✔️ | | | ✔️ |
| DELETE | ✔️ | ✔️ | | | ✔️ |
| REFERENCES | ✔️ | ✔️ | | ✔️ | ✔️ |
| EXECUTE | | | ✔️ | ✔️ | |
| ALTER | ❌ | ❌ | ❌ | ❌ | ❌ |
УПРАВЛЯТЬ всеми разрешениями
Оказывается, все они были обмануты. Для другого разрешения были созданы. Одно разрешение управлять ими всеми:
- КОНТРОЛЬ
Предоставляет правообладателю возможности, подобные владению. Получатель фактически имеет все определенные разрешения на защищаемый объект. Принципал, которому был предоставлен КОНТРОЛЬ, может также предоставлять разрешения на защищаемый объект. Поскольку модель безопасности SQL Server является иерархической, в конкретной области управления CONTROL неявно включает в себя CONTROL для всех защищаемых объектов в этой области. Например, CONTROL для базы данных подразумевает все разрешения для базы данных, все разрешения для всех сборок в базе данных, все разрешения для всех схем в базе данных и все разрешения для объектов во всех схемах в базе данных.
Они продолжают перечислять разрешения, которые подразумеваются, когда у вас есть КОНТРОЛЬ:
- ALTER
- КОНТРОЛЬ
- УДАЛЯТЬ
- ВЫПОЛНИТЬ
- ВСТАВИТЬ
- ПОЛУЧАТЬ
- РЕКОМЕНДАЦИИ
- КОНТРОЛЬ
- ВЗЯТЬ НА СЕБЯ ОТВЕТСТВЕННОСТЬ
- ОБНОВИТЬ
- СМОТРЕТЬ ИЗМЕНЕНИЕ ОТСЛЕЖИВАНИЯ
- ПОСМОТРЕТЬ ОПРЕДЕЛЕНИЕ
Это гораздо лучше. Вместо того, чтобы знать все разрешения, я просто предоставляю одно. И вместо того, чтобы знать, какие разрешения применимы к каким объектам, я даю только один. И из-за строки:
Принципал, которому был предоставлен КОНТРОЛЬ, может также предоставлять разрешения на защищаемый объект.
Мне не нужно GRANTWITH GRANT:
EXECUTE sp_msForEachTable 'GRANT CONTROL ON ? TO [Contoso.exe];' --when you have CONTROL you also get WITH GRANT for free
Ко всем объектам
Моя проблема заключается в том, что мне нужно дать разрешение CONTROL для каждого объекта в базе данных. И каждый раз, когда добавляется новый объект, я должен обязательно вернуться и добавить его в роль приложения.
Что мне нужно, это то, что намекнул мой Microsoft:
Например, CONTROL для базы данных подразумевает все разрешения для базы данных, все разрешения для всех сборок в базе данных, все разрешения для всех схем в базе данных и все разрешения для объектов во всех схемах в базе данных.
Переформулировать, если вы даете CONTROL в базе данных, тогда у вас будут все разрешения:
- в базе данных
- все объекты
- все сборки в базе данных
- все схемы
Это то, что я хочу. Я хочу предоставить разрешение КОНТРОЛЯ на Grobber база данных в [Contoso.exe] роль приложения:
GRANT CONTROL ON Grobber TO "Contoso.exe";
Msg 15151, Level 16, State 1, Line 23
Cannot find the object 'Grobber ', because it does not exist or you do not have permission.
Возможно, я почти решил свою проблему, но меня остановили на линии 1 ярд.
Или я могу быть рядом. Поэтому я спрашиваю об этом:
Как предоставить разрешения db_owner другой роли?
1 ответ
"Вы не можете поместить роль приложения в роль базы данных", кажется, это часть, которая не является правильной. Роль (приложения) может быть добавлена в качестве члена другой роли:
EXEC sp_addrolemember 'db_owner', 'ApplicationRoleName';
(С 2012 года эта процедура устарела в пользу нового ALTER ROLE .. ADD MEMBER синтаксис.)
к GRANT CONTROL для всей базы данных в роли, будет делать следующее:
USE [DatabaseName];
GRANT CONTROL ON DATABASE::[DatabaseName] TO [ApplicationRoleName];
USE необходимо внести роль в сферу; DATABASE:: Спецификатор области всегда необходим при обращении к базам данных.
Сказав все это, роль приложения, вероятно, не является хорошим кандидатом для предоставления самых широких полномочий. Пароль для него передается в незашифрованном виде, если только не предприняты меры для шифрования самого соединения, мониторинг и аудит могут пропустить его, и легко забыть вернуться, когда разрешения больше не нужны. Эта последняя часть очень коварна, поскольку активация необратимой роли приложения будет сохраняться через пул соединений, оставляя соединение "зависшим" в режиме администратора. Альтернативы включают в себя открытие отдельного соединения с новыми учетными данными для произвольных действий и использование хранимых процедур с EXECUTE AS для разрешений, которые не могут быть GRANTэффективно.
Одна из вещей, которые
sp_reset_connectionпытается отменить тот факт, что вы являетесь пользователем роли приложения. Это не разрешено (потому что сервер не знает, кем вы были раньше).
...
Единственный способ "исправить" - отключить пул соединений.
Отменить роль приложения нельзя автоматически с помощью sp_reset_connection, но это возможно (начиная с SQL Server 2005). Это просто нужно сделать вручную с помощью процедуры sp_unsetapprole. Это требует инструктированияsp_setapprole для создания файла cookie (VARBINARY value), захватывая его при выполнении этой системной процедуры и сохраняя где-нибудь (в SQL Server через CONTEXT_INFO или новее SESSION_CONTEXTили на уровне приложения). Затем вам просто нужно передать это значение обратно вsp_unsetapprole перед закрытиемSqlConnection (т.е. возврат этого соединения в пул).
Теоретически возможно, что использование EXECUTE AS переключение контекстов безопасности было бы чем-то, что sp_reset_connection может отменить, так как для этого требуется только выполнение REVERT в T-SQL (значение cookie не требуется) и не вызывает ошибку, если не было переключателя контекста, который нужно вернуть (REVERT выдает ошибку только при вызове, когда активная база данных отличается от того, когда EXECUTE ASбыл казнен). Однако у меня нет времени проверять это (да и вообще это не рекомендуется).
Обычно приложение входит в систему как пользователь, который является членом
db_ownerфиксированная роль.
Это, конечно, не идеально (особенно если логин, которому принадлежит база данных, имеет какие-либо права администратора). Да, это, безусловно, упрощает некоторые задачи, поскольку вы фактически отключаете всю безопасность, чтобы никогда не получить ошибку разрешений, но этот подход очень небезопасен, даже если вы добавили дополнительный шаг пароля, необходимый для установки роли приложения.
Предпочтительный подход (который очень детализирован и очень безопасен) - использовать подписывание модулей. Подписывая свои модули (хранимые процедуры, скалярные функции, TVF с несколькими операторами и триггеры), вы можете предоставить им (модулям) любые разрешения, необходимые для выполнения кода, который инкапсулируется, не опасаясь, что кто-либо возьмет эти разрешения и сделает что-то. непреднамеренно с ними. В этом подходе вы предоставляете разрешения коду, а не пользователям (то есть людям / логинам приложений). У меня есть более подробная информация о том, почему вы хотите отказаться от ролей приложений иEXECUTE AS, и переключитесь на подписывание модуля: ПОЖАЛУЙСТА, пожалуйста, прекратите использовать олицетворение, TRUSTWORTHY и связывание владения между базами данных. Конкретные примеры см.: