USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [authorising].[usp_INS_revoke_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: 11-07-2025
-- Description:	Initial creation
-- Approves request to revoke authoriser privileges.   
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2025
*/
-- =============================================
CREATE PROCEDURE [authorising].[usp_INS_revoke_authoriser] 

	 @request_id bigint               = NULL,
	 @user_sid_id bigint              = NULL,
	 @notes nvarchar(1000)            = NULL,
	 @app_reference nvarchar(1000)    = '',    -- Optional
	 @message nvarchar(1000)          = '' OUTPUT,
	 @transaction_status nvarchar(50) = NULL OUTPUT


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

  DECLARE 
        @tempmessage nvarchar(300)           = '',
		@username nvarchar(150)              = ORIGINAL_LOGIN(),     -- The username 
		@connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@connectedusersid_id bigint          = NULL,  -- SID ID of the connected user. 
		@subject_sid varbinary(100)          = NULL, -- The SID of the user relating to the request . 
		@subject_username nvarchar(128)      = '',  -- The username of the user relating to the request. 
		@userauthentication_status nchar(10) = 'Fail', -- The outcome of the authentication check of the user 
		@transaction_ready nchar(10)         = 'Ready',
		@data_validation_status nchar(10)    = 'Pass';

  -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions.
  SET @transaction_status = 'Transaction not attempted'; 
  SET @connectedusersid_id = (SELECT sl.sid_id 
                                FROM user_restr.sid_list AS sl
							   WHERE sl.sid = @connectedusersid)
  
  -- Connected user authentication
  -- Authenticate the connected user as an authoriser
  EXEC [internal].[usp_AUTHENTICATE_authoriser] 
		@user_authentication_result = @userauthentication_status OUTPUT;
  IF @userauthentication_status = 'Fail'
    BEGIN  -- The user does not have permission for this action
		SET @transaction_ready      = 'Fail';
	    EXEC internal.usp_SEL_message 
            @message_id   = 'NoPermission', 
			@message_text = @tempmessage OUTPUT;
	    IF (@tempmessage IS NOT NULL) 
  	       SET @message = CONCAT(@message, ' | ', ISNULL(@username, ''), '  ', @tempmessage);
	    ELSE 
		   SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoPermission')
	END

  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN
	  -- Data validation
		 IF @request_id = 0
		    SET @request_id = NULL;

		 IF @user_sid_id = 0
		    SET @user_sid_id = NULL;
		
		 IF @request_id IS NULL AND @user_sid_id IS NULL
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoSidNoRequ', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
			 	SET @message = CONCAT_WS(' | ', @message, @tempmessage);
		     ELSE 
				SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoSidNoRequ.');
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END

		IF @user_sid_id IS NOT NULL  -- If a SID ID has been supplied then validate it. 
		  BEGIN
		    IF NOT EXISTS (SELECT sid_id
			                FROM user_restr.sid_list
						   WHERE sid_id = @user_sid_id)
					   BEGIN
						 EXEC internal.usp_SEL_message 
							  @message_id   = 'SidIDNotExist', 
							  @message_text = @tempmessage OUTPUT;
						 IF (@tempmessage IS NOT NULL) 
			 				SET @message = CONCAT_WS(' | ', @message, @tempmessage);
						 ELSE 
							SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on SidIDNotExist.');
						 SET @data_validation_status = 'Fail';
						 SET @transaction_ready      = 'Fail';
					   END				   
		  END


		IF @data_validation_status = 'Pass' AND @user_sid_id IS NOT NULL
		  BEGIN
			IF NOT EXISTS (SELECT a.authoriser_sid_id 	     -- Check if the sid_id of the request is in the list of authorisers. 
				    	 FROM user_restr.authorisers AS a						   
						 WHERE a.authoriser_sid_id = @user_sid_id) 
				   BEGIN
					 EXEC internal.usp_SEL_message 
						  @message_id   = 'NotExist', 
						  @message_text = @tempmessage OUTPUT;
					 IF (@tempmessage IS NOT NULL) 
			 			SET @message = CONCAT_WS(' | ', @message, @tempmessage);
					 ELSE 
						SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NotExist.');
					 SET @data_validation_status = 'Fail';
					 SET @transaction_ready      = 'Fail';
				   END
				   ELSE -- If it is already in the list of authorisers, check that it is not the same as the approver.
				        -- Self revoking can result in the number of authorisers falling below two, resulting in the need for dba
						-- support to re-bootstrap the authorisers structure.
				     BEGIN
					   IF EXISTS (SELECT sl.sid_id
					                FROM user_restr.sid_list AS sl
								   WHERE sl.sid 
								         = @connectedusersid
									 AND sl.sid_id
									     = @user_sid_id)
							BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'NoSelfRevoke', 
									  @message_text = @tempmessage OUTPUT;
								 IF (@tempmessage IS NOT NULL) 
			 						SET @message = CONCAT_WS(' | ', @message, @tempmessage);
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoSelfRevoke.');
								 SET @data_validation_status = 'Fail';
								 SET @transaction_ready      = 'Fail';										 
							END
					 END
		   END

		IF @data_validation_status = 'Pass'
		  BEGIN
		    IF @request_id IS NOT NULL AND @user_sid_id IS NOT NULL
			  BEGIN -- If both request ID and sid_id are supplied then check if a record exists
			    IF NOT EXISTS (SELECT apr.request_id
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.sid_id = @user_sid_id
								  AND apr.request_id = @request_id
								  AND apr.type = 'Revoke'
								  AND apr.status = 'Pending')
							BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'NotExist', 
									  @message_text = @tempmessage OUTPUT;
								 IF (@tempmessage IS NOT NULL) 
			 						SET @message = CONCAT_WS(' | ', @message, @tempmessage);
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NotExist.');
								 SET @data_validation_status = 'Fail';
								 SET @transaction_ready      = 'Fail';										 
							END
							ELSE  -- If a record does exist then check that the current approver is no the same as the first. 
							   BEGIN
							     IF EXISTS (SELECT apr.approved_by_sid_id_1
								              FROM user_restr.authoriser_privilege_requests AS apr
										     WHERE apr.request_id = @request_id
											   AND apr.type = 'Revoke'
											   AND apr.status = 'Pending'
											   AND apr.approved_by_sid_id_1 = @connectedusersid_id)
									BEGIN
										 EXEC internal.usp_SEL_message 
											  @message_id   = 'DuplApprover', 
											  @message_text = @tempmessage OUTPUT;
										 IF (@tempmessage IS NOT NULL) 
			 								SET @message = CONCAT_WS(' | ', @message, @tempmessage);
										 ELSE 
											SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on DuplApprover.');
										 SET @data_validation_status = 'Fail';
										 SET @transaction_ready      = 'Fail';										 
									END					 
							   END
			  END
		  END

		IF @data_validation_status = 'Pass'
		  BEGIN
		    IF @request_id IS NOT NULL AND @user_sid_id IS NULL
			  BEGIN -- If request ID is supplied then check if a record exists
			    IF NOT EXISTS (SELECT apr.request_id
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.request_id = @request_id
								  AND apr.type = 'Revoke'
								  AND apr.status = 'Pending')
							BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'NotExist', 
									  @message_text = @tempmessage OUTPUT;
								 IF (@tempmessage IS NOT NULL) 
			 						SET @message = CONCAT_WS(' | ', @message, @tempmessage);
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NotExist.');
								 SET @data_validation_status = 'Fail';
								 SET @transaction_ready      = 'Fail';										 
							END
							ELSE  -- If a record does exist then check that the current approver is no the same as the first. 
							   BEGIN
							     IF EXISTS (SELECT apr.approved_by_sid_id_1
								              FROM user_restr.authoriser_privilege_requests AS apr
										     WHERE apr.request_id = @request_id
											   AND apr.type = 'Revoke'
											   AND apr.status = 'Pending'
											   AND apr.approved_by_sid_id_1 = @connectedusersid_id)
									BEGIN
										 EXEC internal.usp_SEL_message 
											  @message_id   = 'DuplApprover', 
											  @message_text = @tempmessage OUTPUT;
										 IF (@tempmessage IS NOT NULL) 
			 								SET @message = CONCAT_WS(' | ', @message, @tempmessage);
										 ELSE 
											SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on DuplApprover.');
										 SET @data_validation_status = 'Fail';
										 SET @transaction_ready      = 'Fail';										 
									END						 
							   END
			  END
		  END

		IF @data_validation_status = 'Pass'
		  BEGIN
		    IF @request_id IS NULL AND @user_sid_id IS NOT NULL
			  BEGIN -- If SID ID is supplied then check if a record exists
			    IF EXISTS (SELECT apr.request_id
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.sid_id = @user_sid_id
								  AND apr.type = 'Revoke'
								  AND apr.status = 'Pending')
					  -- If a record does exist then check that the current approver is no the same as the first. 
							   BEGIN

							   SET @request_id = (SELECT TOP 1 apr.request_id
							                        FROM user_restr.authoriser_privilege_requests AS apr
												   WHERE apr.sid_id = @user_sid_id
													 AND apr.type = 'Revoke'
													 AND apr.status = 'Pending')

							     IF EXISTS (SELECT apr.approved_by_sid_id_1
								              FROM user_restr.authoriser_privilege_requests AS apr
										     WHERE apr.sid_id = @user_sid_id
											   AND apr.type = 'Revoke'
											   AND apr.status = 'Pending'
											   AND apr.approved_by_sid_id_1 = @connectedusersid_id)
									BEGIN
										 EXEC internal.usp_SEL_message 
											  @message_id   = 'DuplApprover', 
											  @message_text = @tempmessage OUTPUT;
										 IF (@tempmessage IS NOT NULL) 
			 								SET @message = CONCAT_WS(' | ', @message, @tempmessage);
										 ELSE 
											SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on DuplApprover.');
										 SET @data_validation_status = 'Fail';
										 SET @transaction_ready      = 'Fail';										 
									END						 
							   END
			  END
		  END


		IF @data_validation_status = 'Pass'
		  BEGIN
				-- Check current number of authorisers before proceeding with revocation
				-- Two authorisers are required to grant privileges to a new authoriser.  
				-- Therefore if there are not two authorisers then DBA access is required to re-bootstrap the authorisers. 
				IF (SELECT COUNT(*) FROM user_restr.authorisers) <= 2
					BEGIN
						EXEC internal.usp_SEL_message 
							@message_id   = 'TooFewAuthorisers', 
							@message_text = @tempmessage OUTPUT;

						IF (@tempmessage IS NOT NULL) 
							SET @message = CONCAT_WS(' | ', @message, @tempmessage);
						ELSE 
							SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on TooFewAuthorisers.');
						SET @data_validation_status = 'Fail';
						SET @transaction_ready      = 'Fail';		
					END
		  END


	  -- Output the data validation status failed message
	  IF @data_validation_status = 'Fail'
		BEGIN
		  EXEC internal.usp_SEL_message 
			   @message_id   = 'FailedDataValidation', 
			   @message_text = @tempmessage OUTPUT;
		  IF (@tempmessage IS NOT NULL) 
			SET @message = CONCAT_WS(' | ', @message, @tempmessage);
		  ELSE 
			SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FailedDataValidation.');
		END
	  -- End data validation
    END -- End of IF authentication status = Pass.

-- Execute the insert query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY

	  BEGIN TRANSACTION

		    IF @request_id IS NULL AND @user_sid_id IS NOT NULL
			  BEGIN -- If sid_id is supplied then check if a record exists
							   -- Retrieve the username of the user related to the request.
								SET @subject_sid = (SELECT sl.sid
													FROM user_restr.sid_list as sl
													WHERE sl.sid_id = @user_sid_id)
								SET @subject_username = ISNULL(SUSER_SNAME(@subject_sid), '');

			    IF NOT EXISTS (SELECT apr.request_id  -- If no record then create a new one and populate approver 1
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.sid_id = @user_sid_id
								  AND apr.type = 'Revoke'
								  AND apr.status = 'Pending')
							BEGIN
							  INSERT INTO user_restr.authoriser_privilege_requests
							              (sid_id,       type,    username, approved_by_sid_id_1, approved_by_username_1, approved_on_1, status, notes, app_reference)
								   VALUES (@user_sid_id, 'Revoke',@subject_username, @connectedusersid_id, @username,              SYSDATETIME(), 'Pending',
								   @notes, @app_reference)
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'Approver1Complete', 
									  @message_text = @tempmessage OUTPUT;
  								 IF (@tempmessage IS NOT NULL) 
									SET @message = CONCAT_WS(' | ', @message, @tempmessage, @subject_username);
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Approver1Complete.');
								 SET @transaction_status = 'Good';
							END
					ELSE -- The record exists.  Update it with approver 2.
					  BEGIN
					   UPDATE user_restr.authoriser_privilege_requests
					      SET approved_by_sid_id_2 = @connectedusersid_id,
						      approved_by_username_2 = @username,
						      approved_on_2 = SYSDATETIME(),
						      status = 'Approved'
					    WHERE sid_id = @user_sid_id
						  AND type = 'Revoke'
						  AND status = 'Pending'
						  EXEC internal.usp_REVOKE_authoriser
						       @revoke_request_id = @request_id,
							   @request_result = @tempmessage OUTPUT;
						  IF @tempmessage = 'Pass'
						    BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'Success', 
									  @message_text = @tempmessage OUTPUT;
  								 IF (@tempmessage IS NOT NULL) 
									SET @message = CONCAT_WS(' | ', @message, @tempmessage, @subject_username, 'Authoriser privilege revoked');
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.');
								 SET @transaction_status = 'Good';
		                   END
						   ELSE
								BEGIN
									 EXEC internal.usp_SEL_message 
										  @message_id   = 'ApprovalFailed', 
										  @message_text = @tempmessage OUTPUT;
  									 IF (@tempmessage IS NOT NULL) 
										SET @message = CONCAT_WS(' | ', @message, @tempmessage);
									 ELSE 
										SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on ApprovalFailed.');
									 SET @transaction_status = 'Bad';
							   END						   
					  END
			  END
			  ELSE -- A record exists and is referred to by @request_id.  Populate approver 2. 
			    BEGIN
					 -- Retrieve the username of the user related to the request.
					  SET @subject_sid = (SELECT sl.sid
											FROM user_restr.sid_list as sl
										   WHERE sl.sid_id = (SELECT apr.sid_id
																FROM user_restr.authoriser_privilege_requests AS apr
															   WHERE apr.request_id = @request_id))
					  SET @subject_username = ISNULL(SUSER_SNAME(@subject_sid), '');

					   UPDATE user_restr.authoriser_privilege_requests
					      SET approved_by_sid_id_2 = @connectedusersid_id,
						      approved_by_username_2 = @username,
						      approved_on_2 = SYSDATETIME(),
						      status = 'Approved'
					    WHERE request_id = @request_id

						  EXEC internal.usp_REVOKE_authoriser
						       @revoke_request_id = @request_id,
							   @request_result = @tempmessage OUTPUT;
						  IF @tempmessage = 'Pass'
						    BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'Success', 
									  @message_text = @tempmessage OUTPUT;
  								 IF (@tempmessage IS NOT NULL) 
									SET @message = CONCAT_WS(' | ', @message, @tempmessage, @subject_username, 'Authoriser privilege revoked');
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.');
								 SET @transaction_status = 'Good';
		                   END
						   ELSE
								BEGIN
									 EXEC internal.usp_SEL_message 
										  @message_id   = 'ApprovalFailed', 
										  @message_text = @tempmessage OUTPUT;
  									 IF (@tempmessage IS NOT NULL) 
										SET @message = CONCAT_WS(' | ', @message, @tempmessage);
									 ELSE 
										SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on ApprovalFailed.');
									 SET @transaction_status = 'Bad';
							   END		
				END
       COMMIT TRANSACTION;

   	  END TRY
	  BEGIN CATCH

	     IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

	     SET @transaction_status = 'Bad';
         EXEC internal.usp_SEL_message 
              @message_id   = 'InsertError', 
              @message_text = @tempmessage OUTPUT;
		 IF (@tempmessage IS NOT NULL) 
		    SET @message = CONCAT(@message, ' |', @tempmessage, ' | ', 
		    CONVERT(nvarchar(10),ERROR_NUMBER()), ' | ', ERROR_MESSAGE());
		 ELSE 
			SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on InsertError.');
	  END CATCH
    END


END
GO