ORDS & SQL Developer Web 22.4.2 – Self Service Schemas

Oracle REST Data Services version 22.4.2 is now available. There are four things of note in this release:

• BUG 34943125 : Prehook proc called on PL/SQL Gateway Requests (FIXED)
• Data Pump Import Wizard is back, and improved
• SQL Developer Web Look & Feel gets a facelift
• New Feature: Self Service Schemas! (Docs)

Download ORDS 22.4.2

Upgrading ORDS and Enabling the New Feature

We’re going to use the interactive installer to upgrade ORDS in my database. My pool and configuration files are already in-place.

Our new feature, ‘Self Service Schema’ is very powerful and requires a parameter to be added in the ORDS configuration files before you can use it in the UI.

When enabled, folks in your organization can go to a ORDS hosted page, and ask for a database USER account to be created for them.

To make the feature available in SQL Developer Web, run this command.

Introducing the Self-Service USER Portal

Let’s say you are an associate professor at State University XYZ, and are teaching a Database 101 Course. And, because you are awesome, you are using Oracle Database to instruct your students the in’s and out’s of SQL, database design, and just data in general.

And you’re going to host an Oracle Database, so your students don’t have to learn how to do an Oracle Database install, or bother managing said instance.

Are you, the professor going to manually sign-up 100 people every semester? In fact, many of you have been doing this.

Wouldn’t it be nicer if you could just send your class a link, so they could request their account? That’s exactly the experience you can provide now via Oracle SQL Developer Web and ORDS.

This feature isn’t bespoke for Universities and students. It could be used for various scenarios. Maybe you run an IT department and need to make it easy for business analysts to get access to your database. Or maybe you have a better imagination than I do 🙂

New User Request Workflow

1. System Administrator enables the feature
   • Requires ORDS and SQL Developer Web
   • Requires Database to have SMTP configured (for email notifications)
   • DBA defines PREFIX to be applied to all system generated accounts
2. A public URL is hosted by ORDS
3. Cholula, our new student, needs an account, and comes to the page and fills out the form
4. The request is submitted
5. A DBA reviews requests, accepts or rejects
6. If accepted, an email goes to Lula with a link for her to follow
7. Lula follows her link, and picks her password
8. Lula is then sent a link to follow to login and start using her database!

Enabling and Configuring Self Service Schema

Step 1 of enabling this, is making sure the ORDS server config has the parameter ‘feature.sdw.selfServiceSchema’ set to true.

After that, in SQL Developer Web’s USER Administration page, you need to activate the feature.

This will open a slider, as shown here:

Now, for ANY user that gets generated, it will start with ‘JEFF_BLOG,’ followed by a system generated string.

Note: if you don’t have SMTP configured on your database, you’ll get a warning.

Walking the walk – Requesting our new database account!

I’m going to get an account for my doodle, Cholula. She goes by Lula for short. So Lula will get the link from her ‘professor’ to sign up for her database user.

https://cs.thatjeffsmith.ed/ords/hr/_sdw/sign-up/ — not a real link

The ‘hr/’ derives from the DBA who enabled the service. I setup the feature using my HR account, so that’s why the link is under ‘ords/hr/’

Once Cholula goes there, this is what she will see –

The “Justification” box is free text field. Maybe you want your students to be able to add a note beyond just the ‘Gimme Oracle!’

After clicking ‘Next,’ Cholula will see this –

Walking the walk – Admins approve/reject requests

Come back to the User Administration page in SQL Developer Web. We’re going to navigate to the ‘Self Service Schema’ item on the navigation menu bar.

And these ‘PENDING’ accounts don’t exist, yet.

I can simply click the Approve or Reject button, or I could click into the details of the request.

Approve!

Walking the walk – User finishes the process

After the admin hits ‘Approve’ – there is still no database account. It is not until Cholula accepts receipt, follows the link, and provides her password that the USER is created.

The approvals panel automatically filters to show only ‘Pending’ requests, but if you remove the filter, you’ll see everything, including approved, rejected, and fulfilled requests.

Once the admin hits ‘Approve,’ they’ll get a preview of what the user will see in their email.

Lula gets her email notification, which looks like this:

Which looks like this:

We wanted to show the password complexity required, but there’s no easy way to query this out of the database dynamically. So instead, I suggest on your internal Sharepoint/Confluence/Whatever page, you have instructions for your students or employees that include the password complexity rules.

Once Lula hits ‘Submit’ and the account is successfully created, she gets this message with a link to get started with her new account.

It’s at this point that the database account/user/schema has been created. If we query DBA_USERS for everything started with ‘JEFF_BLOG%’ we’ll see everyone who has come through the portal.

Walking the walk – Logging in

Once Lula clicks the ‘Start using schema’ button, she’s brought to the login page.

Once Lula is logged in, she can choose where to go next, but MOST people, and I suspect dogs, would go straight to the ‘SQL’ page.

Questions?

• But Jeff, we don’t want this feature!
  No worries! It’s off by default and requires 2 steps to enabled it.
• Is it available in Autonomous?
  Not yet. If you want it there, please let us know!
• What do the new user accounts get?
  The account is REST Enabled so they can login to SQL Developer Web. It has the DEFAULT profile, and additional is granted CONNECT and RESOURCE.