🔒 CAPÍTULO 17: SEGURANÇA DCL E EVOLUÇÃO DE SCHEMA

🎯 Objetivos de Aprendizagem

Ao final deste capítulo (estimativa: 2 horas de estudo autoguiado), você será capaz de:

• 🔹 Dominar a linguagem de controle de dados DCL: GRANT (conceder privilégios), REVOKE (revogar) e gerenciamento de Roles de usuários.
• 🔹 Aplicar o Princípio do Menor Privilégio (Least Privilege) separando usuários de aplicação (DML) de administradores (DDL/DBA).
• 🔹 Gerenciar a evolução de esquemas em produção sem indisponibilidade (Zero Downtime Migrations).
• 🔹 Configurar pipelines de migração contínua com Alembic integrados à esteira de CI/CD.

🏢 O Cenário Corporativo (Seu Desafio)

Na TecProExpress, novas políticas de governança exigem que desenvolvedores não tenham privilégios de exclusão de tabelas em produção. Seu desafio é implementar o Princípio do Menor Privilégio via DCL.

Em ambientes corporativos, nem todo usuário deve ter acesso a tudo. A Data Control Language (DCL) é a sub-linguagem SQL responsável por controlar quem pode fazer o quê no banco de dados.

Objetivo: Dominar os comandos GRANT e REVOKE para criar políticas de acesso granulares, compreendendo o conceito de Roles (Papéis) em MySQL 8.4 e PostgreSQL 17.

🧠 Fundamentos: Segurança DCL e Controle Granular de Privilégios O Modelo de Privilégios

🔹 Hierarquia de Acesso

flowchart TD
    DBA["👑 DBA\nTODOS os privilégios"] --> DEV["💻 Desenvolvedor\nSELECT + INSERT + UPDATE"]
    DBA --> REPORT["📊 Analista\nApenas SELECT"]
    DBA --> EST["🎓 Estagiário\nSELECT em Views"]

Tipos de Privilégios SQL

🔹 PASSO 2: Criando Usuários

🐬 MySQL 8.4

-- Criar usuário
CREATE USER 'analista'@'localhost' IDENTIFIED BY 'Senha@Forte123';

-- Criar usuário que pode acessar de qualquer host
CREATE USER 'dev'@'%' IDENTIFIED BY 'Dev@2026!';

🐘 PostgreSQL 17

-- Criar usuário (ROLE com LOGIN)
CREATE USER analista WITH PASSWORD 'Senha@Forte123';

-- Criar role sem login (grupo de permissões)
CREATE ROLE equipe_relatorios;

🔹 PASSO 3: Concedendo Privilégios (GRANT)

🐬 MySQL 8.4

-- SELECT em todas as tabelas do schema tecpro_express
GRANT SELECT ON tecpro_express.* TO 'analista'@'localhost';

-- SELECT + INSERT em uma tabela específica
GRANT SELECT, INSERT ON tecpro_express.pedido TO 'dev'@'%';

-- Todos os privilégios (apenas DBA!)
GRANT ALL PRIVILEGES ON tecpro_express.* TO 'dba_master'@'localhost';

-- Aplicar as alterações
FLUSH PRIVILEGES;

🐘 PostgreSQL 17

-- SELECT em todas as tabelas do schema
GRANT SELECT ON ALL TABLES IN SCHEMA tecpro_express TO analista;

-- SELECT + INSERT em tabela específica
GRANT SELECT, INSERT ON tecpro_express.pedido TO analista;

-- Acesso ao schema (obrigatório no PostgreSQL)
GRANT USAGE ON SCHEMA tecpro_express TO analista;

🔹 PASSO 4: Revogando Privilégios (REVOKE)

-- MySQL
REVOKE INSERT ON tecpro_express.pedido FROM 'dev'@'%';

-- PostgreSQL
REVOKE INSERT ON tecpro_express.pedido FROM analista;

🔹 PASSO 5: Roles (Papéis) — Agrupando Permissões

Em vez de conceder permissões usuário por usuário, criamos Roles (grupos de permissões):

