Mostly Making oracle easy, mostly:
  • SQL and SQL Developer Data Modeler 21.1 are now available
  • JSON Event: Choose your own Adventure!
  • CI/CD with Oracle Database and APEX
  • Four Options for working with ORDS & PLSQL REFCURSORs
  • Creating Users & Granting Storage Quota using Database Actions
  • Building REST APIs, with beer!
  • Downloading/Streaming Content with ORDS (File Downloads!)
  • Creating REST APIs for AWR with ORDS and SQL
Search for:
  • Twitter
  • YouTube
  • LinkedIn
ThatJeffSmith ThatJeffSmith
  • SQL Developer
  • SQL Developer Web
  • ORDS
  • Data Modeling
  • Ask A Question
  • About
In ORDS

REST Enable Schema Aliases and SQL Developer Web

January 3, 2020 3 Mins Read
Share
Share on Facebook Share on Twitter LinkedIn Email

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
4
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

SQL and SQL Developer Data Modeler 21.1 are now available

April 14, 2021

JSON Event: Choose your own Adventure!

April 13, 2021

CI/CD with Oracle Database and APEX

April 8, 2021

Four Options for working with ORDS & PLSQL REFCURSORs

March 30, 2021

Creating Users & Granting Storage Quota using Database Actions

March 26, 2021

Building REST APIs, with beer!

March 21, 2021

4 Comments

  1. Avatar
    Ram 6 months 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 6 months 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 1 year 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 1 year 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 900+ posts
  • What it is I do here
    About Me

    I'm a product manager at Oracle - I want YOU to be happy using Database Development tools.
    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
    • Top 10 Preferences to Tweak when using SQL Developer
    • Enabling DBMS_OUTPUT by default in SQL Developer
    • Formatting Query Results to CSV in Oracle SQL Developer
    • Resetting Your Oracle User Password with SQL Developer
    • How To: Generate an ERD for Selected Tables in SQL Developer
    • Migrating Oracle SQL Developer Connections (with Passwords!)
  • Subscribe - Posts Emailed To You!

  • Mostly Making Oracle Easy, Mostly

    Privacy Policy 

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

© Jeff Smith and ThatJeffSmith, 2019

Top
ThatJeffSmith