Tinkering: a SQL script for the ORDS_ADMIN.ENABLE_SCHEMA procedure

Post-ORDS installation

Once you’ve installed ORDS, you need to REST-enable your schema before taking advantage of ORDS (I used to forget this step, but now it’s like second nature).

RESOURCES: I've discussed ORDS installation here and here. I'd check both pages if you're unfamiliar with it or want a refresher. 

ORDS.ENABLE_SCHEMA / ADMIN_ORDS.ENABLE_SCHEMA

While logged into your database with SQLcl, you can issue the following commands to not only create a new user but grant them the required Roles (and the underlying Privileges) and REST-enable their schema (aka “ORDS-ifying” a schema):

/* Remember to remove the brackets when you run this code */ 

Create User [username] Identified By [password];
Grant Connect to [username];
Grant Resource to [username];

Grant Unlimited Tablespace to [newuser];

/* This PL/SQL procedure assumes you are logged in as the SYS.
If you are logged in as that new user, AND HAVE BEEN GRANTED
THE DBA ROLE, then you can execute the ORDS.ENABLE_SCHEMA 
procedure */

Begin
    ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
End;
/
Create User [username] Identified By [password];
Grant Connect to [username];
Grant Resource to [username];

Grant Unlimited Tablespace to [newuser];

Begin
    ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
    Commit;
End;
/

Automate because lazy

But even that is too much work for me, so I took a stab at automating this via an SQL script. As you can see, the above commands are simple and repeatable (a perfect candidate for automation). And since I’m constantly adding and dropping users for various reasons, copying and pasting code from an old blog or writing everything by hand gets annoying. Additional reasons for automating:

1. laziness
2. a desire to improve SQL and PL/SQL skills
3. an interest in scripting
4. I get easily distracted

The script

After about a day and a half, I have a working prototype script to call upon when I’m on the SQLcl command line. Here is what I came up with:

NOTE: If you just came here to remix the code, I have this in the scripts folder in my GitHub blog repo as well. Please feel free to sample it and/or roast it/me 🔥. But if you keep scrolling, I'll go into more detail section-by-section.

18-OCT-2023 UPDATE: I've slightly changed this code to include the Commit; command in the PL/SQL portion of the script. You'll see that reflected in line 44. Thanks René 🙌🏻!

INPUT
PROMPT Choose a new database username:
ACCEPT NEWUSER CHAR PROMPT 'Enter new user name hurrr:'
PROMPT Choose a temporary password for &&NEWUSER: 
ACCEPT NEWPASS CHAR PROMPT 'Make it super secret:'

/*
I wish I could figure out a way to ONLY ask for username > check 
that against existing database users > AND THEN allow a user to 
continue with the 'choose password' step. I was only able to figure 
out how to ask for the username and password AND THEN checks 
against the database. I stole the code from this thread: 
https://stackoverflow.com/questions/30710990/creating-an-oracle-user-if-it-doesnt-already-exist 

Currently, its just extra steps for the user; kind of annoying. If you're 
reading this and can figure out a way to get this working, let me know! 
I'll make the change and attribute you in the comments :) 
*/

Set Verify On 

/*
You can refer to section 6.3.10.11 for more details on this 
SET VERIFY OFF command 
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-3ACD41F3-A5A2-48D5-8E81-C29F9C14C865
*/

/*
The difference between using single and double ampersands: 
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-C6BE6E41-821F-413E-B4B1-56AAE4A46298
*/

Declare
check_if_user_exists Integer;
plsql_block VARCHAR2(500);
NEWUSER VARCHAR2(20) := '&&NEWUSER';
Begin
  Select count(*) Into check_if_user_exists From dba_users Where username=NEWUSER;
  If (check_if_user_exists = 0) Then
  Execute Immediate 'Create User &&NEWUSER Identified By &&NEWPASS';
  Execute Immediate 'Grant Connect To &&NEWUSER';
  Execute Immediate 'Grant Resource To &&NEWUSER';
  Execute Immediate 'Grant Unlimited Tablespace To &&NEWUSER';
  plsql_block := 'Begin ORDS_ADMIN.ENABLE_SCHEMA(p_schema => :1); Commit; End;';
  Execute Immediate plsql_block using NEWUSER;
  End If;
End;
/

/*
The p_schema parameter is mandatory, that's why I'm including it. 
If you omit the other parameters, the procedure will use the default 
parameter values. 

Learn more about this procedure here: 
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.2/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html#GUID-459B8B6F-16EC-4FEC-9969-E8231668AD85

I was able to get this entire thing to work through trial-and-error, 
while also using this for reference: 
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm
*/

PROMPT
PROMPT
PROMPT Congrats 🎉 the user: &&NEWUSER, with the password: &&NEWPASS is now a bona fide database user 🙌🏻! 
PROMPT Not only that, &&NEWUSER can log into Database Actions and REST-Enable their database objects too 😍!
PROMPT
PROMPT
PROMPT Click RETURN to return to the SQLcl prompt. And NEVER forget:
PAUSE "You're good enough, you're smart enough, and doggone it, people like you!"

PROPS: I owe much credit to this StackOverflow post and Jon Heller's and Mark Bobak's comments.

Breaking it down

Starting up podman

I’ll first start up podman and cd in the correct directory. “Correct” insomuch that this is where my ordsuserl.sql script lives.

Once my container status displays healthy, I’ll execute the ords serve command. This will launch ORDS in standalone mode (using the embedded Jetty server). I’ll use ORDS in a few minutes, so I should prepare it now.

