Oracle Cheatsheet: August 2021

Wednesday, August 25, 2021

Using Google Fonts in Oracle APEX

1. Pick the font you want from Google Fonts. In this example, I picked Roboto, and you should add all the styles to the font family.

2. Add this CSS via Theme Roller->Custom CSS

@import url('https://fonts.googleapis.com/css2?family=Roboto:ital,wght@0,100;0,300;0,400;0,500;0,700;0,900;1,100;1,300;1,400;1,500;1,700;1,900&display=swap');
:root {
  --a-base-font-family: 'Roboto', sans-serif;
}

Sunday, August 8, 2021

Add Prism Syntax Hightlighter to Blogger

1. Goto Usage with CDNs

2. Go to your blogger, Template->Edit HTML and paste the followings right before the </head> tag. We have also included Line Numbers and Copy to Clipboard Button plugin.

<head>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/themes/prism.min.css' integrity='sha512-tN7Ec6zAFaVSG3TpNAKtk4DOHNpSwKHxxrsiw4GHKESGPs5njn/0sMCUMl2svV4wo4BK/rCP7juYz+zx+l6oeQ==' referrerpolicy='no-referrer' rel='stylesheet'/>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/line-numbers/prism-line-numbers.min.css' integrity='sha512-cbQXwDFK7lj2Fqfkuxbo5iD1dSbLlJGXGpfTDqbggqjHJeyzx88I3rfwjS38WJag/ihH7lzuGlGHpDBymLirZQ==' referrerpolicy='no-referrer' rel='stylesheet'/>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/toolbar/prism-toolbar.min.css' integrity='sha512-ycl7dIZ0VJ5535/dzskAMTwOI6OoTNZ3PeD+tfckvYqMmAzaEwQfJHqJTSqcV2iQeJnp5XxnFy5jKotibstp7A==' referrerpolicy='no-referrer' rel='stylesheet'/>
<style>
  pre {
    font-size:smaller;
   }
</style>
</head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/components/prism-core.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/line-numbers/prism-line-numbers.min.js" integrity="sha512-dubtf8xMHSQlExGRQ5R7toxHLgSDZ0K7AunqPWHXmJQ8XyVIG19S1T95gBxlAeGOK02P4Da2RTnQz0Za0H0ebQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/toolbar/prism-toolbar.min.js" integrity="sha512-YrvgEHAi5/3o2OT+/vh1z19oJXk/Kk0qdVKbjEFl9VRmcLTaWRYzVziZCvoGpJ2TrnV7rB8pnJcz1ioVJjgw2A==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/copy-to-clipboard/prism-copy-to-clipboard.min.js" integrity="sha512-pUNGXbOrc+Y3dm5z2ZN7JYQ/2Tq0jppMDOUsN4sQHVJ9AUQpaeERCUfYYBAnaRB9r8d4gtPKMWICNhm3tRr4Fg==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/autoloader/prism-autoloader.min.js"></script>

3. In your edit post screen, hit HTML and put this surrounding the code you want to hightlight.

<pre class="line-numbers"><code class="language-sql">
Your SQL Statement here
</code></pre>

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