USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_file_ed_perm]    Script Date: Fri 02-10-2026 8:29:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 18-12-2023
-- Description:	Checks whether the connected user has permission
-- to edit the metadata of a given file. 
-- Also returns fail if the id does not exist.
-- Input is the id of the file to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.

  -- A user has edit permission for a file if there is a chain from the file ID through
  -- either a function list or a people list to a sid in the sid list which is the sid for the user.
  -- If user is a controller then they have editing rights to any file they have viewing rights to
  -- except if the file is linked to a controller group, then if the connected user is not part of that group
  -- then the user is excluded from editing rights.
  -- There are two layers of editing permissions.  At one level, controllers are granted exclusive editing
  -- permissions by an authoriser, referred to as controller level permission. Other controllers cannot alter those permissions.
  -- At the other level controllers grant editing permissions to editors, referred to as editor level permission.  Controller level permissions
  -- exclude and override any editor level permission.  A controller cannot delegate permission for a controller level permission. 

/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

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

	@file_id_to_check_ep bigint = NULL,
	@user_authentication_result_fep nvarchar(10) = NULL OUTPUT


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

  DECLARE 
  		@usersid varbinary(100)            = NULL, -- The SID of the connected user
		@view_permission  nvarchar(10)     = NULL,
		@iscontroller nvarchar(5)          = NULL,
		@fileiscontrollergroup nvarchar(5) = NULL,
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = NULL,
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';		 

  -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions.
  SET @user_authentication_result_fep  = 'Fail'

  SET @usersid = SUSER_SID(ORIGINAL_LOGIN()); -- Read the SID of the connected user
  
  		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid = @usersid;
	

  -- Check if the user has viewing permission for the file.
  EXEC [internal].[usp_AUTHENTICATE_user_file_id]
         @file_id_to_check = @file_id_to_check_ep,
		 @user_authentication_result = @view_permission OUTPUT;

  IF @view_permission = 'Fail' SET @failure_type = 'File does not exist or user does not have access to it';

   IF @view_permission = 'Pass' -- The user has viewing permission for the file
    BEGIN 
      
	  EXEC [internal].[usp_AUTHENTICATE_user_role] 
	  		 @role_to_check = 'Controller',
			 @user_authentication_result = @iscontroller OUTPUT;


	  IF EXISTS -- Is the file id linked to a controller group?
	    (SELECT cfgl.file_id 
		   FROM xref.controller_file_group_links AS cfgl
		  WHERE cfgl.file_id = @file_id_to_check_ep)
	  SET @fileiscontrollergroup = 'Yes'
	  ELSE SET @fileiscontrollergroup = 'No';


	  IF @fileiscontrollergroup = 'Yes' -- If the file is linked to a controller group, test if the user is a member of that group and is also a controller
	  BEGIN 
		IF (EXISTS 
			  (SELECT sl.sid_id
				 FROM user_restr.sid_list AS sl
			LEFT JOIN user_restr.controller_file_grp_sid_links AS cfgsl
				   ON      sl.sid_id
					  = cfgsl.sid_id
			LEFT JOIN xref.controller_file_group_names AS cfgn 
				   ON  cfgsl.controller_file_group_name_id
					  = cfgn.controller_file_group_name_id
			LEFT JOIN xref.controller_file_group_links AS cfgl
				   ON   cfgn.controller_file_group_name_id
					  = cfgl.controller_file_group_name_id
	 			WHERE sl.sid = @usersid   			      
				  AND cfgl.file_id = @file_id_to_check_ep
				  AND (cfgsl.valid_from IS NULL OR cfgsl.valid_from <= @now)
				  AND (cfgsl.valid_until IS NULL OR cfgsl.valid_until >= @now)                  
                  ))
		    AND @iscontroller = 'Pass'
		 SET @user_authentication_result_fep = 'Pass';
		 ELSE SET @user_authentication_result_fep = 'Fail';
	 END
	 ELSE SET @user_authentication_result_fep = 'Pass';

	  -- If the file is not linked to a controller group then if the user is a controller then they have editing
	  -- rights to any file they have viewing rights to. 

	  IF @fileiscontrollergroup = 'No' AND @user_authentication_result_fep = 'Pass' -- The user is not excluded by controller level restrictions
	  -- If the user is not excluded by controller level permissions but the file is not part of a controller group then permission will be determined by this segment.
		 BEGIN -- Check if the user is linked to the file group through either a function list or a people list
		   IF @iscontroller = 'Pass' SET @user_authentication_result_fep = 'Pass'  -- If the user is a controller at this step then they are granted permission
		   ELSE -- User is not a controller and is not excluded by controller level permissions
		    BEGIN
			  IF  
				     (EXISTS (SELECT fgn.file_group_id    -- Check through duty function lists
					           FROM base.file_metadata AS fm
						  LEFT JOIN xref.file_group_links AS fgl
						            ON    fm.file_id
									   = fgl.file_id
						  LEFT JOIN xref.file_group_names AS fgn
						            ON   fgl.file_group_id
									   = fgn.file_group_id
						  LEFT JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
						            ON      fgn.file_group_id
									   = fgepfl.file_group_id
						  LEFT JOIN people.function_list_names AS fln
						            ON fgepfl.function_list_id
									   =  fln.function_list_id
						  LEFT JOIN people.function_lists AS fl
						            ON  fln.function_list_id
									   = fl.function_list_id
						  LEFT JOIN people.duty_functions AS df
						            ON   fl.function_id
									   = df.function_id
						  LEFT JOIN people.duty_function_sid_links AS dfsl
						            ON     df.function_id
									   = dfsl.function_id
						 INNER JOIN user_restr.sid_list AS sl
									ON dfsl.sid_id
									   = sl.sid_id
							  WHERE fgl.file_id = @file_id_to_check_ep
									AND sl.sid = @usersid
								    -- Check time-bound restrictions
								    AND (fgepfl.valid_from IS NULL OR fgepfl.valid_from <= @now)
								    AND (fgepfl.valid_until IS NULL OR fgepfl.valid_until >= @now)
								    AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
								    AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
								    AND (dfsl.valid_from IS NULL OR dfsl.valid_from <= @now)
								    AND (dfsl.valid_until IS NULL OR dfsl.valid_until >= @now)
  							          
                    UNION ALL
				            (SELECT fgn.file_group_id  -- Check through people lists
					           FROM base.file_metadata AS fm
						  LEFT JOIN xref.file_group_links AS fgl
						            ON    fm.file_id
									   = fgl.file_id
						  LEFT JOIN xref.file_group_names AS fgn
						            ON   fgl.file_group_id
									   = fgn.file_group_id
						  LEFT JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
						            ON      fgn.file_group_id
									   = fgeppl.file_group_id
						  LEFT JOIN people.people_list_names AS pln
						            ON fgeppl.people_list_id
									   =  pln.people_list_id
						  LEFT JOIN people.people_lists AS pl
						            ON  pln.people_list_id
									   = pl.people_list_id
						 INNER JOIN user_restr.sid_list AS sl
									ON   pl.sid_id
									   = sl.sid_id
							  WHERE fgl.file_id = @file_id_to_check_ep
									 AND sl.sid = @usersid
									 -- Check time-bound restrictions
									 AND (fgeppl.valid_from IS NULL OR fgeppl.valid_from <= @now)
									 AND (fgeppl.valid_until IS NULL OR fgeppl.valid_until >= @now)
									 AND (pl.valid_from IS NULL OR pl.valid_from <= @now)
									 AND (pl.valid_until IS NULL OR pl.valid_until >= @now)	
  							         ))) 
					
					BEGIN  -- The user has permission for this action
					  SET @user_authentication_result_fep = 'Pass';
					END
				ELSE
				 BEGIN  -- The user is not a controller and does not have edit permission.
				  SET @user_authentication_result_fep = 'Fail'; -- The user is not a controller and does not have edit permission.
