Только для ваших глаз

© 2003 Алексей Морозов

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

В качестве примера для статьи взят фрагмент метаданных системы автоматизации коммерческой службы радиостанции, работа над которой служит кладезем полезных трюков. Чтобы было понятнее, что к чему, немного коснусь предметной области: в рамках системы могут существовать несколько коммерческих служб (таблица CMCSERVICES), содержащих свой штат менеджеров (таблица MANAGERS), некоторые из которых могут выступать в качестве операторов системы, и обслуживающих несколько радиостанций (таблицы CHANNELS и SERVEDCHANNELS). Все остальные таблицы фрагмента системы служат для организации системы разграничения доступа, а также для системы регистрации изменений в данных. Права (GRANTS) непосредственно на таблицу даны только для ADDRESSES, доступ ко всем остальным таблицам возможен только через соответствующие VIEW.

Сразу необходимо упомянуть о положенной "ложке дегтя": из-за ошибки в InterBase 5.*, 6.* и 6.* Open Source предложенное решение работает на Yaffil и FireBird (не было возможности проверить на IB 7). При добавлении записи в редактируемые VIEW старые версии InterBase почему-то пытаются вставить запись в таблицу дважды, и возникает резонный KEY VIOLATION на первичном автогенерируемом ключе. Почему до сих пор не исправят - загадка!

Database Model
Модель данных.

Определимся с возможными разрешениями доступа: DENY - полный запрет, ANY - читать или модифицировать любую запись, All - читать или модифицировать запись, относящуюся к своей коммерческой службе, Self - читать или модифицировать только свои записи. Так как мы будем использовать битовые поля, договоримся, что DENY = 8, ANY = 4, All = 2 и Self = 1. Как видим, для хранения этих флагов достаточно 4 бит, поэтому использование типа SMALLINT позволит упаковать три группы флагов изменения (создание, модификация и удаление) в одно поле. Предостерегу от упаковки всех групп флагов в одно поле, так как при установке старшего бита поле принимает отрицательное значение и его "ручная" интерпретация становится затруднительной.

CREATE TABLE ENTITIES (
 ENT_ID "PRIMARYKEY",
 ENT_NAME "NAME" NOT NULL,
 ENT_RELATION "IBINTERNAL" NOT NULL,
 ENT_DESCRIPTION "DESCRIPTION",
 ENT_READACCESSMASK SMALLINT DEFAULT 8 NOT NULL, /* 8 = DENY, 4 = ANY, 2 = All, 1 = Self, 0 = None */
 ENT_WRITEACCESSMASK SMALLINT DEFAULT 2184 NOT NULL, /* Create << 8 | Modify << 4 | Delete (0x080808) */
CONSTRAINT PK_ENTITIES PRIMARY KEY (ENT_ID),
CONSTRAINT UQ1_ENTITIES UNIQUE (ENT_NAME),
CONSTRAINT UQ2_ENTITIES UNIQUE (ENT_RELATION));

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

CREATE TABLE ACCESSGROUPS (
 ACC_ID "PRIMARYKEY",
 ACC_CMCSERVICE INTEGER NOT NULL,
 ACC_NAME "NAME" NOT NULL,
 ACC_DESCRIPTION "DESCRIPTION",
CONSTRAINT PK_ACCESSGROUPS PRIMARY KEY (ACC_ID),
CONSTRAINT UQ_ACCESSGROUPS UNIQUE (ACC_CMCSERVICE, ACC_NAME),
CONSTRAINT FK_ACCESSGROUPS FOREIGN KEY (ACC_CMCSERVICE) REFERENCES CMCSERVICES (CMC_ID) ON UPDATE CASCADE ON DELETE CASCADE);

Таблица ACCESSGROUPS описывает группы доступа, которые могут содержать менеджеров соответствующей коммерческой службы и имеют разрешения доступа ко всем сущностям безопасности из таблицы ENTITIES. Поле ACC_CMCSERVICE ассоциирует каждую группу со своей коммерческой службой, поэтому возможно определение разрешения All для групп доступа в случае, если текущий оператор системы также является членом соответствующей коммерческой службы (разрешение ANY действительно всегда, когда имеется принципиальная возможность чтения или изменения данных).

CREATE TABLE GROUPMEMBERS (
 GRM_ACCESSGROUP INTEGER NOT NULL,
 GRM_MANAGER INTEGER NOT NULL,
CONSTRAINT PK_GROUPMEMBERS PRIMARY KEY (GRM_ACCESSGROUP, GRM_MANAGER),
CONSTRAINT FK1_GROUPMEMBERS FOREIGN KEY (GRM_ACCESSGROUP) REFERENCES ACCESSGROUPS (ACC_ID) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FK2_GROUPMEMBERS FOREIGN KEY (GRM_MANAGER) REFERENCES MANAGERS (MAN_ID) ON UPDATE CASCADE ON DELETE CASCADE);

