Grant для объектов баз данных

Эта разновидность команды GRANT даёт одной или нескольким ролям определённые права для доступа к объекту базы данных. Эти права добавляются к списку имеющихся, если роль уже наделена какими-то правами.

Также можно дать роли некоторое право для всех объектов одного типа в одной или нескольких схемах. Эта функциональность в настоящее время поддерживается только для таблиц, последовательностей и функций (но заметьте, что указание ALL TABLES распространяется также на представления и сторонние таблицы).

Ключевое слово PUBLIC означает, что права даются всем ролям, включая те, что могут быть созданы позже. PUBLIC можно воспринимать как неявно определённую группу, в которую входят все роли. Любая конкретная роль получит в сумме все права, данные непосредственно ей и ролям, членом которых она является, а также права, данные роли PUBLIC.

Если указано WITH GRANT OPTION, получатель права, в свою очередь, может давать его другим. Без этого указания распоряжаться своим правом он не сможет. Группе PUBLIC право передачи права дать нельзя.

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

Право удалять объект или изменять его определение произвольным образом не считается назначаемым; оно неотъемлемо связано с владельцем, так что отозвать это право или дать его кому-то другому нельзя (однако похожий эффект можно получить, управляя членством в роли, владеющей объектом). Владелец также неявно получает право распоряжения всеми правами для своего объекта.

PostgreSQL по умолчанию назначает группе PUBLIC определённые права для некоторых типов объектов. Для таблиц, столбцов, последовательностей, обёрток сторонних данных, сторонних серверов, больших объектов, схем или табличных пространств PUBLIC по умолчанию никаких прав не имеет. Для других типов объектов PUBLIC имеет следующие права по умолчанию: CONNECT и TEMPORARY (создание временных таблиц) для баз данных; EXECUTE – для функций, USAGE – для языков и типов данных (включая домены). Владелец объекта, конечно же, может отозвать (с помощью REVOKE) как явно назначенные права, так и права по умолчанию (для максимальной безопасности команду REVOKE нужно выполнять в транзакции, создающей объект; тогда не образуется окно, в котором другой пользователь смог бы обратиться к объекту). Кроме того, эти изначально назначаемые права по умолчанию можно изменить, воспользовавшись командой ALTER DEFAULT PRIVILEGES.

Все возможные права перечислены ниже:

SELECT

Позволяет выполнять SELECT для любого столбца или перечисленных столбцов в заданной таблице, представлении или последовательности. Также позволяет выполнять COPY TO. Помимо того, это право требуется для обращения к существующим значениям столбцов в UPDATE или DELETE. Для последовательностей это право позволяет пользоваться функцией currval. Для больших объектов это право позволяет читать содержимое объекта.

INSERT

Позволяет вставлять строки в заданную таблицу с помощью INSERT. Если право ограничивается несколькими столбцами, только их значение можно будет задать в команде INSERT (другие столбцы получат значения по умолчанию). Также позволяет выполнять COPY FROM.

UPDATE

Позволяет изменять (с помощью UPDATE) данные во всех, либо только перечисленных, столбцах в заданной таблице (на практике для любой нетривиальной команды UPDATE потребуется и право SELECT, так как она должна обратиться к столбцам таблицы, чтобы определить, какие строки подлежат изменению, и/или вычислить новые значения столбцов). Для SELECT... FOR UPDATE и SELECT... FOR SHARE также необходимо это право как минимум для одного столбца, помимо права SELECT. Для последовательностей это право позволяет пользоваться функциями nextval и setval. Для больших объектов это право позволяет записывать данные в объект или обрезать его.

DELETE

Позволяет удалять строки из заданной таблицы с помощью DELETE (на практике для любой нетривиальной команды DELETE потребуется также право SELECT, так как она должна обратиться к столбцам таблицы, чтобы определить, какие строки подлежат удалению).

TRUNCATE

Позволяет опустошить заданную таблицу с помощью TRUNCATE.

REFERENCES

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

TRIGGER

Позволяет создавать триггеры в заданной таблице.

CREATE

Для баз данных это право позволяет создавать схемы в заданной базе.

Для схем это право позволяет создавать новые объекты в заданной схеме. Чтобы переименовать существующий объект, необходимо быть владельцем этого объекта и иметь это право для схемы, содержащей его.

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

