PublicouVictor Cassis Alterado mais de 4 anos atrás
2 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.
3 Exemplo de Asserção G disjoint isa 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)) )
4 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 )
5 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 )
6 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.
7 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.
8 Codificação do Exemplo em SQL:1999
9 Eventos e Acções de Triggers em SQL
10 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)
11 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 begin insert into aencomendar (select item, quant from reposicoes where reposicoes.item = orow.item) end
12 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!!!
13 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.
14 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.
15 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
16 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
17 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.
18 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.
19 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
20 Atribuição de Privilégios
21 Grafo de atribuição de privilégios
22 Especificações de Segurança em SQL
23 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.
24 Privilégio de atribuir privilégios
25 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
26 Retirar de privilégios em SQL
27 Retirar de privilégios em SQL (Cont.)
28 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
©Silberschatz, Korth and Sudarshan (modificado)5.1.1Database System Concepts Capítulo 5: Outras linguagens Query-by-Example (QBE) Datalog.