Visões Constraints Triggers e Stored Procedures

Prévia do material em texto

1 
Cliquez pour modifier le style du titre Banco de Dados 
Visões, Constraints 
Triggers, Stored Procedures 
Profa. Vaninha Vieira 
(vaninha@dcc.ufba.br) 
Salvador, Agosto/2013 
Universidade Federal da Bahia 
Instituto de Matemática 
Departamento de Ciência da Computação 
MATA60 – Banco de Dados 
2 
Capítulo 5 
Créditos: parte dos slides utilizados foram 
adaptados de cursos disponibilizados na Web por: 
Profa Jennifer Widow, Stanford 
Profa Vania Bogorny, UFSC 
Cliquez pour modifier le style du titre 
Visões em BD 
4 
O que são visões (ou VIEW) 
§  Tabela única derivada de outras tabelas 
§  Considerada uma tabela virtual 
§  É uma relação que não armazena dados, composta 
dinamicamente por uma consulta que é previamente 
analisada e otimizada 
5 
São úteis pra que? 
§  Três objetivos principais das visões: 
•  Simplificar consultas 
•  Autorização de acesso (segurança) 
•  Manter dados independentes da estrutura do BD 
A utilização de visões permite simplificar e 
personalizar tabelas no seu banco de dados. 
6 
Comandos SQL 
§  Criar Visão CREATE VIEW nome_da_visão [(a1, a2, …)] AS 
 SELECT atributo1, atributo2, ... 
 FROM nome_da_tabela1, nome_da_tabela2, ... 
 WHERE condição; 
2 
7 
Exemplo 1: Visão com uma relação 
§  Empregados do Alto Escalão (salário > 10000) 
•  Trazer codigo, nome e salário 
 
Create view vAltoEscalao (cod, nome, salario) as 
 select e.codEmp, e.nome, e.sal 
 from empregado e 
 where e.sal>10000 
Projeto (codProj, tipo, descricao)‏!
ProjetoEmpregado (codProj, codEmp, dtInicial, dtFinal)!
Empregado (codEmp, nome, categoria, sal)!
8 
Exemplo 2: Visão com várias relações 
 
§ Projetos de Empregados do Alto Escalão (salário > 10000) 
•  Trazer código, nome do empregado, salário e nome do projeto 
 
Create view vProjetoAltoEscalao as 
 select e.codEmp, e.nome, e.sal, p.descricao 
 from empregado e, projeto p, projetoEmpregado pe 
 where e.sal>10000 AND e.codEmp=pe.codEmp AND 
 pe.codProj=p.codProj 
Projeto (codProj, tipo, descricao)‏!
ProjetoEmpregado (codProj, codEmp, dtInicial, dtFinal)!
Empregado (codEmp, nome, categoria, sal)!
9 
Exemplo 2: Visão sobre visão 
 
§ Projetos de Empregados do Alto Escalão (salário > 10000) 
•  Trazer código, nome do empregado, salário e nome do projeto 
 
Create view vProjetoAltoEscalao as 
 select v1.cod, v1.nome, v1.salario, p.descricao 
 from vAltoEscalao v1, projeto p, projetoEmpregado pe 
 where v1.cod=pe.codEmp AND pe.codProj=p.codProj 
Projeto (codProj, tipo, descricao)‏!
ProjetoEmpregado (codProj, codEmp, dtInicial, dtFinal)!
Empregado (codEmp, nome, categoria, salario)!
vAltoEscalao(cod, nome, salario)!
10 
Exemplo 3: Visão com consultas aninhadas 
§  Sistema de controle de vendas: obter listagem dos produtos 
vendidos e suas respectivas quantidades 
•  Produto (codigo, nome, marca, preço) 
•  Cliente (codigo, nome, CPF, sexo, dtNas) 
•  Venda (codVenda, codProduto, codCliente, qtde, valorTotal). 
CREATE VIEW produtos_vendidos AS 
SELECT P.nome, (SELECT SUM(V.qtde) 
 FROM Venda V 
 WHERE P.codigo = V.codProduto) 
 AS vendidos 
 FROM Produto P; 
11 
Consultas em Visões 
§  Consultas SQL podem ser especificadas sobre a 
visão 
select nome 
 from vProjetosAltoEscalao 
