Bryan's COL4 Journal

Connections Online 4.0 Database Security

2006-02-17T17:01:00.000-05:00

This is intended to be a very low-level, technical explanation of the security model in Connections Online 4.0. It is very complicated and requires a strong understanding of relational databases and information security models. This is all stuff that the end user does not need to know. From his perspective, the security will be very easy to understand. I will write a 30,000-foot executive overview in the future that will explain the security to Joe User. For now, though, here's the 30-foot view. :-)

...

With our new version comes a chance for us to implement bulletproof security from the start instead of tacking on security as an afterthought like we did with the 3.0 version. Like it or not, though, people are familiar with the way the security works in our current application, so the new security model needs to be outwardly similar to the current model.

To accomplish this, we will be using ideas taken from the Microsoft white paper, "Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005," integrated with the role-based security model used by the Microsoft ASP.NET 2.0 Membership and Role Providers. I'm not going to go over either of these things in detail here, so you may want to read up on both of them before continuing on. You may also want to check out the current Connections Online security model if you are new to Connections Online.

...

Okay, now that you are familiar with Connections Online 3.0 (COL3) security, the membership and roles provider in ASP.NET 2.0 (MRP), and row-level security (RLS) recommended in the white paper, you may be wondering how all of that will be tied together in Connections Online 4.0 (COL4).

COL3's security model of employees and security groups directly relates to the users and roles in the MRP model, so this transition will be easy for us to wrap our heads around. We could continue to use the COL3 model, but the MRP model should be easier for other developers to understand. If they haven't used it before, they can easily Google up some samples and documentation.

Unfortunately, the MRP model is really based on the idea that security can be handled at the user interface level. It assumes that the Web interface is the only way the user can get to the information. The COL3 model makes the same assumption, but this is something we need to fix. In COL4, we want users to be able to connect to our information through the Web site, through third-party applications, through Web Services, and even through direct access to the database. We really need to move the security back to the database level.

The MRP model can be moved back to the database level, but doing so requires each user to log in to the application with a unique database login. This limits our ability to use connection pooling for speed, and it also requires a SQL Server client access license for each user. We really need to be able to use one SQL login account from the Web interface, and we also need to allow the user to connect to the SQL Server however they want and still only give them the access they should have.

That's where the RLS model comes in. The RLS model gives us application-level security like we had in COL3, but it is built directly into the database. This is really our perfect solution. All we need to do is change the multi-level secure (MLS) format used in the white paper to a roles-based format similar to the MRP model. While the Alias fan in me was enamoured with the governmental security settings like Top Secret and Classified in the RLS model, the application architect in me knew most of our end users would hate them.

Figure 1: Windows NTLM Security

So here's how the COL4 model differs from the RLS model. Each data table has a uniqueidentifier column called ID that is used as the table's primary key. It seems silly to add yet another uniqueidentifier called RLSMappingID to the table, especially when it would just be a one-to-one relationship with the KeyMapping table, so we will relate the data table's ID field directly to the KeyMapping.ID field. We will still use the RowLabel field to keep the row's security with the row, however.

As an aside, the ID column was an integer in COL3, but integration with Outlook and other outside data stores really requires that we know for sure which task or deliverable they are connected with. If someone accidentally changes their database link to a different COL4 database, we can't have task #20 in database A accidentally overwriting task #20 in database B. So the row identifier needs to be unique over all databases, not just one. It may be overkill to turn ALL of the ID fields into uniqueidentifiers, but I believe the flexibility far outweighs the performance and readability issues.

Next, we need to replace the idea of classifications and compartments with roles. Classifications work just like we want our Administrator roles to work. Each classification is a subset of the next item down up in the list. So, all Global Administrators have Site Administrator access across all sites, all Site Administrators have Organization Administrator Access across all Organizations in their site, all Organization Administrators have Authorized User access to their Organizations, and all Authorized Users have Guest access as well.

Figure 2: COL4 Security Model

Compartments work like our group-level and user-level security permissions should work. Each compartment is all by itself, just like the members of one team have nothing to do with the members of another.

Each role then has a specific level of access to the given information. Instead of having full access to an item as in the MLS model, COL4 will split this into Create, Read, Update, Delete, and Delegate permissions. (The Create and Delete permissions are new in COL4.) We will also add both an Allow and a Deny flag for each type of permission, much like Windows NTLM security (Figure 1).

After combining RLS and MRP, renaming the pieces and fitting everything to our model, we end up with the data structure in Figure 2. To the end user, this will work much like the COL3 model, but it will work no matter how they connect to the database.

Versioning

2005-08-09T13:18:00.000-04:00

We've always tried to stick with a standard way of numbering our file versions, but we've never really explained what that standard was.

Previously, we used numbers like 3.2 Build 2400. This was pretty easy to understand: in this version, the major version number is 3, the minor version is 2, and the build number for the application is 2400. The build number was automatically increased each time we compiled the application, so sometimes a few build numbers would be skipped between releases. This could be somewhat confusing to anyone who didn't know what that "build" number meant.

In Visual Basic 6, the coding language we used to write Connections Online 3, that was just the way version numbers worked. In other languages, like Visual C++, there were actually four parts to the version number. Visual Basic just didn't use one of the parts. Now, with the .NET Framework, code compiled from any language has the ability to use all four parts of the version number.

Because of this, Connections Online 4.0 will now use the new versioning standard. The new version will look like this:

Major . Minor . Revision . Build
Example: 4.0.0.100 or 4.0.0 Build 100
Major: This identifies the generic version number. For the upcoming release of Connections Online, this will always be 4.
Minor: This identifies how many new feature updates have been done to this major version. This will start out as 0. As new features are added, they will be bundled together and released every three to six months. Each time a release includes new features, the Minor version number will increase by one.
Revision: This identifies how many times the current Minor version has been released. BETA versions of a release will have a 0 in the revision number. Once the minor version is made official, the number will become a 1. It will then increment each time bug fixes or cosmetic changes are made. When a new Minor version is begun, this number will return to 0.
Build: This identifies how many times the Major version has been compiled. This number is now much less important to the end user. It is mainly used by the developers to keep track of file versioning. This number will continue to increment even when Minor and Revision numbers change. Since the Revision number changes for each release, the build number can be left off most of the time.

So, using this versioning method, our beta versions of Connections Online will be numbered 4.0.0, and once we are ready to release an official version, the number will become 4.0.1.

I hope this is easy enough to understand. Once we start using this new numbering, I'm sure everyone will see the benefits. :)

Starting with a Wimper

2005-08-09T12:35:00.000-04:00

We have been planning the 4.0 release for more than a year now, but requested features for the current 3.2 version and occasional bug fixes have kept us too occupied to really dive into 4.0 completely. We have made some advances in the past couple of months, though:

In April, we spent a day discussing where we want to go with the next version: what stays the same from 3.2, what gets improved, and what gets added.
In May, the basic database design was prototyped in SQL Server 2005.
In June, we hired another developer to help with Connections Online support, bug fixes in the 3.2 version, and eventually with 4.0 development. (Hi, Robbie!)
We have come up with some new logo ideas, one of which is shown on the main page of the Sypher Technology Web site.

For the past few months, I've been doing some custom work for one of our new clients. Once this work is done, I will be focusing on 4.0. Some of the work I've done for this new client has actually allowed me to use some of the ASP.NET 2.0 features that I am planning on using in 4.0, so this hasn't been a total loss of momentum.

Much of the work has allowed me to create pseudo-prototypes of data edit screens and even XML Excel reports that don't require Excel to be installed on the Web server. I still can't put graphics or charts in those Excel reports, but the increased speed, security, and price benefits outweigh the cost of not having graphics or charts. That being said, I still hope we can eventually use this XML format for Excel/Word reports and include graphics and charts...

One of the most frequent questions I hear regarding Connections Online 4.0 is "When is it expected to be released?" As tempting as it is to give a date, it really is too early to say quite yet. We do have an internal timeline and an estimate of when it will be ready, but our timeline is very flexible. I would like to wait until we have a full, working prototype before giving a three-month release window, and I would like to have the entire application written and going through final testing before giving an offical release date. Wishful thinking perhaps... :)

Introduction to COL4

2005-05-09T17:00:00.000-04:00

COL4 is an abbreviation for Connections Online 4.0. From the main page on the Connections Online Web site: "Connections Online is a productivity solution empowering managers and employees by aligning them with your corporate objectives." Historically, it has mainly been used by credit unions, but the banking industry is beginning to pick up on its usefulness now as well. It has also been used in the manufacturing, restaurant management, and law industries.

Connections Online is owned by the Cardwell Group in Cleveland, Ohio, but the software development has always been handled by outside companies. This is how I became involved. The first version, then called Accountability Mapping (AMX), was developed by Advanced Production Systems (APS) in Louisville, Kentucky. I began working at APS in 1996 in the application development group and eventually joined the AMX development team to work on bugs and new features.

At the time, AMX was written in 16-bit Visual Basic 4.0 (or 3.0, I'm not sure) and used an Access database to store its information. The decision was made to move to 32-bit Visual Basic 4.0 to take advantage of some of the new features. During this upgrade, the name of the application was also changed to Connections.

Connections worked well most of the time, but many customers had problems with installing the application on every computer where it was being used. Either the setup didn't work properly in their unique environment, or their IT departments didn't like having to upgrade every single computer when a new update or bug fix came out.

From the appearance of the first Web sites in 1993, I was very interested in Web technology. By 1999, Microsoft's Active Server Pages (ASP) and other technologies were beginning to make Web application development an alternative to the standard install-everywhere application development we were using. I suggested we write a new version of Connections that used Internet Explorer for its user interface and SQL Server for its database, Cardwell and APS agreed, and Connections Online was born. From them on, I've been the lead developer on the Connections Online project.

The first version called Connections Online was numbered 2.0 to show that it was an upgrade from Connections, but it was actually a total re-write of the entire application. This rewrite moved the installation from every machine to one or two servers, and it even allowed organizations to use the application from the main Connections Online Web site and not have to install anything at all.

In January of 2003, I left APS and continued working on Connections Online through my own software development company, Sypher Technology. Over the last two years, Connections Online has transformed into Connections Online 3.2, but the 3.2 version is still based on the original 2.0 code. There have been many advances in Web technology over that time, including Web Services, XHTML, and the Microsoft .NET Framework. Now it is time to make another leap.

The main theme to Connections Online 4.0 will be "simple connectivity." The user interface will be simplified to be more Web-page-like and less boxed-application-like. Repetitive data entry will be minimalized with options to use previously entered data as templates for new items. Logging in to COL4 will be changed to make single sign-on (SSO) possible. Because it will be build on Web Services, exchanging information with other applications that use Web Services will be possible without the need for custom-built connections (like the current Outlook and Project add-ins and the Word/Excel templates.) The same reports will be distributed with COL4 as were in COL3, but the new reporting features in SQL Server 2005 will allow users to make their own customized reports.

This journal will go into more detail on each of these new features and allow current users and IT departments working with Connections Online 3.2 to see what's in store. It will also document how development is going: what we're working on, who's doing what, etc. Occasionally, I may even ask for comments about potential features or design. Hopefully, this journal will be interesting for other people to read, or at least help me keep my thoughts organized. :-)