Таблица GROUPMEMBERS определяет членство конкретных менеджеров в группах доступа, что, в свою очередь, определяет эффективные разрешения доступа менеджеров. При добавлении или удалении записей происходит пересчет эффективных (действующих) разрешений доступа для соответствующего менеджера. Так как мы применяем битовые флаги, пересчет прост до смешного: логическое ИЛИ (OR) разрешений групп, в которые входит менеджер и будет эффективным разрешением доступа этого менеджера. Замечу также, что установленное для любой группы разрешение DENY приведет к тому, что все члены такой группы будут иметь разрешение DENY для выбранной сущности. Чем не NTFS? :-) Сущность безопасности для доступа к таблице GROUPMEMBERS одна и та же, что и у ACCESSGROUPS, хотя вы вправе завести отдельную. Через связь с таблицей ACCESSGROUPS можно получить идентификатор коммерческой службы, а поле GRM_MANAGER содержит идентификатор менеджера, что позволяет определить разрешения ANY, All и Self для доступа к данным этой таблицы.

CREATE TABLE GROUPPERMISSIONS (
 GPM_ACCESSGROUP INTEGER NOT NULL,
 GPM_ENTITY INTEGER NOT NULL,
 GPM_READACCESS SMALLINT DEFAULT 0 NOT NULL, /* 8 = DENY, 4 = ANY, 2 = All, 1 = Self, 0 = None */
 GPM_WRITEACCESS SMALLINT DEFAULT 0 NOT NULL, /* Create << 8 | Modify << 4 | Delete */
CONSTRAINT PK_GROUPPERMISSIONS PRIMARY KEY (GPM_ACCESSGROUP, GPM_ENTITY),
CONSTRAINT FK1_GROUPPERMISSIONS FOREIGN KEY (GPM_ACCESSGROUP) REFERENCES ACCESSGROUPS (ACC_ID) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FK2_GROUPPERMISSIONS FOREIGN KEY (GPM_ENTITY) REFERENCES ENTITIES (ENT_ID) ON UPDATE CASCADE ON DELETE CASCADE);

Таблица GROUPPERMISSIONS содержит разрешения для групп доступа из таблицы ACCESSGROUPS к сущностям безопасности из таблицы ENTITIES. Непосредственно за разрешения отвечают поля GPM_READACCESS и GPM_WRITEACCESS типа SMALLINT. В поле GPM_READACCESS используются только 4 младших бита, поле же GPM_WRITEACCESS содержит в себе сдвинутые влево на 8 позиций биты разрешения создания, сдвинутые влево на 4 позиции биты разрешения модификации и биты разрешения удаления данных в младших позициях. Для работы с битовыми полями применим UDF библиотеку, исходные коды которой можно взять здесь. Через связь с таблицей ACCESSGROUPS можно получить идентификатор коммерческой службы, а через связь с таблицей GROUPMEMBERS становится возможным определить идентификатор менеджера - итоговые возможные разрешения ANY, All и Self. Добавлением и удалением записей в таблице GROUPPERMISSIONS ведает система безопасности, поэтому можно разрешить только модификацию данных (крайне желательно не позволять модификацию для разрешения доступа Self, ибо это позволит кому угодно изменить свои разрешения доступа, сделав всю модель безопасности бессильной).

CREATE TABLE PERMISSIONS (
 PER_MANAGER INTEGER NOT NULL,
 PER_ENTITY INTEGER NOT NULL,
 PER_READACCESS SMALLINT DEFAULT 0 NOT NULL, /* 4 = ANY, 2 = All, 1 = Self, 0 = None */
 PER_WRITEACCESS SMALLINT DEFAULT 0 NOT NULL, /* Create << 8 | Modify << 4 | Delete */
CONSTRAINT PK_PERMISSIONS PRIMARY KEY (PER_MANAGER, PER_ENTITY),
CONSTRAINT FK1_PERMISSIONS FOREIGN KEY (PER_MANAGER) REFERENCES MANAGERS (MAN_ID) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FK2_PERMISSIONS FOREIGN KEY (PER_ENTITY) REFERENCES ENTITIES (ENT_ID) ON UPDATE CASCADE ON DELETE CASCADE);

Таблица PERMISSIONS содержит эффективные разрешения доступа менеджеров системы, которые могут являться операторами, т.е. взаимодействовать с системой интерактивно. Чтобы упростить интерпретацию данных этой таблицы, будем полагать, что флаг DENY приводит к обнулению соответствующих бит доступа. Я не рекомендую прямое редактирование данных этой таблицы, так как измененные значения могут быть переписаны при изменениях в таблицах GROUPPERMISSIONS или GROUPMEMBERS автоматически. Через связь с таблицей MANAGERS можно получить идентификатор коммерческой службы, а поле PER_MANAGER содержит идентификатор менеджера, к которому относится запись об эффективных разрешениях, поэтому возможно определить разрешения на доступ ANY, All и Self. Добавлением и удалением записей в таблице PERMISSIONS ведает система безопасности, поэтому можно разрешить только модификацию данных (по поводу модификации данных с разрешением Self см. комментарий к предыдущему абзацу).

