Authorization schema for BioSQL databases

The BioSQL relational database model provides a set of tables for storing biological sequences and information related to them. The model covers many common use cases when representing sequence information, and serves as a reusable component that can be plugged into many projects.

For larger web-based projects, users often would like a permissions system that:

• Allows storage of data available only to the user, for collecting experimental data that is in progress.
• Shares data with other colleagues within defined groups.
• Defines the ability to edit data.
• Stores user specific preferences and defaults.

Here I present a simple relational database model for MySQL that ties in with BioSQL and provides this functionality. The full schema can be downloaded from github and is described in more detail below.

The authorization framework is based on a three tiered system used in the Turbogears web framework:

• Users — these are the base logged in users. The auth_user table defines them and includes user_names, passwords, and e-mail information.
• Groups — each user can belong to one or more groups. These groups will often represent real life groups, like research laboratories or collaborative groups.
• Permissions — each group has one or more permissions associated with it. This assigns a group one or more things it can do or access.

In BioSQL, we can associate permissions with many items using generic key value pairs. These are implemented as qualifier values, and many different items have this association mechanism including biological entries (bioentry), features, and database references (dbxref). The BioSQL schema description goes into more detail about the representation, which is very flexible.

As an example of using this framework, suppose you add several new sequence you want to be available to only users in your research group. You would do the following:

1. Create a new permission (auth_permission table).
2. Associate the permission with your group (auth_group_permission table).
3. Assign the new permission id to a qualifer value named permissions on the bioentries of interest (bioentry_qualifer_value table, in BioSQL).

The web interface should then be designed to check for permissions and display only those available to the current users. This can be done very flexibly according to your design preferences; in the most transparent case displays would only include those items for which permissions are available. Users without permissions could proceed unaware of these items they cannot access.

The final desired item of functionality is storing user specific information. This helps a user set defaults for a web interface, save queries or other complex information, and generally achieve a more pleasant browsing experience. This information is stored using the same key-value pair mechanism described above, in the auth_user_qualifier_value table. This should be very familiar to BioSQL developers, and flexibly allows us to store any kind of information of interest.

Hopefully this authentication system is useful to others implementing interfaces on top of BioSQL databases. In subsequent posts, I will provide client and server code which helps manage user authorization within this system.