Where descricao = “Projeto A” 
 
 
12 
Algumas Características das Visões 
§  Comportamento similar a uma tabela real 
§  O SGBD armazena a definição da visão 
•  nome da visão + comando SELECT 
§  A visão é instanciada quando uma consulta sobre ela é 
executada 
•  Oculta detalhes técnicos de obtenção dos dados 
§  Uma visão está sempre atualizada 
•  Ao modificar tuplas nas tabelas envolvidas na visão, a 
visão reflete automaticamente as alterações 
Toda visão pode ser consultada 
mas nem toda visão pode ser atualizada 
3 
13 
Remoção de Visões 
§  Quando uma visão não é mais necessária 
podemos eliminá-la, usando o comando drop view 
DROP VIEW nomeDaVisao 
 
 
14 
Atualização de visões 
§  Estratégia de materialização de view 
§  Criar fisicamente uma tabela de view temporária quando 
a view for consultada pela primeira vez 
§  Manter essa tabela na suposição de que outras 
consultas à view acontecerão em seguida 
§  Requer estratégia eficiente para atualizar a tabela da 
view quando as tabelas de base forem atualizadas 
§  Estratégia de atualização incremental 
•  SGBD determina que novas tuplas devem ser inseridas, 
excluídas ou modificadas em uma view materializada 
15 
Atualização de Visões 
§  Cláusula WITH CHECK OPTION 
§  Precisa ser acrescentada ao final da definição de view 
se uma view tiver de ser atualizada 
§  Atualização em uma view definida sobre uma única tabela 
sem quaisquer funções de agregação 
§  Pode ser mapeada para uma atualização sobre a 
tabela da base 
§  View que envolve junções (joins) 
§  Não é possível que o SGBD determine qual das 
atualizações é intencionada 
16 
O comando DROP 
§  Comando DROP 
§  Usado para remover elementos nomeados do esquema, como 
tabelas, visões, domínios ou restrições 
§  Opções de comportamento de drop: 
§  CASCADE: remove automaticamente objetos que dependem do 
elemento removido (ex. views e tabelas) 
§  RESTRICT: não remove o elemento caso existam outros objetos 
que dependam dele 
§  Exemplo: 
§  DROP SCHEMA EMPRESA CASCADE; 
17 
O comando ALTER 
§  Ações de alteração de tabela incluem: 
§  Acrescentar ou remover uma coluna (atributo) 
§  Alterar uma definição de coluna 
§  Acrescentar ou remover restrições de tabela 
§  Exemplo: 
§  ALTER TABLE EMPRESA.FUNCIONARIO 
ADD COLUMN Tarefa VARCHAR (12); 
§  Para remover uma coluna 
§  Escolher CASCADE ou RESTRICT 
18 
O comando ALTER (cont.) 
§  Alterar as restrições especificadas sobre uma tabela 
§  Acrescentar ou remover uma restrição nomeada 
4 
Cliquez pour modifier le style du titre 
Constraints 
20 
Constraints e Triggers 
§  Contexto de SGBDs relacionais 
§  SQL oferece padrões para constraints e triggers 
§  Mas os SGBDs implementam de maneira bem variada 
§  Sintaxe, expressividade, limitações 
§  Constraints (ou restrições de integridade) 
§  Limitam os estados permitidos para o banco de dados 
§  Triggers (Gatilho) 
§  Monitoram mudanças no banco de dados 
§  Quando um evento ocorre, o trigger é executado (disparado) 
§  Ao ser executado verificam condições e 
§  Se necessário, iniciam ações 
Estático 
Dinâmico 
21 
Restrições de Integridade 
§  Objetivo 
•  Impor restrições nos dados permitidos, além do que foi definido na 
estrutura e tipos do banco 
•  Implementar restrições mais relacionadas à semântica do negócio 
à regras de negócio 
§  Exemplos (Sistema Acadêmico – Matrícula) 
•  Artigo 11 - A inscrição semestral em componentes curriculares será 
efetivada atendendo aos limites mínimo de seis (6) horas semanais 
e máximo de trinta e seis (36) horas semanais, ao(s) pré-requisito(s) 
e/ou ao(s) co-requisito(s) dos componentes curriculares, não sendo 
permitida a superposição parcial ou total de horários entre os 
componentes selecionados. 
22 
Porque usar constraints? 
•  Prevenir erros na entrada de dados (INSERT) 
•  A entrada de um dado incorreto, que viole uma regra de negócio 
definida, pode ser detectada automaticamente pelo SGBD 
•  Garantir corretude em atualizações (UPDATE) 
•  Garantir a consistência do banco de dados 
•  Em caso de cópias dos dados em lugares diferentes 
•  Explicar ao SGBD o que se espera dos dados 
•  Apoiar armazenamento eficiente e processamento de consultas 
•  Exemplo 
§  Valores de um atributo são únicos 
§  Um atributo não admite valor desconhecido 
23 
Classificação das Restrições de Integridade 
§  Restrição de vazio (Non Null) 
§  Restrições de chave (primária, única) 
§  Integridade referencial (chave estrangeira) 
§  Check Constraints 
§  Restrições de valores de atributos 
§  Restrições de tuplas (valoresde atributos de uma tupla) 
§  Assertions 
§  Asserções gerais (entre tabelas de um BD) 
Nem todo SGBD suporta a implementação 
de todos os tipos de constraints, em 
particular Check Constraints e Assertions 
24 
Declaração e Aplicação das Restrições 
§  Declaração (realizada pelo projetista/programador) 
•  Ao definir o esquema do BD 
§  Constraints são verificadas ao criar/carregar o BD 
•  Posteriormente, com o BD em operação/uso 
§  Constraints são verificadas sobre o estado atual do BD 
§  Aplicação (realizada pelo SGBD) 
•  Verifica após cada modificação no estado do BD 
§  Modificações críticas/perigosas 
•  Verifica ao final de uma transação 
§  Modificações isoladas podem causar inconsistência, que podem 
ser corrigidas em outras operações da transação 
5 
25 
Demo do Uso de Restrições 
§  Material do curso de Jennifer Widow 
•  Stanford, Introduction to Databases 
 
