Creating Customer Managed Oracle REST Data Services User

To use Autonomous Database with Oracle REST Data Services (ORDS) running in a customer managed environment on your Autonomous Database, you must create a user, grant privileges to the user, and run the procedure ORDS_ADMIN.PROVISION_RUNTIME_ROLE.

Perform the following steps to create a user for the ORDS JDBC Connection Pool and prepare the Autonomous Database instance for using Oracle REST Data Services in a customer managed environment:

1. Connect to your Autonomous Database as the ADMIN user.
2. Create a new database user and grant the required privileges as follows:

   CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY "password";
   GRANT "CONNECT" TO "ORDS_PUBLIC_USER2";

   ORDS_PUBLIC_USER2 is the recommended database user name although you can choose a different database user name. If you choose a different user name, then all the steps need to use the user name you choose.

3. Grant the ORDS runtime role to the new database user so that it can act as an ORDS runtime user.

   BEGIN
        ORDS_ADMIN.PROVISION_RUNTIME_ROLE(
            p_user => 'ORDS_PUBLIC_USER2',
            p_proxy_enabled_schemas => TRUE);
   END;
   /

   Following are the parameters:

   • p_user: The name of the user to be configured.

   • p_proxy_enabled_schemas: When set to true, proxy grants are added for any REST enabled schemas.

   Note:

   It is highly recommended not to skip the following steps. If you skip these steps, then ORDS will run APEX, OWA, PL/SQL gateway requests directly as the runtime user and warnings similar to the following will be logged for each request:

   WARNING Running PL/SQL Gateway directly as
             ORDS_PUBLIC_USER is not advised in multi-user environments. Use proxied mode
             instead.

4. Create a new user for PL/SQL Gateway, OWA, and APEX and allow connections through the runtime user created in step 2:

   CREATE USER "ORDS_PLSQL_GATEWAY2" IDENTIFIED BY "password";
   GRANT "CONNECT" TO "ORDS_PLSQL_GATEWAY2";
    
   ALTER USER ORDS_PLSQL_GATEWAY2 GRANT CONNECT THROUGH ORDS_PUBLIC_USER2;

   The new user name ORDS_PLSQL_GATEWAY2 is the recommended user name. If you choose a different user name, then specify that user name in all the steps.

5. Configure the new ORDS runtime user to use the new gateway user.

   BEGIN
     ORDS_ADMIN.CONFIG_PLSQL_GATEWAY(
           p_runtime_user => 'ORDS_PUBLIC_USER2',         /* when using this user */
           p_plsql_gateway_user => 'ORDS_PLSQL_GATEWAY2'  /* run OWA things as this user */
     );
   END;
   /