Total Knowledge Projects Jobs Clientele Contact

UU Database

Personal tools
From Total Knowledge
(Difference between revisions)
Jump to: navigation, search
(Security implementation)
Current revision (13:10, 3 April 2011) (view source)
(HowTo: Find out if person is currently a student of a class)
 
</OL>
</OL>
 +
===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 ==
== Database objects ==
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.
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.
+
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.
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 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.
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