§  Esquema 
Cliquez pour modifier le style du titre 
Triggers 
27 
Triggers 
§  Regras do tipo “Evento-Condição-Ação” 
•  Quando um Evento ocorre, verifique a condição 
•  Se for verdadeiro, dispare a ação 
§  Exemplos 
•  Indicar uma turma diferente, apropriada, em uma matrícula, em caso 
de choque de horário 
•  Criar um empréstimo se o saldo ficar negativo. O valor do 
empréstimo será o valor do saldo negativo. 
•  Realizar auditorias sobre operações x usuários 
28 
Porque usar Triggers? 
§  Mover lógica das aplicações para o SGBD 
§  Aumenta modularidade 
§  Garante que o monitoramento ocorrerá automaticamente 
para qualquer aplicação que rode sobre o BD 
§  Aplicar/Garantir constraints 
§  Expressividade permitida em triggers é maior que o 
sistema de constraints de qualquer SGBD 
§  Permite corrigir erro que violou a constraint 
29 
Sintaxe Básica SQL para Triggers 
CREATE Trigger nome 
Before | After | Instead Of eventos 
[ referencing-variables ] 
[ For Each Row ] 
When (condição) 
Ação 
30 
6 
31 
CREATE TRIGGER nome_trigger 
{ BEFORE | AFTER } {DELETE | INSERT | UPDATE} 
ON Tabela 
FOR EACH ROW BEGIN 
 corpo da trigger 
END; 
 
32 
Variáveis 
§  Declaração de variáveis - Sintaxe: 
DECLARE VARIABLE nome_variável tipo_dados; Exemplo: 
DECLARE VARIABLE total float; 
DECLARE VARIABLE nome_aluno varchar(50); 
§  NEW.column indica o novo valor que está sendo atribuído 
ao atributo na tabela 
§  OLD.column indica o valor antes da alteração 
§  Corpo do programa – comandos 
•  while, if-then-else, for-select-do e tratamentos de erros. 
33 
Exemplo – Triggers 
Quando o saldo da conta do cliente for 
negativo, insira automaticamente um registro 
na tabela empréstimo e faça o valor do saldo 
na conta receber o valor zero 
CREATE TRIGGER saldo-negativo 
BEFORE Update on deposito d 
 (if new d.saldo<0 then 
 insert into emprestimo values( d.nomeAgencia, 
 d.numeroConta, d.nomeCliente, -new d.saldo) 
 update deposito s 
 set s.saldo=0 where s.numeroConta=d.numeroConta)) 
 