CONNECT

Позволяет пользователю подключаться к указанной базе данных. Это право проверяется при установлении соединения (в дополнение к условиям, определённым в конфигурации pg_hba.conf).

TEMPORARY TEMP

Позволяет создавать временные таблицы в заданной базе данных.

EXECUTE

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

USAGE

Для процедурных языков это право позволяет создавать функции на заданном языке. Это единственный тип прав, применимый к процедурным языкам.

Для схем это право даёт доступ к объектам, содержащимся в заданной схеме (предполагается, что при этом имеются права, необходимые для доступа к самим объектам). По сути это право позволяет субъекту «просматривать» объекты внутри схемы. Без этого разрешения имена объектов всё же можно будет узнать, например, обратившись к системным таблицам. Кроме того, если отозвать это право, в существующих сеансах могут оказаться операторы, для которых просмотр имён объектов был выполнен ранее, так что это право не позволяет абсолютно надёжно перекрыть доступ к объектам.

Для последовательностей это право позволяет использовать функции currval и nextval.

Для типов и доменов это право позволяет использовать заданный тип или домен при создании таблиц, функций или других объектов схемы (это право не ограничивает общее «использование» типа, например, обращение к значениям типа в запросах. Не имея этого права, субъект лишается только возможности создавать объекты, зависящие от заданного типа. Основное предназначение этого права в том, чтобы ограничить круг пользователей, способных создавать зависимости от типа, которые могут впоследствии помешать владельцу типа изменить его).

Для обёрток сторонних данных это право позволяет субъекту создавать определения сторонних серверов, использующих заданную обёртку сторонних данных.

Для серверов это право позволяет субъекту создавать определения сторонних таблиц на заданном сервере, а также создавать, изменять и удалять сопоставление для собственного имени пользователя, связанное с этим сервером.

ALL PRIVILEGES

Даёт целевой роли все права сразу. Ключевое слово PRIVILEGES является необязательным в PostgreSQL, хотя в строгом SQL оно требуется.

Более подробная информация: PostgreSQL: Документация: 9.6: GRANT

 

Оборудование

Персональный компьютер с установленной операционной системой WindowsXP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБДPostgreSQL.

 

Задание на работу

1. Создайте учетные записи (в PostgreSQL они называются «роли входа») двух пользователей по одному на каждую локальную область из вашего варианта и две учетные записи помощников администратора.

2. На учетных записях помощников администратора продемонстрируйте работу привилегий на доступ к схеме – USAGE, CREATE. Две из указанных системных привилегий должны быть у обоих пользователей, однако администратор выдает их только одному пользователю (с возможностью наследованной передачи – конструкция WITH GRANT OPTION).

На учетных записях пользователей проверьте работу привилегий на доступ к объекту – SELECT, DELETE, UPDATE, INSERT, а также UPDATE для определенного столбца. Покажите, как работает конструкция WITH GRANT OPTION в данном случае.

Работа каждой привилегии должна быть продемонстрирована следующим образом:

а) выполнить SQL-команду, требующую привилегии. Убедиться, что ее выполнение вызывает ошибку.

б) выдать привилегию

в) выполнить команду из пункта «а» и убедиться, что все заработало.

7. Контрольные вопросы

1. Каким объектам могут быть предоставлены системные привилегии?

2. Укажите все способы наделения пользователя привилегиями. Какой пользователь может назначить другому пользователю роль?

3. Как полностью/частично аннулировать пользователю его привилегии? Какой пользователь может это сделать?

4. Приведите примеры использования оператора GRANT средствами встроенного SQL.

5. Приведите примеры использования оператора REVOKE средствами встроенного SQL

 

Лабораторная работа № 15

Роли

Цель и задачи работы

Целью лабораторной работы является изучение и практическое применение ролей.

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

 

Теоретические сведения

Большое число пользователей, статус которых требует различных привилегий для доступа к ресурсам базы данных, создает значительный объем рутинной работы администратору. Роль – это поименованный набор привилегий, который может быть предоставлен пользователю или другой роли. Роль не является объектом какой-либо схемы.

