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.
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 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
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:
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.
SettingsManager class reads this section from the application configuration file:
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 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:
internal sealed class UserData : IUserData
private static readonly string SQL_CREATE =
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";
#region IUserData implementation.
public int Create(
DataSettings settings = SettingsManager.DataSettings;
SqlParameter parms = SqlHelperParameterCache.GetCachedParameterSet(
if (parms == null)
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,
parms.Value = userName;
parms.Value = userPwd;
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:
connectionString="packet size=4096;integrated security=SSPI;
data source='localhost';persist security info=False;
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!
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! )