Mostly Making oracle easy, mostly:
  • Oracle SQL Developer Web 20.4 and JSON
  • Oracle SQL Developer Web 20.4 and AutoREST
  • Translating JSON Doc Store Query By Examples (QBE) to SQL!
  • ORDS Overview – in 9 minutes!
  • Connecting via JDBC to Oracle Autonomous…via HTTPS/REST
  • 2020 Recap: Your Favorite Content
  • Working with BLOBs in Oracle SQL Developer Web
  • Working with {JSON} in the Oracle Autonomous Database and loading documents with ORDS
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

Oracle SQL Developer Web 20.4 and JSON

January 20, 2021

Oracle SQL Developer Web 20.4 and AutoREST

January 19, 2021

Translating JSON Doc Store Query By Examples (QBE) to SQL!

January 15, 2021
ORDS logo

ORDS Overview – in 9 minutes!

January 14, 2021

Connecting via JDBC to Oracle Autonomous…via HTTPS/REST

January 4, 2021

Working with BLOBs in Oracle SQL Developer Web

December 14, 2020

4 Comments

  1. Ram 3 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 3 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. Piotr 11 months 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 11 months 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
    • Formatting Query Results to CSV in Oracle SQL Developer
    • Enabling DBMS_OUTPUT by default in 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