Implementing Security in B2B Applications

This chapter is from the book

Implementing B2B Commerce with .NET: A Guide for Programmers and Technical Managers

Learn More Buy

This chapter is from the book

This chapter is from the book 

Implementing B2B Commerce with .NET: A Guide for Programmers and Technical Managers

Learn More Buy

2.3 Implementing SQL-Based Authentication and Authorization

Application-specific authentication and authorization can be implemented using a relational database server. The authentication process is simply a matter of prompting (forcing) the user to enter a user ID and a password and then looking up that user ID and password in the database to make sure they are valid. If a principal enters a valid user ID and password, they are authenticated. An invalid user ID and password are not authenticated, and the principal is denied access to any secure resources.

As already discussed in this chapter, typical B2B applications require a matrix of authorization levels, and the authority to access resources must be granted based on multiple criteria, such as the principal's identity, employer, role, and so on.

Resources can be Web pages, documents on the Web site, SOAP methods, portions of Web pages that display data from internal systems, and so forth. A resource could be anything on the B2B site to which you may want to control access.

An authorization process to control access to resources requires that all of the secured resources be listed in the database. In other words, there must to be a row in a database table for each resource that you want to protect.

In Chapters 3 through 5, you will work through an ASP.NET Web Forms example that secures access to documents on a Web site. This chapter contains the code that creates the database the Web Forms application uses.

To work through the Chapter 2 code, create a SQL Server database named "ManufacturerA," and execute the SQL commands in the code listings here. Then enter the data shown later in the chapter to test the database and get it ready for use in Chapter 3.

Run the SQL DDL (data definition language) code shown in Listing 2-1 to create in your "ManufacturerA" database a Documents table that will be used to control access to documents.

LISTING 2-1 Table Linking Permission Lists and Documents

CREATE TABLE Documents (
    DocID varchar (20) NOT NULL ,
    Name varchar (20),
    MimeType varchar (20),
    FilePath varchar (250)
)

The DocID field is the unique identifier, the key field, for each document. The Name field is the name of the document that is displayed on the Web page as a hyper-link, which users can click to see the document. The MimeType is the file type, and the FilePath field points to the location where each document can be accessed and sent out to browsers that request it.

The DocID field is also a foreign key that is used to link each document to Permission List records. A link table is used to link permission lists with documents. Run the SQL code shown in Listing 2-2 to create this table.

LISTING 2-2 Table Linking Permission Lists and Documents

CREATE TABLE PLDocument (
    PermissionListID int NULL ,
    DocumentID varchar (20)
)

A permission list is a collection of criteria. The PermissionLists table holds the permission lists. Create the PermissionLists table defined in Listing 2-3.

LISTING 2-3 Permission List Table

CREATE TABLE PermissionLists (
    PLKey int NOT NULL ,
    Company varchar (50),
    CompanyCategory varchar (50),
    Person varchar (50),
    Role varchar (50)
)

In Listing 2-3, the PLKey field is the primary key for the table and the unique identifier for each permission list. Each permission list is a unique combination of Company, CompanyCategory (which is used to classify companies), Person, and Role.

The intent is for each field to be AND'ed together to determine a unique level of access. For example, a permission list with "Viewstar" as the Company and "Executive Staff" as the Role means that a principal has to be both an employee of Viewstar and a member of the executive staff in order to qualify for that permission. In this case, the CompanyCategory and Person fields would be populated with some value, an "any" symbol of some kind that would indicate there are no restrictions on those fields.

These permissions are linked to documents using the PLDocument table. For example, if a particular document had the just mentioned Viewstar/Executive Staff permission as its lone permission, only principals who are executives of Viewstar would be authorized to access that document. If more than one permission is linked to a particular document, those permissions are OR'ed together, meaning that a principal need only qualify for one of the permissions to gain access to that document.

Permission lists are also associated with principals. This is implemented in the PLPerson table. Run the code to create the PLPerson table defined in Listing 2-4.

LISTING 2-4 Table Linking Permission Lists and Persons

CREATE TABLE PLPerson (
    PermissionListID int NULL ,
    PersonID varchar (20) NULL
)

There also needs to be a table that holds person data. Run the SQL code to create the table that holds person data that is shown in Listing 2-5.

LISTING 2-5 Table Containing Person Data

CREATE TABLE Persons (
    UserID varchar (20) NOT NULL ,
    Password varchar (50) NOT NULL
)

In Listing 2-5, the UserID field is the primary key for the table, and it is the unique identifier for each permission list. Of course, there would be several other fields in the Persons table of a real B2B application. These fields might include such information as each person's employer, role or roles, contact information, and so forth. The schema is simplified here for clarity.

The Password field holds the user's password. It is specified here as 50 characters in length to accommodate encrypted passwords. An even better idea than storing encrypted passwords is storing the passwords as hash values, which are more difficult to reverse.

You could use a message digest algorithm such as MD5 or SHA-1 to distill each password into a value of 128 or 256 bits in length, which you could store in the database instead of the password itself. Performing a search for "MD5" or "SHA-1" with a Web search engine will net you a long list of resources that will help you implement these message digest functions in your applications.

Each user in the database could potentially be associated with several permissions. It is a matter of comparing each person's attributes—such as his or her employer, role, and so on— with the permission lists in the PermissionLists table and then putting a record in the PLPerson table for each permission for which the person qualifies.

Each person and each document are associated with permission lists. This is illustrated in Figure 2-3.

FIGURE 2-3 Relationship Between Persons, Permission Lists, and Documents

The SQL code to enforce these relationships in a Microsoft SQL Server database is shown in Listing 2-6. Run this code to create these constraints in your Manu-facturerA database.

LISTING 2-6 Constraints to Enforce Relationships with Permission Lists

ALTER TABLE PermissionLists WITH NOCHECK ADD
    CONSTRAINT PK_PermissionLists PRIMARY KEY CLUSTERED
    (
        PLKey
    ) ON [PRIMARY]
GO
ALTER TABLE Documents WITH NOCHECK ADD
    CONSTRAINT PK_Documents PRIMARY KEY CLUSTERED
    (
        DocID
    ) ON [PRIMARY]
GO
ALTER TABLE Persons WITH NOCHECK ADD
    CONSTRAINT PK_Persons PRIMARY KEY CLUSTERED
    (
        UserID
    ) ON [PRIMARY]
GO
ALTER TABLE PLDocument ADD
    CONSTRAINT FK_PLDocument_Documents FOREIGN KEY
    (
        DocumentID
    ) REFERENCES Documents (
        DocID
    ),
    CONSTRAINT FK_PLDocument_PermissionLists FOREIGN KEY
    (
        PermissionListID
    ) REFERENCES PermissionLists (
        PLKey
    )
GO
ALTER TABLE PLPerson ADD
    CONSTRAINT FK_PLPerson_PermissionLists FOREIGN KEY
    (
        PermissionListID
    ) REFERENCES PermissionLists (
        PLKey
    ),
    CONSTRAINT FK_PLPerson_Persons FOREIGN KEY
    (
        PersonID
    ) REFERENCES Persons (
        UserID
    )
GO

In a production application, there are no doubt other constraints that you would want to add. For instance, you might want to add a constraint on the Permis-sionLists table to enforce the uniqueness of the combination of the CompanyCate-gory, Person, and Role fields (recall that each record is a unique combination of those fields).

Given a person, it is easy to write a SQL query that returns the IDs of the documents that the person can access. The SQL SELECT statement in Listing 2-7 returns the documents that a principal is authorized to access. In Listing 2-7, the personID of 'SidSalesman' could be a parameter, which is passed into the query to generalize it for use with any person in the database. To make this example a bit more concrete, enter data into the PermissionLists table, as shown in Table 2-1.

LISTING 2-7 Query to Return the Documents a Principal Can Access

SELECT DISTINCT pldocument.documentid from pldocument, plperson
WHERE pldocument.permissionlistID = plperson.permissionlistID
AND plperson.personID = 'SidSalesman'

