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 ==
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