Vistas, gatilhos e controlo de acessos

A loja precisa de consultar totais de encomendas, auditar mudanças de stock e limitar o acesso aos dados.

Uma vista guarda a definição de uma consulta. Um gatilho reage a uma alteração. Uma permissão autoriza uma operação. Cada mecanismo tem regras próprias, que variam entre motores.

Vistas: perguntas com nome

Uma vista virtual guarda a definição de uma consulta, não uma cópia permanente dos seus resultados. Podemos consultá-la como uma tabela:

CREATE VIEW TotalEncomenda AS
SELECT idEncomenda, SUM(qtd * precoUnitario) AS total
FROM Item GROUP BY idEncomenda;
SELECT * FROM TotalEncomenda ORDER BY idEncomenda;
UPDATE Item SET qtd = 3 WHERE idEncomenda = 100 AND idProduto = 10;
SELECT * FROM TotalEncomenda ORDER BY idEncomenda;

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

Não fizemos um UPDATE da vista: alterámos Item, e a consulta da vista passou a calcular outro resultado, porque uma vista virtual guarda a pergunta, não uma cópia dos resultados.

Uma vista pode simplificar consultas e apresentar apenas certas colunas.

Uma vista materializada guarda resultados para reutilização e precisa de uma política de atualização. PostgreSQL oferece vistas materializadas. SQLite não tem o mesmo comando nativo. Guardar o resultado numa tabela comum não o mantém automaticamente sincronizado.

Atualizar uma vista

A atualização pode ser ambígua. Se uma vista mostra um total de 100 euros, escrever 120 não diz qual das linhas de compra devemos mudar. Mesmo numa projeção simples, uma inserção pode deixar sem valor colunas obrigatórias da tabela base.

SQLite não permite modificar vistas diretamente. Um gatilho INSTEAD OF pode definir a tradução. PostgreSQL consegue atualizar automaticamente certas vistas simples; vistas com agregações e outras construções exigem outro tratamento. Não transfiras essa regra entre motores.

CREATE VIEW NomeCliente AS SELECT id, nome FROM Cliente;
CREATE TRIGGER AtualizarNome
INSTEAD OF UPDATE OF nome ON NomeCliente
FOR EACH ROW
BEGIN
UPDATE Cliente SET nome = NEW.nome WHERE id = OLD.id;
END;
UPDATE NomeCliente SET nome = 'Ana Silva' WHERE id = 1;
SELECT id, nome FROM Cliente ORDER BY id;

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

Cliente antes e depois do UPDATE sobre a vista:

A linha 1 da tabela base passa a Ana Silva, porque OLD.id identifica a linha anterior e NEW.nome dá o valor pedido. Este gatilho só define a atualização do nome.

Quando uma vista tem um filtro, uma atualização pode fazer a linha desaparecer da própria vista. Em PostgreSQL, WITH CHECK OPTION pode exigir que os dados continuem a satisfazer o filtro numa vista atualizável. Num gatilho SQLite, escreve a validação correspondente e rejeita pedidos inválidos em vez de assumir que o motor a deduz.

Gatilhos: evento, condição e ação

Um gatilho, ou trigger, segue uma regra evento-condição-ação. O evento é INSERT, UPDATE ou DELETE; a condição WHEN é opcional; a ação executa-se quando a condição é verdadeira.

Em SQLite, os gatilhos são por linha, mesmo que omitas FOR EACH ROW. Uma instrução que altera dez linhas pode dispará-los dez vezes.

BEFORE executa antes da alteração da linha. AFTER executa depois. INSTEAD OF substitui a operação sobre uma vista. Outros motores também oferecem gatilhos por instrução e conjuntos de linhas de transição.

UPDATE OF stock reage a instruções que incluem stock nas colunas atualizadas; não garante que o valor mudou. Acrescenta uma condição de diferença se isso for necessário.

Um registo de alterações

Queremos registar cada mudança efetiva do stock. O valor inicial do teclado é 20.

CREATE TABLE AlteracaoStock(
idProduto INTEGER, anterior INTEGER, novo INTEGER
);
CREATE TRIGGER AuditarStock
AFTER UPDATE OF stock ON Produto
FOR EACH ROW WHEN OLD.stock <> NEW.stock
BEGIN
INSERT INTO AlteracaoStock VALUES (NEW.id, OLD.stock, NEW.stock);
END;
UPDATE Produto SET stock = stock - 2 WHERE id = 10;
UPDATE Produto SET stock = stock WHERE id = 10;
SELECT * FROM AlteracaoStock;

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

AlteracaoStock começa vazia e termina com uma linha com produto 10, stock anterior 20 e stock novo 18: a primeira instrução escreve-a, a segunda dispara o evento mas a condição é falsa e não acrescenta outra. O CHECK (stock >= 0) continua a ser a forma declarativa de proibir stock negativo.

