How to enable SQL Developer Web (SDW) on ORDS 19.4

ORDS (Oracle REST Data Services) 19.4 introduced a new feature called Oracle SQL Developer Web (SDW), a graphical user interface for querying & managing database objects. In this blog post I will explain how you can enable this feature on ORDS 19.4.

For the complete installation steps for ORDS, I refer to my previous two blog posts (here and here) or the official documentation.

The documentation for SQL Developer Web can be found here.

Step 1: activate SDW in ORDS

• during the advanced command-line installation of a new version of ORDS 19.4, select option 1 for the question “Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:”
• for an existing installation of ORDS 19.4, add the following two lines to the defaults.xml configuration file (in my case under /home/oracle/ords194/conf/ords) and restart ORDS:

<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>

Note: you cannot enable SDW without enabling REST-enabled SQL.

Step 2: enable REST access for database user

To allow a database user to access SQL Developer Web, you need to execute the following script with a DBA user (in my case, I REST-enabled access for the database user DBA_MHOYS with alias “mhoys” (please note that it’s important to put the p_url_mapping_pattern in lower case):

BEGIN
ords_admin.enable_schema(
p_enabled => TRUE,
p_schema => 'DBA_MHOYS',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'mhoys',
p_auto_rest_auth => FALSE
);
commit;
END;

Step 3: log-in to SQL Developer Web

Use a web browser to access SQL Developer Web using the address <servername>:port/ords/sql-developer

On the first log-in screen, enter the alias that you defined in step 2 (in my case: “mhoys”):

Click on Next. On the second log-in screen, enter the schema name and password:

Victory! I’m seeing the DBA screen because I granted my user DBA_MHOYS the PDB_DBA role:

grant PDB_DBA to DBA_MHOYS;

Non-DBA users will see a different screen:

HTH

Matthias

 

Administration of ORDS using Oracle SQL Developer

In one of my previous posts I explained how to install ORDS (Oracle Rest Data Services) 19.2 on Oracle database version 12.2.0.1.

After the installation, some configuration work needs to be done. There are several ways to do this. I will explain here how to configure ORDS using Oracle’s free SQL Developer tool.

First, you need to create a user that will be used for the ORDS administration. Run the following command from the folder on the application server where you extracted ORDS 19.2:

$ java -jar ords.war user adminlistener "Listener Administrator"

Note that if you renamed ords.war to apex.war, you will need to adjust the command as follows:

$ java -jar apex.war user adminlistener "Listener Administrator"

Enter a complex password and write it down somewhere.

Now that the user is created, start up SQL Developer (I’m using the last available version, version 19.2.1).

Click on View >> REST Data Services >> Administration

In the new window, right-click on REST Data Services >> Connect

Click on the green “plus” sign to create a new connection.

Fill in all the details. The “username” is the name of the user that was created earlier (in this case: “adminlistener”).

In my case, the hostname of the server is dev.domain.com and I’m connecting to HTTPS port 443 (note that for security reasons, HTTPS is recommended over HTTP). The server path is referring to the name of the war file that was used during deployment (in my case, apex.war).

 

Now when you press OK you should be prompted for the credentials of the “adminlistener” user.

After a successful connection you will see the list of parameters that can be configured:

 

The full list of configurable parameters can be found here:

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.2/aelig/about-REST-configuration-files.html#GUID-37AA1468-DCB3-4D8B-868C-1910A0C04D68

To save a copy of the configuration files to a local folder, click on the “Save As” icon in the top menu (third button from the left).

To test the new settings, click on the “Test Settings” icon in the top menu (second button from the right).

To save the new settings to the server, click on the “Upload Settings” icon in the top menu (third button from the right).

 

That’s it! Happy ORDS-ing :-)

HTH

Matthias

 

 

 

 

Installation of ORDS (Oracle REST Data Services) 19.x on AWS RDS

In one of my previous posts (now taken offline), I explained how a full installation of ORDS 19.1 could be done on AWS RDS by modifying one of the scripts in the installation war file.

However, there’s a better way to perform the installation and it’s documented in the user guide for the installation of APEX on AWS RDS.

The documentation says that you need to use the “Master” database account, but it’s also working with another DBA account, as long as the necessary system grants and roles are given to the user. I tried the installation of ORDS 19.2 on AWS RDS Oracle 12.2.0.1 with APEX 5.1.4 and it was working fine.

Basically you need to provide the following privileges to your Master user or other DBA account (the account you are going to use for the installation):

grant apex_administrator_role to EXAMPLE_DBA;

exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'EXAMPLE_DBA', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'EXAMPLE_DBA', 'SELECT', true); 
exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'EXAMPLE_DBA', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'EXAMPLE_DBA', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_UTILITY', 'EXAMPLE_DBA', 'EXECUTE', true);

After this you should be able to run the ORDS installation without errors:

$ java -jar ords.war setup

Check the installation log file:

INFO: 14:00:49 Validating objects for Oracle REST Data Services.
VALIDATION: 14:00:49 Starting validation for schema: ORDS_METADATA
VALIDATION: 14:00:49 Validating objects
VALIDATION: 14:00:50 Validating ORDS Public Synonyms
VALIDATION: 14:00:51 Total objects: 250, invalid objects: 0
VALIDATION: 14:00:51 72 INDEX
VALIDATION: 14:00:51 3 LOB
VALIDATION: 14:00:51 11 PACKAGE
VALIDATION: 14:00:51 11 PACKAGE BODY
VALIDATION: 14:00:51 1 PROCEDURE
VALIDATION: 14:00:51 43 PUBLIC SYNONYM
VALIDATION: 14:00:51 1 SEQUENCE
VALIDATION: 14:00:51 27 TABLE
VALIDATION: 14:00:51 27 TRIGGER
VALIDATION: 14:00:51 20 TYPE
VALIDATION: 14:00:51 6 TYPE BODY
VALIDATION: 14:00:51 28 VIEW
VALIDATION: 14:00:51 Validation completed.
INFO: 14:00:51 Completed validation for Oracle REST Data Services.

Good luck!

 

Oracle REST Data Services (ORDS) 19.1 now supports installation using a regular DBA account

Previous versions of Oracle REST Data Services (ORDS) required access to the “SYS as SYSDBA account” to allow the installation of the schemas ORDS_METADATA and ORDS_PUBLIC_USER. This was problematic on environments where access to the “SYS as SYSDBA” account is restricted or even impossible, for example when using Oracle RDS on Amazon Web Services (where you have to configure/install ORDS on a separate EC2 server).

The recently released ORDS version 19.1.0 has finally changed this requirement: it is now possible to use a “regular” account with DBA privileges to install all the required ORDS schemas:

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.1/aelig/installing-REST-data-services.html#GUID-52E9155F-B36D-4F40-B0C0-AA7311159828

I have not tried it myself, but this is excellent news for those that want to use recent versions of APEX and ORDS on Oracle AWS – RDS with all the features enabled.

Has anyone tried this?

Matthias