SQL-Ex blog

Мы опять будем использовать базу данных Chinook. Вы можете загрузить ее копию, для работы с примерами этой статьи.

SQL Server

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

В SQL Server это довольно легко: используйте GRANT на SCHEMA. Это все!

Рассмотрим пример, в котором мы создаем схему Finance, владение которой принадлежит роли Finance. С помощью этого простого оператора мы создали Finance и назначили ее владельцем роль Finance. Тем самым роль имеет все привилегии на эту схему.

CREATE SCHEMA [Finance] AUTHORIZATION Finance

Теперь припишем несколько объектов этой схеме:

alter schema Finance transfer Invoice;

alter schema Finance transfer InvoiceLine;

С помощью ALTER SCHEMA... TRANSFER... мы можем перенести таблицы или другие объекты в эту схему.



Мы можем теперь создать роль Supervisor и назначить ей привилегию чтения в схеме Finance:

CREATE ROLE [Supervisor];

GRANT SELECT ON schema :: Finance to Supervisor;

Очень легко! Отметим отличие синтаксиса по сравнению с обычным GRANT. В этом случае мы должны указать ON SCHEMA :: вместо простого ввода имени объекта.

Давайте раскроем в общих чертах понятия владельца схемы и владения объектом. Как видно из примера, мы явно присвоили владение схемы Finance, которую мы создали, роли базы данных Finance, используя опцию AUTHORIZATION. Теперь эта роль имеет все привилегии на эту схему, чего нельзя отменить, пока мы не изменим владение этой схемой. Владение может быть предоставлено как роли базы данных, так и непосредственно пользователю. Очевидно, что роль явилась бы лучшим выбором. Наконец, есть команды CREATE, для которых недоступна опция AUTHORIZATION, например, CREATE TABLE. В этом случае владение наследуется непосредственно из схемы; фактически, мы можем использовать код ниже для проверки двух таблиц в схеме Finance:

SELECT type_desc, name, USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name

FROM sys.objects 

WHERE SCHEMA_NAME(schema_id) = 'Finance' and type='U'

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

SELECT type_desc, name,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name, SCHEMA_NAME(schema_id) as name_schema

FROM sys.objects 

WHERE SCHEMA_NAME(schema_id) <> 'Finance' and type='U'

Новые объекты, которые будут созданы в схеме Finance автоматически получат в качестве владельца владельца схемы. Поскольку мы предоставили привилегию SELECT роли Supervisor в этой схеме, эта привилегия будет унаследована на новые объекты, созданные или перенесенные в эту схему. Давайте рассмотрим пример переноса таблицы Customer в схему Finance:

alter schema Finance transfer customer;

Предположим, что мы проверяем привилегии, предоставленные на три таблицы в схеме Finance. Мы ничего не обнаружим, т.к. привилегии были даны неявно, предоставленные на всю схему:

select object_name(perm.major_id) as ObjectName, princ.name, perm.permission_name, perm.state_desc, perm.class_desc

from sys.database_permissions perm

inner join sys.database_principals princ

on perm.grantee_principal_id = princ.principal_id

where OBJECT_NAME(perm.major_id) in ('Customer','Invoice','InvoiceLine')

Замечание. Поскольку мы предоставили владение схемой Finance роли базы данных Finance, мы теперь больше не видим привилегии SELECT на таблицы Invoice и InvoiceLine, которую мы предоставили роли Finance в первой статье этой серии.

Нам нужно использовать другую стратегию для проверки, имеет ли роль Supervisor непрямую привилегию на эти таблицы.

Сначала создадим новый логин Super, и назначим ему роль Supervisor уровня базы данных:

USE [master]

GO

CREATE LOGIN [Super] WITH PASSWORD=N'TestSup', DEFAULT_DATABASE=[Chinook], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [Chinook]

GO

CREATE USER [Super] FOR LOGIN [Super]

GO

USE [Chinook]

GO

ALTER ROLE [Supervisor] ADD MEMBER [Super]

GO

Теперь мы можем выдать себя за пользователя Super и проверить все разрешения на таблицу Invoice с помощью функции fn_my_permissions:

EXECUTE AS USER = 'Super'

select *

from fn_my_permissions('Finance.Invoice','OBJECT')

