Click here to Skip to main content
Click here to Skip to main content

Quick and easy user-level security checks - Part 2

, 24 Jun 2004
Rate this:
Please Sign up or sign in to vote.
A library to manage the low-level details behind user-level security.

Sample Image - CGSecurityDataMSSQL1.jpg

Introduction

In an earlier article entitled 'Quick and easy user-level security checks' I presented a library that manages the storage and retrieval of security information to and from a back-end database. In that article I made mention of the fact that the database code is logically isolated from the rest of the library, and I went on to say that it is a relatively simple matter to adapt the security library to use practically any kind of RDBMS. I promised you then that I would deliver a SQL-Server extension library, and that I would describe all the code in detail in a future article. Well, welcome to the future! (Who says time travel is difficult?)

If you haven't read my previous article I suggest you do so now. You should also download the binaries from that article, since the code I present here runs in the context of that other code.

The database

To refresh your memory I have included a screen shot of the database schema. The database is pretty simple, there is a table for user credentials, another for system-level roles and one more for system-level rights. Roles can have rights associated with them, and can, in turn, be associated with users. Users can have rights associated with them directly. When a right is directly associated with a user the semantics of that association are controlled by the enable_flag column. When the association is enabled the user will be granted the right no matter what, and when the association is disabled the user will never be granted the right.

The CG.Security library

The library contains classes that deal with each area (user, role, right, etc) using a logical two-tier model. In each case there is a manager class that contains the business logic, a data interface that defines the data operations for the manager, and a data class that provides a RDBMS specific implementation of the data interface. The database contains a table for each functional area, and (optionally) a set of stored procedures for storing and retrieving the data. I wont cover each manager class in detail, since the structure is uniform among all managers. Here is a specific example showing the UserManager class:

Sample Image - CGSecurityDataMSSQL2.jpg

The manager classes themselves are class utilities whose static methods provide whatever business logic is appropriate. Managers rely on their respective data objects to perform database I/O. Each manager creates and then caches an instance of a data object in the class constructor. This is how the library creates an instance of a data object when asked to do so by a manager:

Sample Image - CGSecurityDataMSSQL3.jpg

The DataManager class is responsible for creating the appropriate data object. To do so, it reads the configuration settings through the SettingsManager class. The SettingsManager uses the information stored in the application configuration file to decide what assembly to load (if any), and what object to create. The data object is loaded using standard .NET reflection. Once the object is created it is passed back to the manager and used in an appropriate manner.

The SettingsManager class reads this section from the application configuration file:

<CG.Security.Data>
  <runtimeSetup defaultSection="SQLServer">
    <installedAssembly>
      <add 
        sectionName="SQLServer" 
        targetAssembly="CG.Security.Data.MSSQL"
        targetNamespace="CG.Security.Data.MSSQL"
        connectionString=""
      />
    </installedAssembly>
  </runtimeSetup>
</CG.Security.Data>

The CG.Security.Data section contains everything needed to locate and create a data object. The information is returned to the library as an instance of the DataSetting class. DataSetting objects contain a read-only member for the installedAssmebly element specified in the defaultSection attribute of the runtimeSetup element.

Creating an extension for SQL-Server

The security library exposes these interfaces:

  • IUserData - data access operations for users.
  • IRoleData - data access operations for roles.
  • IRightData - data access operations for rights.
  • ISecurityData - data access operations for authentication and authorization.
  • IRoleRightData - data access operations for role - right associations.
  • IUserRoleData - data access operations for user - role associations.
  • IUserRightData - data access operations for user right associations.

Once again, I will stick to implementing a data class for the IUserData interface. We start by creating a new class library, in this case named CG.Security.Data.MSSQL. In the new library we create a class derived from IUserData, and then we implement each of the methods exposed by that interface. A partial code listing for the new class is shown here, with just the Create method depicted:

/// <summary>
/// A SQL-Server specific implementation of IUserData.
/// </summary>
internal sealed class UserData : IUserData
{

  // *******************************************************************
  // Attributes.
  // *******************************************************************

  #region Attributes.

  /// <summary>
  /// The SQL to create a new user.
  /// </summary>
  private static readonly string SQL_CREATE = 
    "cg_security_user_create";

  /// <summary>
  /// Various parameter names.
  /// </summary>
  private static readonly string PARM_USER_ID = "@user_id";
  private static readonly string PARM_USER_NAME = "@user_name";
  private static readonly string PARM_USER_PWD = "@user_pwd";

  #endregion
  
  // ******************************************************************
  // IUserData implementation.
  // ******************************************************************

  #region IUserData implementation.