The example Data in Table 2-1 shows ten permission lists, each of which is a unique combination of the fields. The "0" in the fields indicates that there is no restriction on that field for that particular permission. For example, PermissionList 8 requires that the principal have a role of "Sales Staff," but there are no other restrictions. To qualify for that permission, the principal must have the "Sales Staff" role but could be employed by any company of any category.

TABLE 2-1 Example Data in PermissionList Table

PermissionList 9 is interesting because it uses a category of "SiteOwner." In this scheme, there is a company table that contains all of the information for each company. The company that owns the B2B Web site is given a category of "SiteOwner." PermissionList 1 is a very restrictive permission. Only a principal with a Person ID of SamSiteAdmin qualifies for that permission.

Enter data in the PLDocument table so that it is populated as shown in Table 2-2. Because of the foreign key constraints, you will first need to add records in the Documents table that correspond to the DocID entries shown in Table 2-2. For now, you can just fill in the DocID field in the Documents table and leave the other fields blank. It goes without saying that you will enter each DocID only once in the Document table. After entering the DocIDs, enter the data in Table 2-2 in the PLDocument table. There is a one-to-many relationship between the Documents table and the PLDocument table, which is why there are duplicate DocIDs in Table 2-2.

You can see that GoldQuotas and GoldPaymentTerms are accessible only by Permission 10, which is only the executive staff of companies with a Gold category. You can also see that most of the admin documents are limited to Permission 1, which is restricted to PersonID SamSiteAdmin.

Enter data so that the PLPerson table is populated as shown in Table 2-3. Table 2-3 is sorted by PersonID so that you can see what is happening with each person.

TABLE 2-2 Example Data in PLDocument Table

TABLE 2-3 Example Data in PLPerson Table

Because of the foreign key constraints, you will first need to add in the Persons table records that correspond to the PersonID entries shown in Table 2-3. For now, you can just fill in the Password field in the Person table with "1234." After entering the PersonIDs in the Persons table, enter the data in Table 2-3 in the PLPerson table.

PersonID SamSiteAdmin has been given almost every permission in the system. This is because he is the system administrator and must have access to lots of places. The only permissions he doesn't have are 5 and 7, which are the Viewstar and T & R Tech executive staff permissions. (In a real implementation, the system administrator would probably qualify for those permissions as well.) PersonID ElmerEmployee has only one permission, that of SiteOwner. He is apparently an employee of the company that owns the B2B Web site, but he hasn't been given much access to it.

TABLE 2-4 Resources that PersonID EdTRExecutive Is Authorized to Access

PersonID EdTRExecutive is apparently an executive with T&R Tech. Running the SQL query in Listing 2-7 with PersonID = 'EdTRExecutive' yields the documents in Table 2-4.

A stored procedure can be created for SQL Server that takes the PersonID and DocumentID as input parameters and returns an output parameter indicating whether that person has access to that document. The stored procedure is shown in Listing 2-8. This stored procedure does a count of the documents that match this document ID and that are among the documents this person is permitted to access. Of course, there is only one document with this document ID, and it may or may not be among the documents this user is permitted to see, so the count will be either 0 or 1. That makes it handy to use in an if condition in C# code. You actually get to use this stored procedure in an ASP.NET Web page in Chapter 3.

LISTING 2-8 Stored Procedure That returns 0 or 1 if the Principal Has Access

CREATE PROCEDURE sp_UserHasAccessToDoc
@userid VARCHAR(20), @docid VARCHAR(20), @HasAccess INT OUTPUT AS
SELECT @HasAccess = COUNT(*) FROM documents
WHERE DocID = @docid AND DocID IN
(SELECT DISTINCT pldocument.documentid from pldocument, plperson
WHERE pldocument.permissionlistID = plperson.permissionlistID
AND plperson.personID = @userid)
GO

Using these tables and SQL queries to retrieve the documents that a principal can access is pretty straightforward. It would certainly also be possible to create HTML forms that enable content managers on the B2B Web site to manage the data in these tables using a Web browser.

To extend this scheme so it secures the data that is posted on the B2B Web site from internal information systems, developers will have to find a way to limit the data coming out of those internal information systems based on the permissions for each principal. Depending on the capabilities and limitations of those information systems, this may be easy, or it may be difficult.