How to differentiate rights at the level of the business logic of stored procedures

Access is delimited based on roles. Each component sets access by roles through the settings (the Roles field). But sometimes a more precise, detailed access differentiation is required (for example, a user of the manager role can only have access to certain projects, and not all in a row).

The main idea is to create standard functions for verifying access to entities, and not to spread the business logic of access verification throughout the system.

This will simplify further changes to the access verification logic (for example, a new role has been added, the verification logic for individual users has been changed, etc.).

Example of a function for the Project entity. We check the right of access to the whole project for a certain person, and not to each individual part of the project - to the ticket, to the project logs, etc.

ALTER    FUNCTION [dbo].[sec_project_hasRight]  (
            @itemID int,
            @username nvarchar(128),
            @right nvarchar(128)
)
RETURNS bit
AS
BEGIN
            declare @res bit = 0
            -- for each right, we make our own business logic of verification based on ItemId and username
            if(@right in ('readBug', 'editBug') ) begin  
                        if( ...  CONDITION - HAS USER THIS RIGHT? ... ) begin
                                   set @res = 1
                        end
            end

            if(@right in ('manageBugChecklist') ) begin  
                        if( ...  CONDITION - HAS USER THIS RIGHT? ... ) begin
                                   set @res = 1
                        end
            end
            --- ...
   RETURN  @res
END

 In the components, we first check access. If there is no specific right, then we issue a message about access restriction (or redirect) 

if([dbo].[sec_project__hasRight](@itemID, @username, 'editDocs=')=0 ) begin
    	select 0 Result, 'No access' Msg
        return
end

Note:

  • These checks do not cancel the use of the roles component property. This field reliably cuts off access by role (even if your verification is forgotten implemented, roles will already restrict access to the table or form in a basic way).
  • Do not call the access check function in where (this will work slowly). Check access before executing the operation. You can always find some generalized object that you can check access to before executing the operation. In other words, you do not need to check access to each task of the project, you need to check the user's access to this project.
Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started
{sp-shortDemostandLinks}

Falcon Space Platform

This is a reduction in the cost of ownership

at the expense of fewer people to support

This is a quick change

while using the program

This is a modern interface

full adaptation for mobile devices

Note