Как же все это работает? Осталось описать последнюю участвующую в системе безопасности таблицу и перейти к рассмотрению VIEW, которые обеспечивают работоспособность всей системы.

CREATE TABLE MANAGERS (
 MAN_ID "PRIMARYKEY",
 MAN_CMCSERVICE INTEGER NOT NULL,
 MAN_LASTNAME "LASTNAME" NOT NULL,
 MAN_FIRSTNAME "FIRSTNAME" NOT NULL,
 MAN_MIDDLENAME "MIDDLENAME" DEFAULT '' NOT NULL,
 MAN_FULLNAME COMPUTED (MAN_LASTNAME || ' ' || MAN_FIRSTNAME || ' ' || MAN_MIDDLENAME),
 MAN_SEX "SEX",
 MAN_DESCRIPTION "DESCRIPTION",
 MAN_ADDRESS INTEGER,
 MAN_PERCENT "PERCENT" DEFAULT 0,
 MAN_ISLOCAL "BOOLEAN" DEFAULT 1 NOT NULL, /* 1 = Operator */
 MAN_ISDISABLED "BOOLEAN" DEFAULT 0 NOT NULL, /* 1 = Disabled, 0 = Active */
 MAN_ISADMIN "BOOLEAN" DEFAULT 0 NOT NULL, /* 1 = Administrator */
 MAN_LOGIN "IBINTERNAL" DEFAULT '' NOT NULL,
CONSTRAINT PK_MANAGERS PRIMARY KEY (MAN_ID),
CONSTRAINT UQ_MANAGERS UNIQUE (MAN_CMCSERVICE, MAN_LASTNAME, MAN_FIRSTNAME, MAN_MIDDLENAME),
CONSTRAINT FK1_MANAGERS FOREIGN KEY (MAN_CMCSERVICE) REFERENCES CMCSERVICES (CMC_ID) ON UPDATE CASCADE,
CONSTRAINT FK2_MANAGERS FOREIGN KEY (MAN_ADDRESS) REFERENCES ADDRESSES (ADR_ID) ON UPDATE CASCADE);

Через поле MAN_CMCSERVICE каждый менеджер связан со своей коммерческой службой, поле MAN_ISLOCAL служит признаком того, может ли данный менеджер быть оператором системы (разрешения доступа к данным имеют только операторы), поле MAN_ISDISABLED позволяет временно запретить оператору вход в систему, поле MAN_ISADMIN позволяет получить максимально возможные разрешения на сущности безопасности (но даже это не позволит удалить "неудаляемое" или создать "несоздаваемое"), а поле MAN_LOGIN должно содержать существующее имя пользователя сервера БД (для менеджеров, не являющихся операторами, значение этого поля пусто, но каждый оператор должен иметь уникальный "логин").

Каждый оператор входит в систему под своим именем, поэтому задача определения текущего менеджера решается просто:

CREATE VIEW ACTIVEMANAGER AS
SELECT MANAGERS.*
FROM MANAGERS
WHERE (MAN_ISLOCAL = 1) AND (MAN_ISDISABLED = 0) AND (MAN_LOGIN = USER);

В дальнейшем мы будем активно использовать это VIEW, поэтому разрешим всем пользователям БД доступ по чтению к нему:

GRANT SELECT ON MANAGERS TO VIEW ACTIVEMANAGER;
GRANT SELECT ON ACTIVEMANAGER TO PUBLIC;

При попытке доступа в систему с "логином", не зарегистрированным за каким-либо менеджером или если доступ для данного менеджера запрещен, вышеприведенный VIEW выдаст пустой результат. В клиентской среде сразу после соединения с БД рекомендуется сделать запрос к VIEW ACTIVEMANAGER, и, в случае пустого результата, завершить исполнение клиентской среды. С другой стороны, любой VIEW доступа к данным будет в этом случае также выдавать пустой результат, что является вполне приемлемым поведением при непосредственном доступе к БД минуя ограничения клиентской среды.

CREATE VIEW ACTIVEPERMISSIONS (MANAGER, CMCSERVICE, ISADMIN, RELATION, READACCESS, WRITEACCESS) AS
SELECT MAN_ID, MAN_CMCSERVICE, MAN_ISADMIN, ENT_RELATION, PER_READACCESS, PER_WRITEACCESS
FROM ACTIVEMANAGER JOIN PERMISSIONS ON PER_MANAGER = MAN_ID
 JOIN ENTITIES ON ENT_ID = PER_ENTITY
