Slide 1
Slide 1 text
Master Product Manager
[email protected]
@thatjeffsmith
Oracle REST Data Services 101
for the Oracle Database Professional
Jeff Smith
Slide 2
Slide 2 text
• HTTP/S
• REST
• JSON
• ORDS 101: Install, Config, & Dev
Today’s Topics
HTTPS/REST
JDBC
{JSON}
Slide 3
Slide 3 text
• 70+% of all APIs are REST Based
• Based on HTTP, which is easy and ubiquitous
• No drivers to install or configure
• Your entire IT stack can be managed by REST APIs…
• …do you want your enterprise data to be left out?
Why would the database care about REST?
Slide 4
Slide 4 text
• Web listener for PL/SQL (APEX)
• RESTful Services for Oracle Database
• SQL Developer Web
• Database Management REST API
• Mongo style API for Oracle Database (SODA)
Why ORDS?
Slide 5
Slide 5 text
JDBC
OCI/SQLNET
Listener
Pre-Internet
• STATEFUL
• Persistent Connections
• Oracle Client / JDBC Driver required
Slide 6
Slide 6 text
The Way of the Web
Listener
• Stateless
• Each operation, new connection
• Just make HTTP calls
Slide 7
Slide 7 text
TNSPING, Dammit!
Slide 8
Slide 8 text
• HTTP: network protocol, built on TCP
• HTTPS: secured, end-to-end version of HTTP
• REST: architectural style
• JSON: open, easy to read, interchange format
Words with Friends
Slide 9
Slide 9 text
• HyperText Transfer Protocol
• Invented, along with HTML, by Tim Berners-Lee at CERN (1989)
• Requests (GET, PUT, POST, HEAD, OPTIONS, DELETE…)
• Status Codes (200, 201, 401, 403, 404, 500…)
• Security (Authentication)
HTTP
Slide 10
Slide 10 text
Chrome Tools…peek behind the curtain!
Slide 11
Slide 11 text
Request, ask for something
Address: https://site.com/collection/item
Method: GET
Header: Cache-Control: no-cache
Body: n/a
Response, what you get back
Status Code: 200 OK
Header: Content-Type: application/json
Body: {…}
Requests and Responses
Slide 12
Slide 12 text
curl --request POST \
--url http://localhost:8080/ords/hr/_/db-api/19.1.0/database/datapump/export \
--header 'authorization: Basic aHI6b3JhY2xl' \
--header 'content-type: application/json' \
--data '{
"datapump_dir": "DATA_PUMP_DIR",
"filter": "HOCKEY_STATS, UNTAPPD",
"job_mode": "TABLE",
"threads": 2
}'
An HTTP Request
Body
Header
Method
Slide 13
Slide 13 text
An HTTP Response
Header
Body
Slide 14
Slide 14 text
1. Define the URL Template
2. Define the Handler
3. Define the DB Work tied to said Request
4. Define Parameters for Request/Response Headers
5. Define the Response format
Implementing this in ORDS
Slide 15
Slide 15 text
A RESTful Service in ORDS
Slide 16
Slide 16 text
A RESTful Service in ORDS
Slide 17
Slide 17 text
A RESTful Service in ORDS
Slide 18
Slide 18 text
ORDS Request
Slide 19
Slide 19 text
ORDS Response
Slide 20
Slide 20 text
• HTTP + TLS = HTTPS
• The communication is encrypted
• HTTP, anyone on your network can see EVERYTHING you
pass back and forth from your server
Passwords!
Pictures!
EVERYTHING
HTTPS always, HTTP never…mostly
Slide 21
Slide 21 text
ORDS & HTTPS
• Jetty listens on 443 vs 8080
• ORDS generates a self-signed cert
• You should get a ‘real’ certificate (Lets Encrypt)
Slide 22
Slide 22 text
Authentication – are you who you say you are?
Authorization – are you allowed to do what you’re asking?
Authenticated User: JEFF
Roles: admin, dev, user, test, god
Requested Resource: ‘user’ role required
Security
Slide 23
Slide 23 text
BASIC Auth
• you provide a username/password on EVERY request
• Not recommended
Cookie Based
• One time user/password, session info returned in a cookie
• Each request now includes this cookie in its request header
• Cookies ‘expire’ after X seconds
OAUTH2
• Waaay too complicated, we’ll cover this ‘next time’
Authentication
Slide 24
Slide 24 text
• WLS, Tomcat makes sure you are who you say you are
• They pass your identity and roles down to ORDS
• ORDS makes sure your role matches up to what’s required to
access a REST Service
Authentication is handled by the Webserver
Slide 25
Slide 25 text
• Worst Case: ORDS Jetty Basic Auth
• Next Best: ORDS OAUTH2
BUT MY APP MUST HAS ORACLE USER/PWD….
…OK, but it’s not GOOD.
But I don’t have anything for AUTH
Slide 26
Slide 26 text
Creating a Jetty User
Slide 27
Slide 27 text
• Services or Patterns can be protected by Privileges
• Privileges are tied to ROLES
• ROLES are granted to authenticated USERS
Authorization in ORDS
Slide 28
Slide 28 text
Architectural Style
• Client – Server
• Stateless
• Cacheable
• Uniform Interface
Exercising the API requires:
• Location of our resource (URL)
• Action to be performed (GET, PUT, …)
REpresentational Stateless Transfer (REST)
Slide 29
Slide 29 text
Database won’t always be available via SQLNet
Scenario: APEX needs to pull data from a different DB
• DB Links aren’t always available
• ORDS powered REST API
• APEX can pull data in via HTTP
Real World Example
Slide 30
Slide 30 text
Things you can do with REST APIs
• Power applications – mobile, desktop, web…
• HTTP(S) is ubiquitous, even avail in COBOL
• Continuous Integration & Delivery
• Automation (BASH + cURL)
• OCI comes with REST APIs for all tenancy and DBCS operations
Slide 31
Slide 31 text
‘Good’ REST
• Model resources, not actions:
• GET /ords/hr/employees/ - GOOD
• GET /ords/hr/delete_emp/ - BAD
• DELETE /ords/hr/employees/97 – GOOD
• Uniform operations on all resources:
GET, POST, PUT, DELETE, OPTIONS, HEAD
• Stateless requests, state transitions comm via hyper-links
• Throwing HTTP onto your API <> REST
Slide 32
Slide 32 text
Tip: Model your Templates first!
No Verbs! ☺
Slide 33
Slide 33 text
HTTP & REST Examples
HTTP(S) client
1. Client makes request GET http://fridge/beers/beer
2. Server responds 200
http://fridge/beers/beer/meta
1. Client makes request POST http://fridge/beers/
{"id":1,
"name":"Premium Ice Lite",…}
2. Server responds 201 Created
http://fridge/beers/beer/1
Webserver
Slide 34
Slide 34 text
JavaScript Object Notation
• Jay-Sun
• Not just for js
• Skinnier than XML
• Flexible
• Link-friendly
Slide 35
Slide 35 text
• Database output =>JSON
• PLSQL OUT params, RETURNs, stdout / feedback => JSON
• JSON on HTTP requests => :BIND parameters, PL/SQL input
parameters
• SQL data types and user defined types supported
ORDS JSON Magic
Slide 36
Slide 36 text
ORDS JSON Magic
Slide 37
Slide 37 text
Oracle
REST
Data
Services
3
7
2011 2015
APEX Listener
• APEX based RESTful
Services
ORDS v3.0
• APEX no longer required
• SODA
• AUTO REST
2012
APEX Listener v2
• ORDS based REST Services
• Standalone Jetty
2017
ORDS v17
• REST enabled SQL
• Type 3 JDBC Driver
• Open API (Swagger)
2018
ORDS v18
• PL/SQL Gateway
enhancements
2019
ORDS v19
• DB API
• SQL Developer Web
Slide 38
Slide 38 text
The PL/SQL Gateway
ORDS started out as the APEX Listener, a way to call PL/SQL
programs via HTTP
A replacement for MOD_PLSQL
GET /ords/user/package.procedure
runs PL/SQL
prints HTML back to the browser
Slide 39
Slide 39 text
Example: PL/SQL web listener
Slide 40
Slide 40 text
• Collection or Item
Template /collection/:item
Handler GET, PUT, POST, DELETE, powered by SQL/PLSQL
• Organized into modules
• Defined for a SCHEMA
REST Services
Slide 41
Slide 41 text
GET https://host/ords/human/peeps/ SELECT *
FROM EMP
HTTP/1.1 200 OK
{ "employee_id": 100,
"first_name": "Kris"
,"last_name": "Rice",
, “email": “[email protected]",
{json}
URI SQL & PLSQL
Map & Bind
HTTP Request
HTTP Response Transform to JSON SQL Result Set / Standard OUT
Oracle REST Data Services
ORDS REST Request & Response Workflow
Slide 42
Slide 42 text
• Simple HTTP call w/query params
• {json} response
• Page the results
• Links for each item
• Handle the data types for me
Example: EMPS of DEPT 50
Slide 43
Slide 43 text
• Oracle 11gR2 and higher databases
• Support tied to your Database license (MOS)
• Single Instance, Container, or Pluggable deployments
• On Premise or Oracle Cloud Infrastructure
• Built by the Oracle Database Team FOR the Oracle Database
All of this is available at no additional cost
Slide 44
Slide 44 text
• SlideShare
• Blogs
• GitHub
• Articles
UKOUG Scene Why REST, and What’s in it or Me?
Oracle Mag AUTO REST & REST Enabled SQL
• And don’t forget Oracle-Base!
ORDS on
Other Resources