Bancos de Dados Relacionais (SQL) na AWS com Amazon RDS.

Overview

Bancos de Dados Relacionais (SQL) na AWS com Amazon RDS

     Explorando o Amazon RDS, um serviço de provisionamente e gerenciamento de banco de dados relacional na AWS.

Serviços utilizados
     Amazon RDS;
     AWS Lambda; e
     MySQL Workbench.

O que é um banco de dados relacional?
     Um banco de dados relacional é um mecanismo de armanezamento que permite a persistência de dados e opcionalmente implementar funcionalidades, armazenando os dados em tabelas, que possuem relacionamentos entre si.

Tabelas de um banco de dados relacional
     São organizadas em colunas, onde cada coluna armazena um tipo de dados;
     Os dados são armazenados em linhas da tabela;
     Possuem uma chave primária (PK) que identificam de forma única cada registro de uma linha;
     Possuem chaves estrangeiras (FK) que estabelecem o relacionamento entre tabelas; e
     Utilizam índices para pesquisa rápida de dados.

Relacionamento entre tabelas em um banco de dados relacional
     1:1 duas tabelas se relacionam de forma direta onde a chave primária de uma tabela é utilizada uma única vez em outra.
     1:n duas tabelas se relacionam de forma direta onde a chave primária de uma tabela é utilizada várias vezes em outra.
     n:n acontece de forma indireta entre duas tabelas, gerando de uma terceira tabela. Na prática ocorrem dois ou mais relacionamentos um para vários.

Sobre o Amazon RDS
     O Amazon Relational Database Service (Amazon RDS) facilita a configuração, a operação e a escalabilidade de bancos de dados relacionais na nuvem. Fornece capacidade econômica e redimensionável e automiza o provisionamento de hardware, configuração de bancos de dados, aplicação de patches e backups.

Mecanismos de banco de dados na Amazon RDS: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle e SQL Server.

Preços do Amazon RDS
     Preços baseados na dimensão de instâncias EC2
     Free tier: 750 horas de uso de instâncias db.t2.micro Single-AZ do Amazon RDS, 20 GB de armazenamento de banco de dados de SSD e 20 GB de armanezamento de backup.

Documentação da AWS sobre o Amazon RDS

Atividade prática
     Criar uma instância RDS;
     Acessar com o MySQ Workbench e criar tabelas e inserir dados;
     Criar uma função Lambda para consultar dados em tabelas do RDS; e
     Código do instrutor

Arquitetura de Tabelas

Arquitetura de Sistemas

Criando queries

  • Criar um database:
CREATE DATABASE PERMISSIONS_DB;
  • Acessar o db criado:
USE PERMISSIONS_DB;
  • Criar uma tabela de usuários:
CREATE TABLE user (
  id bigint(20) NOT NULL, 
  email varchar(40) NOT NULL,
  username varchar(15) NOT NULL,
  password varchar(100) NOT NULL,
  PRIMARY KEY (id)
);
  • Criar uma tabela de carrinho de compras:
CREATE TABLE role (
  id bigint(20) NOT NULL,
  name varchar(60) NOT NULL, 
  PRIMARY KEY (id)
);
  • Criar uma tabela user roles:
CREATE TABLE user_roles (
  user_id bigint(20) NOT NULL,
  role_id bigint(20) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE RESTRICT ON UPDATE CASCADE,
  PRIMARY KEY (user_id, role_id)
);
  • Descrição da tabela:
DESC user
  • Inserindo dados em tabelas:
INSERT INTO user VALUES (1, '[email protected]', 'Lucas', 'strongpasswd');
INSERT INTO user VALUES (2, '[email protected]', 'Igla', 'strongpasswd');

INSERT INTO role VALUES (3, 'ADMIN');
INSERT INTO role VALUES (4, 'USER');

INSERT INTO user_roles VALUES (1, 3);
INSERT INTO user_roles VALUES (1, 4);
INSERT INTO user_roles VALUES (2, 4);
  • Verificando:
SELECT * FROM user_roles;
  • Selecionando dados da tabela associativa:
SELECT user.id, user.email, user.username, role.id AS role_id, role.name AS role_name
FROM user 
JOIN user_roles on (user.id=user_roles.user_id)
JOIN role on (role.id=user_roles.role_id);

Configurando permissões de acesso ao RDS
     Selecionar a função criada -> Configuration -> Permissions -> Selecionar a função criada e abrir no console do AWS IAM; e
     Attach policies -> Pesquisar pela policy AWSLambdaVPCAccessExecutionRole -> Attach policy.

import json
import pymysql

endpoint = 'endpoint-name'
username = 'user-name'
password = 'user-password'
database_name = 'db-name'

connection = pymysql.connect(host=endpoint, user=username, password=password, db=database_name)

def lambda_handler(event, context):
    
    cursor = connection.cursor()
    
    cursor.execute('SELECT user.id, user.email, user.username, role.id AS role_id, role.name AS role_name FROM user JOIN user_roles on (user.id=user_roles.user_id)JOIN role on (role.id=user_roles.role_id)')
    
    rows = cursor.fetchall()
    
    return {
        'statusCode': 200,
        'body': json.dumps(rows)
    }

Comando pip - Solução

