USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_user_file_id]    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: 16-10-2023
-- Description:	Checks whether the connected user has permission
-- to access a given file.  File access is constrained via document groups. 
-- Input is a the id of the file 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_file_id] 

	@file_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 
  		@usersid varbinary(100)            = NULL, -- The SID of the connected user
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = 'Fail',
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';		


  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 connected user has permission to access the file 

		IF (
			EXISTS (
				SELECT fm.file_id
				  FROM base.file_metadata AS fm
				 WHERE fm.file_id = @file_id_to_check
			)
			AND (
				-- Case 1: File is not linked to any document
				NOT EXISTS (
					SELECT ftdl.file_id
					  FROM xref.file_to_document_links AS ftdl
					 WHERE ftdl.file_id = @file_id_to_check
				)

				-- Case 2: Linked document has no group
				OR NOT EXISTS (
					SELECT dgl.doc_id
					FROM xref.file_to_document_links AS ftdl
					LEFT JOIN xref.doc_group_links AS dgl
						   ON ftdl.doc_id = dgl.doc_id
					    WHERE ftdl.file_id = @file_id_to_check
					      AND dgl.doc_group_id IS NOT NULL
				)

				-- Case 3: Group exists, but has no permission SIDs
				OR NOT EXISTS (
					SELECT dgvp.sid_id
					FROM xref.file_to_document_links AS ftdl
					LEFT JOIN xref.doc_group_links AS dgl
						   ON ftdl.doc_id = dgl.doc_id
					LEFT JOIN xref.doc_group_names AS dgn
						   ON dgl.doc_group_id = dgn.doc_group_id
					LEFT JOIN user_restr.doc_group_view_permissions AS dgvp
						   ON dgn.doc_group_id = dgvp.doc_group_id
					    WHERE ftdl.file_id = @file_id_to_check
					     AND dgvp.sid_id IS NOT NULL
				)

				-- Case 4: User has permission
				OR EXISTS (
					SELECT sl.sid
					FROM xref.file_to_document_links AS ftdl
					LEFT JOIN xref.doc_group_links AS dgl
						   ON ftdl.doc_id = dgl.doc_id
					LEFT JOIN xref.doc_group_names AS dgn
						   ON dgl.doc_group_id = dgn.doc_group_id
					LEFT JOIN user_restr.doc_group_view_permissions AS dgvp
						   ON dgn.doc_group_id = dgvp.doc_group_id
					LEFT JOIN user_restr.sid_list AS sl
						   ON dgvp.sid_id = sl.sid_id
					    WHERE ftdl.file_id = @file_id_to_check
					      AND sl.sid = @usersid
						  AND (dgvp.valid_from IS NULL OR dgvp.valid_from <= @now)
						  AND (dgvp.valid_until IS NULL OR dgvp.valid_until >= @now)
				)
			)
		)
			BEGIN
				SET @user_authentication_result = 'Pass';
			END
		ELSE
			BEGIN
				SET @user_authentication_result = 'Fail';

				SET @failure_type = 'Failed';  --  To avoid data leakage, the existence of the file ID is not revealed.  

			-- Write to authorisation failure log

					  INSERT INTO user_restr.authorisation_fail_log
								  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
						   VALUES (
									@sid_id,
									'File ID',
									@file_id_to_check,
									@privilege_name,
									'[internal].[usp_AUTHENTICATE_user_file_id]',
									@failure_type
									)
			END

  

END
GO