Stage 1: Creating the Database Tables

Now that we've covered the basics of permission modes and discussed the naming convention, let's get to the meat of creating this database. Figure 15-1 shows what the whole thing is going to look like.

Figure 15-1: The RBS database

The first thing to do is identify the most basic type of data to be stored in the database. As you can see, there are four key storage tables:

Users: This stores all the relevant information about your application users.
Roles: Each role defines a specific collection of permissions (a typical role might be Administrator).
Permissions: A permission is a point of access into the system (a typical permission might be Document).
PermissionCategories: This allows you to define certain types of permission, such as Tax Documents.

There are also two mapping tables:

UserRoles: This allows you to map roles to users.
RolePermission: This allows you to map permissions to roles.

In this case, everything is going to revolve around the Users table, so let's determine what kind of information you need to store about users.

Users

In this example, you're targeting the database at a Web site application, so you'll need some kind of unique identifier for the user, as well as a login/password combination that you can use to authenticate the user. In addition, you need to keep some contact information on the user. In summary, you'll need to store the following:

Name
Address
Login ID
Password
E-mail address or other contact details

In this example, you're not going to support international addresses because the topic of storing international addresses in a database is probably big enough for its own chapter. The only tricky part of this is the password. How do you store the password in such a way that the following conditions are true:

The customers are guaranteed privacy (your employees can't get at customer passwords).
There's privacy over the wire so that communication between the application and the database server is never disclosing a private password?

Well, you could use encryption or some form of hashing. When designing the database, you don't need to worry too much about how the programmers accomplish the hashing or encryption. You'll simply be storing raw binary bytes for the user's password. If your database server doesn't have a convenient way of storing raw binary data, you can simply use a string column (such as VARCHAR) and store the "hexified" ASCII version of the binary data. The assumption for this case study is that the application will be encrypting the password into some arrangement of binary data. That binary data is the only thing that the database is ever going to see. The user validation attempts will be performed against encrypted passwords, and users will be created and modified with encrypted passwords. This provides the maximum level of security for the user because you're adhering to the following rule: Private data is never transmitted between applications, application components, or physical environments without first being encrypted.

Creating the Users Table

With the previous in mind, let's look at the design of the Users table (see Table 15-2).

Table 15-2: The *Users* Table
Name	Data Type	Size	Description
UserId	INT	4	This is the unique numeric ID of the user. It is an ID field (auto-incrementing) and the primary key.
FirstName	VARCHAR	40	The first name of the user.
LastName	VARCHAR	40	The last name of the user.
LoginId	VARCHAR	10	The login ID of the user.
Password	BINARY	20	The password of the user. To provide maximum security, it's stored in its encrypted format as a set of raw bytes.
Address1	VARCHAR	80	First line of the user's address.
Address2	VARCHAR	80	Second (optional) line of the user's address.
City	VARCHAR	30	User's city.
State	VARCHAR	2	User's state (two-character abbreviation).
ZipCode	VARCHAR	10	User's ZIP code (dashes removed from extended ZIP codes).
EmailAddress	VARCHAR	255	The user's e-mail address.

There really aren't any surprises or complex columns in the Users table, with the exception of the Password column, which is stored in binary. The thing to keep in mind is that SQL allows you to compare a set of raw bytes just as easily as you can compare strings, so you won't have to do anything special to provide authentication services for your users with encrypted passwords. Oracle also allows the comparison of raw binary data (in fact, all string comparisons in Oracle end up as raw binary comparisons eventually). If your underlying database doesn't support binary, then you can store the data as a string as long as you can ensure that the database doesn't attempt to convert byte values more than 127 into something printable.

Let's look at the SQL script for creating the Users table in SQL Server, Oracle, and DB2.

SQL Server

The script should look fairly straightforward to you if you've worked through Chapter 12, "Working with Database Objects." UserID is the primary key column, so you define it as an identity column, with values starting at one and incrementing by one each time:

CREATE TABLE Users (
   UserId int IDENTITY (1, 1) NOT NULL ,
   FirstName varchar (40) NOT NULL ,
   LastName varchar (40) NOT NULL ,
   LoginId varchar (10) NOT NULL ,
   Password binary (20) ,
   Address1 varchar (80) NOT NULL ,
   Address2 varchar (80) NULL ,
   City varchar (30) NOT NULL ,
   State varchar (2) NOT NULL ,
   ZipCode varchar (10) NOT NULL ,
   EmailAddress varchar (255) NOT NULL
);

You then define UserID as the primary key column:

ALTER TABLE Users ADD
   CONSTRAINT PK_Users PRIMARY KEY
   (
      UserId
   );

Note

In the code download for this chapter, there's a separate script that creates all necessary constraints for every table.

Oracle

The code to create the UserID table in Oracle is pretty similar. Oracle doesn't use the binary data type, so we've stored the password in a varchar field although you could also use a raw type:

CREATE TABLE Users
(
   UserId int NOT NULL ,
   FirstName varchar (40) NOT NULL ,
   LastName varchar (40) NOT NULL ,
   LoginId varchar (10) NOT NULL ,
   Password varchar(20),
   Address1 varchar (80) NOT NULL ,
   Address2 varchar (80) NULL ,
   City varchar (30) NOT NULL ,
   State varchar (20) NOT NULL ,
   ZipCode varchar (10) NOT NULL ,
   EmailAddress varchar (255) NOT NULL
);
ALTER TABLE Users
   ADD CONSTRAINT PK_Users PRIMARY KEY (UserID);

We've used the varchar type to maintain as much consistency in the code as possible. This code will work on Oracle, but you should note that the varchar is a deprecated type and it's advised that you use varchar2 instead.

You need to create a sequence and a trigger, as described in Chapter 12, "Working with Database Objects," to automatically supply unique values to the primary key field:

CREATE SEQUENCE userid_seq;

CREATE OR REPLACE TRIGGER Users_AUTONUMBER
BEFORE INSERT ON Users
FOR EACH ROW
BEGIN
SELECT userid_seq.NEXTVAL
INTO :NEW.UserID FROM DUAL;
END;
/

DB2

Again, the code for DB2 is similar. In the absence of a binary data type, you store the password in a simple varchar field:

CREATE TABLE Users
(
   UserId int GENERATED ALWAYS AS IDENTITY,
...<etc.>...
   Password varchar(20),
...<script cropped>...;
ALTER TABLE Users
   ADD CONSTRAINT PK_Users PRIMARY KEY (UserID);

Roles

With the Users table designed in the database, you can move on to other things. Now we're going to talk about roles. What is a role? A role is just a placeholder for a collection of permissions. You also know that you want to keep this database in the appropriate normal form to keep it scalable and easily manageable, so you're not going to use columns to map roles to permissions. Instead, you just need a single table to maintain roles (you'll learn about relating roles and permissions a little later).

For now, let's figure out what information you need to store about a role:

Identifier
Description

That's about it. Really all you need is the unique identity of the role and some verbose description of the role such as "administrator" or "middle management."

Creating the Roles Table

Let's look at the design for the Roles table (see Table 15-3).

Table 15-3: The *Roles* Table
Name	Data Type	Size	Description
RoleId	INT	4	The primary key of the Roles table, the unique numeric identifier for a role
Description	VARCHAR	50	The description of the role

The following is the SQL script used to generate the Roles table in SQL Server (this is taken from SQL by right-clicking the database, choosing All Tasks, and then selecting Generate Script):

CREATE TABLE Roles (
   RoleId int IDENTITY (1, 1) NOT NULL ,
   Description varchar (50) NOT NULL
);
ALTER TABLE Roles
   ADD CONSTRAINT PK_Roles PRIMARY KEY (RoleID);

In DB2, you create the identity column as follows:

RoleID int GENERATED ALWAYS AS IDENTITY,

In Oracle you have to create a sequence and a trigger, exactly as described in the "Creating the Users Table" section.

Permissions

A permission is a point of access into the system. Remember that we're making a big distinction between a permission and a permission mode. An example of a permission might be Document. When that permission is granted to a role, it might be granted with a mode indicating read/update/delete access.

Therefore, with that distinction made, the following is really the only information you need to store about permissions:

Identifier
Description

However, there's a bit of extra information that you can add to a permission. In complex systems with many different kinds of data and resources that need protecting, managing all of the different permissions can be a complicated task. To make things easier for administrators, you're going to provide the ability to categorize permissions. Therefore, you're going to have something called a permission category. This also only needs a description because the majority of the work comes from the mapping of a permission to its parent category. A permission category might group sets of permissions with the type of data they relate to, such as Files, Tax Data, Legal Data, and so on.

Creating the PermissionCategories Table

The PermissionCategories table is a simple table that is defined in Table 15-4.

Table 15-4: The *PermissionCategories* Table
Name	Data Type	Size	Description
CategoryId	INT	4	This is the primary key and identity field. Unique numeric identifier for a permission category.
Description	VARCHAR	50	Description of the permission category.

The SQL Server script for the PermissionCategories table is as follows:

CREATE TABLE PermissionCategories (
   CategoryId int IDENTITY (1, 1) NOT NULL ,
   Description varchar (50) NOT NULL
);

ALTER TABLE PermissionCategories
   ADD CONSTRAINT PK_PermCategories PRIMARY KEY (CategoryID);

By now you can easily convert the table code to DB2 and Oracle, and then you can create the appropriate sequence and trigger for Oracle.

Creating the Permissions Table

The Permissions table is defined in Table 15-5.

Table 15-5: The *Permissions* Table
Name	Data Type	Size	Description
PermissionId	INT	4	This is the primary key and identity field. Unique numeric identifier for a permission.
Description	VARCHAR	50	Description of the permission.
CategoryId	INT	4	Foreign key (PermissionCategories). This is the category to which the permission belongs.

The SQL Server script for the Permissions table is as follows:

CREATE TABLE Permissions (
   PermissionId int IDENTITY (1, 1) NOT NULL ,
   Description varchar (50) NOT NULL ,
   CategoryId int NOT NULL
)

In addition to the primary key, this time you also create a foreign key on the CategoryID column, which references the same column in PermissionCategories:

ALTER TABLE Permissions
   ADD CONSTRAINT PK_Permissions PRIMARY KEY (PermissionID);

ALTER TABLE Permissions
   ADD CONSTRAINT FK_Perms_PermCats FOREIGN KEY (CategoryID)
   REFERENCES PermissionCategories (CategoryID)
     ON DELETE CASCADE;

Notice that you enforce cascading deletes in the relationship between this table and the PermissionCategories table so that on a delete operations, all related items are removed.

Mapping Tables

So far, we've covered the design for the Users, Roles, Permissions, and PermissionCategories tables. Each of these has been basically stand-alone data with no relation to any other data in the system. Now we're going to cover the tables you need to create the mappings and relationships that will finish up the database design for the RBS system.

Creating the UserRoles Table

The first thing you're going to look at is the ability to assign roles to users. Table 15-6 defines the UserRoles table.

Table 15-6: The *UserRoles* Table
Name	Data Type	Size	Description
UserId	Int	4	User ID to which the role is mapped. This is part of the primary key.
RoleId	Int	4	Role ID assigned to the user.
CanGrant	BIT	1	Indicates whether that user may grant the role to others if they also have the ability to edit security within the application.

Here's the script for the UserRoles table:

CREATE TABLE UserRoles (
   UserId int NOT NULL ,
   RoleId int NOT NULL ,
   CanGrant bit NOT NULL
);

For Oracle and DB2, you can use a smallint data type for the CanGrant column in place of bit.

Note

As we explain shortly, you actually want the CanGrant column to take a default value of zero. In DB2, it's best to do that when you actually create the table.

You create the primary key as usual. Note that this time, though, you're using a composite primary key. The values in the UserID and RoleID columns together will uniquely identify each row:

ALTER TABLE UserRoles
   ADD CONSTRAINT PK_UserRoles PRIMARY KEY (UserID, RoleID);

This next ALTER command creates two foreign keys on the UserRoles table. One key references RoleID from the Roles table, and the other key references the UserID from the Users table. Because this table has foreign keys pointing at both the User and Role table, it should be impossible to create a mapping between any role or user that doesn't exist. The database will also not allow the removal of a user or a role that contains a mapping in this table. The following is the code for Oracle and DB2:

ALTER TABLE UserRoles
   ADD CONSTRAINT FK_UserRoles_Roles FOREIGN KEY (RoleID)
   REFERENCES Roles (RoleID) ON DELETE CASCADE
   ADD CONSTRAINT FK_UserRoles_Users FOREIGN KEY (UserID)
   REFERENCES Users (UserID) ON DELETE CASCADE;

The code for SQL Server is only slightly different:

ALTER TABLE UserRoles
   ADD CONSTRAINT FK_UserRoles_Roles FOREIGN KEY (RoleId)
   REFERENCES Roles (RoleId) ON DELETE CASCADE,
   CONSTRAINT FK_UserRoles_Users FOREIGN KEY
   (UserId) REFERENCES Users (UserId) ON DELETE CASCADE;

You might've noticed the CanGrant flag on this mapping table. One thing it was necessary to include in the RBS was the ability to prevent administrators from giving out permissions they're not allowed to give. For example, let's say an administrator has the ability to modify users, but they don't have the Middle Management role. The system needs to be able to make sure that the user can't grant roles they aren't supposed to be granting as a way of circumventing the security system. The default data would be set up to allow the System user to grant all roles, with the regular administrator allowed to grant everything but the System role. It can be used to create the equivalent of a Unix root user or the SQL Server sa user.

So, let's now alter the CanGrant column so that it takes a default value of zero. In SQL Server, you do it like this:

ALTER TABLE UserRoles ADD
   CONSTRAINT DF_UserRoles_CanGrant DEFAULT (0) FOR CanGrant;

In Oracle, you can use this:

ALTER TABLE UserRoles
   MODIFY (CanGrant smallint DEFAULT 0);

In DB2, you create the DEFAULT constraint when you create the table:

CREATE TABLE UserRoles (
   UserID int NOT NULL,
   RoleID int NOT NULL,
   CanGrant smallint NOT NULL DEFAULT 0
);

Creating the RolePermission Table

The next table, RolePermission, takes care of assigning permissions to roles, the guts of allowing users to perform any secured task in the application (see Table 15-7). Obviously, you need to store the RoleID and the PermissionID. This is also where you're going to make use of the bitmasked access mode discussed earlier in the chapter, so you need a field to contain all 32-bits of the bitmask flags.

Table 15-7: The *RolePermission* Table
Name	Data Type	Size	Description
RoleId	Int	4	This is the role ID to which the permission is mapped.
PermissionId	INT	4	This is the permission ID mapped to the role.
Mode	INT	4	This indicates how this permission is mapped to the role. This is a bitmasked value and can contain multiple flags.

This is the script for the RolePermission table:

CREATE TABLE RolePermission (
   RoleId int NOT NULL ,
   PermissionId int NOT NULL ,
   Mode int NOT NULL
);

Interestingly, Mode is a reserved word in Oracle, so you need to use a different column name (such as PermMode).

You define a composite primary key:

ALTER TABLE RolePermission
   ADD CONSTRAINT PK_RolePermission
   PRIMARY KEY (RoleID, PermissionID);

Much like for the previous mapping table, the RolePermission table maps roles to permissions. As such, you create a foreign key referencing the Permissions table (on the RolePermissionID column) and another foreign key on the Roles table (on the RolesID column). For Oracle and DB2, you use the following:

ALTER TABLE RolePermission
   ADD CONSTRAINT FK_RolePermission_Permissions
     FOREIGN KEY(PermissionId)
     REFERENCES Permissions (PermissionId
       ON DELETE CASCADE
   ADD CONSTRAINT FK_RolePermission_Roles
     FOREIGN KEY (RoleId)
     REFERENCES Roles (RoleId)
       ON DELETE CASCADE;

For SQL Server, simply replace the second ADD keyword with a comma.

Now that you have the tables taken care of, let's take a look at the stored procedures that are going to use these table and relations.

Starting Again

If at any point you want to drop all of the tables and start over, the following is the script that will do it:

DROP TABLE UserRoles;
DROP TABLE Users;
DROP TABLE RolePermisison;
DROP TABLE Roles;
DROP TABLE Permissions;
DROP TABLE Permissioncategories;