List of articles   Choose language


Whole control for access


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:

  1. hiding against selecting some records and protection against updating some records
  2. hiding against selecting some records and protection against updating some columns
  3. journalizing updates
Second task is already solved by industry [1], solution of third task is suggested [2]. Thus only first question remains open.

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

[2] Time of beginning and end of existence are stored in each record: beginning is assigned equal to moment of creation, end - to infinity at inserting record; end becomes equal to moment of deleting at removal; record is copied at updating, new copy is updated, end of existence of old copy and beginning of existence of new copy are assigned equal to moment of coping, end of existence of new copy is assigned equal infinity. For whole control, it is necessary only to append and automatically fill two hidden fields - identifier of user, created record, and identifier of user, removed record

[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



List of articles   Choose language


Используются технологии uCoz