Описание привилегий, характерных для той или иной роли, готовится заранее. При регистрации нового пользователя в системе администратор выполняет только предоставление пользователю привилегий конкретной роли. При необходимости изменить привилегии конкретному приложению достаточно изменить только привилегии соответствующей роли. Все пользователи, отображенные на эту роль, автоматически получат измененные привилегии. Дополнительное преимущество использования ролей состоит в том, что роль может динамически разрешаться или запрещаться (например, при запуске приложения).

GRANT для ролей

Эта разновидность команды GRANT включает роль в члены одной или нескольких других ролей. Членство в ролях играет важную роль, так как права, данные роли, распространяются и на всех её членов.

С указанием WITH ADMIN OPTION новоиспечённый член роли сможет, в свою очередь, включать в члены этой роли, а также исключать из неё другие роли. Без этого указания обычные пользователи не могут это делать. Считается, что роль не имеет права WITH ADMIN OPTION для самой себя, но ей позволено управлять своими членами из сеанса, в котором пользователь сеанса соответствует данной роли. Суперпользователи баз данных могут включать или исключать любые роли из любых ролей. Роли с правом CREATEROLE могут управлять членством в любых ролях, кроме ролей суперпользователей.

В отличие от прав, членство в ролях нельзя назначить группе PUBLIC. Заметьте также, что эта форма команды не принимает избыточное слово GROUP.

Для лишения субъектов прав доступа применяется команда REVOKE.

Начиная с PostgreSQL версии 8.1, концепции пользователей и групп объединены в единую сущность, названную ролью. Таким образом, теперь нет необходимости добавлять ключевое слово GROUP, чтобы показать, что субъект является группой, а не пользователем. Слово GROUP всё ещё принимается этой командой, но оно лишено смысловой нагрузки.

Пользователь может выполнять SELECT, INSERT и подобные команды со столбцом таблицы, если он имеет такое право для данного столбца или для всей таблицы. Если назначить пользователю требуемое право на уровне таблицы, а затем отозвать его для одного из столбцов, это не даст эффекта, которого можно было бы ожидать: операция с правами на уровне столбцов не затронет право на уровне таблицы.

Если назначить право доступа к объекту (с помощью GRANT) попытается не владелец объекта, команда завершится ошибкой, если пользователь не имеет никаких прав для этого объекта. Если же пользователь имеет какие-то права, команда будет выполняться, но пользователь сможет давать другим только те права, которые даны ему с правом передачи. Формы GRANT ALL PRIVILEGES будут выдавать предупреждение, если у него вовсе нет таких прав, тогда как другие формы будут выдавать предупреждения, если пользователь не имеет прав распоряжаться именно правами, указанными в команде (эти утверждения применимы и к владельцу объекта, но ему разрешено распоряжаться всеми правами, поэтому такие ситуации невозможны).

Следует отметить, что суперпользователи баз данных могут обращаться к любым объектам, вне зависимости от наличия каких-либо прав. Это сравнимо с привилегиями пользователя root в системе Unix. И так же, как root, роль суперпользователя следует использовать только когда это абсолютно необходимо.

Если суперпользователь решит выполнить команду GRANT или REVOKE, она будет выполнена, как если бы её выполнял владелец заданного объекта. В частности, права, назначенные такой командой, будут представлены как права, назначенные владельцем объекта (если так же установить членство в роли, оно будет представлено как назначенное самой ролью).

GRANT и REVOKE также могут быть выполнены ролью, которая не является владельцем заданного объекта, но является членом роли-владельца, либо членом роли, имеющей права WITH GRANT OPTION для этого объекта. В этом случае права будут записаны как назначенные ролью, которая действительно владеет объектом, либо имеет право WITH GRANT OPTION. Например, если таблица t1 принадлежит роли g1, членом которой является u1, то u1 может дать права на использование t1 роли u2, но эти права будут представлены, как назначенные непосредственно ролью g1. Отозвать эти права позже сможет любой член роли g1.

Если роль, выполняющая команду GRANT, получает требуемое право по нескольким путям членства, какая именно роль будет выбрана в качестве назначающей право, не определено. Если это важно, в таких случаях рекомендуется воспользоваться командой SET ROLE и переключиться на роль, которую хочется видеть в качестве выполняющей GRANT.

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

