Mostly Making oracle easy, mostly:
  • ORDS & SQL Developer Web 22.4.2 – Self Service Schemas
  • Generating DDL for your Oracle objects in SQL Developer Web
  • Hours wasted watching Grey’s Anatomy? Finding out with SQL!
  • Learning SQL using Spotify II: Functions, Group By, & Dates
  • Learning SQL with your own Spotify Streaming History data
  • Troubleshooting ORDS startup errors, the pool is invalid.
  • Create a ORDS REST API in a single PL/SQL call
  • Physical Properties in Oracle TABLE Liquibase changeSets
Search for:
  • Twitter
  • YouTube
  • LinkedIn
ThatJeffSmith ThatJeffSmith
  • SQL Developer
  • SQL Developer Web
  • REST APIs
  • Data Modeling
  • Ask A Question
  • About
In ORDS

REST Enable Schema Aliases and SQL Developer Web

January 3, 2020 3 Mins Read

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
REST Enabling your SCHEMA in SQL Developer (Desktop)

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
Step 1
Step 2
Step 3, you’re done!

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:

ORDS doesn’t know hrrest, but it DOES know abc
loginrestsqldev-web
thatjeffsmith
Author thatjeffsmith

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

  • Website
Prev Post

SQL Developer Web is now Available

December 23, 2019 4 Mins Read
Next Post

Protect your data with Encrypted Data Pump Jobs

January 6, 2020 2 Mins Read

Related Posts

ORDS & SQL Developer Web 22.4.2 – Self Service Schemas

January 31, 2023

Generating DDL for your Oracle objects in SQL Developer Web

January 26, 2023

Learning SQL with your own Spotify Streaming History data

January 17, 2023

Troubleshooting ORDS startup errors, the pool is invalid.

January 10, 2023

Create a ORDS REST API in a single PL/SQL call

January 6, 2023

Physical Properties in Oracle TABLE Liquibase changeSets

January 4, 2023

4 Comments

  1. Avatar
    Ram 2 years ago Reply

    I am using SQL Developer Version 4.1.4.21 .
    I am not sure there is some issue with tool or I am missing some steps,
    Sometimes , at connecion/scema level when I right click , it does not show option for enabling/disabling REST services , is it a bug ??

    • thatjeffsmith
      thatjeffsmith Post Author 2 years ago

      That only works when at least ords v3.. something or higher is installed on that database.

      Also, version 4.1 is ancient, go grab version 19.4 or 20.2

  2. Avatar
    Piotr 3 years ago Reply

    Is it possible to use SQL Developer Web with multiple database mapping configuration of ORDS based on request path prefix?
    e.g. if I have in url-mapping.xml two pools named db1 and db2 mapped respectively to base-paths /db1 and /db2 and no other default pools configured how would I access SQL Dev web for each database?
    Using /ords/sql-developer shows logon page, but providing alias of ORDS enabled schema fails with 404 as shown on last screenshot in this blog.
    Using /ords/db1/sql-developer results in instant 404 error page.
    Did I miss something?

    • thatjeffsmith
      thatjeffsmith Post Author 3 years ago

      You didn’t miss anything, you found a bug with the /sql-developer page logic. It assumes a single connection pool mapped to ords/

      So you need to use
      /ords/db1/user/_sdw for your 1st pool
      /ords/db2/user/_sdw for your 2nd pool

Write A Comment Cancel Reply

  • Search 1,000+ posts
  • What it is I do here

    I'm a product manager at Oracle, and
    I publish weekly - tips and tricks for Oracle SQL Developer, SQLcl, SQL Developer Data Modeler, and Oracle REST Data Services.

    Twitter YouTube LinkedIn
  • Your Favorite Posts
    • How to Import from Excel to Oracle with SQL Developer
    • Keyboard Shortcuts in Oracle SQL Developer
    • Enabling DBMS_OUTPUT by default in SQL Developer
    • Top 10 Preferences to Tweak when using SQL Developer
    • Formatting Query Results to CSV in Oracle SQL Developer
    • How To: Generate an ERD for Selected Tables in SQL Developer
    • Resetting Your Oracle User Password with SQL Developer
    • Die! Or How to Cancel Queries in Oracle SQL Developer
  • Enter your email address
  • Mostly Making Oracle Easy, Mostly

    Privacy Policy 

  • Mastodon
  • Twitter
  • YouTube
  • LinkedIn
  • Download SQL Developer
  • Developer Tools Message Boards

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle. © Jeff Smith and ThatJeffSmith, 2021

Top
ThatJeffSmith