Как ожидалось, пользователь Super имеет привилегию SELECT на таблицу Invoice и все ее столбцы.

Oracle

В Oracle реализован несколько отличный подход к назначению привилегий на всю схему. Если в двух словах, то вы не можете! Требуется небольшой обход типа явного назначения их всем объектам схемы с помощью некоторого динамического скрипта SQL.

Давайте проверим это на тех же самых примерах, которые мы использовали в разделе SQL Server. Сначала создадим новую схему:

CREATE USER "QFINANCE" IDENTIFIED BY "FINANCE"  

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

Сразу можно увидеть, что нам нужно дать другое имя пользователю/схеме, поскольку у нас уже имеется роль с именем Finance. В противном случае Oracle вернет ошибку ORA-01920: user name '***' conflicts with another user or role name (имя пользователя конфликтует с другим именем пользователя или роли). Помните, что понятие пользователя и схемы - это одно и то же в Oracle. Вернемся к разговору о владельце схемы.

Давайте переместим две таблицы, Invoice и InvoiceLine, в схему QFINANCE. И снова, в Oracle это нельзя сделать непосредственно. Как обход можно использовать 1) копирование таблиц, 2) использование impdp/expdp или 3) если вы имеете enterprise edition, используйте секционирование! Для данного случая, давайте использовать копирование:

create table qfinance.invoice as

select *

from chinook.invoice;

create table qfinance.invoiceline as

select *

from chinook.invoiceline;

Создайте ограничения, индексы и триггеры, присутствующие в таблицах, а затем удалите старые таблицы:

alter table QFINANCE.INVOICE add CONSTRAINT "PK_INVOICE" PRIMARY KEY ("INVOICEID");

GRANT REFERENCES ON CHINOOK.CUSTOMER to qfinance; 

alter TABLE QFINANCE.INVOICE ADD CONSTRAINT "FK_INVOICECUSTOMERID" FOREIGN KEY ("CUSTOMERID")

REFERENCES "CHINOOK"."CUSTOMER" ("CUSTOMERID") ENABLE;     

ALTER TABLE QFINANCE.INVOICELINE ADD CONSTRAINT "PK_INVOICELINE" PRIMARY KEY ("INVOICELINEID");

ALTER TABLE QFINANCE.INVOICELINE ADD CONSTRAINT "FK_INVOICELINEINVOICEID" FOREIGN KEY ("INVOICEID")

REFERENCES "QFINANCE"."INVOICE" ("INVOICEID") ENABLE; 

GRANT REFERENCES ON CHINOOK.track to qfinance; 

ALTER TABLE QFINANCE.INVOICELINE ADD CONSTRAINT "FK_INVOICELINETRACKID" FOREIGN KEY ("TRACKID")

REFERENCES "CHINOOK"."TRACK" ("TRACKID") ENABLE;

 

drop table chinook.invoiceline;

drop table chinook.invoice;

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

Если это делается в производственной среде, то процесс потребует некоторый запланированный простой.

Как вы могли уже догадаться, схема и пользователь QFINANCE является теперь владельцем двух таблиц в этой схеме, Invoice и InvoiceLine. Таким образом, она уже имеет все привилегии на них, и нам нет необходимости предоставлять привилегию на создание внешнего ключа в InvoiceLine, который ссылается на таблицу Invoice. Тут нет способа предоставить владение схемой роли Finance, как мы это делали в SQL Server, - здесь схема - это также и пользователь!

Теперь создадим роль Supervisor, и с помощью простого скрипта динамического SQL присвоим привилегию SELECT на две таблицы в схеме QFINANCE:

create role supervisor;

SELECT 'GRANT SELECT ON '||OWNER||'.'||table_name||' TO supervisor;'

 FROM dba_all_tables

 where owner='QFINANCE';

Мы сделали запрос к системному представлению dba_all_tables, чтобы получить все таблицы, владельцем которых является схема QFINANCE, и создали два скрипта для предоставления привилегии SELECT. Выполнив эти два скрипта, мы присвоили эту привилегию.

Подобно SQL Server, все объекты, созданные в схеме QFINANCE, принадлежат владельцу схемы - в нашем случае тому же пользователю QFINANCE. Но поскольку в Oracle невозможно присвоить привилегию на всю схему, нам придется предоставлять привлегию SELECT на каждый новый объект, если это необходимо роли супервизора!

