Total Knowledge Projects Jobs Clientele Contact

UU Database

Personal tools
From Total Knowledge
(Difference between revisions)
Jump to: navigation, search
(ACL implementation)
Current revision (13:10, 3 April 2011) (view source)
(HowTo: Find out if person is currently a student of a class)
 
(21 intermediate revisions not shown.)
Line 1: Line 1:
 +
<keywords content="database,design,structure,schema,table,field,university,school,course,study,online,on-line,postgre,postgreSQL,SQL"/>
 +
== '''Database structure requirements''' ==
== '''Database structure requirements''' ==
Line 52: Line 54:
</OL>
</OL>
-
 
+
===Implementation===
-
== ACL implementation ==
+
* All access to database tables and views is revoked from DB role under which application connects
-
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:
+
* All DML access is granted to role under which stored procedures are created
-
<table border=1 bgcolor="#A0FFA0">
+
* Application user is granted EXECUTE access on all public stored procedures
-
<th>access name</th><th>access value</th>
+
* All public stored procedures are marked as ''SECURITY DEFINER''
-
<tr><td> No access </td><td> 0 or NULL </td></tr>
+
* All internal stored procedures are '''not''' marked as ''SECURITY DEFINER''
-
<tr><td> View </td><td> 1 </td></tr>
+
* Public stored procedures call is_authorized function with relevant access parameters in order to verify user's access to requested object
-
<tr><td> Modify </td><td> 2 </td></tr>
+
* Application will perform ''login'' procedure before any transaction and ''logout'' procedure after. These procedures take care of setting up environment for is_authorized call.
-
<tr><td> Delete </td><td> 1 </td></tr>
+
-
<tr><td> Grant Access </td><td> 4 </td></tr>
+
-
</table>
+
-
<p>
+
-
Here is the example of the topic_list_acl table:
+
-
<p>
+
-
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
+
-
</p>
+
-
</p>
+
== Database objects ==
== Database objects ==
Line 86: Line 75:
The financial relations are described with:
The financial relations are described with:
Company, company types, subscription, subscription types, rate, payments.
Company, company types, subscription, subscription types, rate, payments.
 +
 +
== User Managed Objects (UMOs) ==
 +
 +
Some of the database objects can be managed by users. That includes (but isn't limited to) creating new UMOs, connecting the existing UMOs to other UMOs, modifying UMOs, and access permissions management.
 +
 +
All the different types of the UMO are still stored in the database exactly the same way. This allows to implement the wide range of the operations (stored procedures) covering common (for all UMO types) actions such as creating a new UMO, creating a UMO version, etc. Every UMO has a single (common for all versions of this UMO) 'base' record. Base record owns the UMO version records (one per UMO version), and every version record owns 'content' records (one per content language). This structure allows creating multiple versions per UMO, and every version may provide content in one or more languages.
 +
 +
One of the most important properties of UMO is versioning. That means, that modification of existing published UMO is always done by creating a new version of the UMO, modifying that new version to acceptable state, and publishing it. The old versions of the UMO are kept in the database for future references (if needed). The number of the UMO versions isn't limited. The latest available version is considered as default version, however if there is a need to use an older version of the UMO - it may be used (if permissions are enough, of course).
 +
 +
Access permissions is another important access of the UMOs. UU database defines multiple access permission types that may be assigned to groups. Every UMO connects to one or more groups like 'Students', 'Teachers', 'Administrators' that have different set of permissions. Users can be included into groups to get access permissions to UMOs connected to groups. Users included into 'Administrators' group of the UMO can include or exclude users in/from UMO user groups.
 +
The global 'Public' group automatically includes all users. So, if UMO grants any permissions to the 'Public' group then all the users receive these permissions. The most popular permission to grant this way is 'View'.
 +
 +
All UMOs are equal, but some UMOs are more equal than others :) Even though all the UMOs are stored in the database the same way, some of them may have extra actions not typical for others. These actions are implemented as stored UMO-type specific procedures.
 +
 +
== Security implementation ==
 +
Most modern databases allow to define primitive access permissions (SELECT,UPDATE,DELETE) on the table level, or even on the level of table column. The general limitation of this approach is that it doesn't offer any access restrictions on the row level. One of the ways to implement different restrictions for the same user to different records within the same table is to implement all the access to the database tables throw stored procedures while revoking all the access rights from the users on the restricted tables. Stored procedures, in that case, must be created with SECURITY DEFINER attribute. Such stored procedures can be called by regular users but use their creator's permissions when access the tables.
 +
 +
