Showing posts with label ords. Show all posts
Showing posts with label ords. Show all posts

Thursday, August 5, 2021

NGINX SSL Reverse Proxy for Tomcat/ORDS/APEX

This is a simple reverse proxy to achieve any URL structure you want for your APEX server. NGINX is responsible for handle almost everything, reverse proxy, URL redirection, HTTP/2, cache, gzip, HSTS, OCSP stapling, etc. Tomcat/ORDS/APEX is sitting behind NGINX, communicating with NGINX via HTTP.

This is the stack I am running at the moment.

  • NGINX 1.21.1
  • Tomcat 9.0.50
  • ORDS 21.2.0.r1741826
  • APEX 21.1.2
C:\Program Files\nginx\conf\nginx.conf
worker_processes auto;
pid        logs/nginx.pid;

events {
    worker_connections  1024;
}

http {
    include       mime.types;
    default_type  application/octet-stream;
    sendfile      on;
    keepalive_timeout 75;
	client_max_body_size 200M;
	proxy_cache_path "C:/Program Files/nginx/temp/proxy_cache" levels=1:2 keys_zone=STATIC:10m inactive=24h  max_size=1g use_temp_path=off;

server {
    listen 80 default_server;
    server_name _;
	
    rewrite ^ https://$host$request_uri? permanent;
}

server {
    listen 443 ssl http2 default_server;
    server_name _;
 
	gzip on;
	gzip_types	text/css text/plain text/javascript	application/javascript application/json application/x-javascript application/xml application/xml+rss application/xhtml+xml application/x-font-ttf application/x-font-opentype application/vnd.ms-fontobject image/svg+xml image/x-icon application/rss+xml application/atom_xml;
    gzip_proxied    no-cache no-store private expired auth;
    gzip_min_length 1000;
	
	ssl_certificate "E:/ssl/fullchain.cer";
	ssl_certificate_key "E:/ssl/leavemealone.com.key";
	ssl_protocols TLSv1.2 TLSv1.3;
	ssl_ciphers "EECDH+ECDSA+AESGCM EECDH+aRSA+AESGCM EECDH+ECDSA+SHA384 EECDH+ECDSA+SHA256 EECDH+aRSA+SHA384 EECDH+aRSA+SHA256 EECDH+aRSA+RC4 EECDH EDH+aRSA HIGH !RC4 !aNULL !eNULL !LOW !3DES !MD5 !EXP !PSK !SRP !DSS";
	ssl_ecdh_curve secp384r1; 
	ssl_prefer_server_ciphers on;	
	ssl_dhparam "E:/ssl/dhparam4096.pem";
	
	ssl_session_cache   shared:SSL:10m;
	ssl_session_timeout 10m;
	ssl_session_tickets off;
	
	ssl_stapling on;
	ssl_stapling_verify on;
	ssl_trusted_certificate "E:/ssl/ca.cer";

	add_header          Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
#	add_header          X-Content-Type-Options nosniff;
#   add_header          X-Frame-Options SAMEORIGIN;
#   add_header          X-XSS-Protection "1; mode=block";

    location / {
        proxy_connect_timeout       600;
        proxy_send_timeout          600;
        proxy_read_timeout          600;
        send_timeout                600;

		proxy_set_header Host $host;
		proxy_set_header X-Forwarded-Host $host:$server_port;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		proxy_set_header X-Forwarded-Proto $scheme;
		proxy_set_header Origin "";
        proxy_pass http://127.0.0.1:8080;
		proxy_http_version 1.1;
    }
	
	# cache apex application/workspace static files
	location ~* /ords/(.*)/r/([0-9/]*)files/static/v([0-9]+)/ {
		proxy_set_header Host $host;
		proxy_set_header X-Forwarded-Host $host:$server_port;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		proxy_set_header X-Forwarded-Proto $scheme;
		proxy_set_header Origin "";
        proxy_pass http://127.0.0.1:8080;
		proxy_http_version 1.1;
		
		proxy_redirect off;
		add_header X-Cache-Status $upstream_cache_status;
		expires 300d;
		proxy_cache STATIC;
		proxy_cache_key $host$uri$is_args$args;
		proxy_cache_valid 200 24h;
		proxy_cache_use_stale  error timeout invalid_header updating http_500 http_502 http_503 http_504;
	}
  }  	
}
Let me explain this line by line:
  • Line 14, proxy cache setting
  • Line 17-20, rediect all HTTP traffic to HTTPS
  • Line 24, setup HTTP/2
  • Line 27-30, enable gzip
  • Line 32-33, SSL key pair. fullchain.cer contains the server public certificate followed by immediate certificate in the same file
  • Line 34-38, SSL protocol, chipers setting
  • Line 40-42, SSL session cache setting
  • Line 44-46, OCSP stapling setting. ca.cer contains only the immediate certificate
  • Line 48, add HSTS header
  • Line 54-57, proxy timeout setting
  • Line 59-63, pass some extra headers to ORDS, so that your app can now where this request originally comes from
  • Line 64, Google Chrome enforces stricter CORS rules, than e.g. Firefox. By setting the Origin to blank we can make reverse proxying work, otherwise Chrome would block it
  • Line 65, the actual reverse proxy command saying that traffic is internally rerouted to http://127.0.0.1:8080
  • Line 80-86, proxy cache setting. We put every file found on a path like /ords/*/r/*files/static/vnnn/ subfolder for at least 24hrs and also send a 300 day expiry header to the client

There is not a lot of changes in Tomcat. Basically we need to ensure HTTP (port 8080) is working, limit access to localhost and adding the actual IP address %{X-Forwarded-For} to tomcat log file.

E:\tomcat9\conf\server.xml
<Connector port="8080" protocol="HTTP/1.1"
		scheme="https"
        proxyPort="443"
		maxHttpHeaderSize="32767"
		maxPostSize="-1"
		disableUploadTimeout="true" />
        
   <Engine name="Catalina" defaultHost="localhost">
      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">
            
...

		<Valve className="org.apache.catalina.valves.RemoteAddrValve"
			allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1"/>

		<Valve className="org.apache.catalina.valves.RemoteIpValve"
				internalProxies="127\.0\.[0-1]\.1"
				remoteIpHeader="X-Forwarded-For"
				requestAttributesEnabled="true"
				protocolHeader="x-forwarded-proto"
				protocolHeaderHttpsValue="https"/>
		
        <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
               prefix="localhost_access_log" suffix=".txt"
               pattern="%{X-Forwarded-For}i %h %l %u %t &quot;%r&quot; %s %b" />
      </Host>
   </Engine>
  • Line 2-3, in some situations APEX internally creates a redirect to a different URL path, e.g. during Authentication using Social-Login it will redirect to …/ords/apex_authentication.callback… With the verison of the stack I am using, these two lines might not be required anymore. I am still leaving them here for the peace of mind.
  • Line 14-15, to allow access only for the clients connecting from localhost
  • Line 17-26, adding the actual IP address %{X-Forwarded-For} to tomcat log file

Sunday, July 4, 2021

Upgrade ORDS in OCI

Update ORDS

yum -y update

Fix ords.conf

yum update will mess up the ORDS config file. We need to remove the extra ORDS_BASE_PATH.

vi /etc/ords/ords.conf

# ORDS Service Script Configuration File (ords.config)
#
# This file should be placed in the same folder as the ORDS Service Script
# (ords.sh). Following are the properties that can be set via this file:
#
# ORDS_CONFIGDIR  The absolute path to an ORDS configuration directory. If set
#                 and not empty then the script will overwrite the configdir
#                 property in the ords.war file located next to the script
#                 whenever the script is called with the start parameter.
# JAVA_HOME       The Java base path to be used when starting ORDS. It should
#                 contain the binaries folder "bin" and the "java" executable
#                 under it. If the path provided does not exist, is not
#                 readable or doesn't contain the "bin/java" executable, then
#                 the PATH's java binary will be used to determine the
#                 JAVA_HOME.
# JAVA_OPTIONS    A string containing the Java options to be passed to the JVM
#                 when starting ORDS. Parameters should be in a single line
#                 separates by spaces just as they'd be when invoking a JVM
#                 from the command line. No shell expansions are performed. No
#                 options are passed by default to the ORDS' JVM.
# ORDS_BASE_PATH  The base path for ORDS installation by default is /opt/oracle
#                 but if the rpm was installed with --prefix [path] to relocate
#                 the package this variable will track the installation path.

#####Example#####
#ORDS_CONFIGDIR=/opt/oracle/ords/conf
#JAVA_HOME=/usr/java/jre1.8.0_211-amd64/
#JAVA_OPTIONS=-Dsecurity.forceHTTPS=true
ORDS_BASE_PATH=/opt/oracle
ORDS_BASE_PATH=/opt/oracle

Run ORDS and setup the config directory

ords standalone and enter /opt/oracle/ords/config

Saturday, January 23, 2021

Setup ORDS Standalone Against Autonomous Database

Why ORDS standalone

For standalone ORDS, Oracle use Jetty. Jetty is a very capable webserver that on my laptop scale to 200+ rest calls per second. There deeper details on it's scaling abilities here: http://www.eclipse.org/jetty/documentation/current/high-load.html The best advantage is it simply works, scales, easy to get up and running. The disadvantage is mainly it's a purpose built and configured web server for ORDS. If someone needs more general web server features, it'd best to use WLS / Tomcat / Glassfish.

Download the following software to the Compute Instance

Install software via yum

yum-config-manager --enable ol7_oci_included
yum update -y
yum install -y ords
yum install -y java
yum install -y jq
yum install -y oracle-release-el7
#yum search oracle-instant
yum install -y oracle-instantclient19.9-basic.x86_64
yum install -y oracle-instantclient19.9-tools.x86_64

Allow access to Port 8080

firewall-cmd --zone=public --add-port 8080/tcp --permanent
firewall-cmd --zone=public --add-port 8443/tcp --permanent
#firewall-cmd --permanent --zone=public --add-service=http
#firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload

Install SQLcl and ADMIN wallet

unzip sqlcl-20.4.1.351.1718.zip -d /opt
unzip Wallet_PROD.zip -d /usr/lib/oracle/19.9/client64/lib/network/admin
ln -s /opt/sqlcl/bin/sql /usr/lib/oracle/19.9/client64/bin/sql

add these to ~oracle/.bash_profile
export PATH=/usr/lib/oracle/19.9/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.9/client64/lib
export TNS_ADMIN=/usr/lib/oracle/19.9/client64/lib/network/admin

Install APEX and Patch Set

RELEASE=20.2.0.00.20
mkdir -p /opt/oracle/apex/images/$RELEASE
unzip apex_20.2.zip -d /tmp/
cp -R /tmp/apex/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/apex

unzip  p32006852_2020_Generic.zip -d /tmp/
cp -R /tmp/32006852/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/32006852

ORDS Setup

Create alternate ORDS_PUBLIC_USER2 user

sql admin@prod_low
create user ords_public_user2 identified by "Opt12345678901234567890!";
grant connect to ORDS_PUBLIC_USER2;
begin
    ords_admin.provision_runtime_role(
        p_user => 'ORDS_PUBLIC_USER2'
        , p_proxy_enabled_schemas => true
    );
end;
/

Setup enviornment variables

ORDS_CONFIG_DIR=/opt/oracle/ords/config
ORDS_USER=ORDS_PUBLIC_USER2
ORDS_PASSWORD=Opt12345678901234567890!
SERVICE_NAME=prod_low
WALLET_BASE64=`base64 -w 0 Wallet_PROD.zip`

Create directories

mkdir -p $ORDS_CONFIG_DIR/ords/conf
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/doc_root
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/etc
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/logs

Create $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="db.username">$ORDS_USER</entry>
  <entry key="db.password">!$ORDS_PASSWORD</entry>
  <entry key="db.wallet.zip.service">$SERVICE_NAME</entry>
  <entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF

Create $ORDS_CONFIG_DIR/ords/defaults.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="plsql.gateway.enabled">true</entry>
  <entry key="jdbc.InitialLimit">20</entry>
  <entry key="jdbc.MinLimit">20</entry>
  <entry key="jdbc.MaxLimit">50</entry>
  <entry key="jdbc.MaxStatementsLimit">20</entry>
</properties>
EOF

Create $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml
<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure id="Server" class="org.eclipse.jetty.server.Server">
    <Ref id="Handlers">
      <Call name="addHandler">
        <Arg>
          <New id="RequestLog" class="org.eclipse.jetty.server.handler.RequestLogHandler">
            <Set name="requestLog">
              <New id="RequestLogImpl" class="org.eclipse.jetty.server.NCSARequestLog">
                <Set name="filename"><Property name="jetty.logs" default="/opt/oracle/ords/config/ords/standalone/logs/"/>ords-access-yyyy_mm_dd.log</Set>
                <Set name="filenameDateFormat">yyyy_MM_dd</Set>
                <Set name="retainDays">90</Set>
                <Set name="append">true</Set>
                <Set name="extended">false</Set>
                <Set name="logCookies">false</Set>
                <Set name="LogTimeZone">GMT</Set>
            </New>
          </Set>
        </New>
        </Arg>
      </Call>
    </Ref>
</Configure>
EOF

Edit /opt/oracle/ords/config/ords/standalone/standalone.properties

jetty.port=8080
standalone.context.path=/ords
standalone.doc.root=/opt/oracle/ords/config/ords/standalone/doc_root
standalone.scheme.do.not.prompt=true
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images
jetty.secure.port=8443
#ssl.cert=leavemealone.com.pem
#ssl.cert.key=leavemealone.com.key
#ssl.host=pws.leavemealone.com

Configure ORDS

ords configdir $ORDS_CONFIG_DIR

/etc/ords/ords.conf ORDS_BASE_PATH=/opt/oracle

Test run ORDS

ords standalone
wget http://localhost:8080/i/20.2.0.00.20/apex_version.txt

Auto Start ORDS

systemctl start ords
systemctl enable ords
systemctl status ords

Switch APEX static resources repository to Oracle Content Delivery Network (CDN).

BEGIN
    apex_instance_admin.set_parameter (
        p_parameter   => 'IMAGE_PREFIX',
        p_value       => 'https://static.oracle.com/cdn/apex/20.2.0.00.20/');

    COMMIT;
END;

Thursday, November 7, 2019

Tuning Tomcat 9 for APEX/ORDS in Production

Logging

Configuration File: $CATALINA_HOME/conf/logging.properties
Change all occurrences of FINE and INFO to WARNING or SEVERE

JVM Options

Run tomcat9w.exe
Change Initial Memory pool and Maximum memory pool to 4096.


Main Tomcat Server Configurations

Configuration File: $CATALINA_HOME/conf/server.xml
<Connector port="443" protocol="org.apache.coyote.http11.Http11AprProtocol" 
  SSLEnabled="true" scheme="https" secure="true"
  maxHttpHeaderSize="32767"
  compression="on"
  disableUploadTimeout="true">
  <UpgradeProtocol overheadDataThreshold="0" compression="on" className="org.apache.coyote.http2.Http2Protocol" />

...

</Connector>

Close to the end of server.xml
<Host name="localhost" appBase="webapps"
            unpackWARs="true" autoDeploy="true">

        <Context path="/ords" reloadable="false" />

...

</Host>

Oracle REST Data Service

Configuration File: defaults.xml
Change the limits
<entry key="cache.metadata.enabled">true</entry>
<entry key="jdbc.InitialLimit">20</entry>
<entry key="jdbc.MinLimit">20</entry>
<entry key="jdbc.MaxLimit">50</entry>
<entry key="jdbc.MaxStatementsLimit">20</entry>

Tuesday, May 14, 2019

ORDS OAuth 2 Client Credentials

Setup Auto REST for one table with OAuth2
BEGIN
  oauth.create_client(p_name => 'OAuth2 Dynamics Document Client', p_grant_type => 'client_credentials', p_owner => 'LEAVEMEALONE', 
    p_description => 'A client for Dynamics Document', 
    p_support_email   => 'no-reply@leavemealone.com', 
    p_privilege_names => 'oracle.dbtools.autorest.privilege.LEAVEMEALONE');
  ords.enable_object(p_schema => 'LEAVEMEALONE', p_object => 'AD_USER', p_object_alias => 'ad_user', p_auto_rest_auth => true);
  oauth.grant_client_role(p_client_name => 'OAuth2 Dynamics Document Client', p_role_name => 'oracle.dbtools.role.autorest.LEAVEMEALONE');
  oauth.grant_client_role(p_client_name => 'OAuth2 Dynamics Document Client', p_role_name => 'oracle.dbtools.role.autorest.LEAVEMEALONE.AD_USER');
  COMMIT;
END;
/

Find out the CLIENT_ID and CLIENT_SECRET to retrieve the ACCESS_TOKEN
SELECT
*
FROM   user_ords_clients;

        ID NAME                            CLIENT_ID                        CLIENT_SECRET                   
---------- ------------------------------- -------------------------------- --------------------------------       
     10130 OAuth2 Dynamics Document Client K5DGLFxUHTbQ_yCaSc1y3A..         SCtjsrfxag2DWcQ35TjFuw..        

SELECT
  client_name,
  role_name
FROM
  user_ords_client_roles;

CLIENT_NAME                     ROLE_NAME
------------------------------- -------------------------------------------------
OAuth2 Dynamics Document Client oracle.dbtools.role.autorest.LEAVEMEALONE
OAuth2 Dynamics Document Client oracle.dbtools.role.autorest.LEAVEMEALONE.ADUSER

Retrieve ACCESS_TOKEN
curl --request POST \
--url https://tst-opptools.leavemealone.com/ords/uat12c/opt/oauth/token \
--header 'Accept: */*' \
--header 'Authorization: Basic N1V1MHVtRy13bnBxWWg3WWtHOUtCQS4uOnptTjE4VnVhQlBpX2FpVEJCcnJncXcuLg==' \
--header 'Cache-Control: no-cache' \
--header 'Connection: keep-alive' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Host: tst-opptools.leavemealone.com' \
--header 'accept-encoding: gzip, deflate' \
--header 'cache-control: no-cache' \
--header 'content-length: 29' \
--data grant_type=client_credentials
Response
{
    "access_token": "-YDi4OaEGgN-mgjGcUfYrg",
    "token_type": "bearer",
    "expires_in": 3600
}

