Managing Database Users with SQL Developer Web

Someone reminded me that I had yet to show you folks a brand new feature in SQL Developer Web – User Management!

SQL Developer Web was made available for on-premises installations this past December, with ORDS 19.4.

For using SQL Developer Web itself, you need a USER. You need a REST Enabled USER. You potentially need to know their schema alias.

This set of screens will allow you to quickly enable access for SQL Developer Web for new/existing users and share links for those folks so they can quickly login.

Requires the DBA Role

If you have ‘the power!’, then clicking the hamburger button in your upper left hand corner will reveal an Admin menu and the new item, ‘User Management.’

From there we’re presented an inventory of database users, as ‘Cards.’

The Interface

Let’s look at a few things presented to us:

Up top, we have MY account. I’m logged in as HR, so I see HR up front, big, and center.

Below that we have the first page of users. We call these display items, ‘Cards,’ and they represent a new design pattern for SQL Developer Web. Instead of just having grids of data to present everything, we can put interesting data up front with each item or object.

Let’s look at a a User card in more detail.

So I have the name of the user.

I can see that they are REST Enabled.

I can see what the URI Template Alias is for accessing things in that user account via ORDS, ‘hrrest’, so I would go to /ords/hrrest/…

I can see the last time they logged in.

I can see the status of their account – is it available, locked, expired?

And finally, I get an easy to copy link for said user to access if they want to login to this database using SQL Developer Web.

If I want to EDIT or change anything about this user, I click the ellipis button in the upper right hand corner.

Create/Edit USER

Clicking the Create User button up top, or the Edit user on a particular card will bring up this dialog, either filled out or blank depending.

For the REST items, ‘Authorization required’ set to TRUE, means you have to authenticate in order to access the metadata-catalog endpoint. This REST service inventories ALL of the REST Services for the schema.

If we toggle over to the Granted Roles…

If you have 200 roles, use ‘Default’ to decide which ones you want to be active at logon, otherwise, LEAVE IT ALONE.

Don’t use ‘Admin’ if you won’t want this user to go and grant said roles to other user.

Drop REST Services

RESTful Services are stored in the ORDS_METADATA schema. When you drop a user in the database, we don’t do go and remove their associated REST Services. That means if you were to ever go and re-create this user AND REST Enable them…their REST Services would become active again.

This action will do two things:

1. Disable REST access
2. Clean up the ORDS_METADATA schema for an account (DELETES the services)

When you DROP a user, you can also say, hey, let’s nuke their REST Services too.

Delete User

Cascade and Drop REST are both off by default.

Searching for our User

We’ll find any object that contains the Search string.

Cards are nice, but gimme a grid!

You might have many hundreds or thousands of users and need to put as much information on the screen at one time. So to stop using the ‘Cards’, click here and get your standard report interface.

And here’s what that looks like.

You still have the search. The grid is sortable, and in this case I’ve asked for the REST Enabled status items to appear first.

Design Patterns going Forward

As we build out support for browsing and managing things, like TABLES or REST Services, you’re going to see these cards. There will also be the ability to toggle over to grids, but Cards will be the default.

You’re also going to see us pivot away from the Menu items up top over to the navigation panel to the left.

Our next update will be version 20.2, and you’ll see a new Home Page and the Instance Viewer added to the Admin panel.

About Autonomous…

This feature is now available in the Oracle Autonomous Shared Databases in the Oracle Cloud (OCI).

Here’s a quick video:

In Autonomous, the availability of the Manage Users card is based on the PDB_DBA role. In a future release, we’ll have it so that any user can manage their own accounts (REST Enable, Change Password).