REST Enable Schema Aliases and SQL Developer Web

Updated: 2 April, 2024

If you have ORDS 19.4 up and going, with SQL Developer Web enabled, you’re almost ready to login and start running queries.

For an Oracle Database USER to be available to login to SQL Developer Web, you must first REST Enable the Oracle USER account.

You can do this a few different ways:

• PL/SQL API (so, from any SQL script or SQL interface)
• SQL Developer Desktop
• SQL Developer Web

All of the interfaces at the end of the day, run the same block of code that executes the ORDS or ORDS_ADMIN PL/SQL API – I share that code, just keep reading.

What does REST Enabling a schema, mean?

The ORDS JDBC connection pools to your Oracle Database(s) are populated with connections using the ORDS_PUBLIC_USER account.

When you go do do some work on your database, ORDS plucks a session from the pool, and Proxy Connects to the REST Enabled Schema defined for the RESTful Service or Endpoint.

I have a user called METERS, and it’s where I keep my home’s smart water utility meter data.

If I want to publish a service in the METERS schema, then I must FIRST REST Enable the METERS user. When you do this, you are also telling ORDS that it’s allowed to login as METERS for a SQL Developer Web session.

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'METERS',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'meters',
                       p_auto_rest_auth => FALSE);
    COMMIT;
END;

This ORDS API call does a couple of things. The first thing it does, it grants proxy connect to the ORDS_PUBLIC_USER account. The second thing it does, it allows you to publish RESTful Services and/or REST enable objects in your schema.

This PL/SQL call is somewhat self-explanatory. You can probably tell by just looking at it, that we’re about to REST Enable the METERS schema. There are two pieces here that are NOT so self-explanatory.

P_URL_MAPPING_PATTERN and P_AUTO_REST_AUTH.

Note: P_AUTO_REST_AUTH => TRUE means to protect the /metadata-catalog and OpenAPI (Swagger) endpoints for the schema. If it’s FALSE, anyone can get an inventory/description of the services published in your schema.

P_URL_MAPPING_PATTERN .. the Schema Alias

This tells ORDS what pattern to recognize in the HTTP requests coming in for a particular database, so it can match up the Oracle schema/user in play.

As defined in the code sample above, if ORDS received a HTTP GET request for https://…/ords/meters/employees/, the ‘meters’ in that URL is the mapping pattern that represents the METERS user in the database.

For security purposes, you may not want to expose things like the names of schemas in your Oracle Database when publishing REST APIs. So if we run this:

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'METERS',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'water',
                       p_auto_rest_auth => FALSE);
    COMMIT;
END;

Any REST service we would want to access published in the METERS schema would instead be addressed as https://…/ords/water/…

What does this have to do with SQL Developer Web?

The login process is pretty simple for REST Enabled schemas, assuming the alias matches the schema name.

NO ALIAS

1. navigate to /ords/sql-developer
2. type in your rest enabled schema
3. type in your Oracle username and password
4. start using SQL Developer Web

ALIAS

1. navigate to /ords/sql-developer
2. click on the advanced panel
3. type in the rest enabled schema alias
4. type in your Oracle username and password
5. start using SQL Developer Web

And if we look at my SQL worksheet, the URL records the alias used for the METERS database user account.

If you try to login to a REST Enable schema that has an ALIAS <> to the username, and you don’t provide the alias in the first login page, you’ll see this error:

Invalid credentials
If your ORDS schema alias is different from your username, you can set it using the "Path" input in the "Advanced" options

How can we make this easier for our users, and keep it extra-secure?

You don’t have to rely on your users to know the ‘click the advanced tab and enter your alias’ trick.

Instead, an administrator (DBA) can go to the Users Administration page in SQL Developer Web, and simply grab the direct link URL to send to your user. They can use this link to simply login with only their username and password (Docs.)