SQL
Parte 5 – Avançado
Parte Retirado de André Vinicius R. P. Nascimento –
Universidade Federal de Sergipe
Parte retirado de Melissa Lemos – PUC-Rio
Parte retirado de Gutember Medeiros que retirou de
Cláudio Baptista – DSC - UFCG
311
Gerenciador, Conexão e Usuário
Comando CONNECT – Permite a
conexão a uma base de dados por
meio de um gerenciador;
Comando DISCONNECT –
Desconecta uma base de dados.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
312
Visões em SGBDs
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Representa uma tabela virtual
derivada de outras tabelas básicas ou
mesmo de outras visões.
Essa derivação é alcançada por meio
de uma consulta SQL
O comando SQL CREATE VIEW é
utilizado para a definição de Visões
(Views)
Visões em SGBDs
CREATE VIEW VW_LOJA
AS
SELECT CD_LOJA, NM_LOJA, CD_CATEGORIA
FROM TB_LOJA
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
314
Visões em SGBDs
ECO031 Banco de Dados Aulas Teóricas
-A visão VW_LOJ-A comporta-se, para
consultas, como uma tabela normal.
SELECT CD_LOJA, NM_JOJA
FROM VW_LOJA
SELECT NM_LOJA
FROM VW_LOJA
WHERE CD_CATEGORIA = 1
315
Visões em SGBDs
ECO031 Banco de Dados Aulas Teóricas
-A visão VW_LOJ-A não existe
fisicamente como uma tabela.
Seus dados ou sua consulta existem
somente no momento de utilização da
visão.
CREATE VIEW VW_LOJA
316
Visões em SGBDs
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Utilizamos o comando DROP VIEW para apagar
uma View
DROP VIEW VW_LOJA
Utilizamos o comando ALTER VIEW para alterar
uma View
ALTER VIEW VW_LOJA
AS
SELECT NM_LOJA, CATEGORIA
FROM TB_LOJA
WHERE ESTADO = ‘SE’
317
Objetivos
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Segurança
Ocultar informações que não sejam relevantes
para um usuário ou aplicação.
Transparência ou Baixo Acoplamento entre
aplicações.
Combinação e Otimização (visões particionadas)
Simplicidade
Criar consultas pré-definidas que omitem a
complexidade dos esquemas de dados.
318
Visões em SGBDs –
Atualizando Visões
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Visões são úteis em consultas, mas existem restrições em relação a
atualizações (é ainda pesquisa corrente)
Para ilustrarmos alguns problemas, considere a visão Alocacao1 e
suponha que queiramos atualizar o atributo nomeP da tupla que
contém ‘João’ de ‘ProdutoX’ para ‘Produto Y’
CREATE VIEW Alocaçao1(nomeE, nomeP, Horas)
AS SELECT E.nome, P.nome, horas
FROM Empregado E, Projeto P, Alocação A
WHERE E.matricula = A.matricula and
P.codproj=A.codigop
UPDATE Alocacao1
SET nomeP = ‘ProdutoY’
WHERE nomeE = ‘João’ and nomeP = ‘ProdutoX’
319
Visões em SGBDs –
Atualizando Visões
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
O update anterior pode ser mapeado em vários updates nas
relações base. Dois possíveis updates:
UPDATE Alocacao
SET codigop = (SELECT codproj FROM Projeto
WHERE nome = ‘ProdutoY’)
WHERE matricula = (SELECT matricula FROM
Empregado WHERE nome = ‘João’)
and codigop = (SELECT codproj
FROM Projeto
WHERE nome = ‘ProdutoX’)
UPDATE Projeto
SET nome = ‘ProdutoX’
WHERE nome = ‘ProdutoY’
320
Visões em SGBDs –
Atualizando Visões
ECO031 Banco de Dados Aulas Teóricas
-Observações:
Uma visão definida numa única tabela é
atualizável se os atributos da visão
contêm a chave primária
Visões definidas sobre múltiplas tabelas
usando junção geralmente não são
atualizáveis
Visões usando funções de agrupamento
e agregados não são atualizáveis
321
Stored Procedures
ECO031 Banco de Dados Aulas Teóricas
-Stored Procedures ou Procedimentos
armazenados representam programas que
são armazenados nos SGBDs.
322
Stored Procedures
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
323
Stored Procedures
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
324
Stored Procedures –
Criação/DROP
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Por meio do comando Create Procedure.
CREATE PROCEDURE
SP_FUNCIONARIOS
AS
SELECT MATRICULA, NOME FROM
TB_FUNCIONARIO
Por meio do comando DROP Procedure.
DROP PROCEDURE SP_FUNCIONARIOS
325
Stored Procedures –
Execução/Alteração
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Por meio do comando EXEC.
EXEC SP_FUNCIONARIOS
Por meio do comando ALTER
PROCEDURE
ALTER PROCEDURE SP_FUNCIONARIOS
AS
SELECT MATRICULA, NOME FROM
TB_FUNCIONARIO
WHERE CD_DEPARTAMENTO = 2
326
Stored Procedures –
Parâmetro de Entrada
ECO031 Banco de Dados Aulas Teóricas
-CREATE PROCEDURE
SP_FUNCIONARIO (@MATRICULA INT)
AS
SELECT * FROM TB_FUNCIONARIO
WHERE MATRICULA = @MATRICULA
--Executando o procedimento
passando o valor 10 como parâmetro
EXEC SP_FUNCIONARIO 10
327
Stored Procedures –
Parâmetro de Saída
ECO031 Banco de Dados Aulas Teóricas
-CREATE PROCEDURE SP_INCLUI_FUNCIONARIO
(@MATRICULA INT, @NOME VARCHAR(40),
@MENSAGEM VARCHAR(40) OUTPUT)
AS
INSERT INTO TB_FUNCIONARIO(MATRICULA, NOME)
VALUES(@MATRICULA, @NOME)
SET @MENSAGEM = ‘FUNCIONARIO INCLUIDO’
--Executando o procedimento
328
Gatilho ou Trigger
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Tipo especial de procedimento armazenado,
associado a uma tabela, que é executado quando
dados são modificados.
Objetivo: Assegurar regras de negócio ou restrições
que, por serem complexas, não podem ser
tratadas por outros mecanismos (CHECK,
FOREIGN KEY, etc)
Quando é executada:
Quando um Evento acontece.
Esse Evento pode ser um INSERT, UPDATE ou
DELETE em uma tabela específica.
329
Gatilho ou Trigger - Criando
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
CREATE TRIGGER TG_ALUNO_INSERT
ON TB_ALUNO
AFTER INSERT
AS
...
CREATE TRIGGER TG_ALUNO_INSERT
ON TB_ALUNO
AFTER INSERT, UPDATE
AS
...
CREATE TRIGGER TG_ALUNO_DELETE
ON TB_ALUNO
AFTER DELETE
AS
...
330
Gatilho ou Trigger –
Remoção/Habilitar/Desabilitar
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
DROP TRIGGER TG_ALUNO_INSERT
ALTER TABLE TB_ALUNO DISABLE
TRIGGER TG_ALUNO_INSERT
ALTER TABLE TB_ALUNO DISABLE
TRIGGER ALL
ALTER TABLE TB_ALUNO ENABLE
TRIGGER TG_ALUNO_INSERT
ALTER TABLE TB_ALUNO ENABLE
TRIGGER ALL
331
Gatilho ou Trigger – Obtendo
Informações
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Listar as triggers associadas a uma
tabela:
sp_helptrigger ‘TB_ALUNO’
Verificar o código de uma trigger:
sp_helptext ‘TG_ALUNO_INSERT’
332
Gatilho ou Trigger –
Observações
ECO031 Banco de Dados Aulas Teóricas
-Não se pode chamar diretamente um trigger
Não é permitido iniciar ou finalizar
transações em meio à TRIGGERS
Não se pode criar triggers para uma tabela
temporária ou para uma visão
Triggers ainda não podem sr
implementadas com a intenção de devolver
para o usuário ou para uma aplicação
mensagens de erros
333
Transação
ECO031 Banco de Dados Aulas Teóricas
-É um programa em execução que forma
uma unidade lógica de processamento
no banco de dados.
Conjunto de operações que leva um
banco de dados de um estado
consistente para outro estado
consistente.
334
Transação - Exemplos
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Transferência Bancária
Saque em terminal de Cash
Compra com Pagamento no Cartão de
Crédito
Compra de Passagem Aérea
335
Transação - Motivação
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
SGBD é um sistema multi-usuário
Reserva de passagens aéreas, bancos comerciais
Multiprogramação
Uso simultâneo do sistema por vários usuários
Processamento de transações concorrentes
Há uso compartilhado de CPU
Para otimização dos recursos computacionais
há execução intercalada dos programas/
transações.
Em ambiente com multiprocessador: há
execução simultânea ou paralela.
336
Transação
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
O que acontece quando dois comandos SQL
atualizam o mesmo dado ou se o sistema
falha durante a execução de um comando?
Se o comando for de consulta, estes
acontecimentos não são problemas.
Se o comando for de atualização, remoção
ou inserção, estas condições podem refletir
em efeitos desastrosos no BD.
337
Transação
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Intuitivamente, uma transação parte com um
estado do BD, realiza sua ação nele, e gera uma
nova versão do BD, causando uma mudança de
estado.
É preciso garantir que
A transação pode ser executada
concorrentemente com outras
Podem acontecer falhas durante a execução de
uma transação
Após a mudança de estado, o BD continua
consistente.
Transação
ECO031 - Banco de Dados - Aulas Teóricas - 339
Transação
ECO031 Banco de Dados Aulas Teóricas
-Uma transação é limitada por declarações de início e fim de
transações
BEGIN TRANSACTION
COMMIT
Término da transação com a efetivação das
atualizações de forma persistente. Neste caso, o
banco de dados mudou de estado, isto é, passou para
um estado diferente do estado inicial.
ROLLBACK
Transação - Exemplo
FLIGHT(FNO, DATE, SRC, DEST, STSOLD, CAP)
Dados do vôo: número, data, origem, destino, qte.
de assentos vendidos, número total de assentos)
CUST(CNAME, ADDR, BAL)
Dados do cliente: nome, endereço, saldo
FC(FNO, DATE, CNAME, SPECIAL)
Relacionamento dos clientes e vôos: número do
vôo, data, nome do cliente, observação especial da
reserva
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Transação - Exemplo
Begin_transaction Reservation
begin
input (flight_no, date, customer_name);
EXEC SQL SELECT STSOLD, CAP
INTO temp1, temp2 FROM FLIGHT
WHERE FNO = flight_no AND DATE = date;
if temp1=temp2 then
begin
output(“no free seats”);
Rollback
end
else begin
EXEC SQL UPDATE FLIGHT SET STSOLD = STSOLD+1 WHERE FNO =
flight_no AND DATE = date;
EXEC SQL INSERT INTO FC(FNO,DATE, CNAME, SPECIAL) VALUES
(flight_no, date, customer_name, null);
Commit;
output(“reservation completed”)
end
end
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
342
Transação – Transações
Incompletas
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Possíveis razões para que a execução de uma
transação não se realize por completo
Problemas de hardware - Conteúdo da memória
principal pode ser perdido
Erros lógicos ou interferência do usuário
-Divisão por zero, Interrupção por CTRL+C
Tratamento de exceções - Conta corrente sem
fundos, Avião com lotação esgotada
Controle de concorrência - Deadlock
Problemas externos - Falta de luz.
343
Transação
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
O conceito de transação requer que todas as
operações sejam executadas de forma indivisível.
Propriedades: atomicidade, consistência, isolamento
e durabilidade
Atomicidade
Uma transação precisa ser atômica (tudo ou
nada).
A execução com sucesso de uma transação é
conhecida como commit.
A falha de uma transação é conhecida como
abort.
344
Transação
ECO031 Banco de Dados Aulas Teóricas
-Consistência
Uma transação deve manter um banco de
dados consistente.
Essa propriedade é, parte responsabilidade do
SGBD, parte responsabilidade dos analistas de
aplicações.
Isolamento
Uma transação não deve sofrer interferência de
outras transações sendo executadas de forma
concorrente.
345
Transação
ECO031 Banco de Dados Aulas Teóricas
-Durabilidade
Segurança em Banco de
Dados
347
Introdução
Uma das maiores preocupações em computação
tem sido segurança da informação
Nos dias atuais, com o uso da Internet os sistemas
tornam-se onipresentes, entretanto também
vulneráveis a ataques maliciosos
Portanto, os SGBDs trazem uma camada de
segurança que visa compor todo o arsenal de
segurança da informação numa corporação
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
348
Introdução
Definição:
Segurança em Banco de dados diz respeito à
proteção do banco de dados contra ataques
intencionais ou não intencionais, utilizando-se
ou não de meios computacionais
Áreas envolvidas:
roubo e fraude
perda de confidencialidade
perda de privacidade
perda de integridade
perda de disponibilidade
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
349
Introdução
O subsistema de segurança é responsável
por proteger o BD contra o acesso não
autorizado.
Formas de acesso não autorizado:
leitura não autorizada
modificação não autorizada
destruição não autorizada
O ABD tem plenos poderes para dar e
revogar privilégios a usuários.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
350
Introdução
Motivação: Exemplo Locadora
Apenas alguns empregados podem modificar
preços dos DVDs
Clientes usando o sistema de consulta, não
devem ter acesso a outras funcionalidades
(vendas, contabilidade, folha de pagamento,
etc)
Apenas o pessoal da gerência deve ter acesso
às informações dos empregados (por exemplo:
empregados-a-demitir)
Clientes não devem ver o preço de compra de
um produto
ECO031 - Banco de Dados - Aulas Teóricas - 351
Introdução
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
e.g. upgrading, virus checking, proxy, firewall, kerberos,
certificados digitais, SSL, SHTTP, etc.
-352
Introdução
Controles de segurança não
computacionais
Política de segurança e plano de
contingência
Posicionamento seguro de equipamentos
Controle de acesso físico
Manutenção
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
353
Introdução
Duas abordagens para segurança de
dados:
Controle de acesso discreto:
Um dado usuário tem direitos de acessos diferentes
(privilégios) em objetos diferentes
Flexível, mas limitado a quais direitos usuários podem
ter em um objeto
Controle de acesso mandatório:
cada dado é rotulado com um certo nível de
classificação
a cada usuário é dado um certo nível de acesso
rígido, hierárquico
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
354
Introdução
Proteção
Privilégio
Aplica-se a
Ver
SELECT
Tabelas, colunas,
métodos invocados
Criar
INSERT
Tabelas, colunas
Modificar
UPDATE
Tabelas, colunas
Remover
DELETE
Tabelas
Referenciar
REFERENCES
Tabelas, colunas
Usar
USAGE
UDT – Tipo Definido
pelo Usuário
Ativar
TRIGGER
Tabelas
Executar
EXECUTE
Stored procedures
ECO031 Banco de Dados Aulas Teóricas
-Fabiana Costa Guedes 355
Introdução
O que se espera do SGBD é o mesmo
tratamento dado à tentativa de acesso a
uma tabela inexistente (“no such table”).
Portanto, se um usuário tentar acessar uma
tabela que ele não tem privilégios para tal o
erro será:
“Either no such table or you have no
privilege on the table”
Razão: Segurança
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
356
Introdução
O usuário tem um auth_ID que o identifica
Existe PUBLIC que representa todos usuários
Privilégios são atribuídos/revogados:
Usuários
Papéis (Roles)
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) estes
privilégios.
A opção with grant option, permite ao usuário que
recebeu um privilégio repassar para quem quiser.
ECO031 - Banco de Dados - Aulas Teóricas - 357
Introdução
Com respeito a DDL:
Um usuário pode executar qualquer
comando DDL no esquema que ele é
dono.
Um usuário NÃO pode executar
nenhuma operação DDL no esquema
que ele não é o dono.
-358
Usuários e Papéis
Identificador de usuário
Alguns SGBDs permitem que o usuário use o mesmo
login e senha do SO
Papéis (Roles)
É um identificador ao qual pode-se atribuir privilégios que
não existem a princípio. Então pode-se atribuir a um
usuário este papel (conjunto de privilégios) com um único
comando GRANT.
Pode-se inclusive ao criar um papel usar outros papéis já
cadastrados.
Ex. PapelVendedor, PapelVendedorSapatos,
PapelVendedoFrutas.
ECO031 Banco de Dados Aulas Teóricas
-Fabiana Costa Guedes 359
Papéis - Roles
CREATE ROLE nome-papel
[WITH ADMIN {CURRENT_USER |
CURRENT_ROLE}]
Para remover um papel:
DROP ROLE nome-papel;
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
360
Papéis - Roles
Existem papéis padrões na maioria dos
SGBD:
DBA: permite desempenhar o papel de
administrador do banco de dados
Resource: permite criar seus próprios objetos
Connect: permite apenas se conectar ao banco
de dados, mas deve receber os privilégios de
alguém para acessar objetos.
ECO031 Banco de Dados Aulas Teóricas
-Fabiana Costa Guedes 361
Regras de Autorização
Expressam os mecanismos de autorização em
relações / visões / stored procedures
São compiladas e armazenadas no dicionário de
dados
São expressas em linguagem de alto nível (Ex.
SQL)
Uma maneira do SGBD implementar estas regras é
usar uma matriz de autorização, onde cada linha
corresponde a um usuário e cada coluna
corresponde a um objeto.
M[i,j] => conjunto de regras de autorização que se
aplica ao usuário i com relação ao objeto j.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
362
Regras de Autorização
O ABD fornece / revoga as autorizações de leitura,
inserção, atualização e remoção aos usuários nas
diversas tabelas / visões, e estes podem
repassá-los caso receba autorização para tal.
Empregado
Departamento
Projeto
João
Select
Update, select
Select,
delete,
update
Maria
None
None
Select
Pedro
None
None
None
Ana
All
All
All
ECO031 - Banco de Dados - Aulas Teóricas - 363
Regras de Autorização
O comando GRANT
GRANT lista-privilégios
ON objeto
TO lista-usuários [WITH GRANT OPTION]
[GRANTED BY
{CURRENT_USER|CURRENT_ROLE}]
-364
Regras de Autorização
Lista de privilégios:
Privilégio1[, privilégio2 ...]
|ALL PRIVILEGES
Privilégios:
SELECT [coluna,...]
|SELECT (método,...)
|DELETE
|INSERT [coluna,...]
|UPDATE [coluna ...]
|REFERENCES [(coluna ...)]
|USAGE
|TRIGGER
|EXECUTE
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
365
Regras de Autorização
Lista de usuários:
authID, [authID ...] | PUBLIC
OBS.: authID pode ser login ou role
A opção GRANTED BY indica se os
privilégios concedidos são autorizados pelo
usuário corrente ou pelo role.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
366
Regras de Autorização
Autorizando papéis
GRANT role-name [, role-name ...]
To lista-usuários
[WITH ADMIN OPTION]
[GRANTED BY
{CURRENT_USER|CURRENT_ROLE}
Um role-name pode ter um número ilimitado
de privilégios ou outros roles
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
367
Exemplos
/*Permite a quem tenha o papel Gerente_Loja
apenas ver a tabela empregados*/
GRANT SELECT ON EMPREGADOS TO
GERENTE_Loja
/*Privilégios de remoção com permissão de
repassar o privilégio */
GRANT DELETE ON Empregados TO Carlos
WITH GRANT OPTION
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
368
Exemplos
/* Update de uma coluna específica */
GRANT UPDATE (preço) ON Produtos
TO Gerente_Loja
/* Privilégios de inserção */
GRANT INSERT ON Produtos
TO Carla, Maria, Marta
/* Inserção só em algumas colunas */
GRANT INSERT (id, preco, descricao, tipo)
ON Produtos TO Assistente
ECO031 - Banco de Dados - Aulas Teóricas - 369
Exemplos
/* Acesso público em views */
GRANT SELECT ON MinhaVisão
TO PUBLIC;
/* referências (foreign key) */
GRANT REFERENCES (titulo)
ON FILMES TO Pedro;
-370
Exemplos
Um privilégio TRIGGER numa tabela
permite criar um trigger para aquela tabela
O privilégio EXECUTE permite um usuário
ou role executar uma determinada stored
procedure.
Exemplo:
GRANT EXECUTE ON AumentaSalario TO
isabel
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
371
Exemplos
ALL PRIVILEGES
Permite especificar uma lista de privilégios que
inclui todos os privilégios de um objeto
específico no qual o usuário executando o
GRANT tem o privilégio para dar o grant
(recebeu WITH GRANT OPTION ou é o dono)
Ex.:
GRANT ALL PRIVILEGES ON Filmes to Patricia
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
372
Revoke
Revoga autorização de privilégios
Se o usuário A tiver concedido o privilégio P para o
usuário B, então A poderá, posteriormente, revogar
o privilégio P de B, através do comando REVOKE
Sintaxe:
REVOKE <privilégios> ON <relação/visão> FROM
<usuários>
Ex.:
REVOKE delete ON projeto FROM Marta, Ana
REVOKE update ON Empregado FROM Ana
REVOKE DBA FROM Bruno
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Auditoria
374
Auditoria
Solução: Auditar, Investigar
??? Quem fez o quê e quando ???
SGBD
ECO031 - Banco de Dados - Aulas Teóricas - 375
Auditoria
Trilha de
auditoria
Opções de
auditoria
Arq. de
parâmetros
Ativa auditoria de
banco de dados
DBA
Especifica
opções
de auditoria
Banco
de dados
Usuário
Executa comando
Gera trilha
de auditoria
Analisa
informações
de auditoria
Trilha de
auditoria
de S/O
Processo
376
Diretrizes de Auditoria
Defina as finalidades da auditoria
Atividade de banco de dados suspeita
Reuna informações históricas
Defina o que você deseja auditar
Auditar usuários, instruções ou objetos
Por sessão
Com ou sem sucesso
Gerencie a trilha de auditoria
Monitorar o crescimento da trilha de auditoria
Proteger a trilha de auditoria de acesso
não-autorizado
ECO031 Banco de Dados Aulas Teóricas
-Fabiana Costa Guedes 377
Diretrizes de Auditoria
Avaliar o propósito de auditoria, evitando auditoria
desnecessária.
Que tipo de atividade do BD você suspeita?
Quem são os suspeitos?
Auditar, inicialmente, de forma genérica e ir
especializando.
Apesar do custo baixo deve-se limitar o nº de eventos
auditados o máximo possível para minimizar:
O impacto de performance na execução de
comandos auditados
O tamanho do audit trail
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
378
Audit Trail
Audit trail: componente de todo SGBD
que armazena histórico de
informações de auditoria
Oracle: tabela
SYS.AUD$
DB2: log DB2AUDIT.LOG
O SO também pode ter um audit trail.
Podendo ser usado em conjunto com
o do BD.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
379
Audit Trail
Algumas informações do audit trail:
Nome do login do usuário no SO;
Nome do usuário no BD;
Identificador de sessão;Identificador do terminal;
Nome do objeto do esquema acessado;
Operação executada ou tentada;
Código de conclusão da operação;
Data e hora.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
Backup e Restore
381
Introdução
Um banco de dados precisa ter um
mecanismo que possa proteger dados no
sistema em caso de falhas.
Para garantir a durabilidade das transações
é obrigatório que nenhuma falha possa criar
dados incorretos.
Para proteger a si mesmo de falhas, um BD
executa várias operações, o que inclui a
criação de cópias de segurança e logs de
transação.
-382
Tipos de Falhas
Falhas na transação – ocorre quando uma
transação não pode ser concluída devido a
um erro nela própria;
Falhas no sistema – ocorre quando o
sistema cai devido à queda de energia ou
outro problema do tipo. A recuperação
ocorre após a reinicialização do sistema;
Falhas de mídia – ocorre quando o disco
rígido que contém o BD é danificado. A
recuperação é feita com a utilização de
cópias de backup.
ECO031 Banco de Dados Aulas Teóricas
-Fabiana Costa Guedes 383
Backup e Restore
Backup é uma copia de segurança que
envolve dois dispositivos distintos.
O restore é o ato de se fazer uso dos dados
armazenados recuperando-os (na maioria
dos casos no próprio dispositivo original).
A recomendação é que mantenha uma
cópia local dos seus arquivos, para que
você seja capaz de fazer a recuperação
instantânea dos dados.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
384
Copy
Copy
Backup e Restauração
Tipos de Backup
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
385
Backup e Restauração
Tipos de Backup
Um backup de cópia copia todos os
arquivos selecionados, mas não os marca
como arquivos que passaram por backup
(ou seja, o atributo de arquivo não é
desmarcado).
A cópia é útil caso você queira fazer backup
de arquivos entre os backups normal e
incremental, pois ela não afeta essas outras
operações de backup.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
386
Copy
Copy
Daily
Daily
Backup e Restauração
Tipos de Backup
ECO031 - Banco de Dados - Aulas Teóricas - 387
Backup e Restauração
Tipos de Backup
Um backup diário copia todos os
arquivos selecionados que foram
modificados no dia de execução do
backup diário.
Os arquivos não são marcados como
arquivos que passaram por backup (o
atributo de arquivo não é
desmarcado).
-388
Copy
Copy
Daily
Daily
Differential
Differential
Tipos de Backup
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
389
Tipos de Backup
Um backup diferencial copia arquivos
criados ou alterados desde o último backup
normal ou incremental.
Não marca os arquivos como arquivos que
passaram por backup (o atributo de arquivo
não é desmarcado).
Se você estiver executando uma
combinação dos backups normal e
diferencial, a restauração de arquivos e
pastas exigirá o último backup normal e o
último backup diferencial.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
390
Copy
Copy
Daily
Daily
Incremental
Incremental
Differential
Differential
Backup e Restauração
Tipos de Backup
ECO031 Banco de Dados Aulas Teóricas
-Fabiana Costa Guedes 391
Backup e Restauração
Tipos de Backup
Um backup incremental copia somente os
arquivos criados ou alterados desde o
último backup normal ou incremental e os
marca como arquivos que passaram por
backup (o atributo de arquivo é
desmarcado).
Se você utilizar uma combinação dos
backups normal e incremental, precisará do
último conjunto de backup normal e de
todos os conjuntos de backups incrementais
para restaurar os dados.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
392
Copy
Copy
Daily
Daily
Normal
Normal
Incremental
Incremental
Differential
Differential
Backup e Restauração
Tipos de Backup
ECO031 - Banco de Dados - Aulas Teóricas - 393
Backup e Restauração
Tipos de Backup
Um backup normal copia todos os arquivos
selecionados e os marca como arquivos
que passaram por backup (ou seja, o
atributo de arquivo é desmarcado).
Com backups normais, você só precisa da
cópia mais recente do arquivo ou da fita de
backup para restaurar todos os arquivos.
Geralmente, o backup normal é executado
quando você cria um conjunto de backup
pela primeira vez.
-394
Tipos de Backup
O backup dos dados que utiliza uma
combinação de backups normal e
incremental exige menos espaço de
armazenamento e é o método mais rápido.
No entanto, a recuperação de arquivos
pode ser difícil e lenta porque o conjunto de
backup pode estar armazenado em vários
discos ou fitas.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
395
Tipos de Backup
O backup dos dados que utiliza uma
combinação dos backups normal e
diferencial é mais longo,
principalmente se os dados forem
alterados com freqüência, mas facilita
a restauração de dados, porque o
conjunto de backup geralmente é
armazenado apenas em alguns discos
ou fitas.
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
396
Backup e Restauração
Tipos de Backup
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
397
Backup
Pode ser automatizado
Afeta a performance do servidor (deve ser
executado após o expediente)
É necessário ter o Language Pack
correspondente no destino
ECO031 Banco de Dados Aulas Teóricas -Fabiana Costa Guedes
398
Tipos de Armazenamento
Armazenamento volátil
Informações residentes em
armazenamento volátil usualmente não
resistem a queda do sistema.
Armazenamento não-volátil
A informação sobrevive a quedas de
sistema.
Estão sujeitos a falhas que pode resultar
em perda da informação.
ECO031 - Banco de Dados - Aulas Teóricas - 399
Tipos de Armazenamento
Armazenamento estável
A informação nunca é perdida.
O Armazenamento estável é
teoricamente impossível de obter, mas
pode-se chegar perto dele usando
técnicas que tornem extremamente
improvável a perda de dados.
-400