Security

In The Beginning...

In the beginning was the data and the data was good. Then John said, "let there be MadSearch2" and after a few months there was MadSearch2 and it was basically pretty good. Somewhere in those few months John said, "let there be data tables and let us populate them with data." Then there were data tables. Then to provide parental support for these data tables John said, "let the creator of these data tables also be the table administrator." And so it is that by default the creator of a data table is the administrator of the table. But the data was not avialable to others besides the table administrator. Therefore, John said "By default, let all users have read permissions on the data tables."

But users became bad and it was soon necessary to impose access restrictions on tables and their data. John then said, "Ok. I will create permissions schemes and other protection mechanisms." And so, access restrictions and security came to be and they were good.

By now you're wondering what you're supposed to take from this little story. There are two things.

• The creator of a table is the default administrator of the table.
• All users of the table have by default permission to execute read queries to the the table's data.

Hmm. Perhaps, you've seen these points previously...

Overview

Our discussion of madsearch2 security will focus on the following:

• protection of madSearch2 tables and their associated files from viewing/usage/modification in manners external to madSearch2's execution
• table level access control
• record level access control
• data encryption

Securing Your MadSearch2 Files

madSearch2 is designed such that all access control to your tables is coordinated and controlled through madSearch2's database engine and associated utilities. Before we can take advantage of madSearch2's access control mechanisms, we must first ensure that our madSearch2 files are protected from any means of access other than madSearch2's.

The key principle to remember in the two points that follow in this section is this: if any user other than yourself and the web server has access to your madsearch2 data directory and can view it's files, all of madSearch2's access control mechanisms have been defeated. your data is now the world's data.

The File System Is Your Friend

The first protection for your tables is based in the permissions scheme of the underlying file system in which your tables are stored. Do you remember how we prepared and specified permissions for directories which would contain madSearch2 tables? The principle concern was to ensure that MadSearch2 tables and their associated files could only be accessed by the owner of the directory and the webserver (user "www" of group "www"). If group "www" has read, write, and execute permissions on the directory then it will be able to access and manipulate your data tables since madSearch2 executes as "www".

Other/world permissions should never be set on either the directory or the MadSearch2 files within it. If you specify other readable permissions on your data directory, any user may copy/use your tables. If you specify other writable permissions on your madsearch2 data directory, then you're a fool and there's no point in continuing our discussion of security.

madSearch2 enforce the appropriate permission settings. If MadSearch2 finds that a table file has other permissions set on it, it will refuse to work with it and return an error message.

.htaccess files

The point of setting appropriate file permissions within the previous section is to hide your madsearch2 tables from the prying eyes and wandering fingers of console users. However, we're not quite done yet.

Many madSearch application designers place their data tables within the web server document tree. If you place you madsearch2 files within the document tree of the webserver, the webserver has access to all of your files. Remember that madSearch2 tables are fully accessible to user "www" of group "www". Since the webserver runs as user "www" of group "www" it can deliver your madSearch2 data tables as if they were any other document. In other words, a user could easily type the url of the directory that contains your madsearch2 files and gain access to their information.

For example, let's say that you've placed your madsearch2 table of user's credit cards within the "/wwws/data/cu/myData/" directory. The table's name is "creditCards". Further, let's say that the webserver's document root is "/wwws/data". If the user were then to type the following url:

https://www1.columbia.edu/sec/cu/myData/creditCards.msdb2

they would now have access to your client's credit card information. Good going, bud.

Don't fret. There is a fix! You must use a ".htaccess" file to restrict web access to your files. A sample ".htaccess" file is as follows:

More information about ".htaccess" files is available here.

Encryption

Finally, even with these precautions, it is never a good idea to have tables containing sensitive information (eg. credit card numbers, social security numbers, grades) lying around the system unencrypted. You must rely upon the unexpected or unthinkable occuring. What if you were to accidently set other read permissions on your directory of tables? What if the web server software had an unknown vulnerability that was exploited? Someone might get hold of your tables.

