Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Control Oracle Databases via Slack's REST API

Control Oracle Databases via Slack's REST API

Sean Scott

August 28, 2023
Tweet

More Decks by Sean Scott

Other Decks in Technology

Transcript

  1. 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
  2. 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
  3. @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
  4. 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
  5. 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
  6. @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
  7. @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
  8. @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
  9. @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
  10. @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
  11. @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
  12. @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
  13. @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
  14. @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
  15. @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
  16. @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;
  17. @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 || ‘"}';
  18. @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;
  19. @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
  20. @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
  21. @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!
  22. @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
  23. @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
  24. @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
  25. @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 ...
  26. @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 <ts>; grant execute on appuser.<pkg> to slack; create synonym slack.<pkg> for appuser.<pkg>;
  27. @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] ...
  28. @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 ...
  29. @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 -
  30. @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
  31. @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
  32. @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"
  33. @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
  34. @ViscosityNA www.viscosityna.com Development Considerations • Limit resources via profiles •

    Prevent DDoS from chat • Control access via roles • Manage roles, not users
  35. @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
  36. @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