WHERE (MAN_ISADMIN = 1) OR (PER_READACCESS > 0);

Следующий VIEW позволяет получить список разрешений текущего оператора на те сущности безопасности, к которым возможен принципиальный доступ по чтению. Поле MANAGER содержит идентификатор текущего менеджера, CMCSERVICE - коммерческая служба текущего менеджера, ISADMIN - признак администратора, RELATION - внутреннее имя сущности безопасности (как правило совпадает с именем защищаемой таблицы, но может использоваться несколькими связанными таблицами с идентичными ограничениями доступа), READACCESS и WRITEACCESS - соответственно битовые флаги разрешений на чтение и изменение данных. Полученных записей будет не более, чем определено сущностей безопасности (или не будет совсем, если менеджер не имеет прав на чтение ни одной сущности или менеджер не зарегистрирован как действующий оператор системы), но это и есть ядро системы разграничения доступа.

Осталось создать VIEW для каждой защищаемой таблицы, используя объединение с VIEW ACTIVEPERMISSIONS для принятия решения по каждой записи таблицы, например:

CREATE VIEW ALLOWEDMANAGERS AS
SELECT MANAGERS.*
FROM MANAGERS JOIN ACTIVEPERMISSIONS ON RELATION = 'MANAGERS'
WHERE (ISADMIN = 1) OR (READACCESS = 4) OR
 ((READACCESS = 2) AND (MAN_CMCSERVICE = CMCSERVICE)) OR
 ((READACCESS = 1) AND (MAN_ID = MANAGER));

С помощью VIEW ALLOWEDMANAGERS можно получить список менеджеров, причем администратор или операторы с разрешением на чтение ANY увидят всех менеджеров, операторы с разрешением All увидят менеджеров только своей коммерческой службы, а операторы с разрешением Self увидят только себя.

GRANT SELECT, INSERT, UPDATE, DELETE ON MANAGERS TO VIEW ALLOWEDMANAGERS;
GRANT ALL ON ALLOWEDMANAGERS TO PUBLIC;

Отсутствие прав (GRANTS) непосредственно на таблицу MANAGERS не позволит прочитать или изменить данные в ней даже минуя клиентскую среду, а с помощью триггеров для изменения VIEW мы получаем возможность контролируемой модификации данных. Аналогичным образом определяем VIEW и GRANTS для оставшихся таблиц (см. исходные тексты). Замечу лишь, что для некоторых таблиц приходится применять объединение с другими таблицами для получения информации о коммерческой службе и (или) менеджере:

CREATE VIEW ALLOWEDACCESSGROUPS AS
SELECT DISTINCT ACCESSGROUPS.*
FROM ACCESSGROUPS LEFT JOIN GROUPMEMBERS ON GRM_ACCESSGROUP = ACC_ID
 JOIN ACTIVEPERMISSIONS ON RELATION = 'ACCESSGROUPS'
WHERE (ISADMIN = 1) OR (READACCESS = 4) OR
 ((READACCESS = 2) AND (ACC_CMCSERVICE = CMCSERVICE)) OR
 ((READACCESS = 1) AND (GRM_MANAGER = MANAGER));

CREATE VIEW ALLOWEDGROUPMEMBERS AS
SELECT GROUPMEMBERS.*
FROM GROUPMEMBERS JOIN ACCESSGROUPS ON ACC_ID = GRM_ACCESSGROUP
 JOIN ACTIVEPERMISSIONS ON RELATION = 'ACCESSGROUPS'
WHERE (ISADMIN = 1) OR (READACCESS = 4) OR
 ((READACCESS = 2) AND (ACC_CMCSERVICE = CMCSERVICE)) OR
 ((READACCESS = 1) AND (GRM_MANAGER = MANAGER));

CREATE VIEW ALLOWEDGROUPPERMISSIONS AS
SELECT DISTINCT GROUPPERMISSIONS.*
FROM GROUPPERMISSIONS JOIN ACCESSGROUPS ON ACC_ID = GPM_ACCESSGROUP
 LEFT JOIN GROUPMEMBERS ON GRM_ACCESSGROUP = GPM_ACCESSGROUP
 JOIN ACTIVEPERMISSIONS ON RELATION = 'PERMISSIONS'
WHERE (ISADMIN = 1) OR (READACCESS = 4) OR
 ((READACCESS = 2) AND (ACC_CMCSERVICE = CMCSERVICE)) OR
 ((READACCESS = 1) AND (GRM_MANAGER = MANAGER));