34 
Gatilhos (triggers) em PostgreSQL 
CREATE TABLE emp ( 
 empname text, salary integer, last_date timestamp, last_user text); 
 
CREATE TRIGGER t_emp BEFORE INSERT OR UPDATE ON emp 
 FOR EACH ROW 
 EXECUTE PROCEDURE emp_stamp(); 
 
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ 
 BEGIN 
 -- Check that empname and salary are given 
 IF NEW.empname IS NULL THEN 
 RAISE EXCEPTION 'empname cannot be null'; 
 END IF; 
 IF NEW.salary IS NULL THEN 
 RAISE EXCEPTION '% cannot have null salary', NEW.empname; 
 END IF; 
 -- Who works for us when she must pay for it? 
 IF NEW.salary < 0 THEN 
 RAISE EXCEPTION '% cannot have a negative salary', NEW.empname; 
 END IF; 
 -- Remember who changed the payroll when 
 NEW.last_date := current_timestamp; 
 NEW.last_user := current_user; 
 RETURN NEW; 
 END; 
$emp_stamp$ LANGUAGE plpgsql; 
 
Cliquez pour modifier le style du titre 
Stored Procedures 
36 
Stored Procedure 
§  Uma Stored procedure é um programa escrito numa 
linguagem do SGBD própria para procedures e triggers 
(gatilhos) 
§  As Stored procedures e triggers são armazenados como 
parte do banco de dados. 
§  Stored procedures podem ser chamadas por aplicações 
cliente ou por outras stored procedures ou triggers. 
§  Triggers são chamadas automaticamente quando uma 
alteração em uma linha da tabela ocorre. 
7 
37 
Stored Procedure – Sintaxe 
CREATE PROCEDURE nome_procedimento 
(parâmetros de entrada ) 
RETURNS (valores retornados) AS 
Declaração de variáveis locais 
BEGIN 
 corpo do programa 
END 
38 
SP – Exemplo 
CREATE PROCEDURE Media_Aluno2 (codigo integer) 
RETURNS (media float) AS 
declare variable existe int; 
BEGIN 
select count(cod_aluno) from aluno where 
cod_aluno =:codigo into :existe; 
if (existe > 0) then 
 SELECT avg(nota) 
 FROM aluno A, conceito C 
 WHERE a.cod_aluno=c.cod_aluno 
 and a.cod_aluno=:codigo 
 INTO :media; 
 else 
 media = -1; SUSPEND; 
END 
Criar uma SP para calcular e 
retornar a média das notas de 
um determinado aluno 
39 
Porque usar SP? 
§  Maior segurança na manipulação dos dados, pois 
impossibilitam que consultas arbitrárias sejam executadas 
pelos usuários; 
§  A execução de procedimentos geralmente é mais rápida, 
pois os comandos são executados diretamente no servidor, 
sem tráfego de dados pela rede; 
§  É possível executar vários comandos SQL a partir de um 
único procedimento. 
Cliquez pour modifier le style du titre 
Exercícios – p/ projeto prático 
41 
Exercícios – considerando o projeto prático 
§  1) Identificar as regras de negócio do sistema 
§  2) Identificar possíveis relatórios emitidos pelo 
sistema 
§  3) Definir visões que reflitam as consultas 
necessárias para os relatórios 
§  4) Indicar possíveis triggers e stored procedures 
que podem ser implementados para as regras de 
negócio identificadas 
Prazo: 13/08 
Cliquez pour modifier le style du titre 
Segurança em BD e 
Autorização de Acesso 
8 
43 
Definição 
§  Segurança em Banco de Dados diz respeito à proteção do 
BD contra ataques, intencionais ou não, utilizando-se ou 
não de meios computacionais 
§  Áreas envolvidas: 
•  roubo e fraude 
•  perda de confidencialidade e privacidade 
•  perda de integridade 
•  perda de disponibilidade 
 
