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: 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


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