Иногда (как в случае с VIEW ALLOWEDACCESSGROUPS и ALLOWEDGROUPPERMISSIONS) конструкция становится громоздкой, да и DISTINCT влияет на производительность, но для данной задачи все приведенные таблицы должны содержать довольно незначительное количество записей. Подробнее рассмотрим VIEW ALLOWEDACCESSGROUPS. Почему LEFT JOIN с таблицей GROUPMEMBERS? Дело в том, что если группа доступа не содержит членов, то она не будет видна даже для администраторов и операторов с высокими разрешениями доступа, если использовать просто JOIN. Аналогичная ситуация и с VIEW ALLOWEDGROUPPERMISSIONS. В этих случаях и нужен DISTINCT для устранения дубликатов записей. Для повышения скорости вычислений логических выражений можно (при условии работы под Yaffil или FireBird) использовать возможности этих серверов по быстрой обработке логических выражений:
ibfix.exe -database база_данных -bool incomplete, хотя сам текст примера от этого не зависит и полностью находится в рамках ANSI SQL92 DIALECT 3.

Чтение данных мы взяли под контроль, теперь перейдем к изменениям данных. Для каждого VIEW напишем три триггера (BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE), которые будут проверять разрешения текущего менеджера на запрошенную операцию и осуществлять ее. Для упрощения кода напишем несколько вспомогательных процедур:

CREATE PROCEDURE GETADMINCREATEACCESS (RELATION VARCHAR(31))
RETURNS (ACCESS SMALLINT)
AS
BEGIN
 SELECT bit_and(bit_shr(ENT_WRITEACCESSMASK, 8), 7) /* DENY exclude */
 FROM ENTITIES
 WHERE ENT_RELATION = :RELATION
 INTO :ACCESS;

 IF (ACCESS >= 4) THEN
 ACCESS = 4;
 ELSE
 IF (ACCESS >= 2) THEN
 ACCESS = 2;

 SUSPEND;
END ^

CREATE PROCEDURE GETCREATEACCESS (RELATION VARCHAR(31))
RETURNS (ACCESS SMALLINT)
AS
DECLARE VARIABLE ISADMIN SMALLINT;
BEGIN
 SELECT ISADMIN, bit_and(bit_shr(WRITEACCESS, 8), 15)
 FROM ACTIVEPERMISSIONS
 WHERE RELATION = :RELATION
 INTO :ISADMIN, :ACCESS;

 IF (ISADMIN = 1) THEN
 EXECUTE PROCEDURE GETADMINCREATEACCESS RELATION
 RETURNING_VALUES ACCESS;

 IF (ACCESS IS NULL) THEN
 ACCESS = 0;

 SUSPEND;
END ^

CREATE PROCEDURE CHECKCREATEABILITY (RELATION VARCHAR(31), MINACCESS SMALLINT)
AS
DECLARE VARIABLE ACCESS SMALLINT;
BEGIN
 EXECUTE PROCEDURE GETCREATEACCESS RELATION
 RETURNING_VALUES ACCESS;

 IF (ACCESS < MINACCESS) THEN
 EXCEPTION EXC_ACCESSDENIED;
END ^

CREATE PROCEDURE CHECKCREATEACCESS (RELATION VARCHAR(31), CMCSERVICE INTEGER, MANAGER INTEGER)
AS
DECLARE VARIABLE MAN INTEGER;
DECLARE VARIABLE CMC INTEGER;
DECLARE VARIABLE ACCESS SMALLINT;
BEGIN
 SELECT MAN_ID, MAN_CMCSERVICE
 FROM ACTIVEMANAGER
 INTO :MAN, :CMC;

 EXECUTE PROCEDURE GETCREATEACCESS RELATION
 RETURNING_VALUES ACCESS;

 IF (NOT ((ACCESS = 4) OR ((ACCESS = 2) AND (CMCSERVICE = CMC)) OR ((ACCESS = 1) AND (MANAGER = MAN)))) THEN
 EXCEPTION EXC_ACCESSDENIED;
END ^

Аналогичные процедуры напишем для определения разрешений на чтение, модификацию и удаление данных (см. исходные тексты). В случае обнаружения конфликта доступа вызываем исключительную ситуацию, которая "откатит" (Rollback) транзакцию и отменит все изменения. Процедуры CHECK*ABILITY будем вызывать для проверки принципиальной возможности произведения изменений с минимально достаточными разрешениями на изменения, а процедуры CHECK*ACCESS проверят переданные ей параметры для принятия решения о допустимости выполнения изменений.

CREATE TRIGGER ALLOWEDGROUPPERMISSIONS_BI0 FOR ALLOWEDGROUPPERMISSIONS BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE ACC_CMCSERVICE INTEGER;
BEGIN
 SELECT ACC_CMCSERVICE
 FROM ACCESSGROUPS
 WHERE ACC_ID = NEW.GPM_ACCESSGROUP
 INTO :ACC_CMCSERVICE;

 EXECUTE PROCEDURE CHECKCREATEACCESS 'PERMISSIONS', ACC_CMCSERVICE, -1;

 INSERT INTO GROUPPERMISSIONS
 VALUES (NEW.GPM_ACCESSGROUP, NEW.GPM_ENTITY, NEW.GPM_READACCESS, NEW.GPM_WRITEACCESS);
