Connections Online 4.0 Database Security
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.
...
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.
posted by Bryan at
5:01 PM
|
0 comments
links to this post
