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

 

Для создания роли используется команда

CREATE ROLE name [ [ WITH ] option [... ] ]

где option может быть:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CREATEUSER | NOCREATEUSER

| INHERIT | NOINHERIT

| LOGIN | NOLOGIN

| CONNECTION LIMIT connlimit

| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'

| VALID UNTIL 'timestamp'

| IN ROLE rolename [,...]

| IN GROUP rolename [,...]

| ROLE rolename [,...]

| ADMIN rolename [,...]

| USER rolename [,...]

| SYSID uid

 

Для разрешения ролям пользоваться информацией используется команда GRANT.

 

GRANT {{SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER}[,...] | ALL [PRIVILEGES]}

ON {[TABLE] table_name [,...] | ALL TABLES IN SCHEMA имя_схемы [,...]}

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {{SELECT | INSERT | UPDATE | REFERENCES} (имя_колонки [,...])[,...] | ALL [PRIVILEGES] (имя_колонки [,...])}

ON [TABLE] table_name [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {{USAGE | SELECT | UPDATE}[,...] | ALL [PRIVILEGES]}

ON {SEQUENCE имя_последовательности [,...] | ALL SEQUENCES IN SCHEMA имя_схемы [,...]}

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {{CREATE | CONNECT | TEMPORARY | TEMP} [,...] | ALL [PRIVILEGES]}

ON DATABASE имя_бд [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {USAGE | ALL [PRIVILEGES]}

ON DOMAIN имя_домена [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {USAGE | ALL [PRIVILEGES]}

ON FOREIGN DATA WRAPPER имя_обёртки_сторонних_данных [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {USAGE | ALL [PRIVILEGES]}

ON FOREIGN SERVER имя_сервера [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {EXECUTE | ALL [PRIVILEGES]}

ON {FUNCTION имя_функции ([[режим_аргумента] [имя_аргумента] тип_аргумента [,...]]) [,...] | ALL FUNCTIONS IN SCHEMA имя_схемы [,...]}

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {USAGE | ALL [PRIVILEGES]}

ON LANGUAGE имя_языка [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {{SELECT | UPDATE} [,...] | ALL [PRIVILEGES]}

ON LARGE OBJECT oid_БО [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {{CREATE | USAGE} [,...] | ALL [PRIVILEGES]}

ON SCHEMA имя_схемы [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {CREATE | ALL [PRIVILEGES]}

ON TABLESPACE табл_пространство [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

GRANT {USAGE | ALL [PRIVILEGES]}

ON TYPE type_name [,...]

TO role_specification [,...] [WITH GRANT OPTION]

 

Здесь role_specification:

[GROUP] имя_роли

| PUBLIC

| CURRENT_USER

| SESSION_USER

 

GRANT имя_роли [,...] TO имя_роли [,...] [WITH ADMIN OPTION]

 

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

 

REVOKE [GRANT OPTION FOR] {{SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER} [,...] | ALL [PRIVILEGES]}

ON {[TABLE] table_name [,...] | ALL TABLES IN SCHEMA имя_схемы [,...]}

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {{SELECT | INSERT | UPDATE | REFERENCES} (имя_колонки [,...]) [,...] | ALL [PRIVILEGES] (имя_колонки [,...])}

ON [TABLE] table_name [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {{USAGE | SELECT | UPDATE} [,...] | ALL [PRIVILEGES]}

ON {SEQUENCE имя_последовательности [,...] | ALL SEQUENCES IN SCHEMA имя_схемы [,...]}

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {{CREATE | CONNECT | TEMPORARY | TEMP} [,...] | ALL [PRIVILEGES]}

ON DATABASE имя_бд [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}

ON DOMAIN имя_домена [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}

ON FOREIGN DATA WRAPPER имя_обёртки_сторонних_данных [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}

ON FOREIGN SERVER имя_сервера [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {EXECUTE | ALL [PRIVILEGES]}

ON {FUNCTION имя_функции ([[режим_аргумента] [имя_аргумента] тип_аргумента [,...]]) [,...] | ALL FUNCTIONS IN SCHEMA имя_схемы [,...]}

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}

ON LANGUAGE имя_языка [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {{SELECT | UPDATE} [,...] | ALL [PRIVILEGES]}

ON LARGE OBJECT oid_БО [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {{CREATE | USAGE} [,...] | ALL [PRIVILEGES]}

ON SCHEMA имя_схемы [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {CREATE | ALL [PRIVILEGES]}

ON TABLESPACE табл_пространство [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}

ON TYPE type_name [,...]

FROM {[GROUP] имя_роли | PUBLIC} [,...] [CASCADE | RESTRICT]

 

REVOKE [ADMIN OPTION FOR] имя_роли [,...] FROM имя_роли [,...] [CASCADE | RESTRICT]

 

CREATE ROLE user1 LOGIN PASSWORD '1111';

ALTER ROLE user1 PASSWORD '1234'

GRANT ALL ON "Book" TO user1;

 

CREATE ROLE user2 LOGIN PASSWORD '4321';

REVOKE INSERT ON "Book" FROM user2;

GRANT UPDATE ON "Book" FROM user2;

REVOKE DELETE ON "Book" FROM user2;

 

Задания для самостоятельной работы (БД BookShop)

 

7. Создайте таблицу Сотрудники в базе данных BookSHOP. Таблица должна содержать поля: личный_номер (первичный ключ), фамилия_имя, размер_комиссионных (значение по умолчанию - 3%), должность («директор», «консультант», «младший продавец», «старший продавец», «закупщик»), дата_приема_на_работу (по умолчанию - текущая дата).

 

CREATE TABLE public."Sotrudnik"

(lic_nomer serial NOT NULL,

fam_im character(30) NOT NULL DEFAULT '',

komission integer DEFAULT 3,

doljnost character(20) NOT NULL DEFAULT 'директор',

data_priem date NOT NULL DEFAULT now(),

CONSTRAINT pk_sotrudnik PRIMARY KEY (lic_nomer),

CONSTRAINT ch_doljnost CHECK (doljnost IN ('директор', 'консультант', 'младший продавец', 'старший продавец', 'закупщик')))

 

8. В таблицу Заказы внесите следующие изменения: а) добавьте столбец Оформил_заказ с ограничениями NOT NULL и внешнего ключа со ссылкой на столбец Личный_номер таблицы Сотрудники; б) добавьте столбец Количество с ограничением NOT NULL и значением по умолчанию - 1.

 

а) ALTER TABLE zakaz

ADD COLUMN lic_sotrudnik integer;

 

ALTER TABLE zakaz ADD CONSTRAINT fk_oformil FOREIGN KEY (lic_sotrudnik)

REFERENCES "Sotrudnik" (lic_nomer)

ON UPDATE CASCADE ON DELETE SET NULL

 

б) ALTER TABLE zakaz ADD COLUMN kolichestvo integer NOT NULL DEFAULT 1

 

9. Создайте копию таблицы Поставки, переопределив первичный ключ как Номер_договора типа serial и упорядочив все строки в новой таблице по полям Код_книги и Код_поставщика.

 

CREATE TABLE postavka2 AS

SELECT num_postavka, num_book, num_postavchik, cnt, data

FROM postavka

ORDER BY num_book, num_postavchik;

ALTER TABLE postavka2 ADD COLUMN num_dogovor serial;

ALTER TABLE postavka2 ADD CONSTRAINT pk_oformil PRIMARY KEY (num_dogovor)

 

10. Измените таблицу Сотрудники так, чтобы размер комиссионных по умолчанию был равен 5% и для этого поля не допускались NULL-значения.

 

ALTER TABLE "Sotrudnik" ALTER COLUMN komission SET DEFAULT 5;

ALTER TABLE "Sotrudnik" ALTER COLUMN komission SET NOT NULL