REST API for Creating Oracle Database Users? Yup.

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.

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

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.

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.

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.

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!

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 –

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 –