44 
Autorização de Acesso 
Objetivo 
•  proteção contra acessos mal intencionados 
•  controlar quais dados um usuário/grupo de usuários pode ter 
acesso 
•  controlar quais operações um usuário/grupo de usuários pode 
realizar sobre estes dados 
§  Formas de acesso não autorizado: 
•  leitura não autorizada 
•  modificação não autorizada 
•  destruição não autorizada 
 
 
Em um sistema bancário, um funcionário 
precisa saber os dados dos clientes, mas 
apenas os que têm conta poupança na 
agência 1899-6. 
Um funcionário de uma empresa pode 
ter acesso aos nomes dos funcionários e 
aos seus projetos, mas não deve ter 
acesso aos salários dos funcionários. 
45 
Autorização de Acesso – o DBA 
§  O DBA é o superusuário do BD, que pode tudo: 
•  Controle de Acesso 
§  Criar contas para usuários ou grupos de usuários 
•  Autorização de acesso 
§  Conceder ou retirar (revogar) privilégios de acesso às contas 
§  Atribuir níveis de segurança às contas criadas 
•  Segurança geral do sistema de banco de dados 
§  recuperação de um BD 
§  configuração de parâmetros do SGBD 
§  Etc. 
§  Políticas de segurança da empresa e do BD são definidos pelo DBA 
juntamente com o administrador de segurança 
46 
Controles de segurança 
Computacionais 
§  Adiciona-se uma camada à 
segurança provida pelo SO 
§  Autorização e autenticação 
§  Views 
§  Backup e recovery 
§  Integridade 
§  Stored procedures 
§  Criptografia 
§  Auditoria 
§  Procedimentos associados 
•  upgrade, virus checking, 
proxy, firewall, kerberos, 
certificados digitais, SSL, 
SHTTP,etc. 
Não computacionais 
§  Política de segurança e plano 
de contigência 
§  Posicionamento seguro dos 
equipamentos 
§  Controle de acesso físico 
§  Manutenção 
47 
Formas de Autorização de Acesso 
1.  Nível de conta (usuário) 
•  O DBA pode dar permissões aos usuários para criar 
esquemas, modificar e criar tabelas e selecionar dados 
•  O DBA estabelece permissões da conta, independente 
das relações do BD 
2.  Nível de relação/visão 
•  O DBA pode controlar o privilégio de acesso de 
relações ou visões específicas do BD para cada usuário 
•  Podem ser dadas permissões de leitura (seleção), 
modificação e referência 
48 
Tipos de Permissão 
§  Permissão de leitura 
•  privilégios apenas para comandos select 
§  Permissão de modificação 
•  Privilégios para insert, delete e update 
•  Para insert e update é possível restringir os atributos 
§  Permissão de referência 
•  uma conta (de usuário) pode fazer referência a uma relação ao 
especificar restrições de integridade 
•  Pode ser por atributo 
O criador de um objeto é o dono do objeto e assim tem todos 
os privilégios sobre o objeto, podendo autorizar a outros 
usuários alguns (ou todos) destes privilégios 
9 
49 
Regras de Autorização 
§  Expressam mecanismos de autorização nos objetos do BD 
•  Compiladas e armazenadas no dicionário de dados 
•  Expressas em linguagem de alto nível (ex. SQL) 
§  Matriz de autorização 
•  Uma maneira do SGBD implementar essas regras 
•  Cada linha corresponde a um usuário 
•  Cada coluna corresponde a um objeto 
•  M[i,j] => conjunto de regras de autorização que se aplica ao 
usuário i em relação ao objeto j 
50 
Autorização em Banco de Dados 
§  Um usuário que tem concedida alguma forma 
de autoridade pode passar essa autoridade 
para outros usuários. 
DBA
U1
U2
U3
U4
U5
51 
Revogação de Autorização 
§  Suponha que o administrador do banco de dados decida 
revogar a autorização do usuário U1. 
 
 
 
 
 
