Tweet
Tweet

Slide 1

Slide 1 text

East Coast Oracle User Group November 5, 2024 Use Slack and MS Teams to observe and control Oracle Databases

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

www.viscosityna.com @ViscosityNA Oracle on Docker Running Oracle Databases in Linux Containers Free sample chapter: https://oraclesean.com

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

@ViscosityNA www.viscosityna.com ChatOps and Service Culture

Slide 6

Slide 6 text

@ViscosityNA www.viscosityna.com Engineering has customers

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

@ViscosityNA www.viscosityna.com How many engineers does it take...

Slide 9

Slide 9 text

@ViscosityNA www.viscosityna.com The ugly side... • Development disconnect • Legacy application • Workload

Slide 10

Slide 10 text

@ViscosityNA www.viscosityna.com Legacy application

Slide 11

Slide 11 text

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.

Slide 12

Slide 12 text

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.

Slide 13

Slide 13 text

@ViscosityNA www.viscosityna.com Service as a service.

Slide 14

Slide 14 text

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.

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

@ViscosityNA www.viscosityna.com Database interaction

Slide 17

Slide 17 text

@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

Slide 18

Slide 18 text

@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!

Slide 19

Slide 19 text

@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 ; create or replace package appuser. ... grant execute on appuser. to slack; create synonym slack. for appuser.;

Slide 20

Slide 20 text

@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 ...

Slide 21

Slide 21 text

@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

Slide 22

Slide 22 text

@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] ...

Slide 23

Slide 23 text

@ViscosityNA www.viscosityna.com

Slide 24

Slide 24 text

@ViscosityNA www.viscosityna.com

Slide 25

Slide 25 text

@ViscosityNA www.viscosityna.com

Slide 26

Slide 26 text

@ViscosityNA www.viscosityna.com

Slide 27

Slide 27 text

@ViscosityNA www.viscosityna.com

Slide 28

Slide 28 text

@ViscosityNA www.viscosityna.com

Slide 29

Slide 29 text

@ViscosityNA www.viscosityna.com That was then

Slide 30

Slide 30 text

@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

Slide 31

Slide 31 text

@ViscosityNA www.viscosityna.com Create a credential begin dbms_cloud.create_credential ( credential_name => 'SLACK_CREDENTIAL' , username => '' , password => ''); end; / begin dbms_cloud.create_credential ( credential_name => 'TEAMS_CREDENTIAL' , username => '' , password => ''); end; /

Slide 32

Slide 32 text

@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 => , principal_type => xs_acl.ptype_db)); end; /

Slide 33

Slide 33 text

@ViscosityNA www.viscosityna.com Send simple output begin dbms_cloud_notification.send_message ( provider => '' , credential_name => '' , message => '' , params => json_object('channel' value '')); end; /

Slide 34

Slide 34 text

@ViscosityNA www.viscosityna.com Create a query declare l_sql clob := q'[select ... from ... where ... ]'; ...

Slide 35

Slide 35 text

@ViscosityNA www.viscosityna.com Slack: Send query output begin dbms_cloud_notification.send_data ( provider => 'slack' , credential_name => '' , query => l_sql , params => json_object('channel' value '' , 'type' value 'csv')); end; /

Slide 36

Slide 36 text

@ViscosityNA www.viscosityna.com Teams: Send query output begin dbms_cloud_notification.send_data ( provider => 'msteams' , credential_name => '' , query => l_sql , params => json_object('tenant' value '' , 'team' value '' , 'channel' value '' , 'title' value '' , 'type' value 'csv')); end; /

Slide 37

Slide 37 text

@ViscosityNA www.viscosityna.com API Design

Slide 38

Slide 38 text

@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

Slide 39

Slide 39 text

@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!

Slide 40

Slide 40 text

@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

Slide 41

Slide 41 text

@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

Slide 42

Slide 42 text

@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

Slide 43

Slide 43 text

@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

Slide 44

Slide 44 text

@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 •

Slide 45

Slide 45 text

www.viscosityna.com @ViscosityNA Questions? Contact Me! [email protected] https://linktr.ee/oraclesean

Slide 46

Slide 46 text

No content