🐘 PostgreSQL 17 (Suporte nativo a Roles)

-- 1. Criar Role com permissões
CREATE ROLE leitura_relatorios;
GRANT USAGE ON SCHEMA tecpro_express TO leitura_relatorios;
GRANT SELECT ON ALL TABLES IN SCHEMA tecpro_express TO leitura_relatorios;

-- 2. Atribuir a Role ao usuário
GRANT leitura_relatorios TO analista;
GRANT leitura_relatorios TO estagiario;

🐬 MySQL 8.4 (Roles a partir do MySQL 8.0)

-- 1. Criar Role
CREATE ROLE 'leitura_relatorios';
GRANT SELECT ON tecpro_express.* TO 'leitura_relatorios';

-- 2. Atribuir ao usuário
GRANT 'leitura_relatorios' TO 'analista'@'localhost';

-- 3. Ativar a role (obrigatório no MySQL)
SET DEFAULT ROLE 'leitura_relatorios' TO 'analista'@'localhost';

🔹 Modelo de Roles

flowchart TD
    R1["🔑 Role: admin_full"] --> U1["👑 DBA"]
    R2["👥 Role: leitura_relatorios"] --> U2["📊 Analista"]
    R2 --> U3["🎓 Estagiário"]
    R3["🛡️ Role: dev_crud"] --> U4["💻 Dev Backend"]

🔹 PASSO 6: Views + DCL = Segurança Completa

A combinação mais poderosa é usar Views (Cap. 16) com DCL:

-- 1. Criar View que oculta dados sensíveis
CREATE VIEW vw_funcionarios_publico AS
SELECT id, nome, data_admissao FROM funcionario;

-- 2. Dar acesso apenas à View, NUNCA à tabela real
GRANT SELECT ON vw_funcionarios_publico TO estagiario;

-- O estagiário pode:
SELECT * FROM vw_funcionarios_publico;  -- ? OK

-- O estagiário NÃO pode:
SELECT * FROM funcionario;  -- ? ACESSO NEGADO (salário protegido!)

🔹 Dica do Especialista: Em produção, NUNCA dê ALL PRIVILEGES a usuários de aplicação. Crie Roles específicas com o mínimo de permissões necessárias. Este é o Princípio do Menor Privilégio (Principle of Least Privilege).

🔄 EVOLUÇÃO E ARQUITETURAS MODERNAS

Mudar a estrutura de um banco de dados em produção é um desafio técnico chamado Schema Evolution.

Objetivo: Dominar os comandos de alteração estrutural (ALTER TABLE), compreender a manipulação de dados semiestruturados (JSON) e diferenciar as arquiteturas SQL e NoSQL.

🔹 PASSO 1: Evolução de Schema (ALTER TABLE)

Sistemas reais mudam. O comando ALTER TABLE permite que a tabela se adapte sem perda de dados.

🔹 Ciclo de Adaptação

flowchart LR
    V1["📦 Tabela V1"] --> |"ADD COLUMN"| V2["📦 Tabela V2"]
    V2 --> |"ALTER COLUMN"| V3["📦 Tabela V3"]
    V3 --> |"DROP COLUMN"| V1

🐬 MySQL 8.4 (MODIFY)

-- Alterando o tipo de uma coluna existente
ALTER TABLE CONTATO MODIFY COLUMN APELIDO VARCHAR(50);

🐘 PostgreSQL 17 (ALTER TYPE)

-- Alterando o tipo de uma coluna existente
ALTER TABLE CONTATO ALTER COLUMN APELIDO TYPE VARCHAR(50);

🔹 PASSO 2: JSON em Bancos Relacionais

Os SGBDs modernos (MySQL 8.4 e Postgres 17) suportam dados híbridos (NoSQL dentro do SQL).

🔹 PASSO 3: O Ecossistema NoSQL

Surgiu da necessidade de Escalabilidade Horizontal (adicionar mais servidores) em vez de apenas um servidor potente.

🔹 Tipos de NoSQL

✅ Verificação de Aprendizagem (Unidade IV)

