Neither Mimer SQL nor the SQL-99 standard implements access privileges on row data. The finest granularity level supported for privileges is columns. You can define user privileges on just about anything except single rows.
But sometimes, in the real world, you want to control access rights on individual rows for some users. And, although it is not directly supported, it is possible and relatively easy to use views to implement database-centric security protection on rows. This article explains how.
Let's say you manage a Mimer SQL database system which has one OS_USER ident for each operating system user on the system. The OS_USER ident feature enables users to log in to the database without supplying a database user name or a password. Mimer SQL relies fully on the operating system's login security system.
Information about each user, including telephone numbers, is stored in the database. One day, you get tired of updating this information yourself, and want to implement a way to allow database users to securely update the information themselves.
Let's assume that the user data is stored in the following table.
create table UINFO.USER_INFO (The first column contains the user ident name, the second a fully human-readable name, and the last column stores a phone number.
IDENT_NAME char(128) primary key,
The Wrong Approach
An easy (and wrong) approach to avoid having to update the information yourself would simply be to allow update access to the table for everyone and design an application which updates the information.
One big problem with this approach is that you would have to grant everyone full access to the table. Users tempted to try and push the limits (just think of secondary school students) might find it an irresistible challenge to try and crack the system by debugging the binary. Exposing the database logic in a Perl-script would make it even easier for a malicious user.
The Right Approach
To make this table secure while at the same time enabling users to update their phone number information, you can create specific Mimer SQL views.
In the example below, I implement functions to view and update user information. And, in order to do this, I create the following views:
create view UINFO.USER_INFO_ACCESS asUSER_INFO_MODIFY View
select IDENT_NAME,FULL_NAME,PHONE from USER_INFO;
create view UINFO.USER_INFO_MODIFY asThe USER_INFO_ACCESS view provides a view of all users on the system. In this particular case, I create a view of all the columns of the table, but I could have chosen to create a view of specific columns.
select IDENT_NAME,FULL_NAME,PHONE from USER_INFO
The USER_INFO_MODIFY view provides a view of the row where the ident name it stores is identical to the current user ident name. So, this view displays information relevant only to the current user account.
The final thing I do is set access privileges on the views. I want to restrict access to the table to the table creator only, and, at the same time, allow SELECT and UPDATE access to the views.
Creator-only access is the default setting in Mimer SQL, so the table is safe. But, as things stand, I am the only one who can access the views. To solve this problem, I create two user groups and assign privileges to them. Afterwards, I design tools to make sure new users are added to these groups.
The following statements create the groups:
create ident USER_INFO_ACCESS_GROUP as group;The following statements assign privileges to the groups:
create ident USER_INFO_MODIFY_GROUP as group;
grant select on UINFO.USER_INFO_ACCESS to USER_INFO_ACCESS_GROUP;The first statement gives all idents who are members of the USER_INFO_ACCESS_GROUP access to the UINFO.USER_INFO_ACCESS view.
grant select on UINFO.USER_INFO_MODIFY to USER_INFO_MODIFY_GROUP;
grant update(PHONE) on UINFO.USER_INFO_MODIFY to USER_INFO_MODIFY_GROUP;
The other statements give access to the UINFO.USER_INFO_MODIFY_GROUP group, and also update access to the PHONE column contained in USER_INFO_MODIFY.
If you already have user idents on the system, you have to issue these statements for each user ident in order to make them members of the groups.
grant member on UINFO.USER_INFO_ACCESS_GROUP to <ident>The script below takes care of new user idents. Of course, you could also design a script to add all your current users to the groups.
grant member on UINFO.USER_INFO_MODIFY_GROUP to <ident>
And, as they say "Bob's your uncle". I now have two views giving select and update access to all users while still keeping the table secure. In fact, users will never know the name of the table. For all they know, the query that builds the view could be a complex join or any other SQL construct.
To help you on your way, I have written some short Perl/DBI tools for the various tasks.
The first tool, new_ident.pl, can only be used by the administrator. It creates a new user ident and at the same time inserts a row in the UINFO.USER_INFO table.
It also makes sure that new idents belong to the user groups I created earlier.
The second tool, view_info.pl, displays the information in the UINFO.USER_INFO_ACCESS view. It can be used by anyone.
The third tool, change_info.pl, allows users to change their account information. This tool can also be used by anyone.
The tools use the schema created by this create.sql script.
The beauty of securing this system at the database level is that you won’t have to worry about distributing SQL-statements in clear text (like the Perl-scripts above) and you don’t have to access the database using a secret user ident.
No matter what SQL-statement or application a user runs, security is always maintained by the Mimer SQL database server.