Поэтому, если мы также переместим таблицу Customer в схему QFINANCE, нам придется предоставить разрешение Supervisor на SELECT из этой таблицы. Давайте попробуем:

create table QFINANCE.CUSTOMER AS

SELECT *

FROM CHINOOK.customer;

 

ALTER TABLE QFINANCE.CUSTOMER ADD CONSTRAINT "PK_CUSTOMER" PRIMARY KEY ("CUSTOMERID");

GRANT REFERENCES ON CHINOOK.EMPLOYEE TO QFINANCE;

ALTER TABLE QFINANCE.CUSTOMER ADD CONSTRAINT "FK_CUSTOMERSUPPORTREPID" FOREIGN KEY ("SUPPORTREPID")

     REFERENCES "CHINOOK"."EMPLOYEE" ("EMPLOYEEID") ENABLE;

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

with foreign_key as 

(SELECT c.constraint_name, c.r_constraint_name, c.table_name, C.OWNER

FROM dba_constraints c 

WHERE constraint_type='R') 

      

SELECT FOREIGN_KEY.OWNER,FOREIGN_KEY.table_name,foreign_key.constraint_name as "Constraint Name",

D.TABLE_NAME AS referenced_table_name,d.constraint_name as "Referenced PK"

FROM dba_constraints d inner join 

foreign_key

on d.constraint_name=foreign_key.r_constraint_name 

WHERE D.table_name='CUSTOMER' AND D.OWNER='CHINOOK';

Нам также необходимо изменить это ограничение внешнего ключа, чтобы все было правильно установлено:

ALTER TABLE QFINANCE.INVOICE DROP CONSTRAINT "FK_INVOICECUSTOMERID";

alter table qfinance.invoice add CONSTRAINT "FK_INVOICECUSTOMERID" FOREIGN KEY ("CUSTOMERID")

     REFERENCES "QFINANCE"."CUSTOMER" ("CUSTOMERID") ENABLE;

Наконец, мы можем удалить таблицу в схеме CHINOOK:

DROP TABLE CHINOOK.CUSTOMER;

Чтобы проверить разрешения, присвоенные роли Supervisor, мы можем использовать системное представление ROLE_TAB_PRIVS:

SELECT *

FROM ROLE_TAB_PRIVS

where role='SUPERVISOR';

Как ожидалось, роль Supervisor имеет привилегию SELECT только на таблицы Invoice и InvoiceLine. Нам нужно явно предоставить привилегию также на новую таблицу, добавленную в схему QFINANCE:

GRANT SELECT ON QFINANCE.CUSTOMER TO supervisor;

Опять проверим привилегии этой роли:



Мы также можем проверить привилегии на таблицы с помощью системного представления dba_tab_privs:

SELECT *

FROM dba_tab_privs

WHERE owner = 'QFINANCE' 

ORDER BY OWNER, TABLE_NAME;

PostgreSQL

Далее мы попробуем выполнить те же задачи в PostgreSQL. Здесь мы имеем подход, подобный SQL Server, но не совсем. Сначала создадим схему Finance с ролью Finance в качестве владельца:

CREATE SCHEMA "Finance"

    AUTHORIZATION "Finance";

Обратите внимание на синтаксис, как в SQL Server. Теперь давайте переместим сюда таблицы:

ALTER TABLE "Invoice" SET SCHEMA "Finance";

ALTER TABLE "InvoiceLine" SET SCHEMA "Finance";

Очень легко. Важно отметить, что в PostgreSQL имеется схема по умолчанию Public и, если не указать схему, то все объекты создаются в этой схеме (аналогично схеме dbo в SQL Server).

Для проверки наличия таблиц в указанной схеме выполним запрос к таблице с системной информацией pg_tables:

select schemaname, tablename, tableowner 

from pg_tables 

where schemaname='Finance';

И проверка в схеме Public:



Видим, что мы успешно переместили две таблицы из схемы public в Finance. Замечание: владельцем этих таблиц по-прежнему является пользователь postgres, поскольку эти таблицы был созданы этим суперпользователем, даже если он теперь находятся в схеме Finance, которой владеет роль Finance. Это поведение несколько отлично от SQL Server.

