Install ORDS & SQL Developer Web for Multitenant, Part Two

In part one we did a CDB install of ORDS, and got our users and PDBs working for SQL Developer Web.

Today, I’m going to show you how to step it up a notch!

1. Configure the PDB Lifecycle Operations for the DBAPI
2. Create a new PDB from a POST
3. Login as a user from said new PDB using SQL Developer Web

If this sounds familiar, it should, I’ve shown a variation of this before back in 2020.

However, repetition is good, AND the process has changed slightly since version 22.1 of ORDS was released and the commands are different now.

My rig remains exactly the same as it was stated in Part One.

The Docs

7.2 Advanced Setup to Enable the ORDS Database API

You could follow along to these docs and be successful, but people like the blogs, so here we are.

How it works

If we’re going to be doing things like creating PDBs, then we need to be in the Container Database, as an administrator.

ORDS normally starts a REST request by grabbing a connection from the pool as ORDS_PUBLIC_USER, switches over to the correct container (PDB), and proxy connects as the schema where the REST API is defined.

But for these endpoints, ORDS is going to need a session in the CDB. So we’re going to create an admin account in the CDB, and configure ORDS to use that.

1. Creating the database user

I’m going to log into the CDB as a highly privileged user, and do this –

CREATE USER C##DBAPI_CDB_ADMIN IDENTIFIED BY oracle;
GRANT SYSDBA, PDB_DBA TO C##DBAPI_CDB_ADMIN CONTAINER = ALL;

You can do it in any tool, but my old standby still works nicely enough.

2. Telling ORDS about our user

You need to know where your configuration directory for ORDS is. You NEED to include that on your call to the ords program in the bin directory.

Jeffreys-Mini:bin thatjeffsmith$ ./ords --config /opt/ords/config config set db.cdb.adminUser "C##DBAPI_CDB_ADMIN as SYSDBA"

ORDS: Release 22.2 Production on Thu Jul 07 12:49:37 2022

Copyright (c) 2010, 2022, Oracle.

Configuration:
  /opt/ords/config/

The setting named: db.cdb.adminUser was set to: C##DBAPI_CDB_ADMIN as SYSDBA in configuration: default

I only have ONE pool, it’s my default pool for my single database I have configured for ORDS.

If you had 3 or 4 pools, you need to include the pool name as a parameter on the config. You can ask for help –

Jeffreys-Mini:bin thatjeffsmith$ ./ords --config /ords/opt/config config -h
ORDS: Release 22.2 Production on Thu Jul 07 14:34:27 2022
Copyright (c) 2010, 2022, Oracle.
Configuration:
  /ords/opt/config
Usage:
  ords config [OPTIONS] [COMMAND]
Configure application and database connection pool settings
Options:
        --db-pool <string>       The name of the database connection pool to
                                 configure
    -h, --help                   Show usage information for the command
...

So I would add –db-pool poolName to set this for a specific database.

Storing the database password in the wallet

We don’t store passwords in the config files anymore, they go in java wallets.

Jeffreys-Mini:bin thatjeffsmith$ ./ords --config /opt/ords/config config secret db.cdb.adminUser.password

ORDS: Release 22.2 Production on Thu Jul 07 12:50:37 2022

Copyright (c) 2010, 2022, Oracle.

Configuration:
  /opt/ords/config/

Enter the secret for db.cdb.adminUser.password: 
Confirm password: 
The setting named: db.cdb.adminUser.password was set to: ****** in configuration: default

Again, I’m ALWAYS including the –config with the path to my ORDS config. If you don’t do this, you’ll get a warning that it’s defaulting to your current working directory.

I’m prompted for the password, twice. Make sure you have this right from where you created the database user previously.

If your db user passwords expire regularly, you’ll need to update it. When that happens, you can follow this post.

Create an ORDS user

What kind of user is this? This is an ORDS user that will have roles attached. This is the user you’ll use to BASIC AUTH your way into the PDB operations as you GET, PUT, POST, DELETE your way through.

This isn’t a database user.

Jeffreys-Mini:bin thatjeffsmith$ ./ords --config /opt/ords/config config user add ordspdbadmin "SQL Administrator, System Administrator"

ORDS: Release 22.2 Production on Thu Jul 07 12:54:41 2022

Copyright (c) 2010, 2022, Oracle.

Configuration:
  /opt/ords/config/

Enter the password for ordspdbadmin: 
Confirm password: 
Created user ordspdbadmin in file /opt/ords/config/global/credentials

The username is arbitrary, use whatever you’d like. The roles required are specified in the Database API docs, but for the PDB lifecycle the docs say you need SQL Administrator

Restart ORDS

When ORDS starts back up, it’ll re-read it’s config files and see that you have made your configuration changes, something like this –

Let’s give it a whirl!

If we continue through the docs we’ll see this line:

The ORDS role, SQL Administrator must be used to access the https://<server>/ords/_/db-api/stable/database/pdbs/ services.

So let’s hit that endpoint, it’s going to bring back a list of our PDBs on the container.

Creating a new PDB

I’m going to use that SEED database to create a brand new PDB.

Straight from the DBAPI docs, I’ve submitted this payload on a POST to the same endpoint above.

{
  "method": "CREATE",
  "pdb_name": "pdb_sample",
  "adminName": "pdbadmin",
  "adminPwd": "W3lc0m31",
  "fileNameConversions": "('/u01/app/oracle/oradata/ORCLCDB/pdbseed/','/u01/app/oracle/oradata/ORCLCDB/pdb_sample/')",
  "unlimitedStorage": true,
  "reuseTempFile": true,
  "totalSize": "UNLIMITED",
  "tempSize": "UNLIMITED"
}

I included the name of my PDB, the username/password for the admin account on that PDB, and I grabbed the file system path’s for the existing SEED database and for the new PDB.

I could confirm this by refreshing my GET request –

Can I use SQL Developer Web on this new PDB?

Yes! ORDS automatically registers new PDBs as they come in. What you’ll need first is a REST Enabled Schema on the new PDB.

ORDS_ADMIN.ENABLE_SCHEMA(.          p_enabled => TRUE,
                                     p_schema => 'JEFF',
                           p_url_mapping_type => 'BASE_PATH',
                        p_url_mapping_pattern => 'jefe',
                             p_auto_rest_auth => FALSE);

ORDS will know and address the JEFF schema as ‘jefe’ – this becomes important when logging in.

Logging into our new PDB and User

And that’s it!

More resources:

• Creating users via REST?
• Using REST Clients easily with the DB API
• DBAPI and APEX
• DBAPI and Data Pump
• DBAPI, curl, and Autonomous