REST Enable Schema Aliases and SQL Developer Web

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
• SQL Developer Desktop UI
• SQL Developer Web UI
• APEX REST Development

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.

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.

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

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       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 HR 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 http:8080/ords/hr/employees/, the ‘hr’ in that URL is the mapping pattern that represents the HR 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 ran this:

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

Any REST service we would want to access published in the HRREST schema would be addressed as http:8080/ords/abc/…

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. type in your REST Enabled schema alias
3. type in your Oracle username and password
4. start using SQL Developer Web

If you try to login to a REST Enable schema that has an ALAS <> to the username, and you don’t provide the alias in the first login page, you’ll see this instead of the username/password dialog: