USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_user_form]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 03-08-2023
-- Description:	Checks whether the connected user has permission
-- to alter a given form. 
-- Input is a the id of the form to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
CREATE PROCEDURE [internal].[usp_AUTHENTICATE_user_form] 

	@form_id_to_check bigint,
	@user_authentication_result nvarchar(10) OUTPUT


AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  DECLARE 
	    @connectedusersid varbinary(100)        = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@roleauthenticationresult nvarchar(10),
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = 'Fail',
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';		


  SET @connectedusersid = SUSER_SID(ORIGINAL_LOGIN()); -- Read the SID of the connected user

  		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid =  @connectedusersid;


  EXEC [internal].[usp_AUTHENTICATE_user_role] 
        @role_to_check = 'Configurator',  -- Configurator has rights to all forms. 
		@user_authentication_result = @roleauthenticationresult OUTPUT;

  IF @roleauthenticationresult = 'Fail'  -- User is not a Configurator
    BEGIN

 -- Check if the user does not have permission
     -- Check if the form is linked to a form owner 
	 -- and the form owner is the connected user. 
  IF NOT 
     (EXISTS (SELECT fo.sid_id AS fis  
               FROM user_restr.form_group_owners AS fo
	     INNER JOIN forms.form_group_names AS fgn
		            ON fo.form_group_id
			        = fgn.form_group_id
		 INNER JOIN forms.form_identifier_names AS fin
		            ON fo.form_group_id
					= fin.form_group_id
		 INNER JOIN user_restr.sid_list AS sl
		            ON fo.sid_id
					   = sl.sid_id
			  WHERE fin.form_id = @form_id_to_check
			         AND sl.sid = @connectedusersid
					 AND (fo.valid_from IS NULL OR fo.valid_from <= @now)
					 AND (fo.valid_until IS NULL OR fo.valid_until >= @now)
			         ) 
					 
	  -- Check if the form is not linked to any form owner
      OR NOT EXISTS (SELECT fo.sid_id AS fis  
                       FROM user_restr.form_group_owners AS fo
	             INNER JOIN forms.form_group_names AS fgn
		                 ON  fo.form_group_id
			              = fgn.form_group_id
		         INNER JOIN forms.form_identifier_names AS fin
		                 ON  fo.form_group_id 
						  = fin.form_group_id
			          WHERE fin.form_id = @form_id_to_check))
		BEGIN  -- The user does not have permission for this action
		  SET @user_authentication_result = 'Fail';
--  ============================================================================================
          -- Authorisation failure diagnostics


				DECLARE
					@ownership_failure nvarchar(1000) = NULL;


				-- ============================================================================================
				-- FORM OWNERSHIP
				--
				-- The authorisation logic is:
				--
				--   Pass if the user is the current owner
				--   OR
				--   Pass if the form has no owner at all
				--
				-- Therefore, failure can only occur when:
				--
				--   1. The form IS linked to an owner
				--   AND
				--   2. The connected user is NOT a currently valid owner
				--
				-- ============================================================================================


				-- Check whether the form has any owner assigned.
				--
				-- If no owner exists, the main authorisation condition passes,
				-- so this is NOT a failure.
				IF EXISTS
				(
					SELECT 1
					  FROM user_restr.form_group_owners AS fo
					 INNER JOIN forms.form_group_names AS fgn
							 ON fo.form_group_id = fgn.form_group_id
					 INNER JOIN forms.form_identifier_names AS fin
							 ON fo.form_group_id = fin.form_group_id
					 WHERE fin.form_id = @form_id_to_check
				)
				BEGIN

					-- The form has an owner, so the connected user must be
					-- that owner with a currently valid ownership link.

					-- First check whether the connected user is linked
					-- as an owner, irrespective of the validity dates.
					IF NOT EXISTS
					(
						SELECT 1
						  FROM user_restr.form_group_owners AS fo
						 INNER JOIN forms.form_group_names AS fgn
								 ON fo.form_group_id = fgn.form_group_id
						 INNER JOIN forms.form_identifier_names AS fin
								 ON fo.form_group_id = fin.form_group_id
						 INNER JOIN user_restr.sid_list AS sl
								 ON fo.sid_id = sl.sid_id
						 WHERE fin.form_id = @form_id_to_check
						   AND sl.sid = @connectedusersid
					)
					BEGIN
						SET @ownership_failure =
							'Form Ownership: User Is Not the Form Owner';
					END
					ELSE
					BEGIN

						-- The connected user is an owner, but check whether
						-- the ownership link is currently valid.
						IF NOT EXISTS
						(
							SELECT 1
							  FROM user_restr.form_group_owners AS fo
							 INNER JOIN forms.form_group_names AS fgn
									 ON fo.form_group_id = fgn.form_group_id
							 INNER JOIN forms.form_identifier_names AS fin
									 ON fo.form_group_id = fin.form_group_id
							 INNER JOIN user_restr.sid_list AS sl
									 ON fo.sid_id = sl.sid_id
							 WHERE fin.form_id = @form_id_to_check
							   AND sl.sid = @connectedusersid
							   AND (fo.valid_from IS NULL OR fo.valid_from <= @now)
							   AND (fo.valid_until IS NULL OR fo.valid_until >= @now)
						)
						BEGIN
							SET @ownership_failure =
								'Form Ownership: User Ownership Link Not Currently Valid';
						END;

					END;

				END;


				-- ============================================================================================
				-- Set failure type for the authorisation failure log
				-- ============================================================================================

				SET @failure_type = @ownership_failure;



		  -- End authorisation failure diagnostics
--  ============================================================================================

            -- Write to the log
			INSERT INTO user_restr.authorisation_fail_log
						(sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
				VALUES (
						@sid_id,
						'Form Ownership',
						@form_id_to_check,
						@privilege_name,
						'[internal].[usp_AUTHENTICATE_user_form]',
						@failure_type
						)

		END
	ELSE
	  SET @user_authentication_result = 'Pass';
   END -- END IF @roleauthenticationresult = 'Fail'
  ELSE -- User is a Configurator
    SET @user_authentication_result = 'Pass';
  

END
GO