Share
Embed
Transcript
67 slides
-
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 -
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 -
@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 -
@ViscosityNA www.viscosityna.com “Conversations, put to work” Sean Regan Head of
Product Marketing Jira/Bitbucket -
@ViscosityNA www.viscosityna.com Chat Adds Value for Operations • Familiar •
Informal • Contextual • Synchronous • Linear -
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 -
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 -
@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 -
@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 -
Application Logs Server Logs Web Logs Database Logs
Streetlight Effect -
@ViscosityNA www.viscosityna.com • Iterate • Automate • Communicate • Find
opportunities Building Service-Driven Cultures -
@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 -
@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 -
@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 -
@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 -
@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 -
@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 -
@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 -
@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 -
@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; -
@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 || ‘"}'; -
@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; -
@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 -
@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 -
@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! -
@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 -
@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 -
@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 -
@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 ... -
@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>; -
@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] ... -
@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 ... -
@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 - -
@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 -
@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 -
@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" -
@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 -
@ViscosityNA www.viscosityna.com Development Considerations • Use exception handlers • No
WHEN OTHERS THEN NULL! • Don’t expose SQL -
@ViscosityNA www.viscosityna.com Development Considerations • Limit resources via profiles •
Prevent DDoS from chat • Control access via roles • Manage roles, not users -
@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 -
@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