END ^

CREATE TRIGGER ALLOWEDGROUPPERMISSIONS_BU0 FOR ALLOWEDGROUPPERMISSIONS BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE ACC_CMCSERVICE INTEGER;
BEGIN
 SELECT ACC_CMCSERVICE
 FROM ACCESSGROUPS
 WHERE ACC_ID = OLD.GPM_ACCESSGROUP
 INTO :ACC_CMCSERVICE;

 EXECUTE PROCEDURE CHECKMODIFYACCESS 'PERMISSIONS', ACC_CMCSERVICE, -1;

 UPDATE GROUPPERMISSIONS
 SET GPM_ACCESSGROUP = NEW.GPM_ACCESSGROUP,
 GPM_ENTITY = NEW.GPM_ENTITY,
 GPM_READACCESS = NEW.GPM_READACCESS,
 GPM_WRITEACCESS = NEW.GPM_WRITEACCESS
 WHERE (GPM_ACCESSGROUP = OLD.GPM_ACCESSGROUP) AND (GPM_ENTITY = OLD.GPM_ENTITY);
END ^

CREATE TRIGGER ALLOWEDGROUPPERMISSIONS_BD0 FOR ALLOWEDGROUPPERMISSIONS BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE ACC_CMCSERVICE INTEGER;
BEGIN
 SELECT ACC_CMCSERVICE
 FROM ACCESSGROUPS
 WHERE ACC_ID = OLD.GPM_ACCESSGROUP
 INTO :ACC_CMCSERVICE;

 EXECUTE PROCEDURE CHECKDELETEACCESS 'PERMISSIONS', ACC_CMCSERVICE, -1;

 DELETE FROM GROUPPERMISSIONS
 WHERE (GPM_ACCESSGROUP = OLD.GPM_ACCESSGROUP) AND (GPM_ENTITY = OLD.GPM_ENTITY);
END ^

Аналогично вышеприведенному коду создаем триггеры для изменения остальных VIEW. Как я отмечал ранее, в код введены ограничения на изменение данных с разрешением доступа Self (параметр MANAGER процедуры проверки CHECK*ACCESS передается как -1), но вы можете эту "несправедливость" отменить. Также не забудьте пропустить в триггерах добавление или изменение вычисляемых полей (например, поле MAN_FULLNAME в таблице MANAGERS).

Тщательно и скрупулезно определяем права (GRANTS) на все таблицы, но только для VIEWS, PROCEDURES и TRIGGERS. Для VIEW, созданных нами для защиты таблиц, позволяем все права для всех пользователей (GRANT ALL ON ... TO PUBLIC). Также позволяем всем запускать некоторые общеупотребительные процедуры (см. исходные тексты), например, процедуры GET*ACCESS позволят клиентской среде определить, какие именно формы можно показывать пользователю и какие пункты меню изменения данных доступны ему для текущей записи.

Осталось инициализировать БД необходимыми для начала работы данными:

INSERT INTO CMCSERVICES (CMC_NAME)
VALUES ('HOST COMMERCE SERVICE');

INSERT INTO MANAGERS (MAN_CMCSERVICE, MAN_LASTNAME, MAN_FIRSTNAME, MAN_ISLOCAL,
 MAN_ISDISABLED, MAN_ISADMIN, MAN_LOGIN)
SELECT CMC_ID, 'Radiator', 'Administrator', 1, 0, 1, 'RA' FROM CMCSERVICES WHERE CMC_NAME = 'HOST COMMERCE SERVICE';

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Message Log', 'MESSAGELOG', 8 + 4, 2048 + 128 + 8 + 4); /* Read DENY/ANY, Create/Modify DENY, Delete DENY/ANY */

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Change Log', 'CHANGELOG', 8 + 4, 2048 + 128 + 8 + 4); /* Read DENY/ANY, Create/Modify DENY, Delete DENY/ANY */

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Commerce Services', 'CMCSERVICES', 8 + 4 + 2, 2048 + 1024 + 128 + 64 + 32 + 8 + 4); /* Read DENY/ANY/All, Create/Delete DENY/ANY, Modify DENY/ANY/All */

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Channels', 'CHANNELS', 8 + 4 + 2, 2048 + 1024 + 128 + 64 + 8 + 4); /* Read DENY/ANY/All, Create/Modify/Delete DENY/ANY */

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Managers', 'MANAGERS', 8 + 4 + 2 + 1, 2048 + 1024 + 512 + 128 + 64 + 32 + 8 + 4 + 2); /* Read DENY/ANY/All/Self, Create/Modify/Delete DENY/ANY/All */

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Security Entities', 'ENTITIES', 8 + 4, 2048 + 128 + 64 + 8); /* Read DENY/ANY, Create/Delete DENY, Modify DENY/ANY */

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Access Groups', 'ACCESSGROUPS', 8 + 4 + 2 + 1, 2048 + 1024 + 512 + 128 + 64 + 32 + 8 + 4 + 2); /* Read DENY/ANY/All/Self, Create/Modify/Delete DENY/ANY/All */

