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?


  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?

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

Client IDs and Secrets always end in a ‘.,.’ – don’t miss those!

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.’

Something is, ‘missing.’

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

  ORDS.CREATE_ROLE(p_role_name => 'beers_client');  
      p_name             => 'beers_client',
      p_client_id        => 'gcw4QUOiITMyPIycaQdsZQ..',
      p_grant_type       => 'client_credentials',
      p_client_name     => 'beers_client',
      p_role_name => 'beers_client'); 


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?

Browsing the ORDS_EXPORT PL/SQL API via VS Code and the SQL Developer Extension.

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

                     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 –

This is the same output from before when using the GUI.

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.’

Make the SECRET, something else.

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.

    CLIENT VARCHAR2(25) := 'beers_client';
        NAME = CLIENT;

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

          p_client_name => client,
         p_editing_user => 'BEERS',
        p_client_secret => secret

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.


I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment