Creating a User

The RBS_CreateUser stored procedure will create a new user in the system. The procedure accepts as input parameters all relevant user details, including the user's password (which is passed as a 20-byte binary parameter) and then inserts a new user into the database based on these values. It passes as an output parameter the UserID value for the newly created user.

CREATE PROCEDURE RBS_CreateUser
@loginid varchar(10),
@FirstName varchar(40),
@LastName varchar(40),
@Password binary(20),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(30),
@State varchar(20),
@ZipCode varchar(10),
@EmailAddress varchar(255),
@NewUserId int output
AS
   INSERT INTO Users(loginid, firstname, lastname, password,
                      address1, address2, city, state, zipcode,
                     emailaddress)
               VALUES(@loginid, @FirstName, @LastName, @Password,
                     @Address1, @Address2, @City, @State, @ZipCode,
                      @EmailAddress)

SET @NewUserId = @@IDENTITY

CREATE OR REPLACE PROCEDURE RBS_CreateUser
(
i_loginid IN varchar,
i_FirstName IN varchar,
i_LastName IN varchar,
i_Password IN varchar,
i_Address1 IN varchar,
i_Address2 IN varchar,
i_City IN varchar,
i_State IN varchar,
i_ZipCode IN varchar,
i_EmailAddress IN varchar,
o_NewUserId OUT int
)
AS
BEGIN
INSERT INTO Users(loginid, firstname, lastname, password,
                   address1, address2, city, state, zipcode,
                   emailaddress)
             VALUES(i_loginid, i_FirstName, i_LastName, i_Password,
                  i_Address1, i_Address2, i_City, i_State, i_ZipCode,
                   i_EmailAddress)
;

select userid_seq.currval into o_NewUserId from dual;

END;
/

CREATE PROCEDURE RBS_CreateUser (
   i_LoginID varchar(10),
   i_FirstName varchar(40),
   i_LastName varchar(40),
   i_Password varchar(20),
   i_Address1 varchar(80),
   i_Address2 varchar(80),
   i_City varchar(30),
   i_State varchar(20),
   i_ZipCode varchar(10),
   i_EmailAddress varchar(255),
   OUT o_NewUserId int)
BEGIN
INSERT INTO Users(loginid, firstname, lastname, password,
                   address1, address2, city, state, zipcode,
                   emailaddress)
             VALUES(i_loginid, i_FirstName, i_LastName, i_Password,
                    i_Address1, i_Address2, i_City, i_State, i_ZipCode,
                     i_EmailAddress)
;

   SET o_NewUserID = IDENTITY_VAL_LOCAL();
END

Deleting a User

The procedure responsible for deleting a user from the system is remarkably simple. In SQL Server, it looks like this:

CREATE PROCEDURE RBS_DeleteUser
@UserID int
AS
   DELETE Users WHERE UserId = @UserId

In DB2 and Oracle, it looks like this (bold indicates additions for the Oracle version):

CREATE OR REPLACE PROCEDURE RBS_DeleteUser (i_UserID int)
AS
BEGIN
   DELETE FROM Users WHERE UserID = i_UserID;
END;
/

The cascading delete options you've chosen on the foreign keys will ensure that related data in the database will be deleted as this procedure is executed.

Updating a User

The RBS_UpdateUser stored procedure will modify an existing user record. It takes as parameters the unique identifier for the user and all of that user's information including the password. A common limitation on many Web sites is that the e-mail address of the user is used as the primary key, so users often can't change their own e-mail addresses without creating a new account. This is cumbersome and often annoying to users. In this implementation, neither the login ID nor the e-mail address is restricted, and users can modify both of them as they choose. The key piece of information you use to identify a user in this stored procedure is their automatically assigned numeric ID.