pip install pymysql -t .
explorer .
Owner
Lucas Magalhães
“Talk is cheap. Show me the code.”
Lucas Magalhães
Script que realiza a identificação de todos os logins e senhas dos wifis conectados em uma máquina e envia os dados para um e-mail especificado.

getWIFIConnection Script que realiza a identificação de todos os logins e senhas dos wifis conectados em uma máquina e envia os dados para um e-mail e

Vinícius Azevedo 3 Nov 27, 2022
A Python library for loading data from a SpaceX Starlink satellite.

Starlink Python A Python library for loading data from a SpaceX Starlink satellite. The goal is to be a simple interface for Starlink. It builds upon

Austin 2 Jan 16, 2022
🎄 JustaGrabber - A discord token grabber written in python3

🎄 JustaGrabber - A discord token grabber written in python3 🎇 Made by kldiscord https://github.com/kldiscord 🌟 Please leave a star if you liked Jus

1 Dec 19, 2022
Python implementation for PetitPotam

PetitPotam Coerce NTLM authentication from Windows hosts Installtion $ pip3 install impacket Usage usage: petitpotam.py [-h] [-debug] [-port [destinat

Oliver Lyak 137 Dec 28, 2022
This very basic script can be used to automate COVID-19 vaccination slot booking on India's Co-WIN Platform.

COVID-19 Vaccination Slot Booking Script This very basic CLI based script can be used to automate covid vaccination slot booking on Co-WIN Platform. I

605 Dec 14, 2022
This project is based on discord.py and is meant to be a 'Quick Start Bot' to cut down on the time it takes to write complex discord bots.

This project is based on discord.py and is meant to be a 'Quick Start Bot' to cut down on the time it takes to write complex discord bots.

Alec Ibarra 1 Mar 03, 2022
Date Time Userbot With Python

DATE_TIME_USERBOT An Telegram Bot By @Pythone_3 Config Vars API_ID : Telegram API_ID, get it from my.telegram.org/apps API_HASH : Telegram API_ID, get

Sinzz-sinan-m 2 Oct 20, 2021
Library written in Python that wraps Halo Infinite API.

haloinfinite Library written in Python that wraps Halo Infinite API. Before start It's unofficial, reverse-engineered, neither stable nor production r

Miguel Ferrer 4 Dec 28, 2022
Free and Open Source Group Voice chat music player for telegram ❤️ with button support youtube playback support

Free and Open Source Group Voice chat music player for telegram ❤️ with button support youtube playback support

Sehath Perera 1 Jan 08, 2022
Deploy your apps on any Cloud provider in just a few seconds

The simplest way to deploy your apps in the Cloud Deploy your apps on any Cloud providers in just a few seconds ⚡ Qovery Engine is an open-source abst

Qovery 1.9k Dec 26, 2022
A fun hangman style game to guess random movie names with a short summary about the movie.

hang-movie-man Hangman but for movies 😉 This is a fun hangman style game to guess random movie names from the local database and show some summary ab

Ankit Josh 10 Sep 07, 2022
A Discord bot for osu!

This is the mostly-complete repo for the owo Discord osu! bot which you can invite here. As you look through this repo, please keep in mind that all o

Stevy 43 Dec 28, 2022
Opasium AI was specifically designed for the Opasium Games discord only. It is a bot that covers the basic functions of any other bot.

OpasiumAI Opasium AI was specifically designed for the Opasium Games discord only. It is a bot that covers the basic functions of any other bot. Insta

Dan 3 Oct 15, 2021
Deleting someone else's Instagram account, repeat until the target account is blocked.

Program Features 📌 Instagram report V4. 📌 Coded with the latest version of Python. 📌 Has automatic scheduling. 📌 Full account report. 📌 Report a

hack4lx 16 Oct 25, 2022
This bot plays the most recent video from the Daily Silksong News Youtube Channel whenever a specific user enters voice chat once a day.

Do you have that one friend that really likes Hollow Knight. Are they waiting for Silksong to come out? Heckle them with this Discord bot.

Tommy Rousey 2 Feb 09, 2022
Streaming Finance Data with AWS Lambda

A data pipeline consisting of an AWS lambda function reading data from yfinance API, an AWS Kinesis stream to receive & store data in S3 buckets and AWS Glue crawler & Athena to run SQL queries.

Aarif Munwar Jahan 4 Aug 30, 2022
A mass account list editor for python

Account-List-Editor This is an mass account list editor Usage Run the editor.py file with python (python3 ./editor.py) Press a button (1/2) and drag &

ExtremeDev 1 Dec 20, 2021
pymobiledevice fork with more recent coding standards and many more features

Description Features Installation Usage Sending your own messages Lockdown messages Instruments messages Example Lockdown services com.apple.instrumen

255 Dec 28, 2022
Integrating Amazon API Gateway private endpoints with on-premises networks

Integrating Amazon API Gateway private endpoints with on-premises networks Read the blog about this application: Integrating Amazon API Gateway privat

AWS Samples 12 Sep 09, 2022
A multi exploit instagram exploitation framework

Instagram Exploitation Framework About IEF Is an open source Instagram Exploitation Framework with various Exploits that could be used to mod your pro

Instagram Exploitation Framework - BirdSecurity 1 May 23, 2022