1. No Postgres 17, qual operador extrai JSON como Texto Limpo? a) -> b) ->> c) JSON_EXTRACT

2. O que ocorre no comando DROP SCHEMA VENDAS CASCADE? a) Apaga o schema e tudo o que depende dele (Tabelas, FKs, Views). b) Dá erro se houver tabelas.

🔹 Perspectiva do Arquiteto: SQL é para consistência. NoSQL é para volume e velocidade. O engenheiro moderno sabe usar ambos de forma híbrida.

🔹 PASSO 4: Versionamento e Migrações de Schema com Alembic

Em ambientes corporativos de produção contínua (CI/CD), comandos imperativos como Base.metadata.create_all() ou scripts manuais executados no terminal apresentam riscos elevados de inconsistência entre ambientes. O comando create_all() apenas cria tabelas ausentes — ele não detecta novas colunas, não renomeia campos existentes e não ajusta constraints em tabelas já criadas.

A engenharia de dados moderna utiliza ferramentas de Schema Migration, sendo o Alembic o padrão oficial para o ecossistema SQLAlchemy:

flowchart LR
    Dev["👨‍💻 Desenvolvedor<br/>Altera Model em Python"] --> Mig["📝 alembic revision<br/>--autogenerate"]
    Mig --> Rev["📄 Script de Migração<br/>(upgrade / downgrade)"]
    Rev --> Exec["🚀 alembic upgrade head"]
    Exec --> DB[("🗄️ PostgreSQL / SQLite<br/>Schema Versionado")]

📄 Anatomia de um Script de Migração do Alembic

Cada evolução do banco de dados gera um arquivo em alembic/versions/ contendo duas funções obrigatórias e reversíveis:

"""adiciona coluna email em fornecedores

Revision ID: 7a8b9c0d1e2f
Revises: 1a2b3c4d5e6f
Create Date: 2026-09-10 10:00:00.000000
"""
from alembic import op
import sqlalchemy as sa

# Identificadores da cadeia de migração
revision = '7a8b9c0d1e2f'
down_revision = '1a2b3c4d5e6f'

def upgrade() -> None:
    """Aplica a evolução no schema sem perda de dados."""
    op.add_column('fornecedores_logistica', sa.Column('email', sa.String(100), nullable=True))

def downgrade() -> None:
    """Reverte a alteração de forma segura caso o deploy seja cancelado."""
    op.drop_column('fornecedores_logistica', 'email')

💻 Ponte Prática: Do SQL Manual ao SQLAlchemy 2.0 ORM

Como o SQLAlchemy 2.0 e a Engenharia de Software aplicam o Princípio do Menor Privilégio (DCL) através de conexões segregadas?

🔴 1. A Abordagem Manual (Superusuário Único na Aplicação)

No modelo amador, a aplicação inteira (inclusive rotas públicas de consulta) conecta como root ou postgres. Se houver uma falha de SQL Injection, o invasor tem poder de DROP DATABASE:

# ❌ FALHA GRAVE DE SEGURANÇA: Aplicação web usando superusuário para tudo
DATABASE_URL = "postgresql://postgres:root_password@localhost:5432/tecpro"

🟢 2. A Abordagem com SQLAlchemy 2.0 (Segregação de Privilégios Read-Only e Read-Write)

Com o SQLAlchemy 2.0, segregamos as fontes de conexão: relatórios e painéis de BI conectam através de um usuário restrito (analista_leitura com apenas GRANT SELECT), enquanto rotas transacionais usam credenciais DML:

# ✅ ABORDAGEM MODERNA COM SQLALCHEMY 2.0: Segregação de Motores (Read Replicas)
from sqlalchemy import create_engine, String, Integer, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class LogAuditoriaDclModel(Base):
    __tablename__ = "logs_auditoria_dcl"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    evento: Mapped[str] = mapped_column(String(100), nullable=False)
    usuario_origem: Mapped[str] = mapped_column(String(50), nullable=False)

    def __repr__(self) -> str:
        return f"AuditLog(id={self.id}, evento='{self.evento}', user='{self.usuario_origem}')"