CREATE PROCEDURE RBS_UpdateUser
@UserId int,
@loginid varchar(10),
@Password binary(20),
@FirstName varchar(40),
@LastName varchar(40),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(30),
@State varchar(2),
@ZipCode varchar(10),
@EmailAddress varchar(255)
AS
   UPDATE Users SET
      loginid = @loginid,
      Password = @Password,
      FirstName = @FirstName,
      LastName = @LastName,
      Address1 = @Address1,
      Address2 = @Address2,
      City = @City,
      State = @State,
      ZipCode = @ZipCode,
      EmailAddress = @EmailAddress
   WHERE UserId = @UserID

CREATE OR REPLACE PROCEDURE RBS_UpdateUser
(
   i_UserID int,
   i_LoginID varchar(10),
   i_FirstName varchar(40),
   i_LastName varchar(40),
   i_Password clob(20),
   i_Address1 varchar(80),
   i_Address2 varchar(80),
   i_City varchar(30),
   i_State varchar(20),
   i_ZipCode varchar(10),
   i_EmailAddress varchar(255))
AS
BEGIN
UPDATE Users SET
loginid = i_loginid,
Password = i_Password,
FirstName = i_FirstName,
LastName = i_LastName,
Address1 = i_Address1,
Address2 = i_Address2,
City = i_City,
State = i_State,
ZipCode = i_ZipCode,
EmailAddress = i_EmailAddress
WHERE UserId = i_UserID;
END;
/

Querying Users

In keeping with the naming convention, you know that this particular procedure, RBS_SelectUsers, retrieves more than one user. In this case, it retrieves all of the users in the system in no particular order. Because you don't exactly know what kind of code is being written for this application, we're providing this procedure as part of a set of default services just in case the application needs this to load a cache or some other function.

CREATE PROCEDURE RBS_SelectUsers
AS
   SELECT FirstName, LastName, LoginId, UserId FROM Users
   ORDER BY Lastname, FirstName

CREATE OR REPLACE PACKAGE RBS_SelectUsers_pkg
AS
TYPE
UserCur IS REF CURSOR;
PROCEDURE GetUsers(o_userCur OUT userCur);
END RBS_SelectUsers_pkg
;
/

CREATE OR REPLACE PACKAGE BODY RBS_SelectUsers_pkg
AS
PROCEDURE GetUsers(o_userCur OUT userCur)
IS
BEGIN
OPEN o_userCur FOR
SELECT FirstName, LastName, LoginId, UserId FROM Users;
END Getusers;
END RBS_SelectUsers_pkg;
/

CREATE PROCEDURE RBS_SelectUsers()
RESULT SETS 1
BEGIN
   DECLARE curUsers CURSOR WITH RETURN FOR
      SELECT FirstName, LastName, LoginID, UserID FROM Users
      ORDER BY LastName, FirstName;
   OPEN curUsers;
END

Loading an Individual User

Chances are fairly good that the application will be loading details for a single user at some point. This will more than likely be done right after a user's login/password combination is validated successfully. The assumption of this stored procedure is that the client code has already retrieved a list of user ID or an individual user ID via a login validation procedure. That numeric user ID is passed to this stored procedure as the key to load a user. Based on this validated user ID, the RBS_LoadUser procedure retrieves all details (except the password) from the Users table and returns them in output parameters.

CREATE PROCEDURE RBS_LoadUser
@UserId int,
@Loginid varchar(10) output,
@FirstName varchar(40) output,
@LastName varchar(40) output,
@Address1 varchar(80) output,
@Address2 varchar(80) output,
@City varchar(30) output,
@State varchar(2) output,
@ZipCode varchar(10) output,
@EmailAddress varchar(255) output
AS
   SELECT
      @LoginId = loginid,
      @FirstName = FirstName,
      @LastName = LastName,
      @Address1 = Address1,
      @Address2 = Address2,
      @City = City,
      @State = State,
      @ZipCode = ZipCode,
      @EmailAddress = EmailAddress
   FROM Users
   WHERE
      UserId = @UserId

CREATE OR REPLACE PROCEDURE RBS_LoadUser
(
o_UserId int,
o_Loginid OUT varchar,
o_FirstName OUT varchar,
o_LastName OUT varchar,
o_Address1 OUT varchar,
o_Address2 OUT varchar,
o_City OUT varchar,
o_State OUT varchar,
o_ZipCode OUT varchar,
o_EmailAddress OUT varchar
)
AS
BEGIN
SELECT loginid, FirstName, LastName, Address1,
       Address2, City, State, ZipCode, EmailAddress