Usa restrições antes de gatilhos quando exprimem a mesma regra. Um UNIQUE é mais claro para unicidade e uma estrangeira com cascata é mais clara para integridade referencial do que reimplementar ambos em código de gatilho.

Rejeitar uma alteração

Em SQLite, RAISE(ABORT, 'mensagem') num gatilho rejeita a instrução e desfaz as suas alterações, sem necessariamente abortar a transação exterior inteira. RAISE(ROLLBACK, ...) também termina a transação ativa; FAIL tem diferenças quanto a alterações anteriores da mesma instrução; IGNORE ignora trabalho sem dar esse erro. Escolhe o efeito que a regra exige.

Um limite de no máximo cinco encomendas por cliente, por exemplo, requer verificar inserções e mudanças de cliente em encomendas existentes. Um gatilho só de inserção deixa uma segunda via para violar a regra. Para mínimos, pensa também nas eliminações. O mecanismo não substitui a análise de todas as operações.

A ação de um gatilho pode ativar outros gatilhos. Evita depender da ordem de gatilhos que o motor não garante e considera ciclos. Se alterares a própria linha num gatilho BEFORE, o resultado pode ter comportamento indefinido em SQLite; usa restrições, rejeição ou um desenho AFTER bem definido.

Validar compatibilidade e reservar stock

Uma oficina tem peças que só servem certos modelos. Ao registar duas unidades de uma peça numa reparação, queremos confirmar a compatibilidade e o stock antes de reduzir a quantidade disponível. Uma estrangeira para Peca e outra para Modelo garantem que ambos existem, mas não que o par é compatível.

O exemplo reduz a reparação a Uso(id,peca,modelo,qtd). A peça 1 tem stock 5 e só é compatível com o modelo 10. Quantidades e stocks são inteiros e quantidade tem de ser positiva. Esta operação ensina a inserção, com dois gatilhos:

CREATE TRIGGER ValidarUso
BEFORE INSERT ON Uso
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'Peca incompativel')
  WHERE NOT EXISTS (
    SELECT 1 FROM Compativel
    WHERE peca = NEW.peca AND modelo = NEW.modelo
  );
  SELECT RAISE(ABORT, 'Stock insuficiente')
  WHERE NEW.qtd > (SELECT stock FROM Peca WHERE id = NEW.peca);
END;
CREATE TRIGGER DebitarUso
AFTER INSERT ON Uso
FOR EACH ROW
BEGIN
  UPDATE Peca SET stock = stock - NEW.qtd WHERE id = NEW.peca;
END;
INSERT INTO Uso VALUES (100,1,10,2);
SELECT id, stock FROM Peca ORDER BY id;
SELECT * FROM Uso ORDER BY id;

PRAGMA foreign_keys = ON;
CREATE TABLE Peca(id INTEGER PRIMARY KEY, stock INTEGER NOT NULL CHECK(stock >= 0));
CREATE TABLE Modelo(id INTEGER PRIMARY KEY);
CREATE TABLE Compativel(peca INTEGER NOT NULL REFERENCES Peca(id),
  modelo INTEGER NOT NULL REFERENCES Modelo(id), PRIMARY KEY(peca,modelo));
CREATE TABLE Uso(id INTEGER PRIMARY KEY, peca INTEGER NOT NULL REFERENCES Peca(id),
  modelo INTEGER NOT NULL REFERENCES Modelo(id), qtd INTEGER NOT NULL CHECK(qtd > 0));
INSERT INTO Peca VALUES (1,5), (2,8);
INSERT INTO Modelo VALUES (10), (20);
INSERT INTO Compativel VALUES (1,10), (2,20);

ValidarUso corre antes da inserção. A primeira procura testa o par completo, não apenas a existência da peça. A segunda compara a quantidade pedida com o stock disponível. Se alguma falhar, RAISE(ABORT,...) rejeita a instrução. DebitarUso só atua depois de a linha ser inserida. O uso 100 fica guardado e o stock da peça 1 passa de 5 para 3; a peça 2 mantém 8.

Se uma inserção de várias linhas aceitar uma primeira linha e falhar na seguinte, ABORT desfaz a instrução inteira, incluindo os débitos dos gatilhos já executados nessa instrução. Não conserva a primeira linha desse lote. Inserções anteriores feitas noutras instruções da transação continuam sujeitas à distinção de rollback da instrução e da transação.

Alterações e eliminações

Estes dois gatilhos implementam só a inserção. Permitir UPDATE ou DELETE em Uso exige definir também os seguintes efeitos:

Com stock 3 depois do uso de duas unidades, mudar esse uso para quatro unidades precisa apenas de duas unidades adicionais. Comparar quatro diretamente com stock 3 recusaria uma alteração possível. Se a peça mudar, as unidades da peça antiga não aumentam o stock disponível da nova.

A política de eliminação depende do significado do registo. Apagar um histórico de peças já consumidas não devolve peças fisicamente ao armazém; cancelar uma reserva ainda não consumida pode devolvê-las. Decide esse significado antes de escrever o gatilho. Alterar a relação Compativel também pode invalidar usos existentes e precisa de uma política própria.

Autenticação e autorização

Autenticação confirma a identidade de quem se liga. Autorização decide o que essa identidade pode fazer. Conhecer a palavra-passe de uma conta não deveria dar automaticamente o direito de apagar tabelas.

SQLite é um motor embebido, normalmente sobre um ficheiro. Não implementa utilizadores SQL, GRANT, REVOKE nem políticas RLS como PostgreSQL. A aplicação e as permissões do sistema operativo controlam o acesso. Uma vista SQLite não protege colunas contra quem consegue abrir livremente o ficheiro da base de dados.

Os exemplos seguintes usam PostgreSQL, que permite criar papéis e políticas de acesso.

Privilégios e papéis em PostgreSQL

Um papel, ou role, reúne privilégios e pode ter capacidade de login. Uma aplicação de relatórios precisa de ler, não de alterar o esquema:

CREATE ROLE consulta;
GRANT SELECT ON Produto, Encomenda TO consulta;
GRANT consulta TO analista;
REVOKE SELECT ON Encomenda FROM consulta;

Este excerto pressupõe as tabelas e o papel analista já criados, e um executor com autoridade para conceder direitos. Depois da revogação, consulta mantém leitura de Produto mas perde a concessão de leitura de Encomenda. Analista ainda pode ter essa permissão por outro papel ou concessão direta.

WITH GRANT OPTION permite transmitir um privilégio de objeto. REVOKE ... CASCADE pode retirar concessões dependentes; RESTRICT impede a revogação quando existem dependências relevantes. A propriedade de um objeto e o direito de o alterar ou eliminar não são apenas um GRANT SELECT mais amplo.

O menor privilégio atribui apenas as operações necessárias. Permissões sobre colunas podem permitir ler nome sem ler email, desde que não exista uma concessão mais ampla que já dê acesso à tabela inteira.

Segurança por linha

Em PostgreSQL, RLS restringe as linhas visíveis ou modificáveis, além dos privilégios de objeto. Para uma tabela Documento(id, dono, texto) em que dono coincide com o nome do utilizador SQL:

ALTER TABLE Documento ENABLE ROW LEVEL SECURITY;
CREATE POLICY documentos_proprios ON Documento
USING (dono = current_user)
WITH CHECK (dono = current_user);
GRANT SELECT ON Documento TO ana;
SET ROLE ana;
SELECT id, dono, texto FROM Documento ORDER BY id;
RESET ROLE;

CREATE ROLE ana;
CREATE TABLE Documento (id INTEGER PRIMARY KEY, dono TEXT, texto TEXT);
INSERT INTO Documento VALUES (1, 'ana', 'Apontamentos de Ana'), (2, 'rui', 'Apontamentos de Rui');

A tabela tem um documento de Ana e outro de Rui. SET ROLE ana faz a consulta com os direitos de Ana, por isso só aparece o seu documento. Executar a consulta como proprietário não provaria o filtro.

USING filtra linhas existentes e WITH CHECK valida os valores novos. Esta política não concede SELECT ou UPDATE; os privilégios continuam necessários. Sem uma política aplicável, ativar RLS faz negar o acesso às linhas para utilizadores sujeitos às políticas.

Superutilizadores e papéis com BYPASSRLS contornam RLS. O proprietário normalmente também a contorna, a menos que imponha FORCE ROW LEVEL SECURITY. Uma aplicação que usa uma conta única para todos os clientes precisa de uma estratégia segura de identidade; current_user distingue contas SQL, não utilizadores da aplicação por magia.

Injeção SQL e parâmetros

Construir uma consulta juntando texto do utilizador ao código permite que esse texto altere a estrutura da instrução. Uma entrada como ' OR 1=1 -- pode transformar um filtro de login numa condição sempre verdadeira.

Usa uma consulta parametrizada e entrega os valores separadamente ao driver, por exemplo WHERE email = ? em SQLite. O carácter ? é um parâmetro, não uma string para substituir manualmente. Escapar ou validar a entrada não substitui a separação entre dados e código.

Nomes de colunas e palavras como ASC normalmente não são valores parametrizáveis. Para escolher uma coluna de ordenação, usa uma lista fechada de opções conhecidas. Combina parâmetros com privilégios reduzidos; cada medida resolve uma parte diferente do problema.