USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_authoriser]    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
-- as an authoriser. 
-- 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_authoriser] 

	@user_authentication_result nvarchar(10) = 'Fail' 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
		@sid_id bigint                              = NULL,
		@failure_type nvarchar(1000)                = '',
		@now datetime2(7)                           = SYSDATETIME();


		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid = @connectedusersid;
		
		SET @user_authentication_result = 'Fail';

 -- Check if the user  has permission
 
  IF EXISTS (SELECT authoriser_sid_id AS asi  
               FROM user_restr.authorisers AS a
	     INNER JOIN user_restr.sid_list AS sl
		         ON a.authoriser_sid_id
					= sl.sid_id
			  WHERE sl.sid = @connectedusersid
			    AND (a.valid_from IS NULL OR a.valid_from <= @now)
				AND (a.valid_until IS NULL OR a.valid_until >= @now))
    -- The user has permission for this action
	  SET @user_authentication_result = 'Pass';
  ELSE
    BEGIN
	  SET @user_authentication_result = 'Fail'; 
	  -- Write to authorisation fail log
	  	IF @sid_id IS NULL
		  SET @failure_type = 'SID not registered in SID List'
	    ELSE
		  BEGIN
			  IF NOT EXISTS (SELECT authoriser_sid_id AS asi  
							   FROM user_restr.authorisers AS a
						 INNER JOIN user_restr.sid_list AS sl
						         ON a.authoriser_sid_id
									= sl.sid_id
							  WHERE sl.sid = @connectedusersid) 
			    SET @failure_type = 'SID not in Authorizers list'
			   ELSE  SET @failure_type = 'Authorization registered but not currently valid'
		  END

	  INSERT INTO user_restr.authorisation_fail_log
	              (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
		   VALUES (
		            @sid_id,
					'Authorizer',
					NULL,
					NULL,
					'[internal].[usp_AUTHENTICATE_authoriser]',
					@failure_type
					)
	END
  

END


GO