Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

Use Slack and MS Teams to Observe and Control O...

Sean Scott
November 05, 2024

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.

Sean Scott

November 05, 2024
Tweet

More Decks by Sean Scott

Other Decks in Technology

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
  4. 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.
  5. 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.
  6. 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.
  7. 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
  8. @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
  9. @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!
  10. @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>;
  11. @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 ...
  12. @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
  13. @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] ...
  14. @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
  15. @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; /
  16. @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; /
  17. @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; /
  18. @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; /
  19. @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; /
  20. @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
  21. @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!
  22. @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
  23. @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
  24. @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
  25. @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
  26. @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 •