User Management in Software Systems Using a SQL Server Database

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

Column Name Data Type Length Comment
ID Int 4 Primary Key
CategoryDescription nvarchar 50  

MAINCATEGORY Sample Data

ID Description
1. Item Master File Maintenance
2. Supplier File Maintenance
3. Good Received Notes
4. Trustee Options
.  
.  
20. User Reports
21. Management Reports
23. Audit Reports

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

SUBCATEGORY Table Format

Column Name Data Type Length Comment
MainID Int 4 Link with id for MAINCATEGORY
SubCatID Int 4  
SubCategoryDesc Nvarchar 50  

MAINCATEGORY Sample Data

MainID SubCatID SubCategoryDesc
3 1. Add GRN’s
3 2. Edit GRN’s Reference Details
3 3. Edit GRN’s Item’s Detail
3 4. Add Items To GRN’s
3 5. Delete GRN’s Item
3 6. Delete GRN’s

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

Column Name Data Type Length
UserID Nvarchar 15
Password Nvarchar 20
UserName Nvarchar 50

USERRIGHTS Table Format

Column Name Data Type Length
UserID Nvarchar 15
CatID Int 4
rightsBMP Int 4

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.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read