INTO
       o_LoginId, o_FirstName, o_LastName,o_Address1,
       o_Address2, o_City, o_State, o_ZipCode,
       o_EmailAddress
FROM Users
WHERE UserId = o_UserId;
END;
/

CREATE PROCEDURE RBS_LoadUser (
   i_UserID int,
   OUT o_LoginID varchar(10),
   OUT o_FirstName varchar(40),
   OUT o_LastName varchar(40),
   OUT o_Address1 varchar(80),
   OUT o_Address2 varchar(80),
   OUT o_City varchar(30),
   OUT o_State varchar(20),
   OUT o_ZipCode varchar(10),
   OUT o_EmailAddress varchar(255))
BEGIN
SELECT loginid, FirstName, LastName, Address1,
       Address2, City, State, ZipCode, EmailAddress
INTO
       o_LoginId, o_FirstName, o_LastName,o_Address1,
       o_Address2, o_City, o_State, o_ZipCode,
       o_EmailAddress
FROM Users
WHERE UserId = o_UserId;
END;
/

Validating User Login

The following stored procedure, RBS_ValidateLogin, will validate a user's login and password combination. The LoginID and Password are sent as input parameters to the stored procedure. The output parameters are the FirstName and LastName. In addition, the stored procedure returns a numeric value that indicates whether the login was successful. Again, note that the user's password should be encrypted before it's even passed as a parameter to the database. You first perform a SELECT statement, looking to retrieve the row from the Users table that matches the LoginID and Password. If no such row exists, the login fails and the stored procedure returns -1. Otherwise, the stored procedure returns the numeric ID of the user, as well as that user's first and last name. The numeric ID of the user must be retained by the client application in some way because it's used as the key parameter in many other procedures.

It is fairly common practice for Web sites and applications to provide some kind of greeting in the application to display to authenticated users. Rather than require the application to make an additional round trip to the database to retrieve the first and last name of a user after they've logged in, you simply return that information as output parameters from this procedure for optimal performance.

CREATE PROCEDURE RBS_ValidateLogin
@loginid varchar(10),
@password binary(20),
@FirstName varchar(40) output,
@LastName varchar(40) output
AS

   DECLARE @UserId int

   SELECT @UserId = UserId, @FirstName = firstname, @LastName = lastname
   FROM Users
   WHERE loginid = @loginid AND password = @password

   IF @UserId IS NOT NULL
      RETURN @UserId
   ELSE
      RETURN -1

CREATE OR REPLACE PROCEDURE RBS_ValidateLogin
(
i_loginid varchar,
i_password varchar,
o_FirstName OUT varchar,
o_LastName OUT varchar,
o_UserID OUT int
)
AS
BEGIN

SELECT UserID, firstname, lastname
INTO o_UserID, o_FirstName, o_LastName
FROM Users
WHERE loginid = i_loginid
AND password = i_password;

IF o_UserId IS NULL
THEN
      o_userid := -1;
END IF;
END;
/

CREATE OR REPLACE FUNCTION RBS_ValidateLogin_FUNC
(
i_loginid varchar,
i_password raw,
o_FirstName OUT varchar,
o_LastName OUT varchar
)
RETURN int
AS
o_UserID int;
BEGIN

SELECT UserID, firstname, lastname
INTO o_UserID, o_FirstName, o_LastName
FROM Users
WHERE loginid = i_loginid
AND password = i_password;

IF o_UserId IS NOT NULL
THEN
RETURN o_userID;
ELSE
Return -1;
END IF;
END;
/

CREATE PROCEDURE RBS_ValidateLogin
(
i_LoginID varchar(10),
i_password clob(20),
OUT o_FirstName varchar(40),
OUT o_LastName varchar(40))
BEGIN
   DECLARE l_UserID int;
   SELECT UserID, FirstName, LastName
   INTO l_UserID, o_FirstName, o_LastName
   FROM Users
   WHERE LoginID = i_LoginID;

   IF l_UserID IS NOT NULL THEN
      RETURN l_UserID;
   ELSE
      RETURN -1;
   END IF;
