Use Slack and MS Teams to Observe and Control Oracle Databases

Use Slack and MS Teams to Observe and Control Oracle Databases
Messaging apps do more than facilitate communication—they're hubs that accelerate work along its journey from idea through completion. Slack and MS Teams plugins shift the work once performed in dedicated tools into chat's single, familiar interface. What began as simple applications that connected people and teams are now indispensable platforms that join people to processes, increase productivity, and ignite collaboration.

One of the last frontiers for integration is connecting chat to databases—an idea that might seem to offer little value. Yet just as chat plugins streamlined other workflows, enabling database-to-chat communications revolutionizes how organizations access database resources. Simple, standardized APIs allow organizations to quickly build custom services on top of a trusted platform and leverage existing role-based access.

This session begins with an introduction to the database-to-chat integrations possible through Oracle built-in packages, including the new DBMS_CLOUD_NOTIFICATION package. You'll learn how to set up and secure passive notifications from an Oracle database to clients, then discover ways to extend this relationship, deploying a fully interactive platform that allows users to issue database commands directly from a chat client. The session concludes with example applications, a discussion of best practices, and coverage of security strategies and considerations.

Transcript

1. East Coast Oracle User Group November 5, 2024 Use Slack and MS Teams to observe and control Oracle Databases
2. Database Reliability Engineering MAA ⁘ RAC ⁘ RMAN Data Guard ⁘ Sharding ⁘ Partitioning Information Lifecycle Management Exadata & Engineered Systems Database Modernization Upgrades ⁘ Patching ⁘ Migrations Cloud ⁘ Hybrid Automation DevOps ⁘ IaC ⁘ Containers ⁘ Terraform Vagrant ⁘ Ansible Observability AHF ⁘ TFA ⁘ CHA ⁘ CHM
3. www.viscosityna.com @ViscosityNA Oracle on Docker Running Oracle Databases in Linux Containers Free sample chapter: https://oraclesean.com
5. @ViscosityNA www.viscosityna.com ChatOps and Service Culture
6. @ViscosityNA www.viscosityna.com Engineering has customers
8. @ViscosityNA www.viscosityna.com How many engineers does it take...
9. @ViscosityNA www.viscosityna.com The ugly side... • Development disconnect • Legacy application • Workload
10. @ViscosityNA www.viscosityna.com Legacy application
11. Help Desk creates a new case and assigns the ticket to Development Development reviews the logs. They appear right. Marks ticket RESOLVED, Marketing is asked to confirm. Development investigates and confirms this is a known problem only DBAs can solve. Assigns ticket to DBA team. A marketing user can't activate a promotion, contacts Help Desk DBA team reviews the ticket, executes a script to solve the issue. Ticket reassigned to Development for review. Marketing confirms the problem is solved and closes the ticket.
12. Help Desk creates a new case and assigns the ticket to Development Development reviews the logs. They appear right. Marks ticket RESOLVED, Marketing is asked to confirm. Development investigates and confirms this is a known problem only DBAs can solve. Assigns ticket to DBA team. A marketing user can't activate a promotion, contacts Help Desk DBA team reviews the ticket, executes a script to solve the issue. Ticket reassigned to Development for review. Marketing confirms the problem is solved and closes the ticket. Solving this involves the end user, help desk, developers, and DBAs. Multiple steps take time and impact external customers and internal teams.
13. @ViscosityNA www.viscosityna.com Service as a service.
14. The Slack API interprets the command and passes the promotion code the backend which calls a PL/SQL package. The marketing user receives a confirmation in the #marketing channel that the issue is resolved. The package validates the parameters, confirms the condition, executes a fix, and sends a result via an OUT parameter to the backend. A marketing user can't activate a promotion. They invoke a command in the #marketing channel with the promotion code. The backend processes the OUT parameter, identifies success/ failure, and builds a response that's passed back to the Slack API.
15. The Slack API interprets the command and passes the promotion code the backend which calls a PL/SQL package. The marketing user receives a confirmation in the #marketing channel that the issue is resolved. The package validates the parameters, confirms the condition, executes a fix, and sends a result via an OUT parameter to the backend. A marketing user can't activate a promotion. They invoke a command in the #marketing channel with the promotion code. The backend processes the OUT parameter, identifies success/ failure, and builds a response that's passed back to the Slack API. Securely exposes the endpoint to chat Automation validates and executes code End user empowered to fix issues when discovered Internal teams are no longer involved
16. @ViscosityNA www.viscosityna.com Database interaction
17. @ViscosityNA www.viscosityna.com Oracle 㲗 Slack: Slack Applications • Foundation for extending Slack • Message via web API, webhooks • Read messages via web and REST APIs • Reply via REST API • Create custom slash commands (/command) • Run bots • Build smart interactions
18. @ViscosityNA www.viscosityna.com Flask/Python translates the JSON payload into a PL/SQL call, sends it to DB via cx_Oracle Slack displays the payload in the channel Oracle executes PL/ SQL and returns a result to the client The user enters a command. Slack sends JSON via HTTP POST. Flask/Python app reads the result and returns a JSON payload to Slack via webhook. Many options for backends!
19. @ViscosityNA www.viscosityna.com /command configuration create user slack identified by ... grant create session to slack; create user appuser identified by ... alter user appuser quota ... on <ts>; create or replace package appuser.<pkg> ... grant execute on appuser.<pkg> to slack; create synonym slack.<pkg> for appuser.<pkg>;
20. @ViscosityNA www.viscosityna.com /command configuration # firewall-cmd --zone=public --permanent --add-port=0000/tcp # firewall-cmd --reload # firewall-cmd --list-all public (active) ... ports: 0000/tcp ...
21. @ViscosityNA www.viscosityna.com /command configuration # yum -y install yum-utils python36 # pip install --upgrade pip # python -m pip install cx_Oracle flask # pip install Flask-API python-dotenv slack # pip install slackclient slackbot requests
22. @ViscosityNA www.viscosityna.com /command configuration 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] ...
23. @ViscosityNA www.viscosityna.com
24. @ViscosityNA www.viscosityna.com
25. @ViscosityNA www.viscosityna.com
26. @ViscosityNA www.viscosityna.com
27. @ViscosityNA www.viscosityna.com
28. @ViscosityNA www.viscosityna.com
29. @ViscosityNA www.viscosityna.com That was then
30. @ViscosityNA www.viscosityna.com DBMS_CLOUD_NOTIFICATION • Send messages from Autonomous Database to: • Email • Slack • Teams • Send database queries, messages to a channel • "Simple" output (text) • Query results attached as CSV Automatically gene tickets when appro
31. @ViscosityNA www.viscosityna.com Create a credential begin dbms_cloud.create_credential ( credential_name => 'SLACK_CREDENTIAL' , username => '<SLACK_USER>' , password => '<SECRET>'); end; / begin dbms_cloud.create_credential ( credential_name => 'TEAMS_CREDENTIAL' , username => '<BOT_ID>' , password => '<SECRET>'); end; /
32. @ViscosityNA www.viscosityna.com Slack: Add ACL begin dbms_network_acl_admin.append_host_ace ( host => 'slack.com' , lower_port => 443 , upper_port => 443 , ace => xs$ace_type ( privilege_list => xs$name_list('http') , principal_name => <SLACK_USER> , principal_type => xs_acl.ptype_db)); end; /
33. @ViscosityNA www.viscosityna.com Send simple output begin dbms_cloud_notification.send_message ( provider => '<slack|msteams>' , credential_name => '<CREDENTIAL>' , message => '<MESSAGE>' , params => json_object('channel' value '<CHANNEL_ID>')); end; /
34. @ViscosityNA www.viscosityna.com Create a query declare l_sql clob := q'[select ... from ... where ... ]'; ...
35. @ViscosityNA www.viscosityna.com Slack: Send query output begin dbms_cloud_notification.send_data ( provider => 'slack' , credential_name => '<CREDENTIAL>' , query => l_sql , params => json_object('channel' value '<CHANNEL_ID>' , 'type' value 'csv')); end; /
36. @ViscosityNA www.viscosityna.com Teams: Send query output begin dbms_cloud_notification.send_data ( provider => 'msteams' , credential_name => '<CREDENTIAL>' , query => l_sql , params => json_object('tenant' value '<TENANT_ID>' , 'team' value '<TEAM_ID>' , 'channel' value '<CHANNEL_ID>' , 'title' value '<TITLE>' , 'type' value 'csv')); end; /
37. @ViscosityNA www.viscosityna.com API Design
38. @ViscosityNA www.viscosityna.com Development considerations • Write modular PL/SQL • Use parameters and variables • Don't hard-code! • Generate logs starting with v0.1 • You won't add this later! • Who, what, when
39. @ViscosityNA www.viscosityna.com Design considerations • Use exception handlers • ... but not WHEN OTHERS THEN NULL! • Don't expose SQL—clean your inputs • Isolate code from users! • Isolate users from data!
40. @ViscosityNA www.viscosityna.com Design considerations • Limit resources through profiles • Avoid DDoS via chat • Don't assign the default profile • Control access via roles • Manage roles, not users
41. @ViscosityNA www.viscosityna.com Application roles • Limit permissions for the bot role • create session • execute on API package(s) • Limit permissions and roles for the API • Separate the application and data schemas • 18c+ Use schema users • <18c: No create session or connect • Easier to turn off roles than manage permissions in an emergency
42. @ViscosityNA www.viscosityna.com Secure applications • Limit application permissions • Limit applications to private channels • Validate users when necessary • Treat chap app development as a "normal" and formal project • Version control • Configuration management • Include chat app logs in regular monitoring operations
43. @ViscosityNA www.viscosityna.com Resources • Slack app quick-start: https:/ /api.slack.com/quickstart • Teams developer portal/quick-start: https:/ /dev.teams.microsoft.com/home https:/ /learn.microsoft.com/en-us/microsoftteams/platform/concepts/build-and-test/teams-developer-portal • Presentation Code Samples: https:/ /github.com/oraclesean/ChatOpsPresentation • ngrok (SSL for development) https:/ /ngrok.com
44. @ViscosityNA www.viscosityna.com Resources - Autonomous • DBMS_CLOUD https:/ /docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-cloud-subprograms.html • DBMS_CLOUD_NOTIFICATION https:/ /docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-dbms-cloud-notification.html • Send email/chat notifications from Autonomous https:/ /docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/send-mail-slack.html • Say Hello from an Autonomous Database (Adrian Png) https:/ /fuzziebrain.com/content/say-hello-from-an-autonomous-database •
45. www.viscosityna.com @ViscosityNA Questions? Contact Me! sean.scott@viscosityna.com https://linktr.ee/oraclesean