  /// <summary>
  /// Creates a new system-level user.
  /// </summary>
  /// <PARAM name="userName">The user name (must be unique).</PARAM>
  /// <PARAM name="userPwd">The user password (must be hashed).</PARAM>
  /// <returns>The identifier for the new user.</returns>
  public int Create(
    string userName,
    string userPwd
    )
  {

    // Read the runtime setup.
    DataSettings settings = SettingsManager.DataSettings;

    // Attempt to load the parameters.
    SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(
      settings.ConnectionString,
      SQL_CREATE
      );

    // Did we fail?
    if (parms == null)
    {

      // Create the parameters.
      parms = new SqlParameter[] 
      {
        new SqlParameter(PARM_USER_NAME, SqlDbType.VarChar, 50),
        new SqlParameter(PARM_USER_PWD, SqlDbType.VarChar, 50),
        new SqlParameter(PARM_USER_ID, SqlDbType.Int, 0, 
           ParameterDirection.Output, false, 0, 0, PARM_USER_ID, 
           DataRowVersion.Current, null)
      };

      // Store the parameters in the cache.
      SqlHelperParameterCache.CacheParameterSet(
        settings.ConnectionString, 
        SQL_CREATE, 
        parms
        );

    } // End if we failed to load the parameters.

    // Assign values to the parameters.
    parms[0].Value = userName;
    parms[1].Value = userPwd;

    // Execute the SQL statement.
    SqlHelper.ExecuteNonQuery(
      settings.ConnectionString, 
      CommandType.StoredProcedure, 
      SQL_CREATE, 
      parms
      );

    // Return the identity value.
    return (Int32)parms[2].Value;

  } // End Create()

  #endregion
  
} // End class UserData

I do almost all of my database coding using the data access application blocks from Microsoft. If you aren't familiar with them, then by all means click here [^] to see for yourself. A description of the data access application block is beyond the scope of this article, so if you need an explanation then click on the link to go to Microsoft's website.

The connection string for the database is read from the configuration file using the SettingsManager.DataSettings property and the returned DataSettings instance. The SqlParameter objects for the stored procedure are cached in the SqlHelperParameterCache, and are retrieved using the stored procedure name as a key. If the SqlParameter objects are not already in the cache then they are created and stored in the cache. The value of the input parameters is copied to the SqlParameters and the stored procedure is executed using the SqlHelper.ExecuteNonQuery method. The user identity is returned using an output parameter, and the value is returned as a last step.

Implementing the other methods of the IUserData interface is just as easy as what I have shown. Writing the whole extension library took me about an hour - from start to finish.

Configuring the demo to use the new extension

I recently updated the binary files for my original article so that the demo application's configuration file would contain an installedSection for this library. Assuming that those changes have been posted by the friendly CP editors, then the only configuration change needed is to uncomment the installedAssembly for this library, and then change the value of the defaultSection attribute to "SQLServer". The final configuration file should look something like this:

<CG.Security.Data>
  <runtimeSetup defaultSection="SQLServer">
    <!--
    <installedAssembly>
      <add 
        sectionName="Access" 
        targetAssembly="CG.Security"
        targetNamespace="CG.Security.Data.Access"
        connectionString=""
      />
    </installedAssembly>
      -->
    <installedAssembly>
      <add 
        sectionName="SQLServer" 
        targetAssembly="CG.Security.Data.MSSQL"
        targetNamespace="CG.Security.Data.MSSQL"
        connectionString="packet size=4096;integrated security=SSPI;
          data source='localhost';persist security info=False;
          initial catalog=security"
      />
    </installedAssembly>
  </runtimeSetup>
</CG.Security.Data>

The database script to create the SQL-Server database is located in the database folder for this project. You can name the database itself anything you like, but the tables and stored-procedures should not be renamed. The SQL script doesn't create a database, so you should do that before running the script.

The only other thing left to mention is that you should copy this library into whatever directory you intend to run the demo application from. That's it!

Conclusion

Now the CG.Security library can store credentials into both Microsoft Access and Microsoft SQL-Server databases. If you want to use any other kind of RDBMS - or even XML - then simply follow what I have done here, insert whatever code is appropriate for your needs, and you should be on your way!

I hope I have done a good job of explaining how the security library works, as well as explaining how to write an extension library. As always, if you have any questions then feel free to email me.

Have fun! Blush | :O )

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Martin Cook
Web Developer
United States United States
I am a C# developer specializing in creating object-oriented software for Microsoft Windows. When I am not programming I enjoy reading, playing the guitar/piano, running, watching New York Ranger hockey, designing and building wacky electronic devices, and of course enjoying good times with my wife and children.

Comments and Discussions


| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 25 Jun 2004
Article Copyright 2004 by Martin Cook
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid