As of dbFront 1.4.0.0517 you can configure dbFront to enable you to connect to an OAuth secured service and receive and store the Security Token.
The connection details, message formats and message sequences are handled by the procedure. This allows you to connect to a wide variety of OAuth like services.
The following example uses a stored procedure for SQL Server to connect to GitHub.
- The procedure [OAuth].[Connect_GitHub] needs to be configured in a RunProcedure action button with the expression parameters of %UserName% and {url(full)}
- The procedure [OAuth].[Connect_GitHub] should also be configured as a Request Handler on the database with a handler name of authGitHub
The result type on the RunProcedure button should be Automatic or ServerDirected. For more details see: Action Buttons
The result type on the Request Handler should be Automatic or ServerDirected. For more details see: Request Handler
Utility Functions
Utility function to extract URL Parameters
CREATE FUNCTION [dbo].[fnGetURLParameter](
@paramName [varchar](16),
@queryString [varchar](2048)
) RETURNS [varchar](1024) AS
BEGIN
-- Strip initial part of URL
declare @paramsStart int = charindex('?', @queryString);
if (@paramsStart > 0)
set @queryString = substring(@queryString, @paramsStart + 1, len(@queryString));
-- Find Parameter=@paramName and return value into @paramValue.
declare @paramValue varchar(1024) = null;
select @paramValue = substring(value, len(@paramName) + 2, 256)
From STRING_SPLIT (@queryString, '&', 1)
WHERE SUBSTRING(value,1,len(@paramName) + 1) = concat(@paramName, '=');
-- Return the Parameter
return @paramValue;
END;
GO
Utility function to encode the URL so that it can be correctly included as a redirect parameter.
CREATE FUNCTION [dbo].[UrlEncode](@url NVARCHAR(1024))
RETURNS NVARCHAR(3072)
AS
BEGIN
-- Source: https://stackoverflow.com/a/2905728/438458
DECLARE @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072)
SET @count = LEN(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i <= @count)
BEGIN
SET @c = SUBSTRING(@url, @i, 1)
IF @c LIKE N'[A-Za-z0-9()''*\-._!~]' COLLATE Latin1_General_BIN ESCAPE N'\' COLLATE Latin1_General_BIN
BEGIN
SET @urlReturn = @urlReturn + @c
END
ELSE
BEGIN
SET @urlReturn =
@urlReturn + '%'
+ SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),3,2)
+ ISNULL(NULLIF(SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),5,2), '00'), '')
END
SET @i = @i +1
END
RETURN @urlReturn
END
GO
Supporting Table
Table used to track and manage the Authentication Requests.
CREATE SCHEMA OAuth;
GO
CREATE TABLE [OAuth].[Requests](
[RequestId] [int] IDENTITY(1,1) NOT NULL,
[State] [varchar](100) NOT NULL,
[Code] [varchar](200) NULL,
[FromUrl] [varchar](2048) NULL,
[CallbackUrl] [varchar](2048) NULL,
[UserId] [varchar](50) NOT NULL,
[Token] [varchar](1024) NULL, -- THIS VALUE SHOULD BE ENCRYPTED.
[RequestDT] [datetime] NOT NULL,
[CompleteDT] [datetime] NULL,
[ExpiryDT] [datetime] NULL,
[Status] [char](1) NOT NULL,
CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED
(
[RequestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [OAuth].[Requests] ADD CONSTRAINT [DF_Requests_RequestDT] DEFAULT (getdate()) FOR [RequestDT]
GO
ALTER TABLE [OAuth].[Requests] ADD CONSTRAINT [DF_Requests_Completed] DEFAULT ('N') FOR [Status]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'[N]ew, [C]omplete, E[X]pired' , @level0type=N'SCHEMA',@level0name=N'OAuth', @level1type=N'TABLE',@level1name=N'Requests', @level2type=N'COLUMN',@level2name=N'Status'
GO
Main Stored Procedure
This stored procedure is called 3 times during a connection request,
- Create the URL and trigger a redirect,
- Receive the callback request and trigger a WebRequest,
- Process the WebRequest.
This stored procedure contains the Client ID and Secret and should also be encrypted.
Create PROCEDURE [OAuth].[Connect_GitHub] (
@currentUser varchar(256), -- Required: current user
@requestUrl varchar(256), -- Required: URL at time of request or callback request
@postData varchar(1024) = null, -- Future: postData if provided.
@jsonDBFInfo varchar(2048) = null, -- Json structure containing extra request info provided by dbFront.
@jsonDBFResponse varchar(2048) = null -- Json structure containing data from WebRequested
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @databaseKey as varchar(100) = dbo.fnGetURLParameter('k', @requestUrl);
-- GitHub Application client id and secret
declare @client_id as varchar(200) = 'Ov-Secret-Secret-Secret-uJ'; -- !!SECRET!!
declare @client_secret as varchar(200) = 'd49-Secret-Secret-Secret-8d3' -- !!SECRET!!
-- Step 1) Create the URL and Redirect.
if (@jsonDBFInfo is null)
begin
declare @new_state as varchar(200) = lower(Format(CAST(CRYPT_GEN_RANDOM(32) AS bigint),'X'));
INSERT INTO [OAuth].[Requests] ([State], [FromUrl], [UserId])
VALUES (@new_state, @requestUrl, @currentUser);
-- Prepare instructions for dbFront which will open the URL and redirect the request
SELECT
'OpenURL' as Action,
concat(
'https://github.com/login/oauth/authorize',
'?client_id=', @client_id,
'&state=', @new_state,
'&response_type=code',
'&scope=repo'
-- '&redirect_uri=',dbo.UrlEncode(concat('http://%YourdbFrontServer%/app/default.aspx?request=authGitHub&k=', @databaseKey);
) as Url,
'Redirect' as Target,
'GitHub OAuth Connection Initiated' as Message;
end
else
begin
-- All requests below are after dbFront receives the callback request.
declare @state as varchar(100) = dbo.fnGetURLParameter('state', @requestUrl);
declare @code as varchar(100) = dbo.fnGetURLParameter('code', @requestUrl);
declare @handle as varchar(100) = IsNull(JSON_VALUE(@jsonDBFInfo, '$.Handle'), @state);
declare @requestId int;
declare @redirectUrl varchar(1024);
declare @status varchar(1);
select top 1
@requestId = r.[requestid],
@redirectUrl = r.[FromUrl],
@status = r.[Status]
from [OAuth].[Requests] r
where r.[state] = @state;
-- Invalid Request - Rejected.
if (@requestId is null or @status = 'S')
begin
SELECT
'OpenDialog' as Action
, 'Invalid or Expired Request' as Message
, @handle as Handle;
-- Step 2) Correct Callback request received with @code and matching @state
end else if (@status = 'N') begin
update [OAuth].[Requests] set
[Code] = @code,
[CallbackUrl] = @requestUrl,
[CompleteDT] = getdate(),
[status] = 'C'
where [requestid] = @requestid;
SELECT
'WebRequest' as Action
, 'https://github.com/login/oauth/access_token' as Url
, (SELECT
'application/json' as Accept
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as Header
, (SELECT
[OAuth].[fnGitHubClientId]() as client_id,
@client_secret as client_secret,
@code as code
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as 'Body'
, @handle as Handle;
end else if (@status = 'C') begin
declare @content as varchar(1024) = JSON_VALUE(@jsonDBFResponse,'$.Content');
declare @accesstoken as varchar(1024) = JSON_VALUE(@content, '$.access_token');
-- Step 3) WebRequest with @accesstoken received.
if (@accesstoken is not null)
begin
update [OAuth].[Requests] set
[Token] = @accesstoken,
[CompleteDT] = getdate(),
[status] = 'A'
where [requestid] = @requestid;
SELECT
'OpenURL' as Action
, @redirectUrl as Url
, 'Redirect' as Target
, 'Access Token Code Saved' as Message
, @state as Handle;
-- Access token not received.
end else begin
SELECT
'OpenDialog' as Action
, concat('Cannot get Access Token: ', JSON_VALUE(@jsonDBFResponse,'$.Status')) as Message
, (SELECT
'retry' as Retry,
'cancel' as Cancel
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as Buttons
, @handle as Handle;
end
-- The request is at an invalid status.
end else begin
SELECT 'OpenDialog' as Action
, 'Invalid Status' as Message
, @handle as Handle;
end;
end;
END
GO
