Non-Interactive ORDS Installation with custom database XOR PL/SQL gateway user.

By

           Non-Interactive ORDS Installation process

 

This blog will provide some examples for non-interactive install/upgrade, it covers multiple scenarios specially if you want to install ORDS with a custom database XOR PL/SQL gateway user. 

Note:
  • During installation, ORDS_PUBLIC_USER will always be created, you can define its password via --proxy-user sub-command, or otherwise a random password will be generated. 

Install/Upgrade ORDS with default user 

1.1. Install: 

  • The runtime and database user is defaulted in this case to ORDS_PUBLIC_USER.
  • Within the configuration file, the db.username will be set to ORDS_PUBLIC_USER, and its password will be stored within the wallet.
  • Consider using  "--proxy-user" sub-command if you want to define a password for ORDS_PUBLIC_USER.
  • Secret file contains the password for the admin user (SYS in this case). 

 ords --config  /path/to/config  install
     --db-pool oradb 
     --db-hostname localhost
     --db-port 1521 
     --db-servicename freepdb1
     --feature-sdw true 
     --admin-user SYS 
     --password-stdin < secret.txt

1.2. Upgrade: 

  • Secret file contains the password for the admin user (SYS in this case)
  • All the required information (database user password and DB connection information) will be retrieved from the configuration file.
ords --config /path/to/config install 
     --db-pool oradb 
     --admin-user SYS 
     --password-stdin < secret.txt
  • If the configuration file is obsolete or you want to create it in another location while updating ORDS:
ords --config /path/to/config install 
     --db-pool oradb 
     --db-hostname localhost 
     --db-port 1521 
     --db-servicename freepdb1
     --feature-sdw true 
     --admin-user SYS 
     --password-stdin < secret.txt
  • If you only want to perform an upgrade without creating/updating the configuration files - You must specify the "--db-only" option and provide the database connection information
  • Secret file contains the password for the admin user (SYS in this case)

 ords install 
    --db-only 
    --admin-user SYS 
    --db-hostname localhost
    --db-port 1521 
    --db-servicename freepdb1 
    --password-stdin < secret.txt



 Install/Upgrade ORDS with custom database and PL/SQL gateway user

2.1. Install

2.1.1. Connect to your database as an admin user and create the required users and assign grants:

CREATE USER CUSTOM_USER identified by <secret>;
GRANT CONNECT TO CUSTOM_USER;
 
CREATE USER CUSTOM_PLSQL_GATEWAY_USER identified by <secret>;
GRANT CONNECT TO CUSTOM_PLSQL_GATEWAY_USER;

2.1.2. Install ORDS: 

  • "--proxy-user" sub-command to specify a custom password for ORDS_PUBLIC_USER.
  • The database user in this case is CUSTOM_USER.

  • The secret file contains 3 secrets: (for --admin-user, --proxy-user, --db-user)

    • Admin user password (SYS in this case)
    • Proxy user password (ORDS_PUBLIC_USER password)
    • DB user password (CUSTOM_USER in this case)
ords --config /path/to/config install 
     --proxy-user 
     --db-pool oradb 
     --db-hostname localhost 
     --db-port 1521 
     --db-servicename freepdb1 
     --admin-user SYS 
     --db-user CUSTOM_USER 
     --gateway-user CUSTOM_PLSQL_GATEWAY_USER 
     --feature-sdw true 
     --password-stdin < secrets.txt

2.1.3. Connect to your database as an admin user and run the following:

Provision the database user (CUSTOM_USER) so that it can act as an ORDS runtime user.

execute ORDS_ADMIN.PROVISION_RUNTIME_ROLE( 
            p_user => 'CUSTOM_USER',
            p_proxy_enabled_schemas => TRUE);

Configure the database proxy user (CUSTOM_PLSQL_GATEWAY_USER) that must be used for PL/SQL Gateway calls serviced by the custom runtime user (CUSTOM_USER).


execute ORDS_ADMIN.CONFIG_PLSQL_GATEWAY( 
            p_runtime_user => 'CUSTOM_USER', 
            p_plsql_gateway_user => 'CUSTOM_PLSQL_GATEWAY_USER');

2.1.4. Verification 

select * from sys.proxy_users where proxy='CUSTOM_USER';
select * from ords_metadata.plsql_gateway_config;

2.2. Update

  • Because ORDS_PUBLIC_USER was not defined as the runtime/dbuser, its password must be included via "--proxy-user" option.

  • The secret file contains 2 secrets: (for --admin-user, --proxy-user)

    • Admin user password (SYS in this case)
    • Proxy user password (ORDS_PUBLIC_USER password)
