USE [JWT-Authentication-With-RefreshToken]
GO
/****** Object: StoredProcedure [dbo].[Proc_SaveRefreshToken_iu] Script Date: 06-01-2024 18:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************************************************************
* Type : Procedure
* Called by :
* Name : Proc_SaveRefreshToken_iu
* Input Parameters : @RefreshTokenData
* Output Parameters : @errorNo,@errorMessage,@spResponse
* Purpose : It checks the refersh tokens in database and deletes them (refresh tokens) which are expired,inactive,revoked and two days older with respect to current date and then saves the data into RefreshTokens table in the Database.
* Created Date : 01/03/2024
* Created By : Kiran BS
Email: [email protected]
Bangalore
LinkedIn : https://www.linkedin.com/in/kiran-b-s-50904b1b0/
* Return Value :
Success : 0
Failure : -1
* Assumptions : None
* Dependencies : None
* ModifiedDate ModifiedBy RevisionNumber Modifications
PROC_EXECUTION_STATEMENT :
DECLARE @RefreshTokenData NVARCHAR(1000)
DECLARE @errorNo_OUT INT
DECLARE @errorMessage_OUT VARCHAR(MAX)
DECLARE @spResponse_OUT VARCHAR(MAX)
SET @RefreshTokenData = N'{
"UserId": "sysAdmin",
"RefreshToken":"xiCZBf7Ae0bDUumhr0R+RVjPGPQAuD1N3RXXSKXNt59U1igjilKRn+kfNdo3OUXEWsY1KldgBk7cqOrlP7VRWQ==",
"ExpirationDate":"2023-10-19",
"CreatedByIp" : "127.0.0"
}'
EXEC Proc_SaveRefreshToken_iu
@RefreshTokenData,
@errorNo = @errorNo_OUT OUTPUT,
@errorMessage = @errorMessage_OUT OUTPUT,
@spResponse = @spResponse_OUT OUTPUT
select @errorNo_OUT,@errorMessage_OUT,@spResponse_OUT
*********************************************************************************************************/
ALTER PROCEDURE [dbo].[Proc_SaveRefreshToken_iu]
@refreshTokenJson NVARCHAR(1000),
@errorNo INT OUTPUT,
@errorMessage VARCHAR(255) OUTPUT,
@spResponse VARCHAR(1000) OUTPUT
AS
BEGIN
DECLARE @LoginId VARCHAR(30),
@RefreshToken VARCHAR(200),
@ExpirationDate DATE,
@CreatedByIp VARCHAR(50),
@ExistenceFlag BIT = 0,
@IdUsr INT,
@CurrentDate DATETIME = GETDATE(),
@ActiveFlag CHAR(1) = 'Y',
@InvalidUser BIT = 0 ,
@isUserActive BIT = 1,
@IsExpired CHAR(1) = 'Y',
@IsRevoked CHAR(1) = 'Y',
@InActiveFlag CHAR(1) = 'N',
@errorNo_OUT1 INT,
@errorMessage_OUT1 VARCHAR(MAX),
@spResponse_OUT1 BIT = 0,
@spDeleteOldRFTokenFlag BIT = 0,
@YesFlag CHAR(1) = 'Y',
@NoFlag CHAR(1) = 'N'
BEGIN TRY
SELECT
@LoginId = UserId,
@RefreshToken = RefreshToken,
@ExpirationDate = CAST(ExpirationDate AS DATE),
@CreatedByIp = CreatedByIp
FROM OPENJSON(@refreshTokenJson)
WITH
(
UserId VARCHAR(30) '$.UserId',
RefreshToken VARCHAR(200) '$.RefreshToken',
ExpirationDate DATETIME '$.ExpirationDate',
CreatedByIp VARCHAR(50) '$.CreatedByIp'
)
SELECT @IdUsr = UserId
FROM UserDetails (NOLOCK)
WHERE LoginId = @LoginId
AND IsActive = @isUserActive
IF NOT EXISTS
(
SELECT 1
FROM UserDetails (NOLOCK)
WHERE LoginId = @LoginId
AND IsActive = @isUserActive
)
BEGIN
SET @InvalidUser = 1
GOTO ExitProc
END
IF EXISTS
(
SElECT 1
FROM RefreshTokens(nolock)
WHERE UserId = @IdUsr
AND RefreshToken = @RefreshToken
)
BEGIN
SET @ExistenceFlag = 1
GOTO ExitProc
END
BEGIN TRANSACTION INSERT_REFRESHTOKEN
--Removing refresh tokens where are expired,inactive, revoked and two days older with respect to current date
IF EXISTS
(
SElECT top 1 1 ----to improve the performance
FROM RefreshTokens(nolock)
WHERE
IsExpired = @IsExpired
AND IsActive = @InActiveFlag
AND IsRevoked = @IsRevoked
AND CAST(CreatedDateTime AS DATE) < CAST(DATEADD(d,-2,@CurrentDate)AS DATE)
)
BEGIN
EXEC Proc_DeleteOlderRefreshToken
NULL,
@errorNo = @errorNo_OUT1 OUTPUT,
@errorMessage = @errorMessage_OUT1 OUTPUT
IF @errorNo_OUT1 = -1
BEGIN
;THROW 51000, @errorMessage_OUT1, 1
END
END
INSERT INTO RefreshTokens
(
UserId,
RefreshToken,
ExpireDateTime,
CreatedByIp,
IsExpired,
IsActive,
IsRevoked,
CreatedDateTime,
CreatedUserId
)
SElECT
@IdUsr,
@RefreshToken,
@ExpirationDate,
@CreatedByIp,
@NoFlag,
@YesFlag,
@NoFlag,
@CurrentDate,
@IdUsr
COMMIT TRANSACTION INSERT_REFRESHTOKEN
SET @errorNo = 0
SET @errorMessage = 'Proc_SaveRefreshToken_iu and Proc_DeleteOlderRefreshToken Executed Successfully'
SET @spResponse =
(
SELECT
1 AS RFTokenSaved,
'Refresh Token Inserted Successfully' AS Message
FOR JSON Path, WITHOUT_ARRAY_WRAPPER
)
ExitProc:
IF(@InvalidUser = 1)
BEGIN
SET @errorNo = 0
SET @errorMessage = 'Proc_SaveRefreshToken_iu Executed Successfully'
SET @spResponse =
(
SELECT
0 AS RFTokenSaved,
'User Not Authorised' AS Message
FOR JSON Path, WITHOUT_ARRAY_WRAPPER
)
END
IF(@ExistenceFlag = 1)
BEGIN
SET @errorNo = 0
SET @errorMessage = 'Proc_SaveRefreshToken_iu Executed Successfully'
SET @spResponse =
(
SELECT
0 AS RFTokenSaved,
'UserId And Refresh Token Combination Already Exists' AS Message
FOR JSON Path, WITHOUT_ARRAY_WRAPPER
)
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
BEGIN
ROLLBACK TRANSACTION INSERT_REFRESHTOKEN
END
SET @errorMessage = ERROR_MESSAGE()+
'Line Num:' + CONVERT (VARCHAR(5), ERROR_LINE())+
',ErrorNum:' + CONVERT (VARCHAR(5), ERROR_NUMBER()) +
',Severity: '+ CONVERT (VARCHAR(5), ERROR_SEVERITY()) +
',State:' + CONVERT (VARCHAR(10), ERROR_STATE())+
', Procedure: ' + CONVERT (VARCHAR(25), OBJECT_NAME (@@procid)) +
', Procedure:' + CONVERT (VARCHAR(25), ERROR_PROCEDURE())
SET @errorNo = -1
SET @spResponse = ''
RAISERROR (@errorMessage,16,1)
END CATCH
END