INSERT INTO ENTITIES (ENT_NAME, ENT_RELATION, ENT_READACCESSMASK, ENT_WRITEACCESSMASK)
VALUES ('Permissions', 'PERMISSIONS', 8 + 4 + 2 + 1, 2048 + 128 + 64 + 32 + 8); /* Read DENY/ANY/All/Self, Create/Delete DENY, Modify DENY/ANY/All */

Система подразумевает, что для сервера БД определен пользователь с "логином" RA (пароль на ваше усмотрение), поэтому не разочаруйте ее или измените исходный код. Не забудьте также исправить пароль для создании БД (первые строки исходного текста), а если он у вас почему-то совпадает, то никакая система разграничения доступа вам не поможет... ;-)

В клиентской среде вы должны использовать выборки из VIEW ALLOWED*. В связи со вставкой записей в VIEW появляется одна нехорошая проблема: DEFAULT значения полей применятся только после вставки записи в таблицу, а CHECK срабатывает еще в теле триггера VIEW (т.е. поля DEFAULT ... NOT NULL вызовут исключение еще до вставки записи). Для борьбы с этим явлением в клиентской среде нужно опрашивать генераторы перед добавлением записи в VIEW и можно использовать следующую процедуру после Append, но до Post (подразумевается работа с InterBase через IBX):

uses
 SysUtils, DB, IBDatabase, IBCustomDataSet, IBQuery, IBSQL;

procedure InitDataSet(DataSet: TDataSet);
 function GetDataType(Qry: TIBQuery): string;
 begin
 with Qry do
 case FieldByName('FIELD_TYPE').AsInteger of
 7: // SMALLINT
 if FieldByName('FIELD_SUB_TYPE').AsInteger = 0 then
 Result := 'SMALLINT'
 else
 Result := Format('NUMERIC(%d, %d)', [FieldByName('FIELD_PRECISION').AsInteger, -FieldByName('FIELD_SCALE').AsInteger]);
 8: // INTEGER
 if FieldByName('FIELD_SUB_TYPE').AsInteger = 0 then
 Result := 'INTEGER'
 else
 Result := Format('NUMERIC(%d, %d)', [FieldByName('FIELD_PRECISION').AsInteger, -FieldByName('FIELD_SCALE').AsInteger]);
 10: // FLOAT
 Result := 'FLOAT';
 11, 27: // D_FLOAT, DOUBLE
 Result := 'DOUBLE PRECISION';
 12: // DATE
 Result := 'DATE';
 13: // TIME
 Result := 'TIME';
 14: // CHAR
 Result := Format('CHAR(%d)', [FieldByName('FIELD_LENGTH').AsInteger]);
 16: // INT64
 if FieldByName('FIELD_SUB_TYPE').AsInteger = 0 then
 Result := 'BIGINT'
 else
 Result := Format('NUMERIC(%d, %d)', [FieldByName('FIELD_PRECISION').AsInteger, -FieldByName('FIELD_SCALE').AsInteger]);
 35: // TIMESTAMP
 Result := 'TIMESTAMP';
 37: // VARCHAR
 Result := Format('VARCHAR(%d)', [FieldByName('FIELD_LENGTH').AsInteger]);
 else
 Result := '';
 end;
 end;
var
 DefaultsQuery: TIBQuery;
 GenericSQL: TIBSQL;
 I: Integer;
 Default: string;
begin
 with DataSet do
 if Active then
 begin
 DefaultsQuery := TIBQuery.Create(nil);
 try
 DefaultsQuery.Database := Database; // <-- Your database here
 DefaultsQuery.SQL.Text := 'SELECT ''"'' || rtrim(R.RDB$RELATION_NAME) || ''"."'' || rtrim(RF1.RDB$FIELD_NAME) || ''"'' AS FIELD_ORIGIN, ' +
 'F.RDB$FIELD_LENGTH AS FIELD_LENGTH, F.RDB$FIELD_PRECISION AS FIELD_PRECISION, ' +
 'F.RDB$FIELD_SCALE AS FIELD_SCALE, F.RDB$FIELD_TYPE AS FIELD_TYPE, ' +
 'F.RDB$FIELD_SUB_TYPE AS FIELD_SUB_TYPE, F.RDB$DEFAULT_SOURCE AS DOMAIN_DEFAULT, ' +
 'RF1.RDB$DEFAULT_SOURCE AS FIELD_DEFAULT, RF2.RDB$DEFAULT_SOURCE AS BASE_FIELD_DEFAULT ' +
 'FROM RDB$RELATIONS R JOIN RDB$RELATION_FIELDS RF1 ON RF1.RDB$RELATION_NAME = R.RDB$RELATION_NAME ' +
 'JOIN RDB$FIELDS F ON F.RDB$FIELD_NAME = RF1.RDB$FIELD_SOURCE ' +
 'LEFT JOIN RDB$VIEW_RELATIONS VR ON (VR.RDB$VIEW_NAME = RF1.RDB$RELATION_NAME) AND (VR.RDB$VIEW_CONTEXT = RF1.RDB$VIEW_CONTEXT) ' +
 'LEFT JOIN RDB$RELATION_FIELDS RF2 ON (RF2.RDB$RELATION_NAME = VR.RDB$RELATION_NAME) AND (RF2.RDB$FIELD_NAME = RF1.RDB$BASE_FIELD) ' +
 'WHERE (R.RDB$SYSTEM_FLAG = 0) AND ((F.RDB$DEFAULT_VALUE IS NOT NULL) OR (RF1.RDB$DEFAULT_VALUE IS NOT NULL) OR (RF2.RDB$DEFAULT_VALUE IS NOT NULL))';
 DefaultsQuery.Open;
 if not DefaultsQuery.Eof then
 begin
 GenericSQL := TIBSQL.Create(nil);
 try
 GenericSQL.Database := Database; // <-- Your database here
 for I := 0 to FieldCount - 1 do
 if Fields[I].IsNull then
 begin
 if DefaultsQuery.Locate('FIELD_ORIGIN', Fields[I].Origin, [loCaseInsensitive]) then
 begin
 if not DefaultsQuery.FieldByName('BASE_FIELD_DEFAULT').IsNull then
 Default := DefaultsQuery.FieldByName('BASE_FIELD_DEFAULT').AsString
 else
 if not DefaultsQuery.FieldByName('FIELD_DEFAULT').IsNull then
 Default := DefaultsQuery.FieldByName('FIELD_DEFAULT').AsString
 else
 Default := DefaultsQuery.FieldByName('DOMAIN_DEFAULT').AsString;
 Default := Copy(Default, Pos('DEFAULT', AnsiUpperCase(Default)) + 8, Length(Default));
 GenericSQL.SQL.Text := 'SELECT CAST(' + Default + ' AS ' + GetDataType(DefaultsQuery) + ') AS DEFAULT_VALUE ' +
 'FROM RDB$DATABASE';
 GenericSQL.ExecQuery;
 Fields[I].Value := GenericSQL.FieldByName('DEFAULT_VALUE').Value;
 GenericSQL.Close;
 end;
 end;
 finally
 GenericSQL.Free;
 end;
 end;
 finally
 DefaultsQuery.Free;
 end;
 end;
end;

Процедура использует тот факт, что поле Origin класса TField содержит значение вида "таблица"."поле". Делаем выборку из системных таблиц со значениями по умолчанию для полей VIEW, полей таблиц-источников и доменов. Определяем тип данных поля и формируем SQL запрос с приведением значения по умолчанию к нужному типу данных. Запрос производим к системной таблице RDB$DATABASE, которая примечательна тем, что всегда содержит одну запись, поэтому результатом запроса гарантированно будет одна запись. Зачем такие сложности, ведь можно интерпретировать текстовое представление значения по умолчанию самим? Но ведь в качестве DEFAULT может быть как литерал, так и NULL или USER, а литерал для полей типа DATE, TIME и TIMESTAMP может принимать значение 'NOW', 'TODAY' и т.п. А в новых версиях может еще что-нибудь измениться... Лучше уж заставить сам сервер получить умалчиваемое значение по собственным правилам.

PS: силы мои на исходе, поэтому позвольте в этой статье не описывать систему регистрации изменений данных (таблицы CHANGELOG и CHANGEDETAILS, а также процедуры NEWCHANGELOG, NEWCHANGEDETAIL и CLEARCHANGELOG). Пытливый читатель может из исходных текстов понять принцип работы этой системы (для автоматической генерации достаточно громоздких триггеров регистрации изменения данных можно использовать программу Logger). Искренне надеюсь, что материалы данной статьи могут быть кому-либо полезны. Будут интересные вопросы или предложения - пишите, постараюсь ответить.

PPS: огромную благодарность хочу выразить коллективу создателей клона InterBase Yaffil за оперативные исправления и замечательную производительность их сервера, а также коллективу разработчиков IB Expert, активно используемого автором этих строк.

Copyright © 2003 Морозов Алексей Викторович
MCSE, MCSD, Brainbench Delphi Professional, Brainbench C Professional

Специально для Delphi Plus



Новости за месяц

  • Август
    2022
  • Пн
  • Вт
  • Ср
  • Чт
  • Пт
  • Сб
  • Вс
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31