Capítulo 6: Integridade e Segurança
 Restrições ao Domínio
 Integridade Referencial
 Asserções
 Triggers
 Segurança e Autorizações
Database System Concepts
6.2.1
©Silberschatz, Korth and Sudarshan (modificado)
Asserções
 Uma asserção é um predicado que exprime uma condição que
gostaríamos de ver sempre satisfeita na base de dados.
 Em SQL as asserções têm a forma:
create assertion <nome> check <predicado>
 Quando se define uma asserção, o sistema testa-a, e volta a
testá-la, sempre que há modificações na base de dados (que a
possam violar)
 Estes testes podem introduzir um overhead significativo; logo as
asserções são para usar com cuidado e de forma comedida.
 Impor uma condição da forma “para todo o X, P(X)”, há que o
fazer usando uma dupla negação: “não existe X tal que not P(X)”
 O Oracle não permite definir asserções.
Database System Concepts
6.2.2
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de Asserção
G
isa
disjoint
E1
E2
 Uma especialização duma entidade geral G (com chave CG) em
E1 e E2 é disjunta.
create assertion disjE1E2 check
(not exists ((select CG from E1)
intersect
(select CG from E2))
)
Database System Concepts
6.2.3
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de Asserção
 Em cada balcão, a soma dos montantes de todos os seus
empréstimos tem que ser sempre inferior à soma de todos os seus
depósitos.
create assertion sum_constraint check
(not exists (select * from branch
where
(select sum(amount) from loan
where loan.branch_name =
branch.branch_name
)
>= some
(select sum(balance) from account
where account.branch_name =
branch.branch_name
)
)
)
Database System Concepts
6.2.4
©Silberschatz, Korth and Sudarshan (modificado)
Outro Exemplo
 Todo o empréstimo tem que estar sempre ligado a pelo menos um
cliente de uma conta (de depósito) cujo saldo é não inferior a
metade do valor do empréstimo
create assertion balance_constraint check
(not exists (
select * from loan
where not exists (
select *
from borrower natural inner join depositor
natural inner join account
where loan.loan_number = borrower.loan_number
and account.balance >= 2 * loan.ammount
)
)
)
Database System Concepts
6.2.5
©Silberschatz, Korth and Sudarshan (modificado)
Triggers
 Um trigger é um “comando” que é executado automaticamente
pelo sistema, como side-effect duma modificação à base de
dados dum determinado tipo pré-definido.
 Para definir um trigger, há que:
 Especificar que evento faz disparar trigger
 Especificar em que condições o trigger deve ser.
 Especificar que acção fazer quando o trigger é executado.
 São conhecidos como event-condition-action rules
 Os triggers são armazenados na base de dados, e executados
para todos as interacções com esta.
 O Oracle suporta triggers, embora com uma sintaxe ligeiramente
diferente da do SQL.
Database System Concepts
6.2.6
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de Trigger
 Imagine uma situação em que o banco aceita que haja saldos
negativos e, nesses casos:
 mete o saldo a 0
 cria um empréstimo com o valor em dívida
 Atribui a este empréstimo um número idêntico ao da conta de
depósito
 O trigger deve ser executado sempre que há uma actualização
na relação account que faz com que o saldo passe a negativo.
Database System Concepts
6.2.7
©Silberschatz, Korth and Sudarshan (modificado)
Codificação do Exemplo em SQL:1999
create trigger overdraft_trigger after update on account
referencing new row as nrow
for each row
when nrow.balance < 0
begin atomic
insert into borrower
(select customer_name, account_number
from depositor
where nrow.account_number =
depositor.account_number);
insert into loan values
(nrow.account_number, nrow.branch_name,
– nrow.balance);
update account set balance = 0
where account.account_number = nrow.account_number
end
Database System Concepts
6.2.8
©Silberschatz, Korth and Sudarshan (modificado)
Eventos e Acções de Triggers em SQL
 Os eventos que podem fazer disparar um trigger são insert, delete ou
