Security

After you understand the system work processes and build the conceptual data model, you must consider the administrative requirements for the system. Administrative requirements don't necessarily impact the database schema directly, but they are nonetheless business rules that must be accommodated in the release system.

Administrative requirements are, in a sense, "meta-requirements," in that they concern the system itself rather than the problem space the system is modeling. They fall into two categories: security requirements, which determine who can access the system; and availability requirements, which determine such things as how often the system must be online (24 hours a day seven days a week, or normal working hours, for example) and how users will back up data. Since availability is almost exclusively an implementation issue, we'll discuss only security here.

The implementation of a security scheme can be a complex business. Fortunately, the processes are well documented for Access and the Jet database engine and for SQL Server. Even more fortunately, database design is distinct from implementation, so you need only consider the logical security arrangements during this phase of the process, and at a logical level the principles are simple.

Security Levels

You must first decide the level of security required. Note that we're talking about securing the data here, not the system code, which is an implementation issue. Access and Visual Basic both provide mechanisms for protecting your code from accidental or malicious damage. At the lowest level of security is a completely unsecured system that allows everyone access to the database at any time. This is obviously easy to implement and administer, as you don't need to do anything in particular.

If your data has any value, however, implementing a completely unsecured system is reckless. It might make sense, however, if the client has implemented a network security scheme by which access can be restricted. There's no need to duplicate security precautions.

The next level is share-level security. At this level, you assign the whole database a password and anyone knowing the password has full access to the system. This is also easy to implement and administer, requiring only that the password be changed periodically. Share-level security is adequate in many situations.

User-level security, although it requires more effort to implement and administer, provides the most discrete control over the database. User-level security allows the system administrator to assign specific privileges for each object to individual users: "Joe can add and edit information in the Customers entity but can only view Orders. Mary can add and edit information in the Customers and Orders entities. Neither Joe nor Mary can delete records of either type."

Actually, to call this "user-level" security is somewhat misleading. Security privileges can be assigned to individual users, but they can also be assigned to generic user roles to which individuals are assigned. This is a more effective mechanism for implementing security, as it requires far less administration.

Using this model, you first identify the types of users—system administrators, order-entry clerks, salespeople, and so forth—and then determine the security privileges each role has for each object in the system. It's not necessary to assign privileges for the data objects; in fact, it's not a good idea to do so. You might decide that salespeople need to be able to add, edit, and delete records in the Customers table, but you don't want them messing about with the table itself. You can assign them privileges to a Customer maintenance form, but not to the table. This ensures that they can't inadvertently bypass the special processing provided by the maintenance form.

Often you need to allow people to view only a portion of the data. For example, you might allow everyone to view the Name and Extension fields of the Employee table but allow only managers to view the Salary field. Or you might allow salespeople to see the Orders placed by their own clients but not anyone else's. To accommodate both these situations, you can assign privileges to queries and deny access to the underlying tables.

Auditing

In addition to controlling who has access to the data, perhaps you also need to know what users have done. These requirements can vary widely. Some organizations want to track who logged on to the system and when. Others require a detailed audit trail of who made what changes. Others require something in between.

How you model your auditing requirements depends on the exact requirements. If you simply need to track who used the system, a single entity with the attributes UserName, LogOn, and LogOff will probably suffice. Simply create a record when a user logs on, and update it when he or she logs off.

Sometimes you also need to know who added a record. This can be accommodated in the primary entity with one or two additional attributes: CreatedBy and perhaps CreatedOn.

Tracking deletions can be more complex. You have a couple of options here. You can prevent users from actually deleting records at all and set a Deleted flag instead, perhaps adding DeletedBy and DeletedOn attributes as well. This is a useful technique if you want to copy the records to an archive file before removing them from the database.

Alternatively, you can allow the deletions but write the necessary information to a log file, in the same way that you would if you needed to track the users who logged on. Of course, you'll probably have to create additional attributes. There's not much point, after all, in knowing that someone deleted a record unless you have some way of reconstructing what the record was.

If you need to know in detail what changes were made, you'll need to add an additional audit table to the model for each entity. This could track the user making the change, the date on which it was changed, the record changed, and the old and new values.

If you're going to implement any of these auditing features using a Jet database, you'll have to prohibit users from direct access to the tables, since that would allow them to bypass your security measures. SQL Server doesn't require this, since it supports database triggers, which cannot be overridden.

However you model the auditing requirements, you must also consider how this information will be used, by whom, and under what circumstances. Obviously, you need to restrict access to the audit tables. You might also need to add work processes to your system design to accommodate the auditing. Do the system administrators need to be able to reverse changes made? Are usage reports necessary?

In my experience, most auditing requirements are simply a form of insurance, and the information is intended to be used only in exceptional circumstances. If this is the case, you might not need to expand the work processes at all. The system administrators can easily use Access interactively or use the SQL Server Enterprise Manager to manually interrogate the data and perform any actions required.