How To: Configure Key / Secret
API keys and secrets can be administered by the Admin API or Sandbox Admin App,
but some implementers may prefer to work directly with the data. The SQL queries
on this page enable a database administrator to create keys and secrets. These
queries run against the EdFi_Admin
database.
Before you begin:
- Make sure you have configured ODS instance(s) before administering keys and secrets to connect to it. See How To: Configure ODS Instances
You should replace the values for variables with values you desire.
DECLARE @VendorName nvarchar(150) = 'Local Test Vendor'
DECLARE @NamespacePrefix nvarchar (255) = 'uri://ed-fi.org'
DECLARE @UserFullName varchar(150) = 'Local Test User'
DECLARE @UserEmailAddress varchar(150) = 'localtest@ed-fi.org'
DECLARE @ApplicationName nvarchar(255) = 'Local Test Application'
DECLARE @ClaimSetName nvarchar(255) = 'SIS Vendor'
DECLARE @ApiClientName nvarchar(50) = 'Local Test Api Client'
DECLARE @EducationOrganizationId int = 255901 --Must be an ed-org in the ODS
DECLARE @Key nvarchar(50) = 'testkey'
DECLARE @Secret nvarchar(100) = 'testsecret'
DECLARE @OdsInstanceId int = 1 -- Must be a valid OdsInstanceId from the OdsInstances table
DECLARE @IsPopulatedSandbox bit = 1
-- For Non-Sandbox deployments
DECLARE @UseSandbox bit = 0
-- For Sandbox
--DECLARE @UseSandbox bit = 1
DECLARE @VendorId int
DECLARE @UserId int
DECLARE @ApplicationId int
DECLARE @ApplicationEducationOrganizationId int
DECLARE @ApiClientId int
-- Clear @IsPopulatedSandbox if not using sandbox
IF (@UseSandbox = 0)
SET @IsPopulatedSandbox = 0
-- Ensure Vendor exists
SELECT @VendorId = VendorId FROM [dbo].[Vendors] WHERE VendorName = @VendorName
IF(@VendorId IS NULL)
BEGIN
INSERT INTO [dbo].[Vendors] (VendorName)
VALUES (@VendorName)
SET @VendorId = SCOPE_IDENTITY()
END
-- Ensure correct namespace prefixes are set up
DELETE FROM [dbo].[VendorNamespacePrefixes] WHERE Vendor_VendorId = @VendorId
INSERT INTO [dbo].[VendorNamespacePrefixes] (Vendor_VendorId, NamespacePrefix)
VALUES (@VendorId, @NamespacePrefix)
-- Ensure User exists for test Vendor
SELECT @UserId = UserId FROM [dbo].[Users] WHERE FullName = @UserFullName AND Vendor_VendorId = @VendorId
IF(@UserId IS NULL)
BEGIN
INSERT INTO [dbo].[Users] (Email, FullName, Vendor_VendorId)
VALUES (@UserEmailAddress, @UserFullName, @VendorId)
SET @UserId = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE [dbo].[Users] SET Email = @UserEmailAddress WHERE UserId = @UserId
END
-- Ensure Application exists
SELECT @ApplicationId = ApplicationId FROM [dbo].[Applications] WHERE ApplicationName = @ApplicationName AND Vendor_VendorId = @VendorId
IF (@ApplicationId IS NULL)
BEGIN
INSERT INTO [dbo].[Applications] (ApplicationName, Vendor_VendorId, ClaimSetName)
VALUES (@ApplicationName, @VendorId, @ClaimSetName)
SET @ApplicationId = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE [dbo].[Applications] SET ClaimSetName = @ClaimSetName WHERE ApplicationId = @ApplicationId
END
-- Ensure ApiClient exists
SELECT @ApiClientId = ApiClientId FROM [dbo].[ApiClients] WHERE Application_ApplicationId = @ApplicationId AND [Name] = @ApiClientName
IF(@ApiClientId IS NULL)
BEGIN
INSERT INTO [dbo].[ApiClients] ([Key], [Secret], [Name], IsApproved, UseSandbox, SandboxType, Application_ApplicationId, User_UserId, SecretIsHashed)
VALUES (@Key, @Secret, @ApiClientName, 1, @UseSandbox, @IsPopulatedSandbox, @ApplicationId, @UserId, 0)
SET @ApiClientId = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE [dbo].[ApiClients] SET [Key] = @Key, [Secret] = @Secret, UseSandbox = @UseSandbox, SandboxType = @IsPopulatedSandbox, User_UserId = @UserId, SecretIsHashed = 0 WHERE ApiClientId = @ApiClientId
END
-- Ensure ApiClientOdsInstanceExists
IF NOT EXISTS (SELECT 1 FROM [dbo].[ApiClientOdsInstances] WHERE ApiClient_ApiClientId = @ApiClientId AND OdsInstance_OdsInstanceId = @OdsInstanceId)
BEGIN
INSERT INTO [dbo].[ApiClientOdsInstances] (ApiClient_ApiClientId, OdsInstance_OdsInstanceId)
VALUES (@ApiClientId, @OdsInstanceId)
END
IF (@EducationOrganizationId IS NOT NULL)
BEGIN
-- Clear all education organization links for the selected application
DELETE acaeo
FROM dbo.ApiClientApplicationEducationOrganizations acaeo
INNER JOIN dbo.ApplicationEducationOrganizations aeo
ON acaeo.ApplicationEducationOrganization_ApplicationEducationOrganizationId = aeo.ApplicationEducationOrganizationId
WHERE aeo.Application_ApplicationId = @ApplicationId
DELETE FROM [dbo].[ApplicationEducationOrganizations] WHERE Application_ApplicationId = @ApplicationId
-- Ensure correct education organizations are set up
INSERT INTO [dbo].[ApplicationEducationOrganizations] (EducationOrganizationId, Application_ApplicationId)
VALUES (@EducationOrganizationId, @ApplicationId)
SELECT @ApplicationEducationOrganizationId = SCOPE_IDENTITY()
INSERT INTO [dbo].[ApiClientApplicationEducationOrganizations] (ApplicationEducationOrganization_ApplicationEducationOrganizationId, ApiClient_ApiClientId)
VALUES (@ApplicationEducationOrganizationId, @ApiClientId)
END
API client creation script samples:
- SQL Server Script: SetupApiClient_SQLServer.sql
- PostgreSQL Script: SetupApiClient_PostgreSQL.sql
Disabling an API Client
An API client may be disabled by setting the IsApproved
column to
false
in the ApiClients
table of the EdFi_Admin
database. Setting
IsApproved
to false
for a client prevents its key and secret from being used
to authenticate and obtain an OAuth bearer token.
To disable the client with the key ApiClientKey1
, the following SQL query could
be run on the EdFi_Admin
database:
BEGIN
UPDATE [dbo].[ApiClients] SET IsApproved = 0 WHERE [Key] = 'ApiClientKey1'
END
While disabling an API client in the EdFi_Admin
database will immediately prevent
the issuance of new OAuth bearer tokens for it, any previously issued
non-expired bearer tokens for the client will remain valid for authorizing API
requests until they expire unless additional actions are taken.
In the event it is necessary to immediately disable a client's ability to authorize API requests using previously issued non-expired OAuth bearer tokens, two additional actions are needed:
-
Removing previously issued tokens for the client from the
ClientAccessTokens
table in theEdFi_Admin
database.For the client used in the example above with the key
ApiClientKey1
, this can be done by running the following SQL query on theEdFi_Admin
database:BEGIN
DELETE cat
FROM [dbo].[ClientAccessTokens] cat
INNER JOIN [dbo].[ApiClients] ac
ON ac.ApiClientId = cat.ApiClientId
WHERE ac.[Key] = 'ApiClientKey1'
END -
Expiring the
ApiClientDetails
cache.The specific actions needed to expire/clear the
ApiClientDetails
cache depend on the configuration of the ODS/API instance.For a deployment using the default in-memory cache and without the "Notifications" feature enabled, this would be accomplished by restarting the ODS/API application.
If the "Notifications" feature is enabled, the in-memory cache may be invalidated by sending the following Notification payload:
{
"type": "expire-cache",
"data": {
"cacheType": "api-client-details"
}
}Additional details on using the "Notifications" feature to expire in-memory caches can be found in Notifications (Expiring Local Caches Remotely).
If an external cache provider is in use, then an administrator would need to manually remove the relevant ApiClientDetails entries from the external cache.