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!
- Configure the PDB Lifecycle Operations for the DBAPI
- Create a new PDB from a POST
- 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.
![](https://www.thatjeffsmith.com/wp-content/uploads/2022/07/image-10-1024x439.png)
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 –
![](https://www.thatjeffsmith.com/wp-content/uploads/2022/07/image-11-1024x719.png)
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.
![](https://www.thatjeffsmith.com/wp-content/uploads/2022/07/image-12-1024x712.png)
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.
![](https://www.thatjeffsmith.com/wp-content/uploads/2022/07/image-13-1024x547.png)
I could confirm this by refreshing my GET request –
![](https://www.thatjeffsmith.com/wp-content/uploads/2022/07/image-14-1024x430.png)
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.
![](https://www.thatjeffsmith.com/wp-content/uploads/2022/07/image-15-1024x755.png)
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
![](https://www.thatjeffsmith.com/wp-content/uploads/2022/07/cloned-pdb-sdw-login.gif)
And that’s it!
More resources: