Tweet
Tweet

Slide 1

Slide 1 text

Control Oracle Databases 
 via Slack's REST API Latin America Community Tour '23 - Colombia July 28, 2023 Sean Scott Oracle ACE Director Managing Principal Consultant @oraclesean linkedin.com/in/soscott/ [email protected] @ViscosityNA www.viscosityna.com

Slide 2

Slide 2 text

DATABASE RELIABILITY ENGINEERING ⁘ DEVOPS & AUTOMATION 
 HIGH AVAILABILITY ⁘ BUSINESS CONTINUITY ⁘ DISASTER RECOVERY 
 MODERNIZATION ⁘ OBSERVABILITY ⁘ ENGINEERED SYSTEMS AutoUpgrade ⁘ Zero Downtime Migrations ⁘ Patching Real Application Clusters ⁘ Data Guard ⁘ Sharding Docker/Containers ⁘ Terraform ⁘ Ansible Exadata ⁘ Oracle Database Appliance AHF ⁘ TFA ⁘ GIMR ⁘ CHA Sean Scott Oracle ACE Director Managing Principal Consultant @oraclesean linkedin.com/in/soscott/ [email protected] @ViscosityNA www.viscosityna.com

Slide 3

Slide 3 text

@ViscosityNA www.viscosityna.com Oracle on Docker Running Oracle Databases in Linux Containers Download a free sample chapter: 
 https:/ /oraclesean.com 20% Discount Code: OracleDocker https:/ /link.springer.com

Slide 4

Slide 4 text

@ViscosityNA www.viscosityna.com Introducing ChatOps

Slide 5

Slide 5 text

@ViscosityNA www.viscosityna.com “Conversations, put to work” Sean Regan Head of Product Marketing Jira/Bitbucket

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

@ViscosityNA www.viscosityna.com Chat Adds Value for Operations • Familiar • Informal • Contextual • Synchronous • Linear

Slide 8

Slide 8 text

@ViscosityNA www.viscosityna.com Building Service-Driven Cultures

Slide 9

Slide 9 text

Help Desk creates a new case and assigns the ticket to Development Development reviews the promotion records. They seem to be correct. Ticket is marked RESOLVED, Marketing is asked to confirm. Development investigates; this appears to be a known application issue. The DBA team has a script to fix it. 
 Ticket is assigned to the DBA team. Marketing user can’t add a promotion. Contacts Help Desk DBA team reviews the ticket and runs script to fix the issue. Ticket reassigned to Development for review. Marketing confirms the issue is fixed. Ticket is closed. Solving this engages the end user, help desk, developers and DBAs Multiple steps take time, impacting external customers and internal teams

Slide 10

Slide 10 text

Slack API interprets the command 
 Passes promotion data to backend 
 Backend calls PL/SQL package Marketing user receives 
 confirmation of fix in channel Package interprets parameters, 
 validates entries, confirms condition and 
 runs automated fix 
 OUT parameter passed to backend Marketing user can’t add a promotion Invokes a chatbot command in the #marketing channel with promotion data Backend processes OUT parameter 
 Identifies success/failure 
 Builds response package 
 Passes response to Slack API Securely exposes endpoint to chat Automation validates and runs code End user empowered to fix issues when discovered Internal teams no longer engaged

Slide 11

Slide 11 text

@ViscosityNA www.viscosityna.com Building Service-Driven Cultures • Exception reporting is passive & reactive • The recipient of an event may not be the audience • Example: Alerts sent to DBA for application deadlocking • DBA observes performance issues, forwards to developers • Developers may not recognize the urgency • Friction grows between DBAs, developers

Slide 12

Slide 12 text

@ViscosityNA www.viscosityna.com Example Projects • Account expiration warnings • Self-service password resets • AdHoc reports • Auto-solve known-issues • Share events w/non-DBAs • ORA-1555 • ORA-60 • Expose DB logs • Report: • Backup status, failure • Unusual login activity • Account locks • Audit exceptions • Object growth Automatically gene tickets when appro

Slide 13

Slide 13 text

Application Logs Server 
 Logs Web 
 Logs Database Logs Streetlight Effect

Slide 14

Slide 14 text

@ViscosityNA www.viscosityna.com • Iterate • Automate • Communicate • Find opportunities Building Service-Driven Cultures

Slide 15

Slide 15 text

@ViscosityNA www.viscosityna.com Case Study: Oracle + Slack

Slide 16

Slide 16 text

@ViscosityNA www.viscosityna.com Code and Environment • Slack (Free Tier) • Oracle OCI (Always Free Tier) • Compute Instance: OEL 7.7, Oracle DB 19.3 • Setup: • oraclesean.com/blog/oracle-19c-rpm-install-on-oci-free-compute-instance • Demo code • github.com/oraclesean/ChatOpsPresentation

Slide 17

Slide 17 text

@ViscosityNA www.viscosityna.com Code and Environment • Slack (Free Tier) • Oracle Autonomous Database • OEL 8 Compute + Docker/Podman • Setup: • oraclesean.com/blog/oracle-19c-rpm-install-on-oci-free-compute-instance • Demo code • github.com/oraclesean/ChatOpsPresentation

Slide 18

Slide 18 text

@ViscosityNA www.viscosityna.com Passive Notifications

Slide 19

Slide 19 text

@ViscosityNA www.viscosityna.com Oracle ➞ Slack: Email a channel • Add the Email application in a channel • Requires the paid plan • Get the channel’s unique email address • Send email • UTL_SMTP

Slide 20

Slide 20 text

@ViscosityNA www.viscosityna.com Oracle ➞ Slack: Incoming Webhooks • Add the Incoming Webhooks application to Slack • Available with the free tier • Install and configure Incoming Webhooks in a channel • Send REST message via POST • UTL_HTTP

Slide 21

Slide 21 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 22

Slide 22 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 23

Slide 23 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 24

Slide 24 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 25

Slide 25 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 26

Slide 26 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 27

Slide 27 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 28

Slide 28 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 29

Slide 29 text

@ViscosityNA www.viscosityna.com Oracle ➞ Slack: Incoming Webhooks • Download Slack’s Root CA certificate • Create a wallet • Add the certificate to the wallet • Send a notification

Slide 30

Slide 30 text

@ViscosityNA www.viscosityna.com Demo Implementation - Environment & Wallet Add certificate --------------- $ orapki wallet add -wallet $WALLET_DIR \ 
 -trusted_cert -cert \ 
 "$WALLET_DIR/slack-com.pem" \ 
 -pwd $WALLET_PWD Set environment --------------- export WALLET_DIR=/wallet_directory export WALLET_PWD=password Create wallet ------------- $ orapki wallet create -wallet \ 
 $WALLET_DIR -pwd $WALLET_PWD \ 
 -auto_login

Slide 31

Slide 31 text

@ViscosityNA www.viscosityna.com var webhook_url varchar2(4000); var wallet_dir varchar2(160); var wallet_pwd varchar2(80); exec :webhook_url := 'https://...'; exec :wallet_dir := 'file:/...'; exec :wallet_pwd := 'SuperStrongPw0rd'; Demo Implementation - Test Connectivity

Slide 32

Slide 32 text

@ViscosityNA www.viscosityna.com Demo Implementation - Test Connectivity "invalid_payload" is expected and verifies the certificate is correct & slack.com can be accessed. select utl_http.request(:webhook_url, NULL, :wallet_dir) as x from dual; X ------------------------------------ invalid_payload

Slide 33

Slide 33 text

@ViscosityNA www.viscosityna.com Demo Implementation - Send a Test Message declare req utl_http.req; res utl_http.resp; url varchar2(4000) := :webhook_url; name varchar2(4000); buffer varchar2(4000); content varchar2(4000); slack_channel varchar2(40) := '#dba'; slack_user varchar2(40) := 'ora-bot'; slack_text varchar2(4000) := 'Test post from database '; slack_icon varchar2(40) := ':mega:'; db_name v$database.name%TYPE;

Slide 34

Slide 34 text

@ViscosityNA www.viscosityna.com Demo Implementation - Send a Test Message begin utl_http.set_wallet(:wallet_dir, :wallet_pwd); select name into db_name from v$database; content := '{"channel": "' || slack_channel || '", ' || '"username": "' || slack_user || '", ' || '"text": "' || slack_text || db_name || '", ' || '"icon_emoji": "' || slack_icon || ‘"}';

Slide 35

Slide 35 text

@ViscosityNA www.viscosityna.com Demo Implementation - Send a Test Message req := utl_http.begin_request(url, 'POST',' HTTP/1.1'); utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); utl_http.set_header(req, 'content-type', 'application/json'); utl_http.set_header(req, 'Content-Length', length(content)); utl_http.write_text(req, content); res := utl_http.get_response(req); begin loop utl_http.read_line(res, buffer); dbms_output.put_line(buffer); end loop; utl_http.end_response(res); exception when utl_http.end_of_body then utl_http.end_response(res); end;

Slide 36

Slide 36 text

@ViscosityNA www.viscosityna.com

Slide 37

Slide 37 text

@ViscosityNA www.viscosityna.com Active Communication

Slide 38

Slide 38 text

@ViscosityNA www.viscosityna.com Oracle 㲗 Slack: Active Communication • Outgoing Webhooks • Deprecated, replaced by Apps • Slack Real-Time Messaging (RTM) • Deprecated, replaced by Apps • Slack Apps

Slide 39

Slide 39 text

@ViscosityNA www.viscosityna.com Oracle 㲗 Slack: Slack Apps • Foundation for extending Slack • Message via Web API, Webhooks • Read messages via Web, RTM, and Events API • Respond via Event API • Create custom slash commands (/command) • Run bots • Build intelligent interactions

Slide 40

Slide 40 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA Flask/Python interprets JSON payload 
 Validates and builds SQL 
 Sends SQL via cx_Oracle Slack displays payload in channel Oracle runs PL/SQL 
 Returns results to Client User enters a command 
 Slack sends JSON via HTTP POST Flask/Python reads and interprets result 
 Returns JSON payload or webhook to Slack Webhook Many options for frameworks!

Slide 41

Slide 41 text

@ViscosityNA www.viscosityna.com Oracle 㲗 Slack: Demo Application Resources • Slack (Free) • Slack API • Python • cx_Oracle • Flask web framework • ngrok (Free) • SSL tunnel to localhost • Oracle Cloud Always-Free Tier • Compute instance • Oracle 19.3

Slide 42

Slide 42 text

@ViscosityNA www.viscosityna.com h tt ps:/ /www.twilio.com/blog/2015/09/6-awesome-reasons-to-use-ngrok-when-tes ti ng-webhooks.html

Slide 43

Slide 43 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA Flask/Python interprets JSON payload 
 Validates and builds SQL 
 Sends SQL via cx_Oracle Slack displays payload in channel Oracle runs PL/SQL 
 Returns results to Client User enters a command 
 Slack sends JSON via HTTP POST Flask/Python reads and interprets result 
 Returns JSON payload or webhook to Slack Testing Framework

Slide 44

Slide 44 text

@ViscosityNA www.viscosityna.com Demo /command Setup # yum -y install yum-utils # yum install -y --disablerepo=ol7_developer_EPEL python36 # pip install --upgrade pip # python -m pip install cx_Oracle flask # pip install Flask-API python-dotenv slack # pip install slackclient slackbot requests

Slide 45

Slide 45 text

@ViscosityNA www.viscosityna.com Demo /command Setup # firewall-cmd --zone=public --permanent --add-port=0000/tcp # firewall-cmd --reload # firewall-cmd --list-all public (active) ... ports: 0000/tcp ...

Slide 46

Slide 46 text

@ViscosityNA www.viscosityna.com Demo /command Setup create user slack identified by ... grant create session to slack; create user appuser identified by ... alter user appuser quota ... on ; grant execute on appuser. to slack; create synonym slack. for appuser.;

Slide 47

Slide 47 text

@ViscosityNA www.viscosityna.com Demo /command Setup import os import cx_Oracle from flask import Flask app = Flask(__name__) @app.route('/') def index(): con = cx_Oracle.Connection('slack', 'pass', 'localhost:1521/orclpdb1') cur = con.cursor() cur.execute("select 'Hello, World' from dual") col = cur.fetchone()[0] ...

Slide 48

Slide 48 text

@ViscosityNA www.viscosityna.com Demo /command Setup mkdir ~/ngrok cd ~/ngrok wget https://bin.equinox.io/c/.../ngrok-stable-linux-amd64.zip unzip ngrok-stable-linux-amd64.zip # Add authtoken (from dashboard.ngrok.com/auth): ./ngrok authtoken ...

Slide 49

Slide 49 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 50

Slide 50 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 51

Slide 51 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 52

Slide 52 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 53

Slide 53 text

@ViscosityNA www.viscosityna.com Demo /command Setup # flask run * Serving Flask app "dbdo.py" * Environment: production WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Debug mode: off * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit) 127.0.0.1 - - [28/Jul/2023 00:52:28] "POST /dbdo HTTP/1.1" 200 - 127.0.0.1 - - [28/Jul/2023 00:52:42] "POST /dbdo HTTP/1.1" 200 -

Slide 54

Slide 54 text

@ViscosityNA www.viscosityna.com Demo /command Setup ./ngrok http 5000 Session Status online Account Sean Scott (Plan: Free) Version 3.3.1 Region United States (us) Latency 46ms Web Interface http://127.0.0.1:4040 Forwarding https://XXX.ngrok-free.app -> http://localhost:5000 Connections ttl opn rt1 rt5 p50 p90 7 0 0.02 0.02 0.18 3.06

Slide 55

Slide 55 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 56

Slide 56 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 57

Slide 57 text

@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA

Slide 58

Slide 58 text

@ViscosityNA www.viscosityna.com Ngrok Redirect URL Changes on Restart ./ngrok http 5000 Session Status online Account Sean Scott (Plan: Free) Version 3.3.1 Region United States (us) Latency 46ms Web Interface http://127.0.0.1:4040 Forwarding https://XXX.ngrok-free.app -> http://localhost:5000 Connections ttl opn rt1 rt5 p50 p90 7 0 0.02 0.02 0.18 3.06

Slide 59

Slide 59 text

@ViscosityNA www.viscosityna.com Ngrok Redirect URL Changes on Restart # ./ngrok http 5000 > /dev/null & # export WEBHOOK_URL="$(curl http://localhost:4040/api/tunnels | \ jq ".tunnels[0].public_url")" # echo $WEBHOOK_URL "https://XXX.ngrok-free.app"

Slide 60

Slide 60 text

@ViscosityNA www.viscosityna.com Thoughts on API Design

Slide 61

Slide 61 text

@ViscosityNA www.viscosityna.com Development Considerations • Write modular PL/SQL • Use parameters and variables • Don’t hard-code anything! • Add logging to v0.1 • Not an afterthought • Who, what, when • Consider multiple levels/verbosity

Slide 62

Slide 62 text

@ViscosityNA www.viscosityna.com Development Considerations • Use exception handlers • No WHEN OTHERS THEN NULL! • Don’t expose SQL

Slide 63

Slide 63 text

@ViscosityNA www.viscosityna.com Development Considerations • Limit resources via profiles • Prevent DDoS from chat • Control access via roles • Manage roles, not users

Slide 64

Slide 64 text

@ViscosityNA www.viscosityna.com Application Roles • Limit permissions to the bot role: • create session • execute on API packages • Limit permissions to the API role: • Separate from application or data owner • 18c+ Define as schema-only users • <18c: No create session or connect

Slide 65

Slide 65 text

@ViscosityNA www.viscosityna.com Application Security • Limit application permissions • Confine applications to private channels • Validate users when necessary • Treat chat development as a “regular” project • Version control • Configuration management • Include log monitoring to operational footprint

Slide 66

Slide 66 text

@ViscosityNA www.viscosityna.com Extending Bots

Slide 67

Slide 67 text

@ViscosityNA www.viscosityna.com Contact [email protected] https://linktr.ee/oraclesean