The ASK: We need a REST API we can use to automate…stuff. One of the things we want to automate is creating database users.

This particular database happens to be an Oracle Autonomous Database, so ORDS already happens to be up and available there.

Now Autonomous Oracle Database already has a set of OCI REST APIs available for various things.

These are VERY handy, but not the level of ADMIN APIs we’re after.

ORDS itself includes database management REST APIs. I’ve demonstrated here before how to use them to do things like…datapump export your schema.

A simple POST to export our your database using Data Pump…no code!

And whilst the DBAPI ORDS deliveres includes some endpoints for USERS, it doesn’t have one for CREATE USER or GRANT…

So what’s an automation engineer to do?

Use the REST Enabled SQL Endpoint!

We can run any ad-hoc SQL script we want via REST. It’s how the SQL Worksheet in SQL Developer Web works.

You can guess where we’re going…

OAuth2 – not database username & passwords, please

I wrote a Best Practices guide for ORDS yesterday. In it, I asked you NOT to use BASIC or Database Authentication, but instead to use OAuth2. It’s faster and more secure.

So here’s some code to create your OAuth2 client.

DECLARE
 l_roles     OWA.VC_ARR;
 l_modules   OWA.VC_ARR;
 l_patterns  OWA.VC_ARR;
BEGIN
 l_roles(1)   := 'SQL Developer';
 ORDS.DEFINE_PRIVILEGE(
     p_privilege_name => 'rest_privilege',
     p_roles          => l_roles,
     p_patterns       => l_patterns,
     p_modules        => l_modules,
     p_label          => '',
     p_description    => '',
     p_comments       => NULL);
 COMMIT;
end;
/

 BEGIN
  OAUTH.create_client(
    p_name            => 'OML_ADMIN_CLIENT',
    p_grant_type      => 'client_credentials',
    p_owner           => 'ADMIN',
    p_description     => 'For creating db users for Oracle Machine Learning',
    p_support_email   => '[email protected]',
    p_privilege_names => 'rest_privilege'
  );

  COMMIT;
END;
/

BEGIN
  OAUTH.grant_client_role(
    p_client_name => 'OML_ADMIN_CLIENT',
    p_role_name   => 'SQL Developer'
  );
  
  COMMIT;
END;
/

The important part in here is the granting of the SQL Developer role to the client. We’re doing this to satisfy this requirement to access the REST Enabled SQL feature in ORDS.

Docs Link

I can see my OAuth2 Client in SQL Developer Web, and I can use it to get my Client ID and Secret – I need these to request my access token.

Below is a screenshot of us requesting a token for you in SQL Developer Web, we even include the cURL command you can try for yourself.

With that ‘Current Token’ I could just paste that directly into Insomnia client, but if it expires, I’d need the Client ID and Client Secret…

We create this OAuth2 Client in the ADMIN schema, and when we call the _/sql endpoint, it’s going to be in the /ords/admin URI, so what we run, will run as the ADMIN database user.

Calling the API

I’m going to use a REST Client, Insomnia.

It has a handy OAuth2 workflow. It can get me access tokens as I need them (expired?), or I can manually supply one if I have it handy.

We’re going to be issuing a POST to /ords/admin/_/sql with a request header Content-Type set to application/sql

You have a few options for how to include the single SQL statement or script you want to run on the API. Like JSON, you can use JSON!

Doc Link. SQL Developer Web actually uses application/json over application/sql for this feature.

The body of our request will be the script we want to execute as the ADMIN user.

CREATE USER OMLUSER IDENTIFIED BY "very very very good password";
GRANT DWROLE TO OMLUSER;
GRANT OML_DEVELOPER TO OMLUSER;
ALTER USER OMLUSER GRANT CONNECT THROUGH OML$PROXY;
ALTER USER OMLUSER QUOTA UNLIMITED on DATA;

CREATE USER OMLUSER2 IDENTIFIED BY "very very very good password";
GRANT DWROLE TO OMLUSER2;
GRANT OML_DEVELOPER TO OMLUSER2;
ALTER USER OMLUSER2 GRANT CONNECT THROUGH OML$PROXY;
ALTER USER OMLUSER2 QUOTA UNLIMITED on DATA;

Now let’s look and see what happens –

I have the exact text as shown in the code snippet above in the Body of the request here.

Did it work? Well the response JSON document seems to indicate so. I see useful messages like ‘User created’ and ‘Grant succeeded.’

I can go into SQL Developer Web for my database and look for my new OML users –

My users, and yes, they’re OML_Enabled, sweet!

Author

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

4 Comments

  1. Curl looks good. I got the following response:

    {“access_token”:”My_Access_Token”,”token_type”:”bearer”,”expires_in”:3600}

    Now I installed Insomia followed your screenshot to setup it same. I can click on “Refresh Token” and get a new one but when I send the Post same 401 as response.

    • Now I got it. Yesterday I run the commands to create the privilege, client and client_role in the SQL sheet of SQL Developer Web. Today I did the same in SQL Developer.

      And now I get an result as expected. Thanks for you post with the idea.

  2. Hello Jeff,

    I was trying your example step by step. The only difference here I’m using Visual Studio Code with the Thunder Client plugin as my REST Client.

    The token is generated successfull but if I issue the POST “https://xxx.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/_/sql” I get a “401 Unauthorized” as response.

    Header “Content-Type” is set to “application/json” and in the body I use “JSON Content” with “{ “statementText”:”SELECT TO_DATE(’01-01-1976′,’dd-mm-yyyy’) FROM dual;”}”.

    Do you have any idea where I went to wrong path in your manual?

    Best,
    Carsten

    • It shouldn’t matter which interface/client you’re using.

      You’ve included the access token on your request?

      When in doubt, test with cURL.

Reply To Carsten Cancel Reply