Tech: Semantic DB for Low Level LIMS Operations

(Ref Id: 1378252220)

Ok, I transferred the above semantic net to a PNG using DOT (GraphViz). Click on it to see the larger-size diagram. Here's the explanation.

Things start with the User that requests something from the Controller. The Controller in turn either makes a call to the Model or sends a usage violation to the Log (note, in cases where the eventual UI is going to be a web-based view this will need to capture things like client IP address, etc.). Now, the Model in turn calls the DBAccessor. Why not just call the DBAccessor directly? Because this design first has to assess what DB you want to access (Oracle, PostgreSQL, etc.) and there may be different DBAccessors depending upon the particular database type based on a configuration setting. Now, the DBAccessor does its SQL thing which isn't very interesting except for the usage of a DB Monitor. The DB Monitor checks the Group settings for each database access request and verifies that the original User is a member of those groups. If not, the records not accessible to the User making the request should not be made available.

Now things get tricky because lots of LIMS models go right on ahead and modify the SQL to reduce the return dataset according to the presence (or lack thereof) of the user in that group list. The problem with this model is that you cannot easily track access violations. Let's say User A is a member of RAWMATERIALS and User B is in the group FINISHEDPRODUCTS. If User A tries to access a record that belongs to FINISHEDPRODUCTS the default behavior should be to log the access request error. Proper logging should include what was being accessed and when the access violation occurred and by which user account. This can result in quite a bit of access violation logging so some kind of summarizing setting would have to be made available. For instance, one method would be to only enter a single line no matter how many records were being violated per transaction. If you simply add a WHERE clause to restrict the data in a SQL statement then the dataset you get back from the database will not normally tell you that an access violation occurred unless you ran the SQL twice and compared the results somehow.

The proposed solution here is to accommodate both styles of operation based on a configuration item. In some environments the default, higher-security method will be preferable with all of the access violation logging and in lower-security environments simply modifying the SQL to restrict the return dataset will work and perhaps perform better.

None of this covers multi-table transactions, of course. This is basically for CRUD, create/read/update/delete for single table operations. The more comprehensive stuff will come later.

Back to the semantic diagram. Once the Model is done with the DB Accessor it pushes the results to the View which is accessible by the User. All this presumes that a User is configured with Groups and Roles before any of this takes place. Roles are data that specifies what operations the User is allowed to perform. Obviously these settings would include yes/no configuration data to allow/deny CRUD operations against named tables. So User A could have read and update privileges for Table X but no create and deletion privileges for instance. All this gets complicated when we look toward archiving data. There the permissions change to solid denials for everything -- no CUD, only Read and only for data that matches group settings.

Oh yes, I have a growing bias toward moving away from database-centric models. Much more of the access permissions should involve the object model that represents the 'database' rather than literal descriptions of tables/fields in the database. Essentially this means that there probably shouldn't be a 1:1 relationship between access permissions and physical tables in the background. We'll see if that flies under testing. The eventual goal is to give you the opportunity to change field names on a whim without having to fear breaking code.

Next step is to do some sequence diagrams to analyze my CRC (class responsibility collaboration) cards.

Go Back

Citation: Tech: Semantic DB for Low Level LIMS Operations. (2013). Retrieved Sun Apr 30 05:01:22 2017, from;iid=readMore;go=1378252220