User Management in Software Systems Using a SQL Server Database

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Environment: SQL, databases

Introduction

In a software system, there are many people who use it. Let’s say that, in an inventory system, there will be storekeepers, accountants, managers, and supervisors. Depending on their responsibilities, each user should be allowed to use limited options of the system.

System Failure

I have seen many software systems without trustee options so that any user can use any option at his will. For example, a storekeeper can order goods and an account can alter the entered quantity in GRNs! Ultimately, there is no one to take responsibility.

In some software systems, these trusted options are hard coded; therefore, the end user can’t change them. Whenever new options are added or a customer requests a change in his user rights, the entire system should be changed and to distributed!

There are some systems, not very user friendly. In this article, I will discuss how to develop user-friendly, methodical trustee options.

Using a SQL Server Database

First, we divided all the system options into categories. In our example, the inventory system categories will be the following:

MAINCATEGORY Table Format

MAINCATEGORY Sample Data

Next, we have to define subcategories for the each main categories. Let’s take main category number 3 (Good Received Notes).

SUBCATEGORY Table Format

MAINCATEGORY Sample Data

Likewise, we can define subcategories for each and every main category. Now, we come to the users. Here I discuss simple user rights methods.

You can extend this to a group-level, user rights method. We will have two tables for users. One is to keep user details, and the other to keep user rights.

USER Table Format

USERRIGHTS Table Format

It is obvious that the user table keeps general user data. The USERRIGHTS table is used to keep the rights data for each category. Say, for a user accountant, we need to give 1,3,6 for the GRN main category. Then, rightsBMP will be = (1^2 + 3^2 + 6^2) = 1 + 8 + 64 = 73

This will be a unique number for 1,3,6 options. Likewise, we can define a unique BMP for all the rights options!

Now, the method is defined. So, we need to implement this in SQL Server by using SQL Server tables, views, and stored procedures. We have already defined tables; now we have to write the stored procedure.

1. When we need to know whether a particular user has rights for some operation. Say, the accountant user has rights on delete GRN.

   Stored Procedure: GetUserRights

   Parameters: UserID, CatID, SubID

   Return 1/0

   /*
   **This procedure will return whether the user has rights.
   **if 1, the user has rights for the given rights category
   **if 0, the user does not have rights for the given rights
   **category given option
   */
   
   CREATE PROCEDURE [dbo].[CheckUserRights]
   
   @UserID nvarchar(20),@CatID int,@SubID int
   AS
   DECLARE @intretVal int
   
   if exists (Select rightsBMP
              From USERRIGHTS Where UserID = @UserID
              And CatID = @CatID)
   begin
   Select @intretVal = (rightsBMP & POWER(2,@SubID))
   From USERRIGHTS Where UserID = @UserID And CatID = @CatID
   if @intretVal > 0
     Select 1
   else
     Select 0
     end
   else
     Select 0
   GO
   

2. When we need to set/reset user rights for a user. For an accountant, we need to set the 1,2,3 rights category.

   Stored Procedure: SetUserRights

   Parameters: UserID, MainID, SubID, Set

   /*
   **This procedure to will Set/ReSet user's right bit,
   **depending on the @bSet.If @bSet is 1, it will set
   **the given right Id for given User Id or Else.
   **Created By Dinesh Asanka
   */
   
   CREATE PROCEDURE [dbo].[SetUserRights]
   @UserID nvarchar(20),@CatID int,@SubID int,@bSet bit = 1
   AS
   DECLARE @intretVal int
   
   if exists (Select RightsBMP From USERRIGHTS
              Where UserID = @UserID
              And CatID = @CatID)
   if (@bSet > 0 )
     Update USERRIGHTS
       Set rightsBMP = rightsBMP | (POWER(2,@SubID))
       Where UserID = @UserID And CatID = @CatID
   else
     Update USERRIGHTS
       Set rightsBMP = rightsBMP &
           ( 2147483647 - Power(2,@SubID))
        Where UserID = @UserID And CatID = @CatID
   else
     Insert Into USERRIGHTS (UserID,CatID,rightsBMP)
            Values (@UserID,@CatID,POWER(2,@SubID))
   GO
   

3. When a new user is added, there should be a way to set all the options of one category at once.

   Set all the options for one category:

   Stored Procedure: SetAllOptions

   Parameters: UserID, MainID

   /*
   **This procedure will Set user's rights for the
   **given Category and User
   **Created By Dinesh Asanka
   */
   
   CREATE PROCEDURE [dbo].[SetAllOptions]
   @UserID (20),@MainCatID int
   AS
   DECLARE @totalbmp int
   
   SELECT @totalbmp = SUM(POWER(2, SubCatID))
     FROM SUBCATEGORY WHERE MainID = @MainCatID
   if exists (Select RightsBMP From USERRIGHTS
              Where UserID = @UserID
              And CatID = @MainCatID)
     Update USERRIGHTS Set rightsBMP = @totalbmp
                       Where UserID = @UserID
                       And CatID = @MainCatID
   else
     Insert Into USERRIGHTS (UserID,CatID,rightsBMP)
       Values (@UserID,@MainCatID,@totalbmp)
   GO
   

4. Delete all the options for one category.

   Stored Procedure: ReSetAllOptions

   Parameters: UserID, MainID

   /*
   **This procedure will ReSet user's rights for the
   **given Category and User
   **Created By Dinesh Asanka
   */
   
   CREATE PROCEDURE [dbo].[ReSetAllOptions]
   @UserID nvarchar(20),@MainCatID int
   AS
   Update USERRIGHTS Set rightsBMP = 0
                     Where UserID = @UserID
                     And CatID = @MainCatID
   GO
   

In this method, there are loopholes as well. One major setback is that you can’t have more than 31 subcategories. If you really need more than 31 subcategories, you can have two main categories, such as GRN – 1 and GRN – 2.

We can extend this to groupwise user rights.