END

Creating a Role

The RBS_CreateRole procedure creates a new role with the supplied description and returns the identity of the newly inserted row.

In SQL Server, you use the @@IDENTITY constant to return the RoleID for the newly inserted row:

CREATE PROCEDURE RBS_CreateRole
@Description varchar(50),
@NewRoleId int output
AS
   INSERT INTO Roles(Description) VALUES(@Description)

   SET @NewRoleId = @@IDENTITY

In addition to the minor differences noted earlier, in place of the @@IDENTITY constant, for Oracle you use the following:

select roleid_seq.currval into o_NewRoleId from dual;

And for DB2 you use the following:

SET o_NewRoleID = IDENTITY_VAL_LOCAL();

Deleting a Role

This procedure deletes an existing role. Note that there's no additional checking to make sure the role exists before it's deleted. Foreign keys and cascading rules will allow all data related to the role to be automatically deleted when the role is deleted. The procedure will simply return without error if an attempt to delete a nonexistent role is made:

CREATE PROCEDURE RBS_DeleteRole
@RoleId int
AS
   DELETE Roles WHERE RoleId = @RoleId

For DB2 and Oracle, you have the following:

CREATE OR REPLACE PROCEDURE RBS_DeleteRole (i_RoleID int)
AS
BEGIN
   DELETE FROM Roles WHERE RoleID = i_RoleID;
END;
/

Updating a Role

This procedure will modify the description of an existing role. It takes as input parameters the unique identifier for the role and the description. In SQL Server, you have the following:

CREATE PROCEDURE RBS_UpdateRole
@RoleId int,
@Description varchar(40)
AS
   UPDATE Roles SET Description = @Description WHERE RoleId = @RoleId

