Any good database design considers security as part of the overall system design. In addition to controlling who can see what data -- something you can control through users and groups -- it is also important to find out who's actually viewing and, more importantly, modifying data. You'll want to know when a record was created and by whom, when it was last modified and by whom, and so forth, and you'll want to store the information in a set of security fields. For optimization work you might want this information logged into a file for analysis.

The key to establishing these fields is the use of system functions, which are a set of global variables that return information about objects, values and settings. You'll find a list of the system variables you can use in SQL Server Books Online. Among the ones of most interest are: Current_TimeStamp, Current_User, Session_User, and User_Name. Some of these functions take a @@ prefix, and in previous versions of SQL Server they were called global variables. But they aren't variables per se and don't behave as such.

This information can be stored in a set of fields in the tables that they relate to, fields that aren't available for viewing by users without the appropriate permissions. More often they are stored in a set of fields in a security table that can be better protected. Information is written to these fields as part of any transaction that creates or modifies a record. Often a user defined function is called that provides the appropriate actions. A security module might not be something that you want to roll out on your own. They can be purchased from third party software vendors. For example, take a look at the Application Level Security System that Cybersoft sells. Another tool of interest is Log Explorer for SQL Server from Lumigent.

Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.