•  Uma vez que o usuário U4 tem a autorização concedida pelo 
usuário U1, a sua autorização também será revogada. 
•  No entanto, U5 mantém sua autorização por ela ter sido 
concedida também por U2. 
DBA
U1
U2
U3
U4
U5
52 
Permissões de Acesso em SQL 
§  O Comando grant é usado para conferir autorização. A 
forma básica deste comando é: 
§  Lista básica: select, insert, update, delete, alter, drop, index, 
references 
§  Exemplo: 
 grant select on cliente to U1, U2, U3 
 grant update (saldo) on deposito to U1, U2 
grant <lista de privilégios> 
on <nome da relação ou visão> 
to <lista de usuários> 
53 
Grant … With Grant Option 
§  Em SQL, um usuário a quem é garantido um privilégio não está 
autorizado a conceder aquele privilégio a outro usuário 
§  A fim de permitir a um usuário passar o privilégio recebido a 
outros, deve-se acrescentar a cláusula with grant option ao 
comando grant 
 
grant select on agencia to U1 with grant option 
54 
Autorização de Acesso: Exemplos 
§  Exemplo 1: Em um sistema bancário, um funcionário precisa saber os 
dados dos clientes, mas apenas os que tem conta poupança na agência 
1899-6 
 
§  Solução: usar views 
Create view vClientePoupancaAgencia as 
 select c.codCli, c.nome, p.numConta 
 from cliente c, poupanca p 
 where c.codCli=p.cod_cli and 
 p.codAgencia=“1899-6” 
§  grant select on vClientePoupancaAgencia to U10 
Cliente (codCli, nome, endereco, codCidade) 
Agencia (codAgencia, nome, descricao)‏ 
Poupança ( numConta, # codAgencia, # codCli, saldo) 
10 
55 
Autorização de Acesso: Exemplos 
§  Exemplo 2: um funcionário de uma empresa deve ter acesso aos nomes 
dos funcionários e aos seus projetos, mas não deve ter acesso ao 
salário dos funcionários 
Create view vEmpregadoProjeto as 
 select e.codEmp, e.nome, p.tipo, p.descricao, j.dataInicial, 
j.dataFinal 
 from empregado e, projeto p, projetoEmpregado j 
 where e.codEmp=j.codEmp and j.codProj=p.codProj 
 
§  grant select on vEmpregadoProjeto to U50 
Empregado (codEmp, nome, categoria, salario) 
Projeto (#codProj, tipo, descricao)‏ 
ProjetoEmpregado (# codProj, # codEmp, dataInicial, dataFinal) 
56 
Usuários e Papeis 
Roles (Papeis): são interessantes quando um grupo de usuários tem as 
mesmas restrições de acesso: 
 Ex: vários caixas de um banco (várias agências) tem permissão para creditar e 
debitar valores na conta dos clientes. Ao invés de dar permissão de inclusão, 
alteração e exclusão para cada um dos caixas que tem este direito, cria-se o 
papel movimentação 
 
Create role movimentaçao 
 
E dá-se a permissão ao papel 
 
Grant insert, update, delete to movimentaçao 
 
Vincula todos os usuarios ao papel 
 
 GRANT movimentação TO A, B, C, C 
GRANT role TO [user,] [role,] 
57 
Revogar autorização de acesso 
§  Para revogar a autorização, o comando revoke é usado 
§  Ele toma a forma quase idêntica à do comando grant: 
revoke <lista de privilégios> 
on <nome da relação ou visão> 
from <lista de usuários> 
Exemplos: 
 
revoke select on agencia from U1, U2, U3 
revoke update on deposito from U1 
revoke references (nome-agencia) on agencia from 
U1 
58 
Exemplo Completo 
Suponha que o DBA crie 4 contas U1, U2, U3 e U4. 
 
1. Somente U1 deve criar relações no banco (privilégio de conta) 
•  Grant Create Table to U1 
2. com essa autorização o usuário U1 pode criar relações e terá TODOS os 
privilégios sobre elas 
•  Suponha que U1 criou as relações 
Empregado (codEmp, nome, categoria, salario) 
Projeto (codProj, tipo, descricao)‏ 
ProjetoEmpregado (codProj, codEmp, dataInicial, dataFinal) 
59 
Exemplo Completo 
3.  U1 dá ao usuário U2 permissão para incluir e remover tuplas em 
Empregado e Projeto 
•  grant INSERT, DELETE ON EMPREGADO, PROJETO TO U2 
4.  U1 dá ao usuário U3 permissão para recuperar tuplas em Empregado 
e seja capaz de PROPAGAR este privilégio 
•  grant SELECT ON EMPREGADO TO U3 WITH GRANT OPTION 
Empregado (codEmp, nome, categoria, salario) 
Projeto (#codProj, tipo, descricao)‏ 
60 
5.  Agora U3 pode conceder privilégio de seleção para U4 sobre a relação 
Empregado 
•  grant SELECT ON EMPREGADO TO U4 
•  Obs: U4 não pode propagar este privilégio 
6.  Suponha que U1 queira revogar a permissão de U3 
•  REVOKE SELECT ON EMPREGADO FROM U4 
Exemplo Completo 
Empregado (codEmp, nome, categoria, salario) 
Projeto (#codProj, tipo, descricao)‏ 
11 
61 
Exemplo Completo 
7.  Suponha que U1 queira dar ao usuário U3 permissão apenas para 
consultar empregados que trabalhem no projeto AATOM. 
Create view vEmpregadoProjeto as 
 select e.codEmp, e.nome, e.categoria, e.salario 
 from empregado e, projeto p, projetoEmpregado j 
 where e.codEmp=j.codEmp and j.codProj=p.codProj and 
 p.descricao=“AATOM” 
•  grant SELECT ON vEMPEGADOProjeto TO U3 
Empregado (codEmp, nome, categoria, salario) 
Projeto (codProj, tipo, descricao)‏ 
ProjetoEmpregado (# codProj, # codEmp, dataInicial, dataFinal) 
62 
§  grant all to {listaUsuários | public} 
63 
Lista de Permissões no PostgreSQL 
GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } 
 [,...] | ALL [ PRIVILEGES ] } 
 ON [ TABLE ] tablename [, ...] 
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] 
 
GRANT { { USAGE | SELECT | UPDATE } 
 [,...] | ALL [ PRIVILEGES ] } 
 ON SEQUENCE sequencename [, ...] 
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] 
 
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } 
 ON DATABASE dbname [, ...] 
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] 
 
