MIFAR UI College of Medicine
Site Map Site Map Documentation Physiologic Imaging
Previous ~ Up ~ Next

Views, Rules and Triggers on Object Tables

Views are the mechanisim by which most SQL databases handle sub-table-level permissions. A view is a predefined selection of data from one or more tables. A view is created by defining a selection rule. This rule may select data in any manner that a SELECT statement may do so. What makes views valuable in the MIFAR framework is that user permissions may be assigned to a view that are different than the permissions on the underlying table. A user that normally would not have read permsissions on a table may be granted read permissions on a view. In this way the user gets permissions to view only a subset of the table's data. Since most database engines do not support row level permissions, views are the only game in town for fine grained access control.

Setting up a view can allow a user to see data even if they have no select prividges on the underlying table, however; they do nothing to grant insert, update and delete rights. The companion to database views are a database rules. Rules allow incoming SQL queries to be rewritten before they are executed. The creator of a rule defines what operations will invoke the rule. These can be INSERT, UPDATE and DELETE. In addition rules can be attached to views or tables. To allow a user to be able to insert into a view, an insert rule can be created and associated with the view. When an incomming insert query is detected by the database engine on a view the rule will rewrite the query and instead insert into the underlying table.


View-Rule Creation Functions

Writting and testing database rules is a labor intensive process. If every table in MIFAR required hand written rules each time a new set of permissions was needed, the system would be useless. To assist in setting permissions the following framework level stored proceedures automatically generate views and rules on a data table:
  • mfnv_on_base_obj - Create a view and rules for a base class table.
  • mfnv_on_grp_1key - Create a view and rules for a table where one column acts as the unique row identifer.
  • mfnv_on_grp_2key - Create a view and rules for a table where two columns taken together act as the unique row identifier.
  • mfnv_on_vgroup_id - Wrapper for mfnv_on_grp_1key where the selector column is named vgroup and the identifier column is named id.

Two Levels of interaction

Once views have been constructed on a table there are two ways to operate on the table data. One way is to use the table name in SQL queries. The other is to access the data via the views and rules. Access permissions on MIFAR object tables adhear to the following conventions.
  1. Normal users are not granted direct access to object tables.

  2. Normal users perform select, insert, update and delete operations on views.

  3. The table owner, which is usually the database superuser, can bypass the views and rules and perform direct operations on the object tables.
The net result is to create two levels of interaction with the data. Table Level interactions which can perform any legal SQL operation on the data and View Level interactions which are filtered through the database rules.

Triggers



Previous ~ Up ~ Next
If you have any problems with the page contact Chris Piker at the
Division of Physiologic Imaging.