ords --config /path/to/config install
     --db-pool oradb 
     --proxy-user 
     --admin-user SYS 
     --password-stdin < secrets.txt
  • If the password of ORDS_PUBLIC_USER was not specified during the first installation, you can either 
    • Directly upgrade its password within the database and then specify it during the upgrade.
    • Use ORDS CLI to update both ORDS and the password of ORDS_PUBLIC_USER.
      • Note that in this case you'll need to provide all the necessary information to the CLI.
      • The configuration file will either be created or updated if it exits. 
ords --config /path/to/config install 
     --proxy-user --db-pool oradb 
     --db-hostname localhost 
     --db-port 1521 
     --db-servicename freepdb1 
     --admin-user SYS 
     --db-user CUSTOM_USER
     --gateway-user CUSTOM_PLSQL_GATEWAY_USER 
     --feature-sdw true 
     --password-stdin < secrets.txt


3. Install adb command with custom runtime and PL/SQL gateway user.

  • No need to manually create the users (db user and gateway user), and no need to setup the runtime user and config_plsql_gateway. They will automatically be configured/created.
  • No no need to use "--proxy-user" option when you're running an ORDS install in ABD context.
  • The secret file contains secrets for: (--admin-user, --db-user and --gateway-user) 
    • Admin user password (ADMIN in this case)
    • DB user password (CUSTOM_USER in this case)
    • GATEWAY user password  ( CUSTOM_PLSQL_GATEWAY_USER in this case)
ords --config /path/to/config install adb     
       --admin-user ADMIN     
       --db-user CUSTOM_USER 
       --gateway-user CUSTOM_PLSQL_GATEWAY_USER 
       --wallet /path/to/wallet 
       --wallet-service-name <name>     
       --feature-sdw true
       --password-stdin < secrets.txt




You can check the official documentation for more information: 



























Comments

Post a Comment

Popular posts from this blog

Introduction to Oracle Rest Data Services (ORDS) – A Beginner’s Guide

By
Image
Hey folks! Today, I want to introduce you to a tool that’s been part of my daily grind: Oracle REST Data Services, or ORDS. So, What’s ORDS? Basically, ORDS is a tool that lets you expose your Oracle database data as RESTful APIs . Instead of wrestling with complex backend code, ORDS makes it super easy to build and deploy APIs. With ORDS, your data becomes accessible from anywhere with just a few HTTP calls. Think of it as giving your database a direct, web-friendly voice. Why ORDS? 🌟 Imagine this: You’ve got an Oracle database packed with valuable data, and you want to make it accessible to an app, a web service, or another system. Usually, you’d need a whole backend to handle data retrieval, authentication, response formatting… you name it. But with ORDS? You just set it up, map it to your database tables or procedures, and voilà—you’ve got an API that you can call from anywhere. Here’s why ORDS is a game-changer: It’s Fast ⚡: You don’t have to build a backend from scratch. It’s...
Read more

Response Format and REST-Enabled SQL service in ORDS

By
Hello fellas!! 👋 Today’s topic: Understanding the REST-Enabled SQL Service in ORDS and How to Customize its Response Format. Rest-Enabled SQL service in ORDS: The REST-Enabled SQL service is a HTTPS web service that provides access to the Oracle Database SQL engine. You can POST SQL statements to the service. The service then runs the SQL statements against Oracle Database and returns the result to the client in a JSON format. Example:  When making a request to the REST ENABLED SQL SERVICE, you typically by default get a response as the following:         curl --location 'http://localhost:8086/ords//<schema_alias>/_/sql' \ --header 'Content-Type: application/json' \ -u "user:dummySecret" \ --data '{   "statementText": "select sysdate from dual"   }' This is typically how the response would look like: {     "env": {         "defaultTimeZone": "UTC"     },     "items": [       ...
Read more

ORDS Auto REST vs C# with Entity Framework.

By
Introduction  If you've ever built REST APIs using common used programming languages, you know the drill: create models, wire up a controller, connect to a database, map DTOs, validate, inject dependencies… and that’s before you even touch   security aspect of the api. 😅 Being a past c# lover, I started questioning how easy was it to build REST apis in ORDS compared to c#. Spoiler : ORDS is crazy simple for the basics. Let me show you a side-by-side comparison using the same use case — managing a Country table — and without diving into security (we’ll ignore auth and tokens, and we'll just focus on the   REST experience). Use Case We want to expose the following operations via REST: GET /countries — list all countries GET /countries/{id} — get one country POST /countries — create a new country PUT /countries/{id} — update a country DELETE /countries/{id} — remove a country Let’s see how much effort it takes.  Option 1:  C# with Entity Framework Core 
Read more