--  ============================================================================================
                   -- Authorisation failure diagnostics. 

                    DECLARE
                        @function_failure nvarchar(1000) = NULL,
                        @people_failure   nvarchar(1000) = NULL;


                    -- ============================================================================================
                    -- FUNCTION LIST PATH
                    -- ============================================================================================

                    -- Check that the file is linked to a file group
                    IF NOT EXISTS
                    (
                        SELECT 1
                          FROM base.file_metadata AS fm
                    INNER JOIN xref.file_group_links AS fgl
                            ON fm.file_id = fgl.file_id
                         WHERE fm.file_id = @file_id_to_check_ep
                    )
                    BEGIN
                        SET @function_failure =
                            CONCAT_WS('; ',
                                @function_failure,
                                'Function Path: File Group Link Not Found'
                            );
                    END
                    ELSE
                    BEGIN

                        -- Check for a Function List edit authorisation
                        IF NOT EXISTS
                        (
                            SELECT 1
                              FROM base.file_metadata AS fm
                        INNER JOIN xref.file_group_links AS fgl
                                ON fm.file_id = fgl.file_id
                        INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                ON fgl.file_group_id = fgepfl.file_group_id
                             WHERE fm.file_id = @file_id_to_check_ep
                        )
                        BEGIN
                            SET @function_failure =
                                CONCAT_WS('; ',
                                    @function_failure,
                                    'Function Path: Edit Authorisation Not Found'
                                );
                        END
                        ELSE
                        BEGIN

                            -- Check validity of the file-group / function-list authorisation
                            IF NOT EXISTS
                            (
                                SELECT 1
                                  FROM base.file_metadata AS fm
                            INNER JOIN xref.file_group_links AS fgl
                                    ON fm.file_id = fgl.file_id
                            INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                    ON fgl.file_group_id = fgepfl.file_group_id
                                 WHERE fm.file_id = @file_id_to_check_ep
                                   AND (fgepfl.valid_from IS NULL OR fgepfl.valid_from <= @now)
                                   AND (fgepfl.valid_until IS NULL OR fgepfl.valid_until >= @now)
                            )
                            BEGIN
                                SET @function_failure =
                                    CONCAT_WS('; ',
                                        @function_failure,
                                        'Function Path: Edit Authorisation Not Currently Valid'
                                    );
                            END;


                            -- Check that the Function List exists
                            IF NOT EXISTS
                            (
                                SELECT 1
                                  FROM base.file_metadata AS fm
                            INNER JOIN xref.file_group_links AS fgl
                                    ON fm.file_id = fgl.file_id
                            INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                    ON fgl.file_group_id = fgepfl.file_group_id
                            INNER JOIN people.function_list_names AS fln
                                    ON fgepfl.function_list_id = fln.function_list_id
                            INNER JOIN people.function_lists AS fl
                                    ON fln.function_list_id = fl.function_list_id
                                 WHERE fm.file_id = @file_id_to_check_ep
                            )
                            BEGIN
                                SET @function_failure =
                                    CONCAT_WS('; ',
                                        @function_failure,
                                        'Function Path: Function List Not Found'
                                    );
                            END
                            ELSE
                            BEGIN

                                -- Check Function List validity
                                IF NOT EXISTS
                                (
                                    SELECT 1
                                      FROM base.file_metadata AS fm
                                INNER JOIN xref.file_group_links AS fgl
                                        ON fm.file_id = fgl.file_id
                                INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                        ON fgl.file_group_id = fgepfl.file_group_id
                                INNER JOIN people.function_list_names AS fln
                                        ON fgepfl.function_list_id = fln.function_list_id
                                INNER JOIN people.function_lists AS fl
                                        ON fln.function_list_id = fl.function_list_id
                                     WHERE fm.file_id = @file_id_to_check_ep
                                       AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
                                       AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
                                )
                                BEGIN
                                    SET @function_failure =
                                        CONCAT_WS('; ',
                                            @function_failure,
                                            'Function Path: Function List Not Currently Valid'
                                        );
                                END;


                                -- Check whether the Function List ultimately provides
                                -- a SID link for this user
                                IF NOT EXISTS
                                (
                                    SELECT 1
                                      FROM base.file_metadata AS fm
                                INNER JOIN xref.file_group_links AS fgl
                                        ON fm.file_id = fgl.file_id
                                INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                        ON fgl.file_group_id = fgepfl.file_group_id
                                INNER JOIN people.function_list_names AS fln
                                        ON fgepfl.function_list_id = fln.function_list_id
                                INNER JOIN people.function_lists AS fl
                                        ON fln.function_list_id = fl.function_list_id
                                INNER JOIN people.duty_functions AS df
                                        ON fl.function_id = df.function_id
                                INNER JOIN people.duty_function_sid_links AS dfsl
                                        ON df.function_id = dfsl.function_id
                                INNER JOIN user_restr.sid_list AS sl
                                        ON dfsl.sid_id = sl.sid_id
                                     WHERE fm.file_id = @file_id_to_check_ep
                                       AND sl.sid = @usersid
                                )
                                BEGIN
                                    SET @function_failure =
                                        CONCAT_WS('; ',
                                            @function_failure,
                                            'Function Path: User Not Linked Through Duty Function'
                                        );
                                END
                                ELSE
                                BEGIN

                                    -- Check validity of the complete function-to-user path
                                    IF NOT EXISTS
                                    (
                                        SELECT 1
                                          FROM base.file_metadata AS fm
                                    INNER JOIN xref.file_group_links AS fgl
                                            ON fm.file_id = fgl.file_id
                                    INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                            ON fgl.file_group_id = fgepfl.file_group_id
                                    INNER JOIN people.function_list_names AS fln
                                            ON fgepfl.function_list_id = fln.function_list_id
                                    INNER JOIN people.function_lists AS fl
                                            ON fln.function_list_id = fl.function_list_id
                                    INNER JOIN people.duty_functions AS df
                                            ON fl.function_id = df.function_id
                                    INNER JOIN people.duty_function_sid_links AS dfsl
                                            ON df.function_id = dfsl.function_id
                                    INNER JOIN user_restr.sid_list AS sl
                                            ON dfsl.sid_id = sl.sid_id
                                         WHERE fm.file_id = @file_id_to_check_ep
                                           AND sl.sid = @usersid
                                           AND (fgepfl.valid_from IS NULL OR fgepfl.valid_from <= @now)
                                           AND (fgepfl.valid_until IS NULL OR fgepfl.valid_until >= @now)
                                           AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
                                           AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
                                           AND (dfsl.valid_from IS NULL OR dfsl.valid_from <= @now)
                                           AND (dfsl.valid_until IS NULL OR dfsl.valid_until >= @now)
                                    )
                                    BEGIN
                                        SET @function_failure =
                                            CONCAT_WS('; ',
                                                @function_failure,
                                                'Function Path: One or More Authorisation Links Not Currently Valid'
                                            );
                                    END;

                                END;
                            END;
                        END;
                    END;


                    -- ============================================================================================
                    -- PEOPLE LIST PATH
                    -- ============================================================================================

                    -- Check that the file is linked to a file group
                    IF NOT EXISTS
                    (
                        SELECT 1
                          FROM base.file_metadata AS fm
                    INNER JOIN xref.file_group_links AS fgl
                            ON fm.file_id = fgl.file_id
                         WHERE fm.file_id = @file_id_to_check_ep
                    )
                    BEGIN
                        SET @people_failure =
                            CONCAT_WS('; ',
                                @people_failure,
                                'People Path: File Group Link Not Found'
                            );
                    END
                    ELSE
                    BEGIN

                        -- Check for a People List edit authorisation
                        IF NOT EXISTS
                        (
                            SELECT 1
                              FROM base.file_metadata AS fm
                        INNER JOIN xref.file_group_links AS fgl
                                ON fm.file_id = fgl.file_id
                        INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                ON fgl.file_group_id = fgeppl.file_group_id
                             WHERE fm.file_id = @file_id_to_check_ep
                        )
                        BEGIN
                            SET @people_failure =
                                CONCAT_WS('; ',
                                    @people_failure,
                                    'People Path: Edit Authorisation Not Found'
                                );
                        END
                        ELSE
                        BEGIN

                            -- Check validity of the file-group / people-list authorisation
                            IF NOT EXISTS
                            (
                                SELECT 1
                                  FROM base.file_metadata AS fm
                            INNER JOIN xref.file_group_links AS fgl
                                    ON fm.file_id = fgl.file_id
                            INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                    ON fgl.file_group_id = fgeppl.file_group_id
                                 WHERE fm.file_id = @file_id_to_check_ep
                                   AND (fgeppl.valid_from IS NULL OR fgeppl.valid_from <= @now)
                                   AND (fgeppl.valid_until IS NULL OR fgeppl.valid_until >= @now)
                            )
                            BEGIN
                                SET @people_failure =
                                    CONCAT_WS('; ',
                                        @people_failure,
                                        'People Path: Edit Authorisation Not Currently Valid'
                                    );
                            END;


                            -- Check that the People List exists and contains the user's SID
                            IF NOT EXISTS
                            (
                                SELECT 1
                                  FROM base.file_metadata AS fm
                            INNER JOIN xref.file_group_links AS fgl
                                    ON fm.file_id = fgl.file_id
                            INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                    ON fgl.file_group_id = fgeppl.file_group_id
                            INNER JOIN people.people_list_names AS pln
                                    ON fgeppl.people_list_id = pln.people_list_id
                            INNER JOIN people.people_lists AS pl
                                    ON pln.people_list_id = pl.people_list_id
                            INNER JOIN user_restr.sid_list AS sl
                                    ON pl.sid_id = sl.sid_id
                                 WHERE fm.file_id = @file_id_to_check_ep
                                   AND sl.sid = @usersid
                            )
                            BEGIN

                                -- Distinguish a missing People List from a People List
                                -- which exists but does not contain the user
                                IF NOT EXISTS
                                (
                                    SELECT 1
                                      FROM base.file_metadata AS fm
                                INNER JOIN xref.file_group_links AS fgl
                                        ON fm.file_id = fgl.file_id
                                INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                        ON fgl.file_group_id = fgeppl.file_group_id
                                INNER JOIN people.people_list_names AS pln
                                        ON fgeppl.people_list_id = pln.people_list_id
                                INNER JOIN people.people_lists AS pl
                                        ON pln.people_list_id = pl.people_list_id
                                     WHERE fm.file_id = @file_id_to_check_ep
                                )
                                BEGIN
                                    SET @people_failure =
                                        CONCAT_WS('; ',
                                            @people_failure,
                                            'People Path: People List Not Found'
                                        );
                                END
                                ELSE
                                BEGIN
                                    SET @people_failure =
                                        CONCAT_WS('; ',
                                            @people_failure,
                                            'People Path: User Not a Member of People List'
                                        );
                                END;

                            END
                            ELSE
                            BEGIN

                                -- Check validity of the complete people-list-to-user path
                                IF NOT EXISTS
                                (
                                    SELECT 1
                                      FROM base.file_metadata AS fm
                                INNER JOIN xref.file_group_links AS fgl
                                        ON fm.file_id = fgl.file_id
                                INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                        ON fgl.file_group_id = fgeppl.file_group_id
                                INNER JOIN people.people_list_names AS pln
                                        ON fgeppl.people_list_id = pln.people_list_id
                                INNER JOIN people.people_lists AS pl
                                        ON pln.people_list_id = pl.people_list_id
                                INNER JOIN user_restr.sid_list AS sl
                                        ON pl.sid_id = sl.sid_id
                                     WHERE fm.file_id = @file_id_to_check_ep
                                       AND sl.sid = @usersid
                                       AND (fgeppl.valid_from IS NULL OR fgeppl.valid_from <= @now)
                                       AND (fgeppl.valid_until IS NULL OR fgeppl.valid_until >= @now)
                                       AND (pl.valid_from IS NULL OR pl.valid_from <= @now)
                                       AND (pl.valid_until IS NULL OR pl.valid_until >= @now)
                                )
                                BEGIN
                                    SET @people_failure =
                                        CONCAT_WS('; ',
                                            @people_failure,
                                            'People Path: One or More Authorisation Links Not Currently Valid'
                                        );
                                END;

                            END;
                        END;
                    END;


                    -- ============================================================================================
                    -- COMBINE THE TWO ALTERNATIVE PATHS
                    --
                    -- Function Path OR People Path
                    --
                    -- The overall authorisation failed, so both paths have failed.
                    -- CONCAT_WS omits any NULL diagnostic.
                    -- ============================================================================================

                    SET @failure_type =
                        CONCAT_WS('; ',
                            @function_failure,
                            @people_failure
                        );



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

				   -- Write to authorisation fail log

					  INSERT INTO user_restr.authorisation_fail_log
								  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
						   VALUES (
									@sid_id,
									'File Edit Permission',
									@file_id_to_check_ep ,
									@privilege_name,
									'[internal].[usp_AUTHENTICATE_file_ed_perm]',
									@failure_type
									)
			     END -- END The user is not a controller and does not have edit permission.

		END -- END ELSE IF @fileiscontrollergroup = 'Yes'
	 END -- END IF @fileiscontrollergroup = 'No' AND @user_authentication_result_fep = 'Pass' 
  END  --IF @view_permission = 'Pass' -- The user has viewing permission for the file
  
END
GO