Теперь мы можем создать роль Supervisor и предоставить ей привилегию SELECT на всю схему Finance:

create role "Supervisor" WITH

   NOLOGIN

   NOSUPERUSER

   NOCREATEDB

   NOCREATEROLE

   INHERIT

   NOREPLICATION

   CONNECTION LIMIT -1;

Существует два способа присвоить привилегию роли/пользователю на всю схему. Первый способ - использовать команду GRANT (как в SQL Server):

grant usage on schema "Finance" to "Supervisor";

grant select on all tables in schema "Finance" to "Supervisor";

Первой строкой предоставляется роли Supervisor возможность подключения к схеме Finance, а второй - дается привилегия SELECT на все таблицы в этой схеме.

Теперь давайте проверим разрешения на таблицы, используя представление table_privileges в information_schema:

select grantee, table_schema, table_name, privilege_type

from information_schema.table_privileges

where table_schema='Finance' and grantee='Supervisor';

Можно увидеть, что роль Supervisor имеет привилегию SELECT на обе таблицы в схеме Finance. Все правильно. Мы смогли предоставить привлегию на все таблицы в схеме.

Однако есть одно НО. Любая новая таблица, созданная в этой схеме не будет наследовать привилегии, которые мы предоставили. Если вам требуется такое поведение, мы должны использовать второй способ:

alter default privileges in schema "Finance" grant select on tables to "Supervisor";

Давайте переместим таблицу Customer в схему Finance, и снова проверим привилегии:

ALTER TABLE "Customer" SET SCHEMA "Finance";

select grantee, table_schema, table_name, privilege_type

from information_schema.table_privileges

where table_schema='Finance' and grantee='Supervisor';

Постойте, это в точности то же самое, что и прежнее перемещение таблицы Customer. Мы не имеем ращрешения на нее как Supervisor. Давайте проверим это с точки зрения роли, обратившись к представлению role_table_grants в information_schema:

select grantee, table_schema, table_name, privilege_typefrom information_schema.role_table_grants

where table_schema='Finance' and grantee='Supervisor';

Хм...мы получаем тот же результат: роль Supervisor не имеет привилегий на таблицу Customer. Это происходит потому, что мы переместили таблицу, а не создали ее, как отмечено в официальной документации:

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

Поэтому, если мы только перемещаем таблицу, она не наследует привилегию. Однако, если мы создадим новую таблицу и проверим ее:

create table "Finance".test_customer (like "Finance"."Customer");

select grantee, table_schema, table_name, privilege_type

from information_schema.table_privileges

where table_schema='Finance' and grantee='Supervisor';

Новая таблица наследует привилегию SELECT для роли Supervisor. Замечание: мы использовали опцию LIKE для создания новой таблицы той же структуры, что и таблица Customer.

Еще одно соображение относительно владельца схем и таблиц. В этом частном случае я создавал все под суперпользователем postgres; владельцем всех созданных таблиц является postgres. Вы можете изметить это, выполнив вначале запрос к системной таблице pg_tables. Владелец имеет все разрешения на эти таблицы. В PostgreSQL мы можем изменить владельца таблиц. Например, мы меняем владельца всех таблиц в схеме Finance, делая владельцем роль Finance с помощью динамического SQL:

select 'alter table '||'"'||schemaname||'"'||'.'||'"'||tablename||'"'||' owner to "Finance";' 

from pg_tables 

where schemaname='Finance';

Выполнив запросы alter, мы можем изменить владельца:



Теперь, проверив pg_tables, видим нового владельца:

Заключение

В этой статье мы рассмотрели различные способы присвоения привилегий на схемы, различные системные представления, которые помогают управлять привилегиями, а также понятие владельца в SQL Server, Oracle и PostgreSQL.

Ссылки по теме

1. Безопасность SQL Server - модель безопасности с использованием определяемых пользователем ролей


2. Как решить, нужно ли вам секционировать таблицы


3. Основы PostgreSQL: роли и привилегии


4. Схемы в PostgreSQL. Изучаем PostgreSQL вместе с Grant Fritchey


5. Основы PostgreSQL: владение объектами и привилегии по умолчанию