USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_user_doc_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: 14-08-2023
-- Description:	Checks whether the connected user has permission
-- to access a given document. 
-- Input is a the id of the document 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_doc_id] 

	@doc_id_to_check nvarchar(50),
	@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 document 

		IF EXISTS (
			SELECT dil.doc_id
			FROM base.document_id_list AS dil
			WHERE dil.doc_id = @doc_id_to_check
			  AND (
				  -- Case 1: No group links
				  NOT EXISTS (
					  SELECT dgl.doc_id
					  FROM xref.doc_group_links AS dgl
					  WHERE dgl.doc_id = dil.doc_id
				  )
          
				  -- Case 2: Group links exist, but no SIDs
				  OR NOT EXISTS (
					  SELECT dgvp.doc_group_id
					    FROM xref.doc_group_links AS dgl
					  LEFT JOIN xref.doc_group_names AS dgn
						     ON dgl.doc_group_id = dgn.doc_group_id
					 INNER JOIN user_restr.doc_group_view_permissions AS dgvp
						     ON dgn.doc_group_id = dgvp.doc_group_id
					      WHERE dgl.doc_id = dil.doc_id
				  )
          
				  -- Case 3: User's SID is explicitly permitted and is current
				  OR EXISTS (
					  SELECT sl.sid_id
					  FROM xref.doc_group_links AS dgl
					  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 dgl.doc_id = dil.doc_id
						    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 document 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,
									'Document ID',
									@doc_id_to_check,
									@privilege_name,
									'[internal].[usp_AUTHENTICATE_user_doc_id]',
									@failure_type
									)
			END

 

END
GO