Redwood Theme, Grey

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.

ords –config c:\ords\23c_config install

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.

ords –config c:\ords\23c_config config set feature.sdw.selfServiceSchema true

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.

Click the ‘Gear’ configuration button to the right of the Create User button.

This will open a slider, as shown here:

I toggle the feature ‘ON’ and I set the USER prefix to ‘JEFF_BLOG’

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!

That’s Lula.

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 –

If you put in a bogus e-mail, you’ll never get your activation email/link.

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 –

Yup, looks right, heel! I mean, Submit!

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.

There could be 0, 1, or hundreds of requests for you to manage.

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.

3 ‘ruffs’ – this is status URGENT.

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.

I could grab that link, and say SMS/text it to Lula.

Lula gets her email notification, which looks like this:

The blue button will navigate Lula back to SQL Developer Web to set their password.

Which looks like this:

Type and confirm the password, hit ‘Submit.’

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.

Woohoo, Cholula is good to go!

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.

Username is pre-filled, but she needs to put in her password.

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.

And now Lula can start learning SQL and doing her homework!

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

    • Cloud stuff, basically. It was purely UI changes for Database Actions and a few lines of CSS being updated.

  1. Rajeshwaran Jeyabal Reply

    Yesterday we got 22.4.1 but today you got changed the blog post title to 22.4.2
    but the first screen shot in this blog post refers to 22.4.1, dont we need to update it ?
    why so much confusion and why sudden (dot) release again ?

    • We pulled 22.4.1 and replaced it with 22.4.2

      If you grabbed 22.4.1, you can simply drop in the new WAR file from 22.4.2 – no need to do an actual database upgrade.

      I didn’t feel like redoing the screenshot because I figured no one would catch that, but you did. Sorry for the confusion.

Write A Comment