Total Knowledge Projects Jobs Clientele Contact

UU Database

Personal tools
From Total Knowledge
Revision as of 02:25, 30 September 2006 by Alexeyp (Talk | contribs)
Jump to: navigation, search


Database structure requirements

Naming Conventions

  1. All tables should have names of two or three words, separated with underscore. The combination of first letters of these words should be unique in the whole database. In this document, this combination, followed with underscore ('_'), is called table name prefix. Example: teachers_list, course_group_list.
  2. Every field in every table should have table name prefix, followed by the field name. Example: teachers_list(tl_id), course_group_list(cgl_id).
  3. Every index name should start from "ndx_", followed with the table name prefix, and the field names, included in the index, separated with underscore.
  4. The primary key name should start from "pk_" followed by the table name prefix
  5. The foreign key name should start from "fk_" followed by the table name prefix and foreign table name prefix

Structure Requirements

  1. Tables in the database are placed in two domains: main and logs. The logs domain contains tables that contain access logs and statistics information. These tables may be emptied at any given moment without affecting the actual data.
  2. Every table should have a unique primary id (integer,auto-increment), logically not connected to the actual data.
  3. Any application that connects to the database, should have only read-access to the tables. All the modification operations, including insert, update, delete operations, should be performed in the stored procedures. The stored procedures should be implemented in a stored procedure language (most likely PL/SQL or equivalent), that doesn't allow to create a stored procedure without an integrity check. Integrity check should validate every object used in the stored procedure.
  4. Any transaction, performed by the application, should include only one stored procedure call.
  5. The important corner-stones tables should have triggers, logging any modifications in these tables as a set of user name, modification date and time, modification type. (If this is accepted, I'd provide the implementation details).
  6. Under no circumstances tables should use automatic cascade-delete on foreign keys. For any delete of detail records, stored procedure should verify the access rights for the given group of the records within the table (assuming that the user is granted access to the stored procedure removing records).

Security Requirements

  1. The security policy should provide several access roles. Besides the role, every user is granted the set of the (objects,projects,courses) he can access. Modification of access to the critical objects is only allowed if person has relation to the (objects,projects,courses). Every user may be granted several roles, if underlying database supports that.
  2. We have to implement at least the following database roles:
    1. DBO/DBA and backup (already implemented)
    2. Customer service role has access to read everything (may be but security tables), access to all maintenance and all security stored procs
    3. User role has access implemented through the stored procedures using ACL.
    4. Reports role has read access to everything but security tables, access to reporting stored procs

    Automated maintenance should use either "Customer Service", or "Backup" roles..

  3. The most interesting case is the access to shared objects. A shared object, included in the course, can be accessed by user using stored procedures. If the user needs to modify a shared object, the modification request is made through the stored procedure. Any details of the versioning are encapsulated within the modification stored procedure. However, the stored procedure must validate the access right of the user upon the modification attempt. Only the owner of the shared object may modify it without creating a new version of the object.

ACL implementation

ACL is implemented on per-table basis. For every table covered by ACL, there is a table with the same name appended with "_acl". The ACL tables have similar structure. They contain references to the user (person id) and to the object (object id), accompanied with the access field. An access field is an integer that contains a bit combination of the available access values:

access nameaccess valueDescription Commentary
No access 0 or NULL User may not have any access to the object
View 1 User may browse and view the content of the object
Modify 2 User may modify the object but not the object access
Delete 4 User may delete the object
Grant Access 8 User may grant or change access to the object to other user(s)
Study 16 User (student) may submit his answer or solution of the problem
Add Version 32 User may create his own version of the object
Add Translation 64 User may create his own translated version of the object Not yet clear - to be discussed
Reference 128 User may refer to this object in his objects

Here is the example of the topic_list_acl table:

create table topic_list_acl ( 
  tla_id serial int primary key, 
  tla_person int references person_list(pl_id), 
  tla_topic int references topic_list(tl_id),
  tla_access int default 0

ACL entries are maintained with the single stored procedure acl_set( table_name varchar(40), person int, access int ). Upon execution, stored procedure checks if the user has "Grant Access" right on the object, and if modifies the access for the person. If the required ACL entry doesn't exist it is created.

Database objects

The database objects represent entities of the project. The following entities are the most important for this project:

The study information is presented with: course, topic, unit, explanation, keyword, reference, problem, test, competition, work-flow, homework, excersize, game, faculty, objective, through-problem, dialogue-of-texts. Any of these can be shared objects.

The users of the system are described with: User, user levels, user groups, user group types.

The financial relations are described with: Company, company types, subscription, subscription types, rate, payments.