ORDS Schema Level Overrides

ORDS Schema Level Overrides

Jon Dixon's photo
Jon Dixon
·

5 min read

Table of contents

Introduction

Oracle REST Data Services (ORDS) configurations are typically stored in configuration files on the ORDS server. When the OCI Autonomous Database service and the OCI APEX Service were released, I wondered how we were going to be able to adjust ORDS configurations. There was no way Oracle would give us access to the ORDS servers (and rightly so).

Of course, we could install and configure Customer Managed ORDS. This would give us access to change the ORDS configurations, but then we lose the benefit of Oracle managing the ORDS server. Check out my previous post Running APEX & ORDS using Customer Managed ORDS for more on running a Customer Managed ORDS environment.

🎉 Enter Schema Level Configuration Overrides, which allow you to override specific ORDS configuration file settings in a particular schema. These overrides can be set from a SQL session (SQLDeveloper, SQLcl, etc.), so there is no need to access the ORDS server.

Schema Level Configuration Overrides

As of ORDS 22.3, three settings can be overridden:

  • debug.printDebugToScreen - Enable/disable debugging

  • restEnabledSql.active - Enable/disable REST Enabled SQL

  • feature.sdw - Enable/disable SQL Developer Web

Setting Overrides

Overrides are set using two ORDS PL/SQL APIs: ords_admin.set_property and ords.set_property. ords.set_property overrides a setting in the current schema, ords_admin.set_property overrides a setting in another schema.

Note: You can only run ords_admin.set_property from a schema that has been granted the ORDS_ADMINISTRATOR_ROLE role.

Effectivity

After calling one of the above APIs, it will take a few minutes (up to 10, in my experience) for the override to take effect. This is because ORDS only checks the database for overrides when it creates a new session in the JDBC connection pool. This means you must wait for connections to be recycled before newly applied overrides become active.

Querying Existing Overrides

You can see what overrides have been set in a schema by running the following SQL from that schema:

SELECT * 
FROM   user_ords_properties;

Examples

Enable Debug / Print to Screen

When developing ORDS REST services, it can be helpful to see additional details about errors when they occur. Let's take an example: I made a typo in a GET request. In the example ORDS Handler below, I made a mistake in the SQL statement:

ORDS Handler With Error.png

If I try to call this API in Postman, I see the following error:

Error_Message_Before_Debug_Turned_On.png

The error message is not very helpful for security reasons, nor should it be. The error message tells me the error but not where it occurred. So, what if we wanted to see more detail about the error? We could, of course, log in to the ORDS server and change the ORDS configuration file setting debug.printToScreen to true. With ORDS Schema Level Overrides, we can set this directly from SQLDeveloper, SQLcl, etc.

As an administrator with the ORDS_ADMINISTRATOR_ROLE role, I can set this for another schema:

BEGIN
  ords_admin.set_property
   (p_schema => 'CNDEMO',
    p_key    => 'debug.printDebugToScreen',
    p_value  => 'true');
    COMMIT;
END;

Or, as a developer with access to the CNDEMO schema, I can set this myself.

-- Logged in to the CNDEMO schema.
BEGIN
  ORDS.set_property
   (p_key    => 'debug.printDebugToScreen',
    p_value  => 'true');
    COMMIT;
END;

And to confirm that it was set, I can query the view user_ords_properties

-- Logged in to the CNDEMO schema.
SELECT * 
FROM   user_ords_properties;

SQL to query user_ords_properties .png

Then we wait a few minutes and re-run the web service:

ORDS_Error_With_Debug_On1.png

We can now see that the error was caused by an ORA-01722: invalid number, and if we scroll to the end, we can see the offending statement:

ORDS_Error_With_Debug_On2.png

To unset the override, we can call either ords_admin.unset_property or ords.unset_property

BEGIN
  -- Logged in to the CNDEMO schema
  ords.unset_property(p_key => 'debug.printDebugToScreen');
  COMMIT;
END;

Running the following will result in no rows returned.

-- Logged in to the CNDEMO schema.
SELECT * 
FROM   user_ords_properties;

Of course, you may have to wait up to 10 minutes for ORDS to pick this up.

Turn off REST Enabled SQL for a Schema

While REST Enabled SQL is an excellent feature, there are situations where you may want to disable it for specific schemas even though you still want to allow Module>Template> Handler-based REST web services. Check out this post for more on REST Enabled SQL.

Run the following to disable REST Enabled SQL for a specific schema:

-- Run from a schema with the role `ORDS_ADMINISTRATOR_ROLE`
BEGIN
  ords_admin.set_property
   (p_schema => 'CNDEMO',
    p_key    => 'restEnabledSql.active',
    p_value  => 'false');
  COMMIT;
END;

ORDS_Verify_REST_ENabled_SQL_Turned_Off.png

After setting this value and waiting a few minutes, you will get the following when attempting to use REST Enabled SQL:

ORDS_Postman_Call_to_REST_ENabled_SQL_Fails.png

Turn off SQL Developer Web for a Schema

Similarly to REST Enabled SQL, you may want to have SQL Developer Web available for your instance but lock it down for specific schemas.

-- Run from a schema with the role `ORDS_ADMINISTRATOR_ROLE`
BEGIN
  ords_admin.set_property
   (p_schema => 'CNDEMO',
    p_key    => 'feature.sdw',
    p_value  => 'false');
  COMMIT;
END;

If after a few minutes, if you try and access SQL Developer Web, you will get the following:

ORDS_Access_SQL_Developer_Web_After_Disabling.png

Feature Disabled in ORDS Config Files

What if the feature is disabled in the configuration file on the ORDS server? Can you enable it using ORDS Schema Level Overrides? You sure can!

For example, if you have disabled SQL Developer Web in the ORDS server configurations file as follows:

<entry key="feature.sdw">false</entry>

You can enable it for a specific schema as follows:

-- Run from a schema with the role `ORDS_ADMINISTRATOR_ROLE`
BEGIN
  ords_admin.set_property
   (p_schema => 'CNDEMO',
    p_key    => 'feature.sdw',
    p_value  => 'true');
  COMMIT;
END;

You can then log in to SQL Developer Web for that schema just fine:

ORDS_SQLDeveloperWeb_Works.png

But not to others:

ORDS_SQLDeveloperWeb_Fails.png

Conclusion

ORDS Schema Level Overrides allow you to override specific ORDS configuration file settings on a schema-by-schema basis. This benefits those running ORDS on a PaaS environment, on-premise, and Customer Managed ORDS.

For those of you running on-premise or Customer Managed ORDS environments, it allows you to disable a service like REST Enabled SQL at the server level and then activate it only for specific schemas.

🔗 Read More

Oracleorclords