Using system functions to create security fields

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

    Requires Free Membership to View

    Login

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.


This was first published in March 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top