Чтобы получить информацию о существующих правах, назначенных для таблиц и столбцов, воспользуйтесь командой \dp в psql. Например:

=> \dp mytable

                         Access privileges

 Schema | Name | Type | Access privileges | Column access privileges

--------+---------+-------+-----------------------+--------------------------

 public | mytable | table | miriam=arwdDxt/miriam | col1:

                    : =r/miriam       : miriam_rw=rw/miriam

                    : admin=arw/miriam       

(1 row)

Записи, выводимые командой \dp, интерпретируются так:

имя_роли=xxxx -- права, назначенные роли

   =xxxx -- права, назначенные PUBLIC

 

       r -- SELECT ("read", чтение)

       w -- UPDATE ("write", запись)

       a -- INSERT ("append", добавление)

       d -- DELETE

       D -- TRUNCATE

       x -- REFERENCES

       t -- TRIGGER

       X -- EXECUTE

       U -- USAGE

       C -- CREATE

       c -- CONNECT

       T -- TEMPORARY

arwdDxt -- ALL PRIVILEGES (все права для таблиц; для других объектов другие)

       * -- право передачи заданного права

 

   /yyyy -- роль, назначившая это право

В примере выше показано, что увидит пользователь miriam, если создаст таблицу mytable и выполнит:

GRANT SELECT ON mytable TO PUBLIC;

GRANT SELECT, UPDATE, INSERT ON mytable TO admin;

GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

Для других объектов есть другие команды \d, которые также позволяют просмотреть назначенные права.

Если столбец «Права доступа» для данного объекта пуст, это значит, что для объекта действуют стандартные права (то есть столбец прав содержит NULL). Права по умолчанию всегда включают все права для владельца и могут также включать некоторые права для PUBLIC в зависимости от типа объекта, как разъяснялось выше. Первая команда GRANT или REVOKE для объекта приводит к проявлению записи прав по умолчанию (например, {miriam=arwdDxt/miriam}), а затем изменяет эту запись в соответствии с заданным запросом. Подобным образом, строки, показанные в столбце «Права доступа к столбцам», выводятся только для столбцов с нестандартными правами доступа. (Заметьте, что в данном контексте под «стандартными правами» всегда подразумевается встроенный набор прав, предопределённый для типа объекта. Если с объектом связан набор прав по умолчанию, полученный после изменения в результате ALTER DEFAULT PRIVILEGES, изменённые права будут всегда выводиться явно, показывая эффект команды ALTER.)

Право распоряжения правами, которое имеет владелец, не отмечается в выводимой сводке. Знаком * отмечаются только те права с правом передачи, которые были явно назначены кому-либо.

Более подробная информация: PostgreSQL: Документация: 9.6: GRANT

 

Оборудование

Оборудование: персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

Задание на работу

1. Создайте три роли (которые в PostgreSQL называются «групповые роли»).

Первая роль – общая. Назначьте этой роли привилегии, необходимые обеим группам пользователей в вашей системе.

Две другие – специальные роли, соответствующие группам пользователям (локальным областям) вашей базы данных. Эти роли должны унаследовать привилегии общей роли с добавлением специальных привилегий, характерных для соответствующей группы пользователей.

Назначьте созданные специальные роли пользователям.

2. Приведите SQL запросы на создание ролей, назначение привилегий, присвоение ролей пользователям, а также экспериментально дать ответы на следующие вопросы:

- что будет, если назначить роль пользователю, а затем изменить состав привилегий в данной роли? Отразится ли это на возможностях пользователя?

- что будет, если назначить специальную роль пользователю, а затем исключить из специальной роли общую роль?

- что будет, если отобрать у пользователя привилегию, которая была передана ему неявно через роль.

7. Контрольные вопросы

1. Понятие «Роль». Область действия роли.

2. Как можно создать, удалить учетную запись пользователя, редактировать ее параметры?

3. Укажите все способы наделения пользователя привилегиями. Какой пользователь может назначить другому пользователю роль?

4. Как полностью/частично аннулировать пользователю его привилегии? Какой пользователь может это сделать?

5. Приведите примеры использования оператора CONNECT средствами встроенного SQL.

Лабораторная работа № 16