update
 No Oracle, também podem disparar triggers eventos de servererror, logon,
logoff, startup e shutdown.
 Triggers sobre update podem-se restringuir só a alguns atributos
 E.g. create trigger overdraft_trigger after update of balance on account
 Pode-se referenciar o valor dos atributos antes e depois da modificação
 referencing old row as : para deletes e updates
 referencing new row as : para inserts e updates
 Pode-se fazer disparar um trigger antes do evento, para codificar restrições.
E.g. converter espaços em null.
create trigger setnull_trigger before update on r
referencing new row as nrow
for each row
when nrow.phone_number = ‘ ‘
set nrow.phone_number = null
 Para além do before e do after no Oracle existe também o instead of.
Database System Concepts
6.2.9
©Silberschatz, Korth and Sudarshan (modificado)
Acções Externas
 Por vezes podemos querer que um dado evento faça disparar uma acção
para o exterior.
 Por exemplo, numa base de dados de uma armazém, sempre que a
quantidade de um produto desce abaixo (devido a um update) de um
determinado valor podemos querer encomendar esse produto, ou disparar
algum alarme.
 Os triggers não podem ser usados para implementar acções sobre o
exterior, mas...
 podem ser usados para guardar numa tabela separada acções-a-levar-a-cabo.
Podem depois haver procedimentos que, periodicamente verificam essa tabela
separada.
 E.g. Uma base de um armazém com as tabelas
 inventario(item, quant): Que quantidade há de cada produto
 quantMin(item, quant) : Qual a quantidade mínima de cada produto
 reposicoes(item, quant): Quanto encomendar sempre que está em falta
 aencomendar(item, quant) : Coisas a encomendar (lido por procedimento)
Database System Concepts
6.2.10
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de Acções Externas
create trigger aenc_trigger after update of quant on inventario
referencing old row as orow, new row as nrow
for each row
when nrow.quant < = some (select quant
from quantMin
where quantMin.item = orow.item)
and orow.quant > some (select quant
from quantMin
where quantMin.item = orow.item)
begin
insert into aencomendar
(select item, quant
from reposicoes
where reposicoes.item = orow.item)
end
Database System Concepts
6.2.11
©Silberschatz, Korth and Sudarshan (modificado)
Sintaxe de Triggers em Oracle
create [or replace] trigger <nome_trigger>
{before | after | instead of} <evento>
[referencing old as <nome_antes>]
[referencing new as <nome_depois>]
for each row
when <condição>
begin
<Sequencia de comandos, terminados por ;>
end;
/
 Evento pode ser:





delete on <tabela ou view>
insert on <tabela ou view>
update on <tabela ou view>
update of <atributos separados por ,> on <tabela ou view>
servererror, logon, logoff, startup ou shutdown
 Os comandos são PL/SQL o que inclui os comandos SQL, mais
WHILEs, IFs, etc (ver manuais)
 Dentro da condição os nome_antes e nome_depois podem ser usados
sem mais. Mas nos comandos têm que ter o símbolo ‘:’ antes!!!
Database System Concepts
6.2.12
©Silberschatz, Korth and Sudarshan (modificado)
Statement Triggers
 São executados após (antes, ou em vez de) uma instrução
completa vs. os anteriores que são executadas após alterações
em cada linha
 Sintaxe:
create [or replace] trigger <nome_trigger>
{before | after | instead of} <evento>
begin
<Sequencia de comandos, terminados por ;>
end;
 Para ser usado quando as condições são para testar
globalmente e não linha a linha.
Database System Concepts
6.2.13
©Silberschatz, Korth and Sudarshan (modificado)
Uso de triggers
 Podem usar-se para implementar assertions, fazendo
raise_application_error quando as condições não se verificam.
 Não usar triggers:
 Quando as restrições podem ser impostas doutra forma!!
 Os triggers são mais difíceis de manter e são menos eficientes.
 Quando se querem manter sumários
 Para tal usem-se views e se eficiência for importante usem-se