In DB2 the code looks like this (for Oracle, add the bold code and don't specify the size of the description parameter):

CREATE OR REPLACE PROCEDURE RBS_UpdateRole
(i_RoleID int, i_Description varchar(40))
AS
BEGIN
   UPDATE Roles
   SET Description = i_Description
   WHERE RoleID = i_RoleID;
END;
/

Loading a Role

This procedure will load the description of an existing role into an output parameter. In SQL Server, use the following:

CREATE PROCEDURE RBS_LoadRole
@RoleId int,
@Description varchar(40) output
AS
   SELECT @Description = Description
   FROM Roles
   WHERE RoleId = @RoleId

In DB2 (for Oracle, add the bold code, remove the parameter size, and move the OUT keyword to after the parameter name):

CREATE OR REPLACE PROCEDURE RBS_LoadRole
(
i_RoleId int,
OUT o_Description varchar(40)
)
AS
BEGIN
   SELECT Description
   into o_Description
   FROM Roles
   WHERE RoleId = i_RoleId;
END;
/

Selecting Roles

The following procedure will retrieve a list of all of the roles in the database, sorted by the name of the role. In SQL Server, use the following:

CREATE PROCEDURE RBS_SelectRoles
AS
   SELECT RoleId, Description FROM Roles ORDER BY Description

In Oracle, as for the RBS_SelectUsers procedure, you load the rows into a cursor and handle this using a package:

CREATE OR REPLACE PACKAGE RBS_SelectRoles_pkg
AS
TYPE
RoleCur IS REF CURSOR;
PROCEDURE GetRoles(o_roleCur OUT roleCur);
END RBS_SelectRoles_pkg
;
/

CREATE OR REPLACE PACKAGE BODY RBS_SelectRoles_pkg
AS
PROCEDURE GetRoles(o_roleCur OUT roleCur)
IS
BEGIN
OPEN o_roleCur FOR
SELECT RoleId, Description FROM Roles ORDER BY Description;
END GetRoles;
END RBS_SelectRoles_pkg;
/

In DB2, you have this:

CREATE PROCEDURE RBS_SelectRoles()
RESULT SETS 1
BEGIN
   DECLARE curRoles CURSOR WITH RETURN FOR
      SELECT RoleID, Description FROM Roles
      ORDER BY Description;
   OPEN curRoles;
END

Creating a Permission

This procedure will create a new permission. It takes a description as an input parameter, and the new ID of the permission created is placed in an output parameter. The SQL Server script is as follows:

CREATE PROCEDURE RBS_CreatePermission
@Description varchar(50),
@NewPermissionId int output
AS

   INSERT INTO Permissions(Description) VALUES(@Description)

   SET @NewPermissionId = @@IDENTITY

You can convert this to DB2 and Oracle in the same manner as you did for the RBS_CreateUser and RBS_CreateRoles procedures.

Updating a Permission

The following procedure will modify the description and category of an existing permission. Some applications may require that you not be able to change the category of a permission once it has been created, but you'll leave it up to the application to enforce that business rule because it won't violate any of the data rules to allow that. For SQL Server, you have the following:

CREATE PROCEDURE RBS_UpdatePermission
@PermissionId int,
@Description varchar(50),
@CategoryId int
AS
   UPDATE Permissions
   SET
      Description = @Description,
      CategoryId = @CategoryId
   WHERE

      PermissionId = @PermissionId;

In DB2, use the following (for Oracle, add the bold code and remove the size attribute from the i_Description parameter):

CREATE OR REPLACE PROCEDURE RBS_UpdatePermission
(i_PermissionID int,
i_Description varchar(50),
i_CategoryID int
)
AS
BEGIN
   UPDATE Permissions
   SET Description = i_Description, CategoryID = i_CategoryID
   WHERE PermissionID = i_PermissionID;
END;
/

Deleting a Permission

The following procedure is responsible for removing a permission from the database. The cascading rules on the foreign keys will take care of removing any related data for you. In SQL Server, the code is as follows:

CREATE PROCEDURE RBS_DeletePermission
@PermissionId int
AS
   DELETE Permissions
   WHERE
      PermissionId = @PermissionId;

Again, here is the DB2/Oracle code:

CREATE OR REPLACE PROCEDURE RBS_DeletePermission
(
i_PermissionID int
)
AS
BEGIN
   DELETE FROM Permissions
   WHERE PermissionID = i_PermissionID;
END;
/

Loading a Permission

The following procedure will load an individual permission and return its data in output parameters. It makes use of an inner join to return the name and description of the category to which the permission belongs as well as the category ID:

CREATE PROCEDURE RBS_LoadPermission
@PermissionId int,
@Description varchar(50) output,
@CategoryName varchar(50) output,
@CategoryId int output
AS
   SELECT @Description = p.Description,
           @CategoryId = p.CategoryId,
          @CategoryName = pc.Description
   FROM
    Permissions p
    INNER JOIN PermissionCategories pc
    ON p.CategoryId = pc.CategoryId
    WHERE p.PermissionId = @PermissionId;

In DB2, you have the following:

CREATE PROCEDURE RBS_LoadPermission
(i_PermissionID int,
OUT o_Description varchar(50),
OUT o_CategoryName varchar(50),
OUT o_CategoryID int)
BEGIN
   SELECT p.Description, p.CategoryID, pc.Description
   INTO o_Description, o_CategoryID, o_CategoryName
   FROM Permissions p
   INNER JOIN PermissionCategories pc
   ON p.CategoryID = pc.CategoryID;
END

In Oracle, the query has been written to run on all Oracle versions (only 9i and later support the INNER JOIN syntax):

CREATE OR REPLACE PROCEDURE RBS_LoadPermission
(
i_PermissionId int,
o_Description OUT varchar,
o_CategoryName OUT varchar,
o_CategoryId OUT int
)
AS
  BEGIN
    SELECT p.Description, p.CategoryId, pc.Description
    INTO o_Description, o_CategoryId, o_CategoryName
    FROM Permissions p, PermissionCategories pc
    WHERE p.CategoryId = pc.CategoryId
    AND p.PermissionId = i_PermissionId;
  END;
/

Querying Permissions

The following stored procedure will return a list of permissions. If a category is supplied, then the list of permissions will be a list of permissions that belong to that category. If a -1 is supplied in place of a category ID, then the list of permissions returned will be unfiltered. All results are ordered by the category name and then by the permission name. In SQL Server, the code is as follows:

CREATE PROCEDURE RBS_SelectPermissions
@CategoryId Int
AS
   IF @CategoryId = -1
      SELECT p.PermissionId, p.CategoryId, p.Description,
              pc.Description as CategoryName
      FROM Permissions p
      INNER JOIN PermissionCategories pc
         ON p.CategoryId = pc.CategoryId
      ORDER BY pc.Description, p.Description
   ELSE
      SELECT p.PermissionId, p.CategoryId, p.Description,
              pc.Description as CategoryName
      FROM Permissions p
      INNER JOIN PermissionCategories pc
         ON p.CategoryId = pc.CategoryId
      WHERE p.CategoryId = @CategoryId
      ORDER BY p.Description;

In DB2, you load the results into a cursor as follows:

CREATE PROCEDURE RBS_SelectPermissions(i_CategoryID int)
RESULT SETS 1
BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
   SELECT p.PermissionID, p.CategoryID, p.Description,
           pc.Description AS CategoryName
      FROM Permissions p
      INNER JOIN PermissionCategories pc
      ON p.CategoryID = pc.CategoryID
      ORDER BY pc.Description, p.Description;
DECLARE cursor2 CURSOR WITH RETURN FOR
   SELECT p.PermissionID, p.CategoryID, p.Description,
           pc.Description AS CategoryName
      FROM Permissions p
      INNER JOIN PermissionCategories pc
      ON p.CategoryID = pc.CategoryID
      WHERE p.CategoryID = i_CategoryID
      ORDER BY p.Description;
IF i_CategoryID = -1 THEN
   OPEN cursor1;
ELSE
   OPEN cursor2;
END IF;
END

In Oracle, you have the usual package specification and body:

CREATE OR REPLACE PACKAGE RBS_SelectPerms_pkg
AS
TYPE permCur IS REF CURSOR;
PROCEDURE Getperms(i_CategoryID IN int, o_permCur OUT permCur);
END RBS_SelectPerms_pkg
;
/

CREATE OR REPLACE PACKAGE BODY RBS_SelectPerms_pkg
AS
PROCEDURE Getperms
(
i_CategoryID IN int, o_permCur OUT permCur
)
IS
BEGIN
IF i_CategoryId = -1
THEN
OPEN o_permCur FOR
SELECT p.PermissionId, p.CategoryId, p.Description, pc.Description as
CategoryName
FROM Permissions p, PermissionCategories pc
WHERE p.CategoryId = pc.CategoryId
ORDER BY pc.Description, p.Description;

ELSE
OPEN o_permCur FOR
SELECT p.PermissionId, p.CategoryId, p.Description, pc.Description as
CategoryName
FROM Permissions p, PermissionCategories pc
WHERE p.CategoryId = pc.CategoryId
AND p.CategoryId = i_CategoryId
ORDER BY p.Description;
END IF;
END Getperms;
END RBS_SelectPerms_pkg;
/

Setting the Role's Permission Mode

The following procedure will create an entry in the RolePermission table. It assigns a permission to a role with a particular numeric mode value. The first thing you do is check to see if a mapping already exists. If it does, then you'll update that mapping with the new mode value. If it doesn't exist, then you'll create a new mapping with the supplied mode value. Note that the change to the role permission mode isn't additive. Therefore, the client application will be responsible for determining the previous value and adding access modes if that's the desired behavior. However, this type of stored procedure lends itself well to an application that provides a form with a list of data-bound checkboxes that can be submitted to invoke this procedure:

CREATE PROCEDURE RBS_SetRolePermissionMode
@PermissionId int,
@RoleId int,
@Mode int
AS
   DECLARE @Count int

   SELECT @Count = Count(PermissionId) FROM RolePermission WHERE
      RoleId = @RoleId AND PermissionId = @PermissionId

   IF @Count = 0
      INSERT INTO RolePermission(RoleId, PermissionId, Mode)
      VALUES(@RoleId, @PermissionId, @Mode)
   ELSE
      UPDATE RolePermission
      SET
         Mode = @Mode
      WHERE
         PermissionId = @PermissionId AND
         RoleId = @RoleId;

Querying the Permissions Assigned to a Role

The following procedure will return a list of permissions that are assigned to a given role, which is ordered by the name of the permission category and then by the name of the permission to produce an alphabetic list of permissions:

CREATE PROCEDURE RBS_SelectRolePermissions
@RoleId int
AS
   SELECT p.PermissionId, p.Description, pc.Description as CategoryName, rp.Mode
   FROM Permissions p
   INNER JOIN PermissionCategories pc
   ON p.CategoryId = pc.CategoryId
      INNER JOIN RolePermission rp
      ON p.PermissionId = rp.PermissionId
   WHERE rp.RoleId = @RoleId
   ORDER BY pc.Description, p.Description;

Querying the User's Effective Permission List

This next procedure is essentially the entire meat of the RBS system. The bottom line is that roles exist for organization and maintenance purposes. Although it might be nice to display to the user or to administrators which roles are assigned to a user, that isn't the data you need.

The data you really need is a list of all of the tasks that a given user can perform. This list is a composition of all of the permissions granted to all of the roles that are assigned to that user and the modes associated with that user.

There's a slight twist you need to be aware of before you start writing your procedure, though. Think about this example: A user is a member of the Network Administrators role, and they're also a member of the Computer Administrators role. The Network Administrators role grants complete and full access to Users, and the Computer Administrators role grants only read access on Users. Which access does the user have if they've been granted two different modes of the same permission? Thankfully, because you chose to use simple integers rather than a pile of columns to indicate all of your mode flags, your choice is easy. You can make it additive. The result will be the most flags available. In other words, all you have to do is select the MAX() of the mode of each permission in a GROUP BY clause:

CREATE PROCEDURE RBS_SelectEffectivePermissions
@UserId int
AS

   SELECT RolePermission.PermissionId,
          Max(Mode) as Mode,
          Description
   FROM RolePermission
      INNER JOIN UserRoles On RolePermission.RoleId = UserRoles.RoleId
      INNER JOIN Permissions ON RolePermission.PermissionId =
   Permissions.PermissionId
      WHERE UserRoles.UserId = @UserId
      GROUP BY RolePermission.PermissionId, Permissions.Description;

Figure 15-2 shows an execution of the stored procedure (from within SQL Server's query tool), retrieving the list of effective permissions belonging to user 1, a full administrator in the sample database.

Figure 15-2: Retrieving a list of effective permissions for User 1

Figure 15-3 shows an execution of selecting the effective permission list for the second user in the database, which does not have quite as much power as the administrator (user 1).

Figure 15-3: Selecting the effective permissions for User 2

To rehash what we were illustrating about bitmasking, a Mode value of 29 given the bitmask key indicates that Create, Read, Update, and Delete are all true, and a Mode value of 4 indicates that only Read is available on the user's permission, as shown in Table 15-8.

Querying a User's Assigned Roles

This procedure allows your application to retrieve all of the roles that have been assigned to a given user and whether that user is allowed to grant those roles to others (assuming they can modify user permissions to begin with):

CREATE PROCEDURE RBS_SelectUserRoles
@UserId int
AS

   SELECT UserRoles.RoleId, Description, CanGrant FROM UserRoles
   INNER JOIN Roles ON UserRoles.RoleId = Roles.RoleId
   WHERE UserId = @UserId
   ORDER BY Description;

Figure 15-4 illustrates how you execute the stored procedure to retrieve the list of roles that are assigned to a user and whether the user can grant that role to other users (assuming they can modify user security settings).

Figure 15-4: Retrieving the list of roles assigned to a user