Secure data processing and access verification in stored procedures

In forms, tables, and other components, you can't trust incoming parameters in all procedures, except for @username.

An attacker can substitute any data through special tools.

Moreover, even the restriction on roles does not always save. For example, a manager can substitute another @ItemId in the client's form and get access to someone else's client.

You can't hope that a valid string will come to @ItemId, because the user can simply replace it in the component snippet or in the URL.

HOW NOT TO DO:

CREATE PROCEDURE [dbo].[fm_project_saveItem]
   @username nvarchar(256), 
   @itemID int,
   @parameters ExtendedDictionaryParameter readonly
AS
BEGIN
   declare @code nvarchar(max)
   set @code=(select value from @parameters where [key]='code')

   update projects set code=@code where id=@itemID

    -- 1 SELECT (Result, Msg)
    select 1 Result, 'Сохранено' Msg
END

 This is the wrong procedure. The incoming user is not checked, it is not checked whether he can change the code of the specified project. 

 Correct procedure:

CREATE PROCEDURE [dbo].[fm_project_saveItem]
   @username nvarchar(256), 
   @itemID int,
   @parameters ExtendedDictionaryParameter readonly
AS
BEGIN
   declare @code nvarchar(max)
   set @code=(select value from @parameters where [key]='code')

   if(dbo.sec_hasProjectRight(@itemID, @username, 'editCode')=0) begin
      select 0 Result, 'No rights' Msg
      return
   end

   update projects set code=@code where id=@itemID

    -- 1 SELECT (Result, Msg)
    select 1 Result, 'Сохранено' Msg
END

We check the access in the procedure. If something is wrong, we return an error. It is important to check access through the function, so that you do not change the access check in many places later.

Important nuances

• Be sure to register access roles in the form settings, avoid specifying all or * there unnecessarily
• If several roles have access to the form, check whether the role of the specified @username can change the data
• If there is a logical connection between the user and the entity being edited in the database (for example, managers and clients), check the incoming @ItemId and @username for compliance with filtering data by entity fields (for example, the Client will have the Linked Manager field)
• An attacker can call any method of the form, so you need to insert such checks into all methods (getItem, CheckItem, saveItem, and others). It is more convenient to put this check in a separate procedure and then call it from the method.
• Do not make universal components that the Administrator and the System Participant have access to. This creates a security hole, complicates support, and increases the risk of making an access error. Also in the future, this functionality will probably develop differently for these very different roles. In general, it is better to make 2 different components for fundamentally different roles (for example, a Supplier and a Customer, or a Performer and an Administrator). 

Template for the procedure for verifying access to some entity (Project): 

-- example of the Project access verification function
Create FUNCTION [dbo].[sec_hasProjectRight]  (
         @id int, 
         @username nvarchar(128),
         @right nvarchar(128)  -- edit, read, or other
)
returns bit
AS
BEGIN
    declare @res bit = 0
    if(exists(select 1 from pr_projectUsers 
      where projectID=@projectID and username=@username)) begin
     set @res =1
    end

    return @res
end