какое sql выражение можно использовать для добавления прав пользователю
Как в MS SQL Server создать пользователя
На первый взгляд, в MS SQL нестандартная система учетных записей. В ней существуют логины и пользователи.
Оба типа учетных записи можно создать как в графике, так и с помощью запроса T-SQL
T-SQL CREATE USER, CREATE LOGIN
Перейдем в базу данных, в которой мы работаем:
Для создания логина SQL выполним:
Посмотреть что этот логин создался можно так:
Для создания пользователя привязанного к логину нужно выполнить:
Отмечу, что пользователь создасться в той базе, откуда мы выполняем запрос.
Что бы просто создать пользователя нужно:
Созданных пользователей можно увидеть так:
Пользователи создаются без каких либо прав на базу.
T-SQL GRANT
GRANT в пер. «Разрешение» дает права на какое-то действие.
Что бы дать пользователю право получать данные из какой-то таблицы нужно выполнить:
Если мы хотим зайти под созданным пользователем нам нужно выполнить ряд действий. Дело в том, что по умолчанию аутентификация по логинам SQL отключена (разрешена только Windows). Для этого зайдем в свойства сервера:
Затем в «Безопасность» и включи аутентификация SQL
Важно сделать так, что бы у нас было 2 окошка для запросов до того как мы сменем пользователя. Благодаря этому у нас каждое окно будет работать под разными пользователями
Затем окно подключения пользователя:
Меняем тип подключени на SQL и вводим логин/пароль. Я создавал логин Anton, с паролем Password1910.
После подключения можем увидеть, что одно окно работает под логином Anton, а другое под учетной записью с помощью которой мы проводили операции выше. Так же обратите внимания, что новые пользователь подключен к другой базе (master).
Перейдем к базе, на которую мы давали права. В моем случае это база pubs
Я давал права на SELECT для таблицы dbo.authors. Проверем работу:
Запрос дожен пройти успешно.
Для другой таблицы, на которую мы не давали прав:
The SELECT permission was denied on the object ‘titles’, database ‘pubs’, schema ‘dbo’.
Что бы отозвать разрешение на SELECT нужно заменить GRANT на REVOKE (не забудьте переключится на окно у которого есть разрешение или переключится на предыдущего пользователя):
Разрешений, которые мы можем выдать пользователю достаточно много. Это основные:
Мы можем так же явно запретить пользователю выполнять определенные запросы. Для этого есть DENY:
Что ба дать разрешение на создание таблиц:
Теперь создадим логин через графический интерфейс. Нажмем следующие кнопки:
В новом окне мы должны заполнить логин (1) и пароль (3). Под цифрой 2 мы можем увидеть выбор способа аутентификации. В случае Windows мы должны будем выбрать локального пользователя или пользователя AD. Перейдем на закладку Server Roles (4).
Приведу описание самых популярных ролей:
Перейдем на следующую вкладку (2)
На этой закладке можно сразу создать пользователя и привязать его к базе данных. На этой закладке отображаются 4 базы, которые вы скорее всего раньше не видели. Это системные базы данных, которые хранят в себе транзакции, структуру подключенных баз, скрипты агента и т.д. В области 2 можно дать права на выделенную базу данных.
Коротко о нескольких ролях баз данных:
Роль public, в отличии от всех остальных, можно изменять. Т.е. можно дать роли public разрешение на SELECT и тогда все пользователи с логином смогут это делать. По умолчанию эта ролько может делать запросы типа:
GRANT, предоставление разрешений на базу данных (Transact-SQL)
Предоставляет разрешения на базу данных в SQL Server.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
permission — указывает предоставляемое разрешение для базы данных. Список разрешений см. в подразделе «Примечания» далее в этом разделе.
ALL — этот параметр предоставляет не все возможные разрешения. Предоставление разрешения ALL эквивалентно предоставлению следующих разрешений: BACKUP DATABASE, BACKUP LOG, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE и CREATE VIEW.
PRIVILEGES — включено для обеспечения совместимости с требованиями ANSI-92. Не изменяет работу ALL.
WITH GRANT OPTION — указывает, что субъекту будет дана возможность предоставлять заданное разрешение другим субъектам.
Database_user — указывает пользователя базы данных.
Database_role — указывает роль базы данных.
Application_role — применимо к: SQL Server 2008 и выше, База данных SQL
Указывает роль приложения.
Database_user_mapped_to_Windows_User — применимо к: SQL Server 2008 и выше
Указывает пользователя базы данных, сопоставленного с пользователем Windows.
Database_user_mapped_to_Windows_Group — применимо к: SQL Server 2008 и выше
Указывает пользователя базы данных, сопоставленного с группой Windows.
Database_user_mapped_to_certificate — применимо к: SQL Server 2008 и выше
Указывает пользователя базы данных, сопоставленного с сертификатом.
Database_user_mapped_to_asymmetric_key — применимо к: SQL Server 2008 и выше
Указывает пользователя базы данных, сопоставленного с асимметричным ключом.
Database_user_with_no_login — указывает пользователя базы данных, не сопоставленного с субъектом серверного уровня.
Remarks
Сочетание разрешений ALTER и REFERENCE в некоторых случаях может позволить просматривать данные или выполнять несанкционированные функции. Пример: Пользователь с разрешением ALTER на таблицу и разрешением REFERENCE на функцию может создавать вычисляемый столбец на основе функции и в результате — выполнять ее. В этом случае пользователю также требуется разрешение SELECT на вычисляемый столбец.
База данных — это защищаемый объект, хранящийся на сервере, который является родителем базы данных в иерархии разрешений. Наиболее специфичные и ограниченные разрешения, которые можно предоставлять в базе данных, перечислены в следующей таблице вместе с общими разрешениями, неявно содержащими их.
| Разрешение в базе данных | Содержится в разрешении базы данных | Подразумевается в разрешении сервера |
|---|---|---|
| ADMINISTER DATABASE BULK OPERATIONS Область применения: База данных SQL. | CONTROL | CONTROL SERVER |
| ALTER | CONTROL | ALTER ANY DATABASE |
| ALTER ANY APPLICATION ROLE | ALTER | CONTROL SERVER |
| ALTER ANY ASSEMBLY | ALTER | CONTROL SERVER |
| ALTER ANY ASYMMETRIC KEY | ALTER | CONTROL SERVER |
| ALTER ANY CERTIFICATE | ALTER | CONTROL SERVER |
| ALTER ANY COLUMN ENCRYPTION KEY | ALTER | CONTROL SERVER |
| ALTER ANY COLUMN MASTER KEY DEFINITION | ALTER | CONTROL SERVER |
| ALTER ANY CONTRACT | ALTER | CONTROL SERVER |
| ALTER ANY DATABASE AUDIT | ALTER | ALTER ANY SERVER AUDIT |
| ALTER ANY DATABASE DDL TRIGGER | ALTER | CONTROL SERVER |
| ALTER ANY DATABASE EVENT NOTIFICATION | ALTER | ALTER ANY EVENT NOTIFICATION |
| ALTER ANY DATABASE EVENT SESSION Применимо к: База данных SQL. | ALTER | ALTER ANY EVENT SESSION |
| ALTER ANY DATABASE SCOPED CONFIGURATION Применимо к: SQL Server 2016 (13.x); и выше, База данных SQL. | CONTROL | CONTROL SERVER |
| ALTER ANY DATASPACE | ALTER | CONTROL SERVER |
| ALTER ANY EXTERNAL DATA SOURCE | ALTER | CONTROL SERVER |
| ALTER ANY EXTERNAL FILE FORMAT | ALTER | CONTROL SERVER |
| ALTER ANY EXTERNAL LIBRARY Применимо к: SQL Server 2017 (14.x);. | CONTROL | CONTROL SERVER |
| ALTER ANY FULLTEXT CATALOG | ALTER | CONTROL SERVER |
| ALTER ANY MASK | CONTROL | CONTROL SERVER |
| ALTER ANY MESSAGE TYPE | ALTER | CONTROL SERVER |
| ALTER ANY REMOTE SERVICE BINDING | ALTER | CONTROL SERVER |
| ALTER ANY ROLE | ALTER | CONTROL SERVER |
| ALTER ANY ROUTE | ALTER | CONTROL SERVER |
| ALTER ANY SCHEMA | ALTER | CONTROL SERVER |
| ALTER ANY SECURITY POLICY Применимо к: База данных SQL Azure. | CONTROL | CONTROL SERVER |
| ALTER ANY SERVICE | ALTER | CONTROL SERVER |
| ALTER ANY SYMMETRIC KEY | ALTER | CONTROL SERVER |
| ALTER ANY USER | ALTER | CONTROL SERVER |
| AUTHENTICATE | CONTROL | AUTHENTICATE SERVER |
| BACKUP DATABASE | CONTROL | CONTROL SERVER |
| BACKUP LOG | CONTROL | CONTROL SERVER |
| CHECKPOINT | CONTROL | CONTROL SERVER |
| CONNECT | CONNECT REPLICATION | CONTROL SERVER |
| CONNECT REPLICATION | CONTROL | CONTROL SERVER |
| CONTROL | CONTROL | CONTROL SERVER |
| CREATE AGGREGATE | ALTER | CONTROL SERVER |
| CREATE ANY EXTERNAL LIBRARY Применимо к: SQL Server 2017 (14.x);. | CONTROL | CONTROL SERVER |
| CREATE ASSEMBLY | ALTER ANY ASSEMBLY | CONTROL SERVER |
| CREATE ASYMMETRIC KEY | ALTER ANY ASYMMETRIC KEY | CONTROL SERVER |
| CREATE CERTIFICATE | ALTER ANY CERTIFICATE | CONTROL SERVER |
| CREATE CONTRACT | ALTER ANY CONTRACT | CONTROL SERVER |
| CREATE DATABASE | CONTROL | CREATE ANY DATABASE |
| CREATE DATABASE DDL EVENT NOTIFICATION | ALTER ANY DATABASE EVENT NOTIFICATION | CREATE DDL EVENT NOTIFICATION |
| CREATE DEFAULT | ALTER | CONTROL SERVER |
| CREATE FULLTEXT CATALOG | ALTER ANY FULLTEXT CATALOG | CONTROL SERVER |
| CREATE FUNCTION | ALTER | CONTROL SERVER |
| CREATE MESSAGE TYPE | ALTER ANY MESSAGE TYPE | CONTROL SERVER |
| CREATE PROCEDURE | ALTER | CONTROL SERVER |
| CREATE QUEUE | ALTER | CONTROL SERVER |
| CREATE REMOTE SERVICE BINDING | ALTER ANY REMOTE SERVICE BINDING | CONTROL SERVER |
| CREATE ROLE | ALTER ANY ROLE | CONTROL SERVER |
| CREATE ROUTE | ALTER ANY ROUTE | CONTROL SERVER |
| CREATE RULE | ALTER | CONTROL SERVER |
| CREATE SCHEMA | ALTER ANY SCHEMA | CONTROL SERVER |
| CREATE SERVICE | ALTER ANY SERVICE | CONTROL SERVER |
| CREATE SYMMETRIC KEY | ALTER ANY SYMMETRIC KEY | CONTROL SERVER |
| CREATE SYNONYM | ALTER | CONTROL SERVER |
| CREATE TABLE | ALTER | CONTROL SERVER |
| CREATE TYPE | ALTER | CONTROL SERVER |
| CREATE VIEW | ALTER | CONTROL SERVER |
| CREATE XML SCHEMA COLLECTION | ALTER | CONTROL SERVER |
| DELETE | CONTROL | CONTROL SERVER |
| EXECUTE | CONTROL | CONTROL SERVER |
| EXECUTE ANY EXTERNAL SCRIPT Применимо к: SQL Server 2016 (13.x);. | CONTROL | CONTROL SERVER |
| EXECUTE EXTERNAL SCRIPT Применимо к: SQL Server 2019 (15.x). | EXECUTE ANY EXTERNAL SCRIPT | CONTROL SERVER |
| INSERT | CONTROL | CONTROL SERVER |
| KILL DATABASE CONNECTION Применимо к: База данных SQL Azure. | CONTROL | ALTER ANY CONNECTION |
| REFERENCES | CONTROL | CONTROL SERVER |
| SELECT | CONTROL | CONTROL SERVER |
| SHOWPLAN | CONTROL | ALTER TRACE |
| SUBSCRIBE QUERY NOTIFICATIONS | CONTROL | CONTROL SERVER |
| TAKE OWNERSHIP | CONTROL | CONTROL SERVER |
| UNMASK | CONTROL | CONTROL SERVER |
| UPDATE | CONTROL | CONTROL SERVER |
| VIEW ANY COLUMN ENCRYPTION KEY DEFINITION | CONTROL | VIEW ANY DEFINITION |
| VIEW ANY COLUMN MASTER KEY DEFINITION | CONTROL | VIEW ANY DEFINITION |
| VIEW DATABASE STATE | CONTROL | VIEW SERVER STATE |
| VIEW DEFINITION | CONTROL | VIEW ANY DEFINITION |
Разрешения
Объект, предоставляющий разрешение (или участник, указанный параметром AS), должен иметь либо само разрешение, выданное с помощью параметра GRANT OPTION, либо разрешение более высокого уровня, которое неявно включает предоставляемое.
При использовании параметра AS налагаются следующие дополнительные требования.
| AS granting_principal | Необходимо дополнительное разрешение |
|---|---|
| пользователь базы данных; | Разрешение IMPERSONATE для пользователя, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| пользователь базы данных, сопоставленный с именем входа Windows; | Разрешение IMPERSONATE для пользователя, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| Пользователь базы данных, сопоставленный группе Windows | Членство в группе Windows, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| пользователь базы данных, сопоставленный с сертификатом; | Членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| пользователь базы данных, сопоставленный с асимметричным ключом; | Членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| Пользователь базы данных, не сопоставленный ни с одним участником на уровне сервера | Разрешение IMPERSONATE для пользователя, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| роль базы данных; | Разрешение ALTER на роль, членство в предопределенной роли базы данных db_securityadmin, предопределенной роли базы данных db_owner или предопределенной роли сервера sysadmin. |
| Роль приложения | Разрешение ALTER на роль, членство в предопределенной роли базы данных db_securityadmin, предопределенной роли базы данных db_owner или предопределенной роли сервера sysadmin. |
Владельцы объектов могут предоставлять разрешения на объекты, которыми они владеют. Участники, имеющие разрешение CONTROL на защищаемый объект, могут предоставлять разрешение на этот защищаемый объект.
Примеры
A. Предоставление разрешения на создание таблиц
Б. Предоставление разрешения SHOWPLAN роли приложения
Применимо к: SQL Server 2008 и выше, База данных SQL
В. Предоставление разрешения CREATE VIEW с параметром GRANT OPTION
В следующем примере пользователю CREATE VIEW предоставляется разрешение AdventureWorks2012 в базе данных CarmineEs с правом предоставлять разрешение CREATE VIEW другим участникам.
Г. Предоставление разрешения CONTROL пользователю базы данных
GRANT, предоставление разрешений на объект (Transact-SQL)
Предоставляет разрешения на таблицу, представление, функцию с табличным значением, хранимую процедуру, расширенную хранимую процедуру, скалярную функцию, агрегатную функцию, очередь обслуживания или синоним.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
permission
Указывает разрешение, которое может быть предоставлено на содержащийся в схеме объект. Список разрешений см. в подразделе «Примечания» далее в этом разделе.
ALL
Предоставление ALL не включает все возможные разрешения, оно эквивалентно предоставлению всех разрешений ANSI-92, применимых к указанному объекту. Значение ALL различается для разных типов объектов
PRIVILEGES
Включено для обеспечения совместимости с ANSI-92. Не изменяет работу ALL.
column
Указывает имя столбца в таблице, представление или функции с табличным значением, на которых предоставляется разрешение. Указание круглых скобок ( ) обязательно. На столбец могут быть предоставлены только разрешения SELECT, REFERENCES и UPDATE. Аргумент column может быть указан в предложении PERMISSIONS или после имени защищаемого объекта.
Запрет (DENY) уровня таблицы имеет меньший приоритет, чем разрешение (GRANT) уровня столбца. Такая несогласованность в иерархии разрешений сохранена в целях обратной совместимости.
TO
Участник, которому предоставляется разрешение.
WITH GRANT OPTION
Показывает, что участнику будет дана возможность предоставлять указанное разрешение другим участникам.
Database_user
Указывает пользователя базы данных.
Database_role
Указывает роль базы данных.
Application_role
Указывает роль приложения.
Database_user_mapped_to_Windows_User
Указывает пользователя базы данных, сопоставленного с пользователем Windows.
Database_user_mapped_to_Windows_Group
Указывает пользователя базы данных, сопоставленного с группой Windows.
Database_user_mapped_to_certificate
Указывает пользователя базы данных, сопоставленного с сертификатом.
Database_user_mapped_to_asymmetric_key
Указывает пользователя базы данных, сопоставленного с асимметричным ключом.
Database_user_with_no_login
Указывает пользователя базы данных, не сопоставленного с субъектом серверного уровня.
Remarks
Сочетание разрешений ALTER и REFERENCE в некоторых случаях может позволить просматривать данные или выполнять несанкционированные функции. Пример: Пользователь с разрешением ALTER на таблицу и разрешением REFERENCE на функцию может создавать вычисляемый столбец на основе функции и в результате — выполнять ее. В этом случае пользователю также требуется разрешение SELECT на вычисляемый столбец.
Сведения об объектах доступны через различные представления каталога. Дополнительные сведения см. в разделе Представления каталога объектов (Transact-SQL).
Объект является защищаемым на уровне схемы. Он содержится в схеме, которая является его родителем в иерархии разрешений. В следующей таблице перечислен ряд отдельных разрешений, которые могут быть предоставлены на объект, а также наиболее общие разрешения, которые неявно их подразумевают.
| Разрешение объекта | Содержится в разрешении объекта | Содержится в разрешении схемы |
|---|---|---|
| ALTER | CONTROL | ALTER |
| CONTROL | CONTROL | CONTROL |
| DELETE | CONTROL | DELETE |
| EXECUTE | CONTROL | EXECUTE |
| INSERT | CONTROL | INSERT |
| RECEIVE | CONTROL | CONTROL |
| REFERENCES | CONTROL | REFERENCES |
| SELECT | RECEIVE | SELECT |
| TAKE OWNERSHIP | CONTROL | CONTROL |
| UPDATE | CONTROL | UPDATE |
| VIEW CHANGE TRACKING | CONTROL | VIEW CHANGE TRACKING |
| VIEW DEFINITION | CONTROL | VIEW DEFINITION |
Разрешения
Объект, предоставляющий разрешение (или участник, указанный параметром AS), должен иметь либо само разрешение, выданное с помощью параметра GRANT OPTION, либо разрешение более высокого уровня, которое неявно включает предоставляемое.
При использовании параметра AS налагаются следующие дополнительные требования.
| AS | Необходимо дополнительное разрешение |
|---|---|
| пользователь базы данных; | Разрешение IMPERSONATE для пользователя, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| пользователь базы данных, сопоставленный с именем входа Windows; | Разрешение IMPERSONATE для пользователя, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| Пользователь базы данных, сопоставленный группе Windows | Членство в группе Windows, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| пользователь базы данных, сопоставленный с сертификатом; | Членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| пользователь базы данных, сопоставленный с асимметричным ключом; | Членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| Пользователь базы данных, не сопоставленный ни с одним участником на уровне сервера | Разрешение IMPERSONATE для пользователя, членство в предопределенной роли базы данных db_securityadmin, членство в предопределенной роли базы данных db_owner или членство в предопределенной роли сервера sysadmin. |
| роль базы данных; | Разрешение ALTER на роль, членство в предопределенной роли базы данных db_securityadmin, предопределенной роли базы данных db_owner или предопределенной роли сервера sysadmin. |
| Роль приложения | Разрешение ALTER на роль, членство в предопределенной роли базы данных db_securityadmin, предопределенной роли базы данных db_owner или предопределенной роли сервера sysadmin. |