USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [authorising].[usp_AUTHORISE_user_role]    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:	Initial creation
-- Inserts a record into user_restr.user_role_link
-- to authorise a user for a particular role.
-- Input is the sid_id of the user from the sid_list table, 
-- and the role.  
-- Output is a status message and a transaction status.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/
-- =============================================
CREATE PROCEDURE [authorising].[usp_AUTHORISE_user_role] 

     @role_to_add nvarchar(50)        = '',    -- Role to grant to the new user
	 @new_user_sid_id bigint          = NULL,  -- User sid_id from the sid_list table
	 @valid_from datetime2(7)         = NULL,  -- Optional
	 @valid_until datetime2(7)        = NULL,  -- Optional
	 @notes nvarchar(1000)            = NULL,  -- Optional
	 @app_reference nvarchar(1000)    = '',    -- Optional
	 @message nvarchar(1000)          = NULL 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
		@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'; 

  -- 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
	     -- Check sid_id exists
		 IF @new_user_sid_id = 0
		    SET @new_user_sid_id = NULL;
		 IF NOT EXISTS (SELECT sid_id 
				  	      FROM user_restr.sid_list 
					     WHERE sid_id = @new_user_sid_id) 
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'UserIdNotExist', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
  			     SET @message = CONCAT(@message, ' | ', ISNULL(CONVERT(nvarchar, @new_user_sid_id), 'NULL'), ' | ', @tempmessage);
			 ELSE 
			     SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on UserIdNotExist.');
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END

            IF EXISTS (SELECT sid -- Check that the supplied sid is not the same as the connected user sid
				        FROM user_restr.sid_list AS sl
                        WHERE sl.sid = @connectedusersid 
							AND sl.sid_id = @new_user_sid_id)
				BEGIN
					EXEC internal.usp_SEL_message 
						@message_id   = 'SelfAuth', 
						@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 SelfAuth.');
					SET @data_validation_status = 'Fail';
					SET @transaction_ready      = 'Fail';
				END

        -- Check the role exists
	     IF @role_to_add = ''
		    SET @role_to_add = NULL;
	     IF NOT EXISTS (SELECT role_name
		                  FROM user_restr.role_list 
						 WHERE role_name = @role_to_add)
		   BEGIN
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'RoleNotExist', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
  				SET @message = CONCAT(@message, ' | ', ISNULL(@role_to_add, 'NULL'), '  ', @tempmessage);
			 ELSE 
				 SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on RoleNotExist.');

		   END

	  -- Date validation
	  IF @valid_from IS NOT NULL
         AND @valid_until IS NOT NULL
         AND @valid_until < @valid_from
			      BEGIN
					SET @data_validation_status = 'Fail';
					SET @transaction_ready      = 'Fail';
					EXEC internal.usp_SEL_message 
						@message_id = 'DateInvalid', 
						@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 DateInvalid.');
				  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 a duplicate exists then delete it first.
	  -- This allows for re-validating of a privilege in a single action.


	  IF EXISTS (SELECT sid_id
	               FROM user_restr.user_role_link 
				  WHERE role_name = @role_to_add
					AND sid_id = @new_user_sid_id)
			BEGIN
				 EXEC internal.usp_SEL_message 
					  @message_id   = 'DuplDeleted', 
					  @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 DuplDeleted.');

			-- Delete the existing record. 
			DELETE FROM user_restr.user_role_link 
				  WHERE role_name = @role_to_add
					AND sid_id = @new_user_sid_id


         -- Write to the privilege log --

		 INSERT INTO user_restr.user_privilege_log
		             (privilege_type,                  privilege_name,           sid_id,      username, action_type, 
					 created_by_sid_id, valid_from, valid_until, notes, app_reference)
			  VALUES ('Role', @role_to_add,         @new_user_sid_id, 
			   (SELECT username
		          FROM user_restr.sid_list
				 WHERE sid_id = @new_user_sid_id), 
			   'Delete',
			         (SELECT sid_id
		                FROM user_restr.sid_list
				   WHERE sid = @connectedusersid), 
					 NULL, NULL, @notes, @app_reference)


			END  -- End of dealing with pre-existing authorisation. 


			-- Perform the new authorisation grant

	     INSERT INTO user_restr.user_role_link (role_name,     sid_id,         granted_by,  valid_from,  valid_until,  notes, app_reference)
		      VALUES                           (@role_to_add, @new_user_sid_id, @username, @valid_from, @valid_until, @notes, @app_reference); 


         -- Write to the privilege log --

		 INSERT INTO user_restr.user_privilege_log
		             (privilege_type,                  privilege_name,           sid_id,      username, action_type, 
					 created_by_sid_id, valid_from, valid_until, notes, app_reference)
			  VALUES ('Role', @role_to_add,         @new_user_sid_id, 
			   (SELECT username
		          FROM user_restr.sid_list
				 WHERE sid_id = @new_user_sid_id), 
			   'Grant',
			         (SELECT sid_id
		                FROM user_restr.sid_list
				   WHERE sid = @connectedusersid), 
					 @valid_from, @valid_until, @notes, @app_reference)


	   COMMIT TRANSACTION;

         EXEC internal.usp_SEL_message 
              @message_id   = 'Success', 
              @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 Success.');
		 SET @transaction_status = 'Good';

   	  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