materialized views
 Os triggers permitem uma grande generalidade na imposição de
restrições e, também por isso mesmo, devem ser usados com
grande cuidado.
Database System Concepts
6.2.14
©Silberschatz, Korth and Sudarshan (modificado)
Segurança
 Segurança – ao contrário das restrições de integridade, que
pretendiam proteger a base de dados contra estragos acidentais, a
segurança preocupa-se com proteger a base de dados de estragos
propositados.
 A nível do sistema operativo
 A nível da rede
 A nível físico
 A nível humano
 A nível da base de dados
 Mecanismos de autenticação e autorização para permitir acessos
selectivos de (certos) utilizadores a (certas) partes dos dados
Database System Concepts
6.2.15
©Silberschatz, Korth and Sudarshan (modificado)
Autorizações
Diferentes formas de autorização, em dados da bases de dados:
 Autorização de leitura – permite ler, mas não modificar dados.
 Autorização de inserção – permite inserir novos tuplos, mas não
modificar tuplos existentes.
 Autorização de modificação – permite modificar tuplos, mas não
apagá-los.
 Autorização de remoção – permite apagar tuplos
Database System Concepts
6.2.16
©Silberschatz, Korth and Sudarshan (modificado)
Autorizações (Cont.)
Diferentes formas de autorização, para alterar esquemas:
 Autorização de index – permite criar e apagar ficheiros de index.
 Autorização de resources – permite criar novas relações.
 Autorização de alteração – permite criar e apagar atributos duma
relação.
 Autorização de drop – permite apagar relações.
Database System Concepts
6.2.17
©Silberschatz, Korth and Sudarshan (modificado)
Autorizações e Views
 Pode-se dar autorização a utilizadores sobre uma view, sem se
lhe dar autorização sobre as tabelas que a definem
 Isto permite não só melhorar a segurança dos dados, como
também tornar mais simples o seu uso
 Uma combinação de segurança a nível de tabelas, com
segurança a nível de views, pode ser usada para limitar o
acesso de um utilizador apenas aos dados de que ele necessita.
Database System Concepts
6.2.18
©Silberschatz, Korth and Sudarshan (modificado)
Autorizações e Views
 A criação de uma view não requere autorização resources pois,
de facto, nenhuma nova tabela é criada
 Quem cria uma view, fica exactamente com os mesmo
privilégios sobre esta que tinha sobre as tabelas.
 E.g. o criador duma view cust_loan sobre as tabelas borrower e
loan, que só tenha autorização de leitura sobre estas tabelas, só
fica com autorização de leitura sobre as view que criou
Database System Concepts
6.2.19
©Silberschatz, Korth and Sudarshan (modificado)
Atribuição de Privilégios
 A passagem de privilégios de um utilizador para outro pode ser
representado por um grafo de autorizações.
 Os nós do grafo são utilizadores.
 A raiz é o administrador da base de dados.
 Considere o grafo abaixo, para e.g. escrita numa relação.
 Um arco Ui Uj indica que o utilizador Ui atribuiu ao utilizador Uj
privilégio de escrita sobre essa relação..
U1
DBA
U4
U2
U5
U3
Database System Concepts
6.2.20
©Silberschatz, Korth and Sudarshan (modificado)
Grafo de atribuição de privilégios
 Requisito: Todos os arcos têm que fazer parte de algum caminho
com origem no administrador.
 Se o administrados retira o privilégio a U1:
 Deve ser retirado privilégio a U4 (pois U1 já não tem autorização)
 Não deve ser retirado a U5 (pois U5 tem autorização vinda de U2)
 Devem ser prevenidos ciclos:
 Administrador dá privilégios a U7
 U7 dá privilégios a U8
 U8 dá privilégios a U7
 DBA retira privilégios de U7
 Deve retirar autorização de U7 para U8 e de U8 para U7 (pois já
não há caminho do administrador nem para U7 nem para U8).
Database System Concepts
6.2.21
©Silberschatz, Korth and Sudarshan (modificado)
Especificações de Segurança em SQL
 O comando grant é usado para atribuir privilégios
