Problem of control for accesss of user to data consist of three parts:
It's important to understand, that user enters not separate records into database, but enters whole graphs. Graphs of different users (different roles) are cross little, so all records of database (from all tables) are decomposed naturally into little interacting classes. We shall name them as departments. It is possible only to specify (by operator CREATE DEPERTMENT), 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 (to role), and to specify department name in records themselves in fields 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;If user has no rights to some operation with record, than record does not exist for this user (he does not see it in this operation). If record has no field of datatype LEGAL, than user has all rights on this record. This field itself is accessible for updating on the same basis, as whole 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 [5]. We shall specify this department by default in parameter TRACE.
CREATE USER u1
IDENTIFIED BY p1
TRACE d2;
All proposals are public domain.
[1] By delegating rights on column
[3] It is impossible to change departments (ALTER DEPARTMENT), because they have no any parameters
[4] Only one field of datatype LEGAL can exist in record
[5] And also deleting in line with [2]