madSearch2 offers encryption of your tables through a symmetric key algorithm. The smallest permissible key length is eight characters (64 bits). madSearch2 stores the key you choose with your table. It is important, therefore, that you set permissions as stated above such that this key not be visible to anyone but yourself.

However, the key/table password you choose is not the actual key used by madSearch2. madSearch2 combines your key with it's own and manipulates these to generate a key that will then be used to encrypt/decrypt your table's data. Thus, even if your key is compromised, unless madSearch2's key is similarly compromised, your data will remain safe.

To encrypt your table, simply use the "Encrypt Table" utility. To later decrypt your table's information, use the "Decrypt Table" utility.

Users of your application need not know the table's key/password to work with the table. Apart from a small performance hit during queries due to the decryption/encryption overhead, use of encrypted tables is transparent to users.

Table Level Access Control

As mentioned previously, there exist two levels of access control within madSearch2. The first of these is known as table level access control. As you may have guessed from the name, this access control mechanism acts at the level of the table. You can think of table level access control mechanisms as being the gate keeper. Finer levels of access control can be achieved through the second level of access control, record level access controls.

Security Tables

madSearch2 implements table level access controls through a flexible system of security tables that are linked to data tables. madSearch2 security tables specify the users and groups that may access the madSearch2 table(s) to which they are linked. Multiple data tables may be linked to the same security table. This proves to be a very useful feature in applications that use several data tables.

A security table is a special type of table for MadSearch2. Although the information within it can be manipulated using the same queries one would use with a data table, security tables have special restrictions and field types. Security tables do not only specify the users and groups that may access a madSearch2 data table, they additionally specify the operations they may perform.

Just as with data tables, the creator of a security table is the default administrator of the table. Through security tables, you can specify additional table administrators for the data tables to which the security table is linked. There are some restrictions to the linking of data tables to security tables. Only the administrator of a data table may link the table to a security table. A data table may be linked to any security table regardless as to whether the administrators of the data and security tables are one and the same. Note that only a fool would link their data table to a security table with which they are not intimately familiar and trusting of the administrator.

Data tables may be linked to security tables using the Link Security Table utility. Data tables may just as easily be unlinked from a security tables by using the Unlink Security Table utility.

Structure and Format of Security Tables

Security tables contain field types not previously described within our "Create A Data Table" chapter. Each row/record of a security table describes an entity and its permissions on the data tables to which the security table is linked. Scurity tables have a fixed structure of four fields storing the following entity attributes:

• EntityType
• EntityName
• EntitityPermissions
• Comments

Before providing instructions as to how to create a security table, we'll first describe these four fields.

EntityType: The "EntityType" field is of type entity. This field defines whether the named entity is a user or group. As mentioned in the section on defining a table structure, a field of type entity may only be populated with the values "user", "group", and "other". What's an "other"? The "other" entity type is a kind of catch all for users and groups not specifically mentioned. The "other" type is often used to specify the default permissions a non-specified entity will have for a table. If the "other" type is not specified, all non-named users have by default read permissions for a table.

EntityName: The "EntityName" field is of type alphanumeric. It specifies the name of the user or group entity.

EntityPermissions: The "EntityPermissions" field is of type permissions. The "EntityPermissions" field specifies the table level permissions of the named entity. Similar to fields to type userPermissions, groupPermissions, and otherPermissions, table level permissions are as follows:

Comments: The "comments" field is of type alphanumberic and can be used by the security table administrator to store notes and comments about the named-entity. For example, if the entity is user jlg10, the table administrator might store jlg10's actual name "john" within the comments field.

The most important thing to remember about table level permissions is that they only allow a user to set their proverbial foot in the door. Just because a user is allowed to execute a read query does not mean that they will be able to retrieve every record within the table. This is due to the second level of access control: record level permissions. We will discuss these shortly.

Managing a Security Table

One of the nicest features of madSearch2's security tables is that they are managed in the same way data tables are managed: queries. To insert, remove, or modify an entity or it's level of access, you'll want to execute a query of type though an html form of your own or using madSearch2's QueryBuilder utility.