For the regular users (not database administrators), the user information is stored in the person_list table, including known user information along with login name and encrypted password. A user id (a primary key from person_list table, or pl_id) is used as primary user identification. However, any permissions to any object are granted to groups of users and not to particular user. The permissions to an object fro a user can be determined by combining group permissions (from the groups user belongs to) for the object.
 +
 +
Every stored procedure that supports access restrictions must check if the current user has enough permissions to perform an operation. The user credentials are obtained during logon (session start). Logon to the system creates temporary table that stores current user information. Without that table, most of the stored procedures would generate exception requiring to login first.
 +
 +
== ACL implementation ==
 +
ACL is implemented on object type and object id basis. The ACL table contains references to the group id, the object type id, and 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:
 +
 +
<table border=1 bgcolor="#A0FFA0">
 +
<th>access name</th><th>access value</th><th>Description</th><th> Comments </th>
 +
<tr><td> No access </td><td> 0 or NULL </td><td> User may not have any access to the object </td></tr>
 +
<tr><td> View </td><td> 1 </td><td>User may browse and view the content of the object</td></tr>
 +
<tr><td> Study </td><td> 2 </td> <td> User (student) may submit his answer or solution of the problem </td> <td> Always set for Open Server </td>  </tr>
 +
<tr><td> Modify </td><td> 4 </td> <td> User may modify the object but not the object access </td> </tr>
 +
<tr><td> Delete </td><td> 8 </td> <td> User may delete the object </td> </tr>
 +
<tr><td> Add Version </td><td> 16 </td> <td> User may create his own version of the object </td> <td> Always set for Open Server </td>  </tr>
 +
<tr><td> Link </td> <td> 32 </td> <td> User may link to this object in his objects </td> <td> Always set for Open Server </td>  </tr>
 +
<tr><td> Translate </td><td> 64 </td> <td> User may create his own translated version of the object </td> <td> Always set for Open Server </td> </tr>
 +
<tr><td> Teach </td><td> 128 </td> <td> User may teach this version of the object </td> <td> </td> </tr>
 +
<tr><td> Group administration </td><td> 256 </td> <td> User may include or exclude other users in the object groups </td> <tr><td> Catalog administration </td><td> 512 </td> <td> User may edit catalogs </td> <td> </td> </tr>
 +
</table>
 +
 +
A particular person has a permission to object that combine permissions of all groups of th object, the person is a member of.
 +
 +
ACL entries are maintained with the stored procedures acl_grant and acl_revoke. Upon execution, stored procedures check if the user has "Modify" right on the object, and then modifies the access for the group. If the required ACL entry doesn't exist it is created.
 +
 +
ACL entries perform slightly differently for the open server and proprietary server configurations. For the open server, the special right bits (Study..Link) are always set, allowing unlimited Study..Link use.
 +
 +
== HowTo ==
 +
This section lists correct functions to use in order to manipulate specific aspects of the database. Please check here before adding any new functions. Make sure to add info here as you add new procedures.
 +
(This section is still incomplete, so make sure to search sources as well).
 +
 +
* How to find list of classes given person is student of
 +
  <code>list_person_classes(person_id, 'STUDY');</code>
 +
* How to find list of classes given person is teaching
 +
  <code>list_person_classes(person_id, 'TEACH');</code>
 +
* How to find list of courses given person is studying
 +
  <code>list_person_umos(person_id, 'COURSE', 'STUDY', 0);</code>
 +
  (last parameter is limit of courses to return)
 +
* How to find out if person is CURRENTLY a student of given class.
 +
  <code>is_student_of(person_id, class_id)

Current revision


Contents

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.

Implementation

  • All access to database tables and views is revoked from DB role under which application connects
  • All DML access is granted to role under which stored procedures are created
  • Application user is granted EXECUTE access on all public stored procedures
  • All public stored procedures are marked as SECURITY DEFINER
  • All internal stored procedures are not marked as SECURITY DEFINER
  • Public stored procedures call is_authorized function with relevant access parameters in order to verify user's access to requested object
  • Application will perform login procedure before any transaction and logout procedure after. These procedures take care of setting up environment for is_authorized call.

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.

User Managed Objects (UMOs)