Use the ACCESS_TOKEN to access table
curl --request GET \
--url https://tst-opptools.leavemealone.com/ords/uat12c/opt/ad_user/ \
--header 'Accept: */*' \
--header 'Authorization: Bearer -YDi4OaEGgN-mgjGcUfYrg' \
--header 'Cache-Control: no-cache' \
--header 'Connection: keep-alive' \
--header 'Host: tst-opptools.leavemealone.com' \
--header 'accept-encoding: gzip, deflate' \
--header 'cache-control: no-cache'

Monday, July 3, 2017

ORDS Install/Upgrade (ORDS Versions 3.0 to 21.4)

Install

If your ords directory is e:\ords, use e:\ below. Do not include \ords

Stop Tomat
copy ords.war apex.war
rmdir $CATALINA_HOME/webapps/apex
copy apex.war $CATALINA_HOME/webapps/
java -jar apex.war configdir e:\
java -jar apex.war

Validate ORDS installation
java -jar apex.war validate

Run this if validation fails
java -jar apex.war schema

Configure Multiple Databases
Do not use workspace-id when adding APEX
java -jar apex.war setup --database dev
java -jar apex.war map-url --type base-path /dev dev
Start Tomcat


Upgrade


Stop Tomat
copy ords.war apex.war
rmdir $CATALINA_HOME/webapps/apex
copy apex.war $CATALINA_HOME/webapps/
java -jar apex.war configdir e:\
java -jar apex.war schema
Start Tomcat