GRANT { EXECUTE | ALL [ PRIVILEGES ] } 
 ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] 
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] 
 
GRANT { USAGE | ALL [ PRIVILEGES ] } 
 ON LANGUAGE langname [, ...] 
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] 
 
GRANT { { CREATE | USAGE} [,...] | ALL [ PRIVILEGES ] } 
 ON SCHEMA schemaname [, ...] 
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] 
 
GRANT { CREATE | ALL [ PRIVILEGES ] } 
 ON TABLESPACE tablespacename [, ...] 
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] 
 
GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ] 
 
64 
Segurança de BD – Pesquisa para casa 
§  Auditoria em BD 
•  O que é? 
•  Para que serve? 
•  Que diretrizes devem ser observadas? 
•  O que é Audit Trail? 
•  Que suporte a auditoria SGBDs comerciais oferecem? 
§  Oracle? SQL Server? 
§  MySQL? PostgreSQL? 
65 
Exercícios 
§  Seja um BD hospitalar 
•  Escreva uma visão que contenha o nome do medico, o nome do 
paciente e a data da consulta 
•  Escreva uma visão que contenha o nome dos médicos que não 
atendem em nenhum ambulatório 
•  Escreva uma visão que recupere os funcionários que nunca 
consultaram 
66 
Exercícios - Permissões 
Crie 4 usuarios: dba, medico, estagiario e secretaria 
§  O usuário estagiário pode ver o nome do medico, o nome do paciente e a data 
da consulta, mas não podem ver a doença do paciente 
§  Somente o estagiario pode incluir novos funcionários 
§  Somente o medico tem acesso completo aos dados do paciente, tanto para 
cadastro quanto para alteração. 
§  Somente o usuário dba pode consultar e alterar o salário dos funcionários 
§  O usuário “secretaria” somente pode ter acesso aos médicos que trabalham no 
ambulatório do 4o andar 
§  Crie um papel chamado consultas. Este papel poderá consultar as tabelas 
paciente e medico. Crie 2 usuarios u1 e u2 que tenham o mesmo papel de 
consulta