Using Slack to observe and control Oracle databases - LAOUC Tour '23 - Colombia

Messaging apps do more than facilitate communication—they're hubs for accelerating work from idea to completion. Slack and MS Teams plugins shift the work once performed in dedicated tools into chat's single, familiar interface. What began as a simple app for connecting people and teams is now an indispensable platform that joins people to processes, increases productivity, and ignites collaboration. In this session, you'll discover the value of connecting an Oracle database to Slack by streamlining and simplifying a business workflow.

Topics include:
- Using REST to integrate an Oracle database with Slack
- Building a custom bot to access PL/SQL packages
- Extending bots with parameters
- Validating and processing data
- Protecting database with layered security strategies

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/ sean.scott@viscosityna.com @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/ sean.scott@viscosityna.com @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. @ViscosityNA www.viscosityna.com Introducing ChatOps
5. @ViscosityNA www.viscosityna.com “Conversations, put to work” Sean Regan Head of Product Marketing Jira/Bitbucket
7. @ViscosityNA www.viscosityna.com Chat Adds Value for Operations • Familiar • Informal • Contextual • Synchronous • Linear
8. @ViscosityNA www.viscosityna.com Building Service-Driven Cultures
9. 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
10. 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
11. @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
12. @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
13. Application Logs Server 
 Logs Web 
 Logs Database Logs Streetlight Effect
14. @ViscosityNA www.viscosityna.com • Iterate • Automate • Communicate • Find opportunities Building Service-Driven Cultures
15. @ViscosityNA www.viscosityna.com Case Study: Oracle + Slack
16. @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
17. @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
18. @ViscosityNA www.viscosityna.com Passive Notifications
19. @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
20. @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
21. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
22. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
23. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
24. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
25. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
26. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
27. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
28. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
29. @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
30. @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
31. @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
32. @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
33. @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;
34. @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 || ‘"}';
35. @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;
36. @ViscosityNA www.viscosityna.com
37. @ViscosityNA www.viscosityna.com Active Communication
38. @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
39. @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
40. @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!
41. @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
42. @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
43. @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
44. @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
45. @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 ...
46. @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>;
47. @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] ...
48. @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 ...
49. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
50. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
51. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
52. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
53. @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 -
54. @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
55. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
56. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
57. @ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA
58. @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
59. @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"
60. @ViscosityNA www.viscosityna.com Thoughts on API Design
61. @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
62. @ViscosityNA www.viscosityna.com Development Considerations • Use exception handlers • No WHEN OTHERS THEN NULL! • Don’t expose SQL
63. @ViscosityNA www.viscosityna.com Development Considerations • Limit resources via profiles • Prevent DDoS from chat • Control access via roles • Manage roles, not users
64. @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
65. @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
66. @ViscosityNA www.viscosityna.com Extending Bots
67. @ViscosityNA www.viscosityna.com Contact sean.scott@viscosityna.com https://linktr.ee/oraclesean