Some of the database objects can be managed by users. That includes (but isn't limited to) creating new UMOs, connecting the existing UMOs to other UMOs, modifying UMOs, and access permissions management.

All the different types of the UMO are still stored in the database exactly the same way. This allows to implement the wide range of the operations (stored procedures) covering common (for all UMO types) actions such as creating a new UMO, creating a UMO version, etc. Every UMO has a single (common for all versions of this UMO) 'base' record. Base record owns the UMO version records (one per UMO version), and every version record owns 'content' records (one per content language). This structure allows creating multiple versions per UMO, and every version may provide content in one or more languages.

One of the most important properties of UMO is versioning. That means, that modification of existing published UMO is always done by creating a new version of the UMO, modifying that new version to acceptable state, and publishing it. The old versions of the UMO are kept in the database for future references (if needed). The number of the UMO versions isn't limited. The latest available version is considered as default version, however if there is a need to use an older version of the UMO - it may be used (if permissions are enough, of course).

Access permissions is another important access of the UMOs. UU database defines multiple access permission types that may be assigned to groups. Every UMO connects to one or more groups like 'Students', 'Teachers', 'Administrators' that have different set of permissions. Users can be included into groups to get access permissions to UMOs connected to groups. Users included into 'Administrators' group of the UMO can include or exclude users in/from UMO user groups. The global 'Public' group automatically includes all users. So, if UMO grants any permissions to the 'Public' group then all the users receive these permissions. The most popular permission to grant this way is 'View'.

All UMOs are equal, but some UMOs are more equal than others :) Even though all the UMOs are stored in the database the same way, some of them may have extra actions not typical for others. These actions are implemented as stored UMO-type specific procedures.

Security implementation

Most modern databases allow to define primitive access permissions (SELECT,UPDATE,DELETE) on the table level, or even on the level of table column. The general limitation of this approach is that it doesn't offer any access restrictions on the row level. One of the ways to implement different restrictions for the same user to different records within the same table is to implement all the access to the database tables throw stored procedures while revoking all the access rights from the users on the restricted tables. Stored procedures, in that case, must be created with SECURITY DEFINER attribute. Such stored procedures can be called by regular users but use their creator's permissions when access the tables.

For the regular users (not database administrators), the user information is stored in the person_list table, including known user information along with login name and encrypted password. A user id (a primary key from person_list table, or pl_id) is used as primary user identification. However, any permissions to any object are granted to groups of users and not to particular user. The permissions to an object fro a user can be determined by combining group permissions (from the groups user belongs to) for the object.

Every stored procedure that supports access restrictions must check if the current user has enough permissions to perform an operation. The user credentials are obtained during logon (session start). Logon to the system creates temporary table that stores current user information. Without that table, most of the stored procedures would generate exception requiring to login first.

ACL implementation

ACL is implemented on object type and object id basis. The ACL table contains references to the group id, the object type id, and 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 Comments
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
Study 2 User (student) may submit his answer or solution of the problem Always set for Open Server
Modify 4 User may modify the object but not the object access
Delete 8 User may delete the object
Add Version 16 User may create his own version of the object Always set for Open Server
Link 32 User may link to this object in his objects Always set for Open Server
Translate 64 User may create his own translated version of the object Always set for Open Server
Teach 128 User may teach this version of the object
Group administration 256 User may include or exclude other users in the object groups
Catalog administration 512 User may edit catalogs

A particular person has a permission to object that combine permissions of all groups of th object, the person is a member of.

ACL entries are maintained with the stored procedures acl_grant and acl_revoke. Upon execution, stored procedures check if the user has "Modify" right on the object, and then modifies the access for the group. If the required ACL entry doesn't exist it is created.

ACL entries perform slightly differently for the open server and proprietary server configurations. For the open server, the special right bits (Study..Link) are always set, allowing unlimited Study..Link use.

HowTo

This section lists correct functions to use in order to manipulate specific aspects of the database. Please check here before adding any new functions. Make sure to add info here as you add new procedures. (This section is still incomplete, so make sure to search sources as well).

  • How to find list of classes given person is student of
 list_person_classes(person_id, 'STUDY');
  • How to find list of classes given person is teaching
 list_person_classes(person_id, 'TEACH');
  • How to find list of courses given person is studying
 list_person_umos(person_id, 'COURSE', 'STUDY', 0);
 (last parameter is limit of courses to return)
  • How to find out if person is CURRENTLY a student of given class.
 is_student_of(person_id, class_id)