For example, let's say our security table is named "security" and resides within the madSearch2 data table directory "/wwws/data/cu/myData. We might want to insert a new entity, user jlg10, and provide him with read and insert permissions to the data tables linked to our security table. One might do this (the hard way), by creating a form like the following:

<form method="post" action="https://www1.columbia.edu/sec-cgi-bin/mad/mad2">
<input type="hidden" name="query" value="
/wwws/data/cu/myData | entity   | entityName | entityPermissions | comments        |
insert               | "user"   | "jlg10"    | "ru"              | "jlg10 is john" |">
<input type="submit" value="do it!">
</form>

There is no change in query syntax in dealing with security tables. Note especially that the "entityPermissions" field uses the same query operators and query field syntax as do it's close cousins: the userPermissions, groupPermissions, and otherPermissions fields.

Record Level Access Control

As mentioned previously, record level access control is the ability to specify, record by record, who may view, modify, or delete a particular record. A person's table level permissions determine whether they can execute a given type of query against a table. Record level permissions specify whether the person's query executes against a given record. As you can see, record level permissions provide a finer level of security for a data table.

As mentioned in the section, "Creating A madSearch2 Table", record-level security is implemented using security fields that in some ways mimic unix file system permission levels.

In madSearch2, just as in unix, there are three levels: user, group, and other. For each level, madSearch2 allows you to specify which level may have access and what types of access (i.e. what permissions they have for a given record).

User & Group Fields and Their Default Permissions

There are two types of security fields that name a specific entity that has permission to a record: fields of type user and group. Fields of type user specify the user id of the user to which a record belongs. Fields of type group specify the group owner of a record.

When one of these fields appears within a record minus it's associated permissions field, by default the named enitity has read, update, and delete permissions for the records (i.e. -- all record level permissions except for access control).

If a field of type user is blank, this means that ownership of the record is up for grabs. Any user that has table level update rights, may take ownership of the record. (The user need not derive access control permissions from permissions specified within a record's groupPermissions field or otherPermissions field.) This feature is often useful in scheduling applications in which users take and relinquish appointment times. The same does not hold true for fields of type group whose values are blank.

When a user has ownership of a record, table level and record level update permissions, but not access control permissions, they may set the user field's value to "blank" or that of their user id. When a group has ownership of a record, table level and record level update permissions, but not access control permissions, the group field may only be set to "blank".

In running queries with record selection criteria, criterion values for user fields may only be those of the user's id and "blank". Similarly, criterion values within a group field may only be "blank" or those groups to which the user executing the query belongs.

When a user has access control permission for a record, the user executing the query may take user ownership of the record or change the group field's value to be that of one of the groups to which they belong. In addition, a user who has access control permission for a record may set the user, group, and other permissions of the record.

Only a table administrator can change the value of a user field to be that of the non-authenticated user. Similarly, only a table administrator can set thevalue of a group field to be other than a group to which the authenticated user belongs.

Finally, in specifying selection criteria, only table administrators may user criterion comparison operators other than "equal to" within a user or group field.

If a user or group field is defined for a table, there is no default access for other users. To provide access to a record to users not mentioned within the user field or not belonging to a group specified within the group field, you must include an "otherPermissions" field.

Permissions Fields

Permissions fields restrict or provide greater access to a record than the default permissions described above.

There are three types of permissions fields: userPermissions, groupPermissions, and otherPermissions. As you might suspect, userPermissions fields specify the record level permissions to be given to a user named within a field of type "user". A groupPermissions field specifies the record permissions to be granted to the group specified within a field of type "group". The otherPermissions field specifies the permissions all other, non-specified users may have on a record.

UserPermissions and GroupPermissions fields may not exist within a table without a corresponding user or group field, respectively.

The following permissions may be specified within permissions fields.

Only table administrators or user owners with table-level access control permission may grant other users or groups record-level access-control permissions.

Fields of type otherPermssions may not be granted access control permissions.