grant <lista de privilégios>
on <nome de relação ou view> to <lista de utilizadores>
 <lista de utilizadores> é:
 Um user-id
 public, o que atribui o privilégios a todos os utilizadores
 Um perfil (role) – veremos à frente
 A atribuição de privilégios sobre uma view não se propaga às
relações nela usadas.
 Quem atribui o privilégio tem que o ter (ou ser o administrador
da base de dados).
Database System Concepts
6.2.22
©Silberschatz, Korth and Sudarshan (modificado)
Privilégios em SQL
 select: permite acesso de leitura sobre a relação ou view
 Exemplo: dar a U1, U2, e U3 autorização de leitura na relação
branch:
grant select on branch to U1, U2, U3
 insert: permite inserir tuplos
 update: permite usar o comando update do SQL
 delete: permite apagar tuplos.
 references: permite a declaração de chaves externas.
 all privileges: forma sumário de atribuir todos os privilégios.
Database System Concepts
6.2.23
©Silberschatz, Korth and Sudarshan (modificado)
Privilégio de atribuir privilégios
 with grant option: autoriza um utilizador a passar um privilégio
a outros utilizadores.
 Exemplo:
grant select on branch to U1 with grant option
dá a U1 o privilégio select sobre a relação branch e autoriza U1 a
passar esse privilégio a qualquer outro utilizador
Database System Concepts
6.2.24
©Silberschatz, Korth and Sudarshan (modificado)
Perfis
 Um perfil permite atribuir, de apenas uma vez, privilégios iguais
para uma classe de utilizadores
 Pode ser atribuídos e retirados privilégios a perfis de utilizadores,
da mesma forma que a utilizadores isolados.
 Podem-se associar perfis a utilizadores, ou mesmo a outros perfis
 Exemplo:
create role caixa
create role gerente
grant select on branch to caixa
grant update (balance) on account to caixa
grant all privileges on account to gerente
grant caixa to gerente
grant caixa to maria, scott
grant gerente to ana
Database System Concepts
6.2.25
©Silberschatz, Korth and Sudarshan (modificado)
Retirar de privilégios em SQL
 O comando revoke serve para retirar privilégios.
revoke <privilégios>
on <relação ou view> from <utilizadores> [restrict|cascade]
 Exemplo:
revoke select on branch from U1, U2, U3 cascade
 Se se colocar cascade o retirar de privilégios de um utilizador
também o pode retirar a outros, conforme descrito pelo grafo.
 Se se usar restrict só é retirado privilégio a esse utilizador
revoke select on branch from U1 restrict
Com restrict, o comando revoke falha (dá erro) se esse
utilizador já passou o privilégio a outros.
Database System Concepts
6.2.26
©Silberschatz, Korth and Sudarshan (modificado)
Retirar de privilégios em SQL (Cont.)
 <privilégios> pode ser all. Nesse caso são retirados todos os
privilégios que foram atribuídos pelo utilizador que deu o
comando.
 Se <utilizadores> incluir public todos os utilizadores perdem
esse privilégio, a não ser que lhe tenha sido atribuído
explicitamente.
 Se o mesmo privilégio for atribuído duas vezes por utilizadores
diferentes, então quem o tem pode ficar com ele mesmo depois
dum revoke (cf. grafo).
 Todos os privilégios que dependem do privilégio retirado, são
também retirados.
Database System Concepts
6.2.27
©Silberschatz, Korth and Sudarshan (modificado)
Limitação a autorizações em SQL
 SQL não permite autorizações a nível de tuplo
 E.g. não se pode restringir de forma a que um aluno só possa ver
as suas notas.
 Neste caso, a tarefa de autorização cai sobre as aplicações (o
que é indesejável, mas o SQL aqui não ajuda).
 Ou então definir view e dar autorizações apenas a essas views
Database System Concepts
6.2.28
©Silberschatz, Korth and Sudarshan (modificado)