Prompting the user

I’ll then log into my podman container using the following string:

sql sys/password1234@//localhost:41465/ORCLPDB1 as sysdba

NOTE: I'm logging in as SYS (I've since been informed you shouldn't really be doing this as SYS; in fact, it looks like you shouldn't be doing this as SYSTEM. Tom has some good comments in this post here. 

Why am I not using the podman exec command here?

If you’ve seen my latest post on podman ports and networking, this command contradicts that entire article. There are actually two ways (maybe more, if I’m unaware) you can connect to your Oracle database in a podman container. The first way is to simultaneously hop on over to the container and sign in from inside that container. Your connection string would look like this:

podman exec -it 21entdb sql sys/password1234@//localhost:1521/ORCLPDB1 as sysdba

The second option is to sign in remotely like I’m doing in this current example:

sql system/password1234@//localhost:41465/ORCLPDB1

This is analogous to when you SSH into a machine remotely. I should have mentioned it in this recent YUM/Oracle Linux post. Still, when you create a Compute Instance, you can later SSH into that Instance and perform actions like you usually would in the Terminal or the Command Prompt. But instead of being on your own machine, you’re on a remote machine. Ports are very confusing (for me, at least), so please read that podman ports post.

When you use the exec command, consider yourself on another computer on the Linux operating system. Once there, you must log in using the 1521 port because that is where the database’s TNS Listener (deep dive on Oracle database connection) is. However, when you are outside that container (i.e., that machine’s Linux OS), you need to use your local port (in this case, 41465) because it essentially acts as a proxy or a pass-through to the container’s 1521 port. Savvy 🏴‍☠️?

DISCLAIMER: This my best-effort attempt at explaining this confusing concept. It is subject to change. But I really want people to take advantage of our tools in the Oracle Container Registry, so I hope this helps! 

---

Hath connected to the database

Once I’m in, I can call upon my script to quickly create a new user and REST-enable their schema (recall, I “cd” into the correct directory in an earlier step). The syntax:

@ordsuser.sql

MORE SQLcl: You can read the different SQL, SQLcl, and PL/SQL commands here.

After pressing Return/Enter, a prompt will appear; this is what it looks like on the “front end”:

HINT: I would also spend some time here, learning about the PROMPT, ACCEPT, and INPUT commands. Yes, this comes from the SQL*Plus documentation, but this works as you'd expect in SQLcl.

Meanwhile, here is the corresponding section in the script:

Once I enter the password and press Enter/Return on my keyboard, the rest of the script is automatically executed using the provided username and password as substitution variables for the rest of the script! There is an in-depth explanation in the docs here, but you should grab the code and tinker with it to see how everything interacts and works together.

NOTE: The Set Verify On command displays the changes made from the original PL/SQL procedure and the updated PL/SQL procedure (with the updated username and password). It isn't necessary, but I wanted to provide some feedback to a user. 

PL/SQL procedure

Assuming the user (i.e., the one you selected) doesn’t exist, the PL/SQL procedure should execute without issues. In the following image, you can see what is happening in real time:

1. A new user is created with the assigned username and password
2.  That user is granted the Connect and Resource roles
3.  The schema is then REST-enabled using the ORDS_ADMIN.ENABLE_SCHEMA PL/SQL procedure

Why NEWUSER in the Declare block?

Yeah, good question. At a minimum, you need to include the p_schema in the ORDS_ADMIN.ENABLE_SCHEMA procedure. Read up on that here. But, I wanted the username to remain in lowercase since that will later be used for my schema’s URIs (e.g., http://localhost:8080/ords/ordstest/emp/).

So I quickly taught myself/attempted a crash course on define variables and bind arguments; most of what I learned came from this EXECUTE IMMEDIATE documentation. And that’s why you see this in lines 25 and 34 of the code:

NEWUSER VARCHAR2(20) := '&&NEWUSER'; 

/* as well as */

ORDS_ADMIN.ENABLE_SCHEMA(p.schema => :1);

And on the front end, courtesy of the Set Verify On command, you’ll see that updated block of code:

Feedback

I’ll then provide myself (or the user) with helpful feedback (along with words of encouragement). Once satisfied, I can click the Return/Enter key to exit the script and sign in to Database Actions as that new user.

The corresponding section in the script looks like this:

Sign-in to Database Actions

Now, I can navigate to the Database Actions sign-in page at localhost:8080/ords/sql-developer. If I wanted to, I could also navigate to the newly introduced landing page at http://localhost:8080/ords/_/landing (obviously, depending on your deployment, this address will differ).

SQL Worksheet then the proof

I’ll head to a SQL Worksheet, select All Objects in the Navigator tab, and a clean schema ready to take on the world!

Summary

And this marks the end of today’s lesson. So what did we learn?

1. You can execute SQL scripts directly from the SQLcl command line.
2. My script is cool, but I wish I could verify if a user exists sooner (I end up forcing the user to go through that password step).
3. The script has no exception handling (I’m giving myself a pass on this since I’m just now getting into PL/SQL).
4. Spend some time with the documentation on one screen and your script on another. After some time, you can actually understand how everything interacts.

One final thought. My process required a lot of trial and error, but seeing how everything flows and works is entertaining.

Please sample/remix my code; it’s located in my GitHub blog repo. Make it better, and let me know what you come up with! And be sure to check out ORDS and SQLcl 😍!

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

• Twitter
• YouTube
• LinkedIn
• Instagram
• TikTok
• GitHub
• Mail
• Facebook
• Medium