# ORDS Schema Level Overrides

# Introduction

Oracle REST Data Services (ORDS) configurations are typically stored in configuration files on the ORDS server. When the [OCI Autonomous](https://www.oracle.com/autonomous-database/) Database service and the [OCI APEX Service](https://www.oracle.com/application-development/apex/) 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](https://blog.cloudnueva.com/production-ready-apex-oci-free) for more on running a Customer Managed ORDS environment.

> 🎉 Enter [Schema Level Configuration Overrides](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/ordig/schema-level-configuration-override.html#GUID-C70F8584-6162-4B22-B882-F61EA20F9342), which allow you to override specific [ORDS configuration file settings](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/ordig/about-REST-configuration-files.html#GUID-006F916B-8594-4A78-B500-BB85F35C12A0) 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](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html#GUID-E1AE2D3E-150B-403A-A030-4DA087FF02FF) and [ords.set\_property](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/orddg/ORDS-reference.html#GUID-A4D72FE9-2032-4C2C-B7B1-AB093F2A5370). `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:

```SQL
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](https://cdn.hashnode.com/res/hashnode/image/upload/v1668952944822/_-AO5nVZxg.png align="left")

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

![Error_Message_Before_Debug_Turned_On.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1669498018810/GjPIJgB4s.png align="left")

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](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/ordig/about-REST-configuration-files.html#GUID-006F916B-8594-4A78-B500-BB85F35C12A0) `debug.printToScreen` to `true`. With ORDS [Schema Level Overrides](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/ordig/schema-level-configuration-override.html#GUID-DA9E5312-71DA-4922-A389-E81325B62E77), 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:

```SQL
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.

```SQL
-- 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`

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

![SQL to query user_ords_properties .png](https://cdn.hashnode.com/res/hashnode/image/upload/v1668952493458/eeLjQiQGG.png align="left")

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

![ORDS_Error_With_Debug_On1.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1668953051538/5UHkV6fxT.png align="left")

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1668953093946/OV_890Jno.png align="left")

To unset the override, we can call either [ords\_admin.unset\_property](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html#GUID-EDF4B20E-9628-49AC-99AE-3CA0004376F5) or [ords.unset\_property](https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/orddg/ORDS-reference.html#GUID-FF706E6A-FCE4-469B-ADF6-BD2D76D1898D)

```SQL
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.

```SQL
-- 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](https://blog.cloudnueva.com/apex-ords-and-rest-enabled-sql).

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

```SQL
-- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1668955196977/ejCOBda0u.png align="left")

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1668955268149/Iov5rYFPX.png align="left")

## 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.

```SQL
-- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1668956440100/jzBbi-gZT.png align="left")

# 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:

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

You can enable it for a specific schema as follows:

```SQL
-- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1668957457470/YmwC57Mn-.png align="left")

But not to others:

![ORDS_SQLDeveloperWeb_Fails.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1668957465934/B0YyWNlwm.png align="left")

# 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

*   #️⃣ [ORDS Posts](https://blog.cloudnueva.com/tag/ords)