🛠️ Mini-Projeto 17 (BD): Gestor de Conexões com Segregação DCL

Objetivo: Implementar um roteador de sessões que impede operações de escrita em sessões configuradas para leitura analítica.

📋 Pré-requisitos e Instalação

No terminal do seu ambiente virtual (PowerShell ou Bash), instale a biblioteca necessária:

pip install sqlalchemy

💻 Código Completo e Autocontido (miniprojeto_17_dcl.py)

Crie o arquivo miniprojeto_17_dcl.py e insira o código abaixo integralmente:

"""
Mini-Projeto 17: Gestor de Conexões com Segregação DCL
Curso: GTI - Banco de Dados Relacionais e Engenharia de Software
Stack: Python 3.11+ | SQLAlchemy 2.0 | SQLite
"""
import os
from sqlalchemy import create_engine, String, Integer, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

# 1. Definição Declarativa do Schema de Auditoria
class Base(DeclarativeBase):
    pass

class LogAuditoriaDclModel(Base):
    __tablename__ = "logs_auditoria_dcl"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    evento: Mapped[str] = mapped_column(String(100), nullable=False)
    usuario_origem: Mapped[str] = mapped_column(String(50), nullable=False)

    def __repr__(self) -> str:
        return f"AuditLog(id={self.id}, evento='{self.evento}', user='{self.usuario_origem}')"

# 2. Roteador de Permissões DCL (Read / Write Segregation)
class SecurityRoutingManager:
    def __init__(self, db_url: str) -> None:
        self.engine_write = create_engine(db_url, echo=False)
        self.engine_read = create_engine(db_url, echo=False)  # Em produção: aponta para Read Replica

    def gravar_log_transacional(self, evento: str, usuario: str) -> None:
        with Session(self.engine_write) as session:
            novo_log = LogAuditoriaDclModel(evento=evento, usuario_origem=usuario)
            session.add(novo_log)
            session.commit()
            print(f"✍️ [DCL-WRITE] Log registrado com sucesso pelo usuário '{usuario}'.")

    def consultar_logs_auditoria(self) -> list[LogAuditoriaDclModel]:
        with Session(self.engine_read) as session:
            print("👁️ [DCL-READ] Executando consulta somente-leitura (GRANT SELECT):")
            return list(session.scalars(select(LogAuditoriaDclModel)).all())

# 3. Ponto de Entrada Executável
if __name__ == "__main__":
    DB_FILE = "tecpro_dcl_demo.db"

    # Reset preventivo para garantir idempotência em testes repetidos
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    db_path = f"sqlite:///{DB_FILE}"
    manager = SecurityRoutingManager(db_path)
    Base.metadata.create_all(bind=manager.engine_write)

    print("🔐 GESTOR DE SEGURANÇA E PRIVILÉGIOS DCL - TECPROEXPRESS")
    print("=" * 65)

    # 1. Escrita Transacional (Permissão DML)
    manager.gravar_log_transacional("LOGIN_EFETUADO", "carlos.silva")
    manager.gravar_log_transacional("ALTERACAO_SENHA", "maria.souza")

    # 2. Leitura Analítica (Permissão DQL / Read-Only)
    logs = manager.consultar_logs_auditoria()
    for log in logs:
        print(f"   • {log}")

    print("=" * 65)

🚀 Como Executar

Execute o script no terminal:

python miniprojeto_17_dcl.py

🖥️ Saída Esperada no Console

🔐 GESTOR DE SEGURANÇA E PRIVILÉGIOS DCL - TECPROEXPRESS
=================================================================
✍️ [DCL-WRITE] Log registrado com sucesso pelo usuário 'carlos.silva'.
✍️ [DCL-WRITE] Log registrado com sucesso pelo usuário 'maria.souza'.
👁️ [DCL-READ] Executando consulta somente-leitura (GRANT SELECT):
   • AuditLog(id=1, evento='LOGIN_EFETUADO', user='carlos.silva')
   • AuditLog(id=2, evento='ALTERACAO_SENHA', user='maria.souza')
=================================================================

💡 Checkpoint de Lógica