In times, when informational resources of companies integrated with internet (not only intranets), question about control for data access to database becomes especially important. Problem consists of three parts:
It is important to understand, that user enters not separate records into database, but whole graphs. Graphs of different users (different roles) are mostly unrelated, so all records of database (from all tables) are decomposed into little interacting classes. We shall name them as departments. It is possible only to specify (by operator CREATE DEPARTMENT), that new department name is entered, or is withdrawn (by operator DROP DEPARTMENT) [3].
CREATE DEPARTMENT dp;It is reasonable to give identical rights on all records of one department to user (role), and to specify department name in records themselves in field of special datatype LEGAL [4].
CREATE TABLE a (... , a5 LEGAL, ...); CREATE TABLE b (... , b7 LEGAL, ...); INSERT INTO a VALUES (... , dp, ...); INSERT INTO b VALUES (... , dp, ...); BESTOW select ON dp FOR usr; BESTOW update ON dp FOR rolename;We shall withdraw rights on department by following operator
VANISH select ON dp FOR usr; VANISH update ON dp FOR rolename;If user has no rights to some operation with record, than record does not exist for this user: for example, absence of right SELECT means invisibility of record, absence of right UPDATE - that record is "read only". This field itself is accessible for updating only for whom, who has right PUBLISH on department, to which record belong.
BESTOW publish ON dp FOR rolename; VANISH publish ON dp FOR rolename;If record has no field of datatype LEGAL, or it is equal to NULL, than user has all rights on this record. We shall specify for user convenient (and for administrating database too) at creating user, by which department he marks all inserted or updated records by default. We shall specify this department-by-default in parameter TRACE.
CREATE USER usr1 IDENTIFIED BY pwd1 TRACE dp1;
[1] By delegating rights on column
[3] It is impossible to change departments
(ALTER DEPARTMENT), because they have no parameters
[4] Only one field of datatype LEGAL can exist in record
P.S.
Article is guide to implement ideas of p.148, 187-190 of pdf-document.
Dima Turin, dmitryturin@yandex.ru