Migrating OAuth2 Clients and maintaining their SECRETs

Our scenario: You have an OAuth2 Client for your Oracle Database REST APIs, defined in database 1, let’s call that environment, DEVELOPMENT.

And you’re ready to move your REST Modules and/or AutoREST enabled objects into say environment 2, UAT. And, you have decided you want to have the client SECRET be the same between said environments.

How could you achieve this?

TL/DR:

1. export client using ORDS_EXPORT.EXPORT_OAUTH_CLIENT
2. retrieve the current secret (stow that step 4)
3. re-create the CLIENT using output from step 1
4. reset the SECRET with value retrieved from step 2 with OAUTH.UPDATE_CLIENT_SECRET (Docs)

Detailed Explanation

But before I get into this, perhaps you’re looking for help when it comes to using OAuth2 clients with one or more REST APIs. Here are the resources I usually pass along to those folks –

Looking for OAuth2 Examples?

• Using OAuth2 clients from a Jupyter Notebook
• Securing Autonomous DB REST APIs with OAuth2
• Deploying REST APIs that span multiple schemas, secured via OAuth2

We have a few interfaces for managing your OAuth2 Clients, including PL/SQL and web. Here’s what the web interface looks like.

OAuth2 Client page in SQL Developer Web

Point & Click Exports

You can also ask to ‘Export’ your module from the web interface, it’ll give your the PL/SQL API call to recreate your Client, ‘somewhere else.’

Let’s take a quick look at that generated code:

BEGIN
  ORDS.CREATE_ROLE(p_role_name => 'beers_client');  
     
  ORDS_METADATA.OAUTH.IMPORT_CLIENT(
      p_name             => 'beers_client',
      p_client_id        => 'gcw4QUOiITMyPIycaQdsZQ..',
      p_grant_type       => 'client_credentials',
...
  ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE( 
      p_client_name     => 'beers_client',
      p_role_name => 'beers_client'); 
    
COMMIT;

END;

The script does 3 things: creates a ROLE, creates the client, and finally it grants the role that was just created to the role.

There are 2 things it does NOT do:

• set the SECRET to match the current one
• anything with REST Privileges.

The Client WILL have a secret, but it’ll system generated, and most definitely different than the current secret.

If you’ve exported your Modules/Objects and privileges previously, then the REST Privilege will have hopefully already been tied to the Role…which is granted to the Client.

Code Exports

There is an ORDS_METADATA PL/SQL package called, ORDS_EXPORT. And in that package, is a FUNCTION called ‘EXPORT_OAUTH_CLIENT.’

That sounds handy, right?

Let’s run this and see what it looks like.

select ORDS_METADATA.ORDS_EXPORT.EXPORT_OAUTH_CLIENT
  (
                     p_client_name => 'beers_client',
    p_include_security_definitions => null
  )
  from dual;

The default for including the security definitions is TRUE, but if you wanted to pass a FALSE prior to Oracle Database 23, you would need to use Lukas’ trick.

That SQL returns a CLOB and if we’re looking at the output from say, SQL Developer Web, it looks like this –

Just as before, we get the client, but no secret.

PL/SQL to assign a Client SECRET

We have another PL/SQL API in ORDS_METADATA, it’s simply called ‘OAUTH.’

In there is a procedure simply named, ‘UPDATE_CLIENT_SECRET.’

Here’s a dumb little pl/sql script to get the current secret, and then change it to…the existing value. For migrating clients, you would query out the existing secret, and then use that value to run the UPDATE secret on the 2nd environment.

DECLARE
    CLIENT VARCHAR2(25) := 'beers_client';
    SECRET VARCHAR2(25);
BEGIN
    SELECT
        CLIENT_SECRET
    INTO SECRET
    FROM
        USER_ORDS_CLIENTS
    WHERE
        NAME = CLIENT;

    DBMS_OUTPUT.PUT_LINE(client || ' secret is currently set to: "' || SECRET || '"');

    ords_metadata.oauth.update_client_secret(
          p_client_name => client,
         p_editing_user => 'BEERS',
        p_client_secret => secret
    );
END;
/

Running that twice, looks like this –

So, can this be automated?

Yes, of course it’s all SQL-scriptable.

But could it be easier?

Yes, also to that. We need to improve both the REST and Liquibase commands so that Clients can easily be exported and imported, but then it would still be on you to update the secrets.

My caution/advice

If you’re moving your OAuth2 clients between environments, and one of those is ‘production,’ I HIGHLY HIGHLY HIGLHY recommend you don’t maintain the secrets across those environments. Just as you wouldn’t share database passwords between test and prod.