Sist. Gerenc. de Banco de Dados

Sistemas Gerenciadores de Banco de Dados
Introdução
Prof. Marcos Alexandruk
EMENTA
Sistemas gerenciadores de banco de dados;
Configurações do ambiente de trabalho;
Diferenças entre as diversas plataformas;
Criação de tabelas e consultas;
Mecanismos de back-up;
Importação e exportação.
© 2014 - Prof. Marcos Alexandruk
OBJETIVO
Conhecer os requisitos de instalação e recursos
dos principais SGBDs (Sistemas Gerenciadores de
Banco de Dados).
© 2014 - Prof. Marcos Alexandruk
BIBLIOGRAFIA BÁSICA
COSTA, Rogério Luís de Carvalho. SQL: guia
prático. 2. ed. Rio de Janeiro: Brasport, 2007.
SILBERSCHATZ, A.; KORTH, H.; SUBARSHAN, S.
Sistema de banco de dados. 3. ed. Rio de Janeiro:
Campus, 2004.
SOARES, Walace. MySQL conceitos e aplicações.
São Paulo: Érica, 2004.
© 2014 - Prof. Marcos Alexandruk
BIBLIOGRAFIA COMPLEMENTAR
BRYLA, Bob; LONEY, Kevin. Oracle Database 11g
manual do DBA. Porto Alegre: Bookman, 2009.
DATE, C. J. Introdução a sistemas de bancos de
dados. Rio de Janeiro: Elsevier, 2004.
ELMASRI, Ramez; NAVATHE, Shamkant B.
Sistemas de banco de dados. 4th ed. São Paulo:
Pearson Addison Wesley, 2010
PEREIRA Neto, Álvaro. PostgreSQL: técnicas
avançadas: versões Open Source 7.x e 8.x:
soluções para desenvolvedores e administradores
de banco de dados. 4. ed. São Paulo: Érica, 2007.
RAMALHO, José Antonio Alves. Oracle 10g: ideal
para quem deseja o aprendizado do Oracle. São
Paulo: Pioneira Thomson Learning, 2005.
© 2014 - Prof. Marcos Alexandruk
Sistemas Gerenciadores de Banco de Dados
Aula 1
Prof. Marcos Alexandruk
Características dos SBGDs
Conceitos gerais
As doze regras de Codd para SGBDRs
© 2014 - Prof. Marcos Alexandruk
Conceitos gerais
SGBD (Sistema de Gerenciamento de Banco de Dados):
coleção de dados inter-relacionados
+
conjunto de programas para acessar e manipular esses dados
Silberschatz p. 4
© 2014 - Prof. Marcos Alexandruk
Conceitos gerais
O gerenciamento de dados envolve:
• Definir estruturas de armazenamento
• Fornecer mecanismos para a manipulação de informações
© 2014 - Prof. Marcos Alexandruk
Conceitos gerais
O principal objetivo de um SGBD é fornecer um
ambiente que seja tanto conveniente como eficiente
para armazenamento e recuperação de informações.
© 2014 - Prof. Marcos Alexandruk
Conceitos gerais
O SGBD precisa garantir a segurança apesar de falhas
de sistema ou tentativas de acesso não autorizado.
© 2014 - Prof. Marcos Alexandruk
Conceitos gerais
Embora as interfaces de usuário ocultem os detalhes de acesso a
um banco de dados, e a maioria das pessoas nem mesmo tenha
consciência de estar lidando com um banco de dados, acessar
banco de dados é uma parte essencial da vida de quase todo
mundo hoje.
Silberschatz p. 2
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
Doze regras estabelecidas por
Edgard F. Codd, em 1985, por meio
das quais podemos determinar o
quanto um banco de dados é
relacional ou não.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
1. Regra das informações em tabelas:
As informações a serem apresentadas no banco de dados devem
ser apresentadas como relações (tabelas formadas por linhas e
colunas) e o vínculo de dados entre as tabelas deve ser
estabelecido por meio de valores de campos comuns.
Aplica-se tanto aos dados quanto aos metadados (descrições dos
objetos do banco de dados).
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
2. Regra de acesso garantido:
Para que o usuário possa acessar as informações contidas no
banco de dados, o método de referência deve ser o nome da tabela,
o valor da chave primária e o nome do campo.
A ordem de apresentação dos dados não tem importância no
contexto.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
3. Regra de tratamento sistemático de valores nulos:
O SGBD deve ter capacidade de tratar valores que não são
fornecidos pelos usuários de maneira que permita a distinção dos
valores reais.
Exemplo: um campo de armazenamento de dados numéricos, pode
conter valores válidos, o valor zero e valores nulos.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
4. Regra do catálogo relacional ativo:
Toda a estrutura do banco de dados (tabelas, campos, índices, etc.)
deve estar disponível em tabelas (catálogo).
Essas tabelas são manipuladas pelo próprio sistema, quando o
usuário efetua alterações na estrutura do banco de dados.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
5. Regra da atualização de alto nível:
O usuário deve ter capacidade de manipular as informações do
banco de dados em grupos de registros, ou seja, ser capaz de
inserir, alterar e excluir vários registros ao mesmo tempo.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
6. Regra da sublinguagem de dados abrangente:
Pelo menos uma linguagem deve ser suportada para que o usuário
possa manipular a estrutura do banco de dados (exemplo: criação
e alteração de tabelas), assim como extrair, inserir, atualizar ou
excluir dados, definir restrições de acesso e controle de
transações (COMMIT/ROLLBACK).
Deve ser possível também a manipulação de dados por meio de
programas aplicativos.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
7. Regra da independência física:
Quando for necessária alguma modificação na forma como os
dados são armazenados fisicamente, nenhuma alteração deve ser
necessária nas aplicações que fazem uso do banco de dados.
Devem também permanecer inalterados os mecanismos de
consulta e manipulação de dados utilizados pelos usuários finais.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
8. Regra da independência lógica:
Qualquer alteração efetuada na estrutura do banco de dados, como
inclusão e exclusão de campos de uma tabela ou alteração no
relacionamento entre tabelas não deve afetar o aplicativo que o usa.
O aplicativo deve manipular visões das tabelas.
Visões são uma espécie de tabela virtual, que agrupam dados de
uma ou mais tabelas físicas e apresentam ao usuário os dados.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
9. Regra da atualização de visões:
Visto que as visões dos dados são teoricamente suscetíveis a
atualizações, então um aplicativo que faz uso desses dados deve
ser capaz de efetuar alterações, exclusões e inclusões neles
As atualizações devem ser repassadas automaticamente às tabelas
originais.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
10. Regra da independência de integridade:
As várias formas de integridade do banco de dados (integridade de
entidade, referencial, restrição e obrigatoriedade de valores, etc.)
precisam ser estabelecidas dentro do catálogo do sistema ou
dicionário de dados, e ser totalmente independente da lógica dos
aplicativos.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
11. Regra da independência de distribuição:
Sistemas de banco de dados podem estar distribuídos em diversas
plataformas, interligados em rede e podem inclusive estar
fisicamente distantes entre si. Essa capacidade de distribuição não
pode afetar a funcionalidade do sistema e dos aplicativos que
fazem uso do banco de dados.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
12. Regra não subversiva:
O sistema deve ser capaz de impedir que qualquer usuário ou
programador de passar por cima de todos os mecanismos de
segurança, regras de integridade do banco de dados e restrições,
utilizando algum recurso ou linguagem de baixo nível que
eventualmente possam ser oferecidas pelo próprio sistema.
© 2014 - Prof. Marcos Alexandruk
As doze regras de Codd
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Regra das informações em tabelas
Regra de acesso garantido
Regra de tratamento sistemático de valores nulos
Regra do catálogo relacional ativo
Regra da atualização de alto nível
Regra da sub linguagem de dados abrangente
Regra da independência física
Regra da independência lógica
Regra da atualização de visões
Regra da independência de integridade
Regra da independência de distribuição
Regra não subversiva
© 2014 - Prof. Marcos Alexandruk
Aula 1: Exercícios
1. Defina em suas palavras o que é um SGBD.
2. Qual é o principal objetivo de um SGBD (Sistema de
Gerenciamento de Banco de Dados)?
3. Quais recursos do SGBD podem ser utilizados para garantir a
segurança dos dados?
4. Liste algumas ocasiões em que você direta ou indiretamente
interagiu com bancos de dados.
5. Escolha três regras elaboradas por Edgard F. Codd e comente
sobre a sua importância para os bancos de dados relacionais.
© 2014 - Prof. Marcos Alexandruk
Aula 1: Use AVA
Comente as últimas ameaças divulgadas pela imprensa que
envolvam a segurança dos dados de pessoas e empresas. Que
soluções ou medidas você propõe para evitar o “roubo” de
informações?
© 2014 - Prof. Marcos Alexandruk
Sistemas Gerenciadores de Banco de Dados
Aula 2
Prof. Marcos Alexandruk
Diferenças entre os principais SBGDs
Classificações dos SGBDs:
• MODELO DE DADOS
• NÚMERO DE USUÁRIOS
• LOCALIZAÇÃO
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
BANCOS DE DADOS HIERÁRQUICOS
•
Um dos primeiros sistemas de controle de
base de dados;
•
IMS (Information Management System):
desenvolvido pela IBM e pela Rockwell no
fim da década de 1960;
•
Ambientes de alta plataforma (mainframes):
o IMS era instalado em sistemas do tipo:
OS/V1, OS/V2, MVS, MVS/XA e ESA;
•
Exemplo: projeto Apolo da NASA.
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
BANCOS DE DADOS HIERÁRQUICOS
Utiliza a organização de endereços físicos do disco em sua estrutura.
Baseado em dois conceitos fundamentais:
•
segmentos (equivalentes a registros);
•
relacionamentos (ligações) pai-filho.
Um segmento pai pode ter nenhum ou vários segmentos filhos, um segmento filho
pode ter apenas um segmento pai.
Estrutura em árvore com dados em níveis hierárquicos.
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
BANCOS DE DADOS HIERÁRQUICOS
DESVANTAGENS
Em relacionamentos N:N (muitos-para-muitos) a duplicação de registros é
necessária para preservar a estrutura de árvore do banco de dados. A duplicação
de registros tem dois inconvenientes principais:
•
atualizações podem levar a inconsistência de dados;
•
desperdício de espaço.
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
BANCOS DE DADOS EM REDE
Definidos pelo DBTG (Data Base Task Group) do comitê CODASYL (Conference on
Data Systems Language) a partir de 1971.
Permitem que um mesmo registro participe de vários relacionamentos devido à
eliminação da hierarquia.
Os comandos de manipulação de registros devem ser incorporados a uma
linguagem hospedeira (COBOL, a mais comum, Pascal e FORTRAN).
Estruturas fundamentais: registros (records) e conjuntos (sets).
Registros contêm dados relacionados e são agrupados em tipos de registros que
armazenam os mesmos tipos de informações.
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
BANCOS DE DADOS RELACIONAIS
A maioria dos SGBDs atualmente em uso se enquadra no modelo relacional.
Baseia-se nos princípios matemáticos utilizados por Edgard F. Codd em 1968:
teoria dos conjuntos e da álgebra relacional.
Em 1985, Codd propôs um conjunto de doze regras para que um banco de dados
fosse considerado como relacional.
Organiza os dados em tabelas (relações) formadas por linhas e colunas.
Tabelas são similares a conjuntos de elementos: relacionam as informações de um
mesmo assunto de um modo organizado.
Facilita a navegação em grandes quantidades de dados, otimizando o uso do
hardware.
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
BANCOS DE DADOS ORIENTADOS A OBJETOS
Surgiram em meados de 1980 para armazenamento de dados complexos, não
adequados aos sistemas relacionais: GIS (Geographical Information System) e
CAD/CAM/CAE.
O modelo é caracterizado pela definição de objetos com suas propriedades e
operações (métodos).
O OMDG (Object Database Management Group) definiu um padrão de estrutura para
bancos de dados orientados a objetos.
O grupo propôs um padrão conhecido como ODMG-93, atualmente revisado e
denomidado ODMG 2.0.
Linguagens: ODL (Object Definition Language) e OQL (Object Query Language).
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
BANCOS DE DADOS ORIENTADOS A OBJETOS
O modelo de dados orientado a objetos baseia-se nos seguintes componentes:
OBJETO: É uma abstração de uma entidade real. Cada objeto representa uma única
ocorrência de uma entidade.
ATRIBUTOS: Descrevem as propriedades de um objeto.
MÉTODOS: Definem o comportamento de um objeto. Equivalem aos procedimentos
da linguagem de programação tradicional.
CLASSE: Conjunto de objetos que compartilham a mesma estrutura (atributos) e
comportamento (métodos).
HERANÇA: Capacidade de herdar atributos e métodos de classes superiores
(dentro de uma hierarquia).
© 2014 - Prof. Marcos Alexandruk
MODELO DE DADOS
© 2014 - Prof. Marcos Alexandruk
NÚMERO DE USUÁRIOS
BANCOS DE DADOS MONOUSUÁRIOS
Permitem que apenas um usuário por vez acesse o banco de dados.
Antigos (1980-1990) e direcionados a uso pessoal: dBASE III, dBASE IV, FoxBase,
FoxPro.
BANCOS DE DADOS MULTIUSUÁRIOS
Suporta o acesso de vários usuários ao mesmo tempo.
A maioria dos bancos de dados atuais oferece suporte a multiusuários.
© 2014 - Prof. Marcos Alexandruk
LOCALIZAÇÃO
BANCOS DE DADOS CENTRALIZADOS
Localizados em uma única máquina denominada Servidor de Banco de Dados.
Embora centralizados, podem oferecer suporte a acesso concorrente de vários
usuários.
BANCOS DE DADOS DISTRIBUÍDOS
O sistema gerenciador e o banco de dados estão localizados em diferentes
máquinas interligadas em redes (LANS ou WANS).
Independentemente de serem centralizados ou distribuídos os SGBDs atualmente
trabalham dentro da arquitetura cliente-servidor.
© 2014 - Prof. Marcos Alexandruk
HETEROGÊNEOS
BANCOS DE DADOS HETEROGÊNEOS
Tendência que vem crescendo muito atualmente.
Envolve basicamente distribuir na arquitetura de SGBDs vários
bancos de dados de fornecedores diferentes.
© 2014 - Prof. Marcos Alexandruk
HETEROGÊNEOS
RESUMO
© 2014 - Prof. Marcos Alexandruk
HETEROGÊNEOS
Quais os tipos de classificações dos bancos de dados?
Podem ser classificados quanto a(o):
• MODELO DE DADOS
• NÚMERO DE USUÁRIOS
• LOCALIZAÇÃO
© 2014 - Prof. Marcos Alexandruk
HETEROGÊNEOS
Como são classificados os bancos de dados quanto aos
MODELOS DE DADOS?
• HIERÁRQUICOS
• EM REDE
• RELACIONAIS
• ORIENTADOS A OBJETOS
© 2014 - Prof. Marcos Alexandruk
HETEROGÊNEOS
Como são classificados os bancos de dados quanto ao NÚMERO
DE USUÁRIOS?
• MONOUSUÁRIOS
• MULTIUSUÁRIOS
© 2014 - Prof. Marcos Alexandruk
HETEROGÊNEOS
Como são classificados os bancos de dados quanto a sua
LOCALIZAÇÃO?
• CENTRALIZADOS
• DISTRIBUÍDOS
© 2014 - Prof. Marcos Alexandruk
Aula 2: Use AVA
Atualmente ocorre uma necessidade crescente de gerenciamento
de dados não estruturados, como os encontrados na maioria dos
documentos e páginas da web.
Quais soluções são utilizadas para o gerenciamento de dados não
estruturados?
© 2014 - Prof. Marcos Alexandruk
Sistemas Gerenciadores de Banco de Dados
Aula 3
Prof. Marcos Alexandruk
Especificações dos principais SGBDs
Principais SGBDs:
• dBase
• Paradox
• DataFlex
• FoxBase / FoxPro
• Access
• InterBase
• FireBird
• Informix
• Sybase
• Oracle Database
• SQL Server
• DB2
• MySQL
• PostgreSQL
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
dBase
Lançado em 1984 pela Ashton-Tate (adquirido, em 1991, pela
Borland)
Apresentava linguagem de programação fácil de aprender, tornava
possível criar aplicações inteiras
Exemplo: para listar dados de um banco, o comando era: LIST ALL
campo1, campo2, campoN TO PRINT
Preparado para arquitetura de hardware em baixa plataforma
(Apple II, Apple Macintosh, IBM-PC)
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
dBase
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
Paradox
Lançado em 1985 pela Ansa Software (adquirida em 1987 pela
Borland)
Apresenta um ambiente integrado de desenvolvimento para
criação de aplicativos
Utiliza o QBE (Query by Example) para consultas
Primeira versão para DOS (até a versão DOS 4.5, quando foi
lançada uma versão para o Windows)
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
Paradox
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
Dataflex
Produzido pela empresa Data Access Corporation, fundada em 1976
Apresentava versões para mainframes e microcomputadores (com
versões para UNIX, VAX/VMS, Novell Netware, CP/M, DOS, OS/2 e Linux)
Possui um ambiente de desenvolvimento VDF (Visual DataFlex) disponível
para Windows
Acessa bases SQL Server, Oracle, DB2, etc.
(Visual DataFlex: ambiente gráfico de desenvolvimento semelhante ao Visual Basic)
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
FoxBase / FoxPro
Lançado em dezembro de 1984 pela Fox Software (mais tarde
adquirida pela Microsoft)
Concorria diretamente com o dBase (inclusive com arquivos-fontes
compatíveis)
Escrito para microcomputadores com plataforma DOS e mais tarde
adaptado para Windows
A versão 9 do FoxPro foi a última a ser lançada
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
Access
Lançado em 1992 pela Microsoft
Um dos primeiros SGBDs a ser lançado para uma plataforma gráfica
(Windows)
Ambiente integrado com interface intuitiva para criação e gerenciamento
do banco de dados e o desenvolvimento de aplicações e relatórios
Incluído em algumas versões do pacote MS Office
Grande integração com a linguagem de programação Visual Basic
Utilizado como banco de dados por pequenas empresas
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
InterBase
Lançado em 1984 pela Groton Database Systems (Interbase, a
partir de 1986, a empresa passou a ser controlada, em 1991, pela
Borland)
Comercializado atualmente através da Embarcadero Technologies
A versão 6.0 deu origem ao FireBird (open source)
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
FireBird
A versão 6.0 do InterBase deu origem ao FireBird (open source)
Versão atual: FireBird 2.5.3, lançada em julho de 2014
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
Informix
Projetado por Roger Sippl no final dos anos 1970
A Informix foi fundada em 1980 e tornou-se pública em 1986
Na década de 1990 era o segundo banco mais popular (depois do
Oracle)
Em 2001 a IBM, por sugestão do Wal-Mart (o maior usuário do
Informix), adquiriu a Informix
Em meados de 2005, a IBM lançou a versão 10 do Informix IDS
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
Sybase
Fundada por Mark Hoffman e Bob Epstein em 1984, em Berkeley,
na Califórnia
A empresa atua em 120 países e tem mais de 82.000 clientes.
2010: A SAP adquire a Sybase por US$ 5,8 bilhões
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
Oracle Database
1977: Larry Ellison, Bob Miner e Ed Oates fundam a SDL (Software Level
Laboratories)
1978: O nome da empresa é mudado para RSI (Rational Software Inc.)
1979: A RSI lança o primeiro produto comercial de banco de dados
relacional utilizando a linguagem SQL
1983: Lançado o Oracle 3, o primeiro SGBD a rodar em mainframes e em
minicomputadores
2013: Última versão: Oracle 12c (c: cloud)
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
SQL Server
Lançado pela Microsoft em 1988
Inicialmente era uma versão especial do Sybase (parceria com a
Microsoft, encerrada em 1994)
SQL Server 2005: grande integração com a plataforma .NET
Versão atual: SQL Server 2014 (oferece novos recursos em nuvem)
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
IBM DB2
Projeto começou no início dos anos 70 (Edgard Frank Codd – IBM)
A princípio o produto foi chamado System R
Lançado em 1983 com base no SQL/DS (para mainframe)
A partir da década de 1990 inclui versões para Windows, Linux e PDAs
2006: Lançamento do DB2 9 Express
DB2 9 foi o primeiro SGBD segundo a IBM a armazenar XML nativo
Última versão: DB2 10.5 (Linux, Windows, etc.) / DB2 11 (z/OS)
(z/OS é um sistema operacional de 64 bits para mainframes, sucessor do OS/390)
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
MySQL
Desenvolvido a partir de 1995 por David Axmark, Allan Larsson e
Michael Widenius
2008: Sun adquire a MySQL (US$ 1 bilhão)
2009: Oracle compra a Sun (US$ 7,8 bilhões)
Licença: GNU-GPL (General Public License)
Versões para Windows, LINUX, UNIX, FreeBSD e Mac OS X
Muito utilizado em soluções para Web
© 2014 - Prof. Marcos Alexandruk
PRINCIPAIS SGBDs
PostgreSQL
Origem: Projeto Postgre, Universidade Berkeley, Califórnia. Equipe
orientada pelo Prof. Michael Stonebraker
1988: primeira versão estável
1991: Código adquirido pela Illustra, a qual se fundiu com a
Informix (de Stonebraker), hoje pertencente à IBM
Licença: BSD (Berkeley Software Distribution)
Versões para LINUX, UNIX, Mac OS X e Windows
© 2014 - Prof. Marcos Alexandruk
Aula 3: Use AVA
Analise o seguinte cenário: Você foi contratado para cuidar do
banco de dados de uma empresa de médio porte que está
iniciando as suas atividades. A empresa optou por trabalhar com
software livre, adotando o sistema operacional Linux.
Você é chamado a opinar sobre que sistema de gerenciamento de
banco de dados deve ser adotado. Qual seria a sua opinião e como
você a justificaria?
© 2014 - Prof. Marcos Alexandruk
Sistemas Gerenciadores de Banco de Dados
Aula 4
Prof. Marcos Alexandruk
Arquitetura dos principais SGBDs
 Oracle Database
 Microsoft SQL Server
 IBM DB2
 MySQL
 PostgreSQL
© 2014 - Prof. Marcos Alexandruk
Oracle Database
© 2014 - Prof. Marcos Alexandruk
Oracle Database
A arquitetura do Oracle é composta de duas partes básicas:
 Instância (Instance)
 Banco de Dados (Database)
© 2014 - Prof. Marcos Alexandruk
Oracle Database
INSTÂNCIA
A Instância é composta por:
 SGA (System Global Area) - Área Global do Sistema
 Background Processes - Processos de Segundo Plano
© 2014 - Prof. Marcos Alexandruk
Oracle Database
SGA (System Global Area)
 É área de memória usada para armazenar informações
compartilhadas pelos processos do banco de dados
 Está alocada na memória virtual do computador.
© 2014 - Prof. Marcos Alexandruk
Oracle Database
Background Processes
 Executam
tarefas
de
E/S
e
monitoram
outros
processos do servidor
 Ajudam a garantir maior paralelismo e integridade dos
dados, aumentando o desempenho e a confiabilidade
do sistema
© 2014 - Prof. Marcos Alexandruk
Oracle Database
Estruturas físicas e lógicas
 O banco de dados Oracle possui uma estrutura física e
uma estrutura lógica.
© 2014 - Prof. Marcos Alexandruk
Oracle Database
Estrutura física
 A estrutura física consiste em três tipos de arquivos:
 Data files
Control Files
Redo Log Files
© 2014 - Prof. Marcos Alexandruk
Oracle Database
Estrutura lógica
 A estrutura lógica do Oracle inclui:
 Tablespaces
 Segmentos
 Extensões
 Blocos de dados
© 2014 - Prof. Marcos Alexandruk
Oracle Database
Hierarquia de armazenamento lógico e físico
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
Os componentes principais do Microsoft SQL Server são:
 Database Engine
 SSRS (SQL Server Reporting Services)
 SSAS (SQL Server Analysis Services)
 SSIS (SQL Server Integration Services)
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
Database Engine
 Principal serviço do SQL Server
 Permite armazenar, recuperar, processar e proteger os
dados
 Fornece acesso controlado
e
processamento
de
transações para atender aos requisitos dos aplicativos
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
SSRS (SQL Server Reporting Services)
 Fornece ferramentas e serviços prontos para criar,
implantar e gerenciar relatórios
 Inclui recursos de programação para estender as
funcionalidades e personalizar relatórios
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
SSAS (SQL Server Analysis Services)
 Fornece a solução de BI (Business Intelligence)
 Responsável por transformar uma quantidade grande
de dados em conjuntos essenciais de informação
 Inclui dois componentes:
 OLAP (On-Line Analytical Processing)
Data Mining
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
SSIS (SQL Server Integration Services)
 Plataforma
para
integração
de
dados
em
nível
corporativo e soluções de transformações de dados
 Extrai e transforma dados de diversas fontes, como
arquivos de dados XML, arquivos simples e bases de
dados relacionais e transfere para um ou mais destinos
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
Defaults Databases
 master: armazena toda a configuração do servidor e
informações das instâncias
 resource: armazena os objetos do sistema
 model: fornece o modelo para todos os bancos de dados que
serão criados
 tempdb: armazena dados temporários
 msdb: responsável pela automação de serviços
© 2014 - Prof. Marcos Alexandruk
http://technet.microsoft.com/en-us/library/bb497064.aspx
© 2014 - Prof. Marcos Alexandruk
© 2014 - Prof. Marcos Alexandruk
IBM DB2
© 2014 - Prof. Marcos Alexandruk
IBM DB2
A arquitetura do sistema da IBM possui componentes que
efetuam os controles de:
 Processos
 Memória
 Armazenamento
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Componente de Processo
 Vários processos são controlados neste componente,
o principal processo é o db2sysc, que abre outros
processos a partir de uma aplicação remota que se
conecta ao servidor pela instrução SQL CONNECT.
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Componente de Memória
 Possui áreas de memória em nível de:
 instância
 base de dados
 aplicação
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Armazenamento
 Constituído de três componentes:
 Páginas e Extents: Página é a unidade mínima de
armazenamento. Extent é composto por um conjunto de
páginas.
 Buffer Pool: Espaço de memória que mantém um cache
de dados e índices evitando perda de tempo de acesso
em operações de entrada e saída de dados em disco,
mantendo-os na memória principal.
 Tablespace: Mantém a estrutura lógica dos dados que
serão armazenados fisicamente.
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Defaults Tablespaces
 SYSCATSPACE: Contém as tabelas de catálogo. O catálogo
também é conhecido como dicionário de dados em outros
SGBDs.
 TEMPSPACE1: Usada quando é necessário espaço adicional
para a realização de algumas operações, como ordenações
(sorts).
 USERSPACE1: Usada para armazenar tabelas de usuários se
não houver a especificação de outra tablespace.
© 2014 - Prof. Marcos Alexandruk
MySQL
© 2014 - Prof. Marcos Alexandruk
MySQL
© 2014 - Prof. Marcos Alexandruk
MySQL
Compatibilidade
 O MySQL apresenta versões para os seguinte SO’s:
 Microsoft Windows
 Linux (Fedora, Debian, SuSE, RedHat)
 Unix (Solaris, HP-UX, AIX, SCO)
 FreeBSD
 Mac OS
© 2014 - Prof. Marcos Alexandruk
MySQL
Padrão SQL
 SQL-92
 SQL-99
 SQL 2003 (parcialmente)
© 2014 - Prof. Marcos Alexandruk
MySQL
Última versão
 MySQL Community Server 5.6.20
 A MySQL disponibiliza também a seguinte "ferramenta gráfica"
(GUI Tool) para administração do banco de dados:
MySQL Workbench 6.1.7
Dados atualizados em setembro/2014
© 2014 - Prof. Marcos Alexandruk
MySQL
Características
 PORTABILIDADE: Desenvolvido em C/C++ torna-se portável entre
diferentes plataformas e compiladores.
 MULTITHREADS:
Aumenta
significativamente
a
velocidade
de
processamento e facilita a integração com hardware com mais de uma
CPU.
 ARMAZENAMENTO: Disponibiliza vários tipos de tabelas para priorizar
a velocidade ou o volume de dados, entre outras características.
 VELOCIDADE: As tabelas tipo MyISAM utilizam cachês em consultas e
indexação BTREE para tabelas tipo HEAP proporcionando maior
velocidade de acesso.
© 2014 - Prof. Marcos Alexandruk
MySQL
Multithreads
 Thread é a forma de um processo dividir a si mesmo em duas ou mais
tarefas que podem ser executadas simultaneamente. O suporte à
thread é fornecido pelo próprio sistema operacional (SO), no caso da
Kernel-Level Thread (KLT), ou implementada através de uma biblioteca
de uma determinada linguagem, no caso de uma User-Level Thread
(ULT).
 Sistemas que suportam apenas uma única tarefa são chamados de
monothread e os que suportam múltiplas tarefas simultâneas são
chamados de multithread.
© 2014 - Prof. Marcos Alexandruk
MySQL
Características
 FULL TEXT SEARCH: Melhora o desempenho em consultas de grandes
quantidades de texto.
 STORED PROCEDURES: Blocos de código armazenados no servidor e que
podem ser invocados a partir de outras aplicações.
 TRIGGERS: Blocos de código armazenados no servidor são invocados
automaticamente a partir de certos eventos.
 CURSORES: Permitem a navegação em conjuntos de resultados através de
laços de repetição possibilitando realizar operações e transações à parte para
cada linha de uma tabela.
 VISÕES: Consultas pré-programadas à partir de determinadas colunas de uma
um mais tabelas.
 TRANSAÇÕES DISTRIBUÍDAS: Fornece a possibilidade de gerenciamento de
transações realizadas com a união de vários bancos de dados.
© 2014 - Prof. Marcos Alexandruk
MySQL
Características
 INTEGRIDADE REFERENCIAL: Relacionamentos entre diferentes tabelas são
gerenciados pelo banco de dados na inclusão, alteração ou exclusão de dados.
 REPLICAÇÃO: Torna possível configurar clones ou réplicas de servidores que
mantém as informações sincronizadas com um servidor principal aumentando a
disponibilidade.
 CLUSTERIZAÇÃO: Baseada na integração e sincronismo de dois ou mais
servidores para dividir a demanda e aumentar a disponibilidade. Este recurso
permite que caso o servidor primário fique indisponível, a carga gerada pelas
consultas seja balanceada entre os outros servidores restantes.
© 2014 - Prof. Marcos Alexandruk
MySQL
Métodos de armazenamento
 MyISAM
 Muito rápido
 Não apresenta restrições de tipos de dados
 Permite o uso de todos os recursos do MySQL, exceto suporte a transações
 Único mecanismo do MySQL que suporta buscas do tipo FullText Searches
 Nível de bloqueio: tabelas
© 2014 - Prof. Marcos Alexandruk
MySQL
Métodos de armazenamento
 InnoDB
 Recomendado para bancos de dados grandes e complexos
 Oferece suporte a transações ACID (Atomicidade, Consistência, Isolamento
e Durabilidade)
 Armazenamento em disco e memória dos dados e índices: processamento
mais veloz
 Nível de bloqueio: linhas. Portanto, aumenta a disponibilidade: apenas os
registros envolvidos em uma transação são bloqueados (não a tabela toda
como no MyISAM)
© 2014 - Prof. Marcos Alexandruk
MySQL
Métodos de armazenamento
 Memory (HEAP)
 O armazenamento dos dados é realizado na memória RAM
 Velocidade de processamento muito rápida: não há busca em disco
 Dados são perdidos quando o servidor é deslidado ou reinicializado
 Indicado em aplicações cujos dados devem ser armazemados apenas
temporariamente (ex: sessões)
 Nível de bloqueio: tabelas
 Não suporta dados do tipo BLOB e TEXT
 Não oferece suporte a transações e índices
© 2014 - Prof. Marcos Alexandruk
MySQL
Métodos de armazenamento
 ARCHIVE
 Dados são gravados em arquivos-texto no formato de tabelas sequenciais
(ordem de gravação)
 Utilizado em aplicações de log e outras que podem gerar grande volume de
dados (podendo ser armazenadas posteriormente em unidades de backup
mensalmente ou em outros períodos)
 Mecanismo de funcionamento limitado: suporta apenas INSERT e SELECT
 Não dá suporte a índices: cada SELECT percorre a tabela inteira para
garantir que todos os resultados foram obtidos
 Não oferece suporte a transações e índices
© 2014 - Prof. Marcos Alexandruk
MySQL
Métodos de armazenamento
 CSV (Comma Separated Values)
 Similar ao método ARCHIVE, armazena os dados em arquivos texto. Os
valores são separados por vírgula ou outro caractere definido previamente.
 O padrão CSV permite que os dados tornem-se portáveis para outras
aplicações (ex: planilhas) de forma simples e rápida
 Não oferece suporte a transações e índices
 Não recomendado o uso de tipos BLOB e TEXT, pois podem comprometer a
portabilidade
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Desenvolvimento
 O POSTGRES foi originalmente patrocinado pelo DARPA (Defense
Advanced Research Projects Agency), ARO (Army Research Office),
NSF (National Science Foundation) e ESL Inc.
 O projeto POSTGRES iniciou em 1986, já em 1987 tornou-se
operacional.
 Em 1989 foi lançada a primeira versão para o público externo.
 Em 1991 foi lançada a versão 3, com melhorias no executor de
consultas e algumas partes do código foram reescritas.
 As versões subsequentes, até o Postgres95, foram focadas em
confiabilidade e portabilidade.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Desenvolvimento
 O Postgres95 teve mudanças radicais em relação ao projeto original. O seu
código foi totalmente revisado, o tamanho dos fontes foi reduzido em 25%. A
performance foi consideravelmente melhorada e vários recursos foram
adicionados.
 Em 1996 o projeto foi rebatizado como PostgreSQL, para enfatizar a relação do
POSTGRES original com a linguagem SQL.
 A numeração da versão voltou a seguir o padrão anterior ao Postgres95
(considerada a 5.0), e a primeira versão do PostgreSQL foi a 6.0.
 Enquanto a ênfase do Postgres95 tinha sido a correção de falhas e otimização
do código, o desenvolvimento das primeiras versões do PostgreSQL foi
orientada à melhoria de recursos e implementação de novos recursos, sempre
seguindo os padrões SQL.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Desenvolvimento
 As versões 7.x lançadas a partir de maio de 2000 trouxeram as seguintes
novidades:
 WAL - Write Ahead Log (as modificações são gravadas em um log antes
de serem aplicadas)
 Schemas SQL
 Outer joins
 Suporte a IPv6
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Desenvolvimento
 A versão 8.0 foi lançada em janeiro de 2005 e entre outras novidades, foi
a primeira a ter suporte nativo para Microsoft Windows (anteriormente o
PostgreSQL só rodava de forma nativa em sistemas Unix e, em sistemas
Windows - através da biblioteca Cygwin).
 Dentre as muitas novidades da versão 8.x, pode-se destacar o suporte a
tablespaces, savepoints, roles e commit em duas fases.
 Em 24 de julho de 2014 foi lançada a versão (estável) mais recente: 9.2.9.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Desenvolvimento
 O Grupo Global de Desenvolvimento do PostgreSQL tem membros nos
Estados Unidos, Canadá, Japão, Rússia e vários outros países.
 Esse grupo é formado essencialmente por empresas especializadas em
PostgreSQL, empresas usuárias do sistema, além dos pesquisadores
acadêmicos e programadores independentes.
 Além da programação, essa comunidade é responsável pela
documentação, tradução, criação de ferramentas de modelagem e
gerenciamento, e elaboração de extensões e acessórios.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Plataformas suportadas pelo PostgreSQL:
 Unix
 Linux
 FreeBSD
 Microsoft Windows
 MacOS
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Sub-consultas
 Subconsulta é uma instrução SELECT aninhada dentro de uma
instrução SELECT, INSERT, DELETE ou UPDATE ou dentro de uma
outra subconsulta.
 EXEMPLO:
SELECT NRPEDIDO FROM PEDIDO WHERE CODCLIENTE =
(SELECT CODCLIENTE FROM CLIENTE WHERE NOMECLIENTE = 'ALFA');
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Controle de concorrência multi-versão (MVCC)
 Ao contrário de outros SGBDs que utilizam que utilizam LOCKs
para
controle
de
concorrência,
o
PostgreSQL
mantém
a
consistência dos dados usando um modelo multiversão. Neste
modelo, cada transação terá sua versão do banco de dados,
estando protegidas de acessar dados inconsistentes que poderiam
ser gerados por outras transações. Portanto, o MVCC oferece o
isolamento de transações, além de garantir que leituras nunca
aguardarão escritas e vice-versa.)
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Integridade Referencial
 A integridade referencial garante a não corrupção dos dados, de
modo a não existir um registro "filho" sem um registro "pai".
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Stored Procedures
 As procedures do PostgreSQL podem ser escritas em várias
linguagens (PL/PgSQL, Perl, Python, Ruby, e outras)
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Triggers
 Recurso de programação executado sempre que o evento
associado ocorrer.
 Utilizados para ajudar a manter a consistência dos dados ou
para propagar alterações em um determinado dado de uma
tabela para outras.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Schemas
 Schema é um espaço lógico (namespace) dentro do banco
de dados para armazenar objetos: tabelas, funções, etc.
 Este conceito é semelhante ao cross-database, a diferença é
que o cross-database relaciona objetos de banco de dados
distintos, já o Schema relaciona objetos que estão no
mesmo banco de dados, mas em estruturas lógicas distintas.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Tablespaces
 Tablespace designa uma subdivisão lógica de um banco de
dados utilizada para agrupar estruturas lógicas relacionadas.
 As tablespaces apenas especificam a localização de
armazenamento
do
banco
de
dados.
armazenadas fisicamente em datafiles.
© 2014 - Prof. Marcos Alexandruk
Os
dados
são
PostgreSQL
Recursos "recentes":
 Savepoints
 Estabelece um novo ponto de salvamento na transação
corrente.
 O ponto de salvamento é uma marca especial dentro da
transação
que
permite
desfazer
todos
os
comandos
executados após o seu estabelecimento, restaurando o
estado da transação ao que era quando o ponto de
salvamento foi estabelecido.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Recursos "recentes":
 Commit em duas fases
 Commit em duas fases refere-se a uma transação que pode utilizar
dois ou mais bancos de dados (multi-database), que podem estar
localizados em servidores diferentes.
 Durante uma transação em bancos com essa característica garantese que o commit seja realizado em todos os bancos participantes
ou em nenhum, ou seja, ou grava tudo ou não grava nada.
 Por exemplo, se sua aplicação atualiza dados em dois bancos de
dados e você faz um commit, o recurso de commit em duas fases
previne situações como a de um dos bancos ficar indisponível e
suas mudanças serem atualizadas somente em um dos bancos
envolvidos.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Capacidade:
 Tamanho máximo do banco de dados: ilimitado
 Tamanho máximo de uma tabela: 32 TB
 Tamanho máximo de uma linha: 1,6 TB
 Tamanho máximo de um campo: 1 GB
 Número máximo de linhas por tabela: ilimitado
 Número máximo de colunas por tabela: 250 a 1600 (dependendo dos tipos)
 Número máximo de índices por tabela: ilimitado
© 2014 - Prof. Marcos Alexandruk
Aula 4: Use AVA
Oracle, Microsoft e IBM lançaram versões gratuitas de seus SGBDs.
Faça uma pesquisa sobre estas versões e comente com seus
colegas sobre as principais diferenças entre elas.
Qual delas você recomendaria para uma empresa de pequeno
porte? Por que?
© 2014 - Prof. Marcos Alexandruk
Sistemas Gerenciadores de Banco de Dados
Aula 5
Prof. Marcos Alexandruk
Permissões nos principais SGBDs
 Oracle Database
 Microsoft SQL Server
 IBM DB2
 MySQL
 PostgreSQL
© 2014 - Prof. Marcos Alexandruk
Segurança e autenticação
 O controle aos dados em um SGBD deve ser realizado em três áreas:
1. QUEM pode acessar os dados
2. O QUE vai ser acessado
3. QUAL o TIPO de acesso que será permitido
 leitura
 inserção
 alteração
 exclusão
© 2014 - Prof. Marcos Alexandruk
DCL - Data Control Language
 A DCL (Data Control Language) é uma "sublinguagem" da SQL
(Structured Query Language).
 Por meio dela são controlados os acessos de usuários ou de grupos de
usuários.
 Principais comandos:
 GRANT: Concede privilégios
 REVOKE: Revoga privilégios
© 2014 - Prof. Marcos Alexandruk
Oracle Database
© 2014 - Prof. Marcos Alexandruk
Oracle
Criação de usuários
 Um usuário de banco de dados somente poderá ser criado pelo DBA ou
por outro usuário com o privilégio de sistema CREATE USER. Além de
informar o nome e a senha (provisória), é possível determinar também
quais tablespaces estarão disponíveis e até mesmo quanto espaço de
armazenamento o novo usuário poderá utilizar em cada tablespace.
 Quando um usuário cria um novo objeto no banco de dados (uma
tabela, por exemplo) este objeto fará parte de um schema (esquema)
que tem o mesmo nome do usuário.
© 2014 - Prof. Marcos Alexandruk
Oracle
Criando um usuário
 Para criar um novo usuário deve-se utilizar o comando CREATE USER, conforme
o exemplo a seguir:
CREATE USER FULANO IDENTIFIED BY ABC123
ACCOUNT UNLOCK
PASSWORD EXPIRE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
ACCOUNT UNLOCK: A conta estará desbloqueada (default).
PASSWORD EXPIRE: Usuário deverá alterar a senha no primeiro login.
DEFAULT TABLESPACE: Tablespace padrão do usuário.
TEMPORARY TABLESPACE: Tablespace temporária para este usuário.
© 2014 - Prof. Marcos Alexandruk
Oracle
Alterando um usuário
 Pode-se alterar os privilégios e outros atributos dos usuários do banco de dados
com o comando ALTER USER.
 O exemplo a seguir estabelece uma cota de 100 MB no tablespace USERS para o
usuário anteriormente criado:
ALTER USER FULANO
QUOTA 100M ON USERS;
© 2014 - Prof. Marcos Alexandruk
Oracle
Concedendo privilégios a um usuário
 Para conceder privilégios a um usuário deve-se utilizar a instrução GRANT.
 O exemplo a seguir apresenta a concessão do privilégio CONNECT ao novo
usuário que permitirá a ele conectar-se ao banco de dados:
GRANT CONNECT TO FULANO;
© 2014 - Prof. Marcos Alexandruk
Oracle
Apresentando os usuários
 Os nomes dos usuários do banco de dados podem ser obtidos através da visão
DBA_USERS. O comando a seguir apresenta os nomes dos usuários:
SELECT USERNAME FROM DBA_USERS;
 Para consultar o status de cada usuário (conta), isto é, para verificar se a conta
está ou não bloqueada, utiliza-se:
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS;
OPEN: A conta está disponível para uso;
LOCKED: A conta foi bloqueada pelo DBA;
EXPIRED: A senha expirou. O usuário deverá redefini-la;
EXPIRED & LOCKED: A conta foi bloqueada e a senha expirou.
© 2014 - Prof. Marcos Alexandruk
Oracle
Eliminando um usuário
 Para eliminar usuários do banco de dados utiliza-se o comando DROP USER.
 O exemplo a seguir elimina o usuário anteriormente criado e seus objetos
(tabelas, visões, etc.):
DROP USER FULANO CASCADE;
© 2014 - Prof. Marcos Alexandruk
Oracle
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
© 2014 - Prof. Marcos Alexandruk
SQL Server
Usuários no SQL Server
 Cada usuário tem dois níveis de nomes e cada nome está
associado a uma ID exclusiva:
 Nome de logon
 Nome do usuário do banco de dados
© 2014 - Prof. Marcos Alexandruk
SQL Server
Nome de logon
 Cada usuário autorizado a conectar-se ao SQL Server tem um
nome de logon que proporciona ao usuário o acesso a uma
instância do SQL Server. Há dois tipos de nomes de logon:
 Nomes da conta do Microsoft Windows
 Nomes de logon SQL Server
© 2014 - Prof. Marcos Alexandruk
SQL Server
Nomes da conta do Microsoft Windows
 Os membros sysadmin ou securityadmin, usando sp_grantlogin,
podem autorizar as contas do Windows de usuários individuais ou os
grupos do Windows a se conectarem a uma instância do SQL Server.
 O usuário identificado pela conta do Windows ou qualquer pessoa no
grupo do Windows pode, então, se conectar a uma instância do SQL
Server usando a Autenticação do Windows.
 Cada conta do Windows ou nome de grupo é armazenado em
sys.server_principals.
 O identificador de segurança do Windows para a conta ou grupo do
Windows é armazenado em sys.server_principals.sid.
© 2014 - Prof. Marcos Alexandruk
SQL Server
Nomes de logon SQL Server
 São usados quando o usuário se conecta usando a Autenticação do
SQL Server. Os nomes de logon do SQL Server são definidos pelos
sysadmin ou securityadmin usando sp_addlogin.
 Cada nome de logon do SQL
master.dbo.syslogins.loginname.
Server
é
armazenado
em
 O SQL Server gera um GUID que é usado como identificador de
segurança e o armazena em sys.server_principals.sid.
 O SQL Server usa sys.server_principals.sid como o security_identifier
para o nome de logon.
© 2014 - Prof. Marcos Alexandruk
SQL Server
Nome do usuário do banco de dados
 Cada conta do Windows ou logon do SQL Server deve estar associada a um
nome de usuário em cada banco de dados que o usuário estiver autorizado a
acessar, ou o banco de dados deve ter um acesso de convidado habilitado.
 Os nomes de usuário são definidos pelos db_owner ou db_accessadmin e são
armazenados na tabela sys.database_principals disponível em cada banco de
dados.
 Cada nome de usuário de banco de dados está associado a uma ID de usuário
de banco de dados armazenada em sys.database_principals.uid.
 O
security
identifier
para
cada
usuário
é
armazenado
em
sys.database_principals.sid; portanto, os usuários podem ser mapeados de
volta para os seus logons associados.
 Pode-se utilizar o mesmo nome do usuário do banco de dados para o logon do
SQL Server ou para a conta do Windows, porém, isso não é obrigatório.
© 2014 - Prof. Marcos Alexandruk
SQL Server
Permissões
 Determinam as ações que os usuários podem executar no SQL Server ou em um
banco de dados.
 Permissões de objeto:
 Nível de servidor: servidores, logins, funções de servidor, etc.
 Nível de banco de dados: stored proocedures, functions, schemas, etc.
 Permissões de instrução:
 Criação de banco de dados, tabelas, visões, etc.
 Permissões implícitas:
 Apenas membros de funções de sistema predefinidas e proprietários de
bancos de dados ou objetos de banco de dados têm permissões
implícitas. Permitem que executem todas atividades no banco de dados
e nos seus objetos (exemplo: ver, adicionar, alterar e excluir dados).
© 2014 - Prof. Marcos Alexandruk
SQL Server
Funções
 Parecidas com os grupos de segurança do Windows, permitem atribuir
permissões a um grupo de usuários e podem ter permissões implícitas
(que não podem ser alteradas). Há dois tipos de funções:
 Funções de servidor
 Funções de banco de dados
© 2014 - Prof. Marcos Alexandruk
SQL Server
Funções de servidor
 Utilizadas para garantir recursos de administração de servidor:
 bulkadmin: Destinada às contas que precisam fazer inserções em massa no
banco de dados.
 dbcreator: Destinada aos usuários que precisam criar, modificar, eliminar e
restaurar bancos de dados.
 diskadmin: Destinada aos usuários que precisam gerenciar arquivos de disco.
 processadmin: Destinada aos usuários que precisam controlar processos do
SQL Server.
 securityadmin: Destinada aos usuários que precisam gerenciar logins, criar
permissões de banco de dados e ler logins de erros.
 serveradmin: Destinada aos usuários que precisam definir opções de
configuração em nível de servidor e encerrar o servidor.
 setupadmin: Destinada aos usuários que precisam gerenciar servidores
vinculados e controlar procedimentos de inicialização.
 sysadmin: Destinada aos usuários que precisam de controle total sobre o
SQL Server e os banco de dados instalados.
© 2014 - Prof. Marcos Alexandruk
SQL Server
Funções de banco de dados
 São definidas para CADA banco de dados:
 public: padrão para todos os usuários do banco de dados. Permissões
atribuídas à função public ficarão disponíveis a TODOS os usuários do banco.
 db_acessadmin: Destinada a usuários que precisam adicionar ou remover
logins.
 db_backupoperator: Destinada aos usuários que precisam fazer backup de
um banco de dados.
 db_datareader: Destinada aos usuários que precisam ver os dados de um
banco de dados.
 db_writer: Destinada aos usuários que precisam adicionar dados em qualquer
tabela de usuário do banco de dados.
 db_ddladmin: Destinada aos usuários que precisam executar tarefas
relacionada à DDL (Definition Data Language).
 db_denydatareader: Destinada a restringir o acesso aos dados em um banco
de dados pelo login.
© 2014 - Prof. Marcos Alexandruk
SQL Server
Funções de banco de dados (continuação)
 db_denydatawriter: Destinada a restringir as permissões de modificação em
um banco de dados pelo login.
 db_owner: Destinada a usuários que precisam de controle total ao banco de
dados.
 db_securityadmin: Destinada aos usuários
permissões, posse de objetos e funções.
que
precisam
gerenciar
 dbm_monitor: Destinada aos usuários que precisam monitor o status atual do
espelhamento de banco de dados.
© 2014 - Prof. Marcos Alexandruk
SQL Server
Criação de usuários com o utilitário SQLCMD
 Abrir o prompt de comando do Windows.
 Acessar o SQLCMD:
SQLCMD -S .\SQLEXPRESS
SQLEXPRESS = Nome da Instância
 Criar um login:
CREATE LOGIN FULANO WITH PASSWORD = 'ABC123';
GO
 Criar um usuário de banco de dados para o login acima:
CREATE USER FULANO FOR LOGIN FULANO;
GO
© 2014 - Prof. Marcos Alexandruk
IBM DB2
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Segurança
 Há três níveis de segurança para acesso ao SGBD IBM DB2:
 Autenticação:
 O acesso à instância é gerenciado por um sistema externo ao SGBD.
 Pode estar incorporado ao sistema operacional ou a outro produto.
 Autoridade:
 Após a autenticação positiva o acesso aos dados é definido e
gerenciado pelo DB2.
 Privilégio:
 Atribuídos aos usuários para que possam trabalhar com os objetos do
banco de dados (SELECT, UPDATE, etc.)
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Autenticação
 O parâmetro AUTHENTICATION no DBM CFG, acionado no servidor
DB2, tem um leque de valores possíveis. Por exemplo, quando o
parâmetro é configurado como SERVER (por omissão), a autenticação
é realizada pelo sistema operacional ou mecanismo exterior de
segurança no servidor.
 No entanto, se a AUTHENTICATION é configurada como CLIENT, a
autenticação é realizada pelo sistema operativo ou mecanismo exterior
de segurança no cliente.
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Autenticação
 O parâmetro AUTHENTICATION pode configurado com um dos seguintes valores:
 SERVER (default): Autenticação ocorre no servidor
 CLIENT: Autenticação ocorre no cliente
 SERVER_ENCRYPT: Igual ao SERVER mas os users Ids e as passwords
estão encriptadas
 KERBEROS: Autenticação
segurança Kerberos
ocorre
usando
o
mecanismo
externo
de
 SQL_AUTHENTICATION_DATAENC: A autenticação é efetuada no servidor e
as ligações tem que usar encriptação de dados
 SQL_AUTHENTICATION_DATAENC_CMP: Como a anterior,
encriptação de dados que apenas é usada quando disponível
exceto
na
 GSSPLUGIN: Autenticação utiliza um mecanismo de segurança externo GSS
API-based (Generic Security Services Application Program Interface)
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Grupo PUBLIC
 O DB2 define um grupo interno chamado PUBLIC. Qualquer usuário identificado
pela autenticação do sistema operacional ou da rede é implicitamente um
membro do grupo PUBLIC.
 Quando uma base de dados é criada, certos privilégios estão garantidos
automaticamente ao grupo PUBLIC:
 CONNECT
 CREATETAB
 IMPLICIT SCHEMA
 BINDADD
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Grupo PUBLIC
 Para segurança adicional, é recomendável revogar estes privilégios do
grupo PUBLIC como segue:
 REVOKE CONNECT ON DATABASE FROM PUBLIC
 REVOKE CREATETAB ON DATABASE FROM PUBLIC
 REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC
 REVOKE BINDADD ON DATABASE FROM PUBLIC
© 2014 - Prof. Marcos Alexandruk
IBM DB2
Consultando privilégios
 Pode-se fazer uma consulta às views do catálogo DB2 SYSCAT para verificar os
privilégios de determinado usuário.
 Por exemplo, se quisermos saber se o usuário DB2ADMIN possui o privilégio
SELECT na tabela TESTE, e saber quem garantiu este privilégio, podemos correr
a seguinte consulta:
SELECT grantor, grantee, selectauth
FROM syscat.tabauth
WHERE tabname = 'TESTE'
GRANTOR
GRANTEE
SELECTAUTH
------------------------------ALUNO
DB2ADMIN
Y
 No exemplo acima, o usuário ALUNO garantiu o privilégio SELECT ao usuário
DB2ADMIN.
© 2014 - Prof. Marcos Alexandruk
MySQL
© 2014 - Prof. Marcos Alexandruk
MySQL
Privilégios de usuários
 Os dados referentes aos privilégios dos usuários do SGBD MySQL, alterados
pelas instruções GRANT e REVOKE são armazenados no banco de dados
denominado mysql.
 Cinco tabelas no banco de dados mysql têm relação com privilégios de usuários:
 user
 db
 host
 tables_priv
 columns_priv
© 2014 - Prof. Marcos Alexandruk
MySQL
Privilégios de usuários
 Estas tabelas podem ser consultadas para solucionar eventuais problemas
referentes a privilégios.
 Ao invés de usar os comandos GRANT e REVOKE o administrador pode
modificar diretamente as tabelas.
 Porém, neste caso, para que as alterações entrem em vigor, precisará executar a
instrução:
flush privileges;
© 2014 - Prof. Marcos Alexandruk
MySQL
Tabela user
 A tabela user contém informações sobre o conjunto de privilégios globais de um
usuário. Esta tabela contém as seguintes colunas:
 Colunas de escopo: Usadas para determinar quando uma linha é relevante.
As colunas de escopo são: Host, User e Password.
 Colunas de privilégios: Cada uma corresponde a um dos privilégios globais.
Podem ter o valor Y (se o usuário tiver o privilégio) ou N (caso não o tenha).
Algumas colunas de privilégios são: Select_priv, Insert_priv, Update_priv,
Delete_priv, etc.
 Colunas de conexão segura: Representam as informações da cláusula
REQUIRE da instrução GRANT. As colunas são: ssl_type, ssl_cypher,
x509_issuer e x509_subject.
 Colunas de limite de recursos: Representam qualquer limite no uso de
recursos especificados no final da cláusula GRANT. As colunas são:
max_questions, max_updates e max_connections.
© 2014 - Prof. Marcos Alexandruk
MySQL
Tabela db
 A tabela db armazena os privilégios de um usuário para determinado banco de
dados. Esta tabela contém as seguintes colunas:
 Colunas de escopo: Usadas para determinar quando uma linha é relevante.
Caso as regras sejam diferentes para cada um dos hosts o campo deve ser
deixado em branco e as regras devem ser declaradas na tabela host. As
colunas de escopo são: Host, Db e User.
 Colunas de privilégios: Especificam se a combinação Host, DB e User têm
cada um dos privilégios listados. Podem ter o valor Y ou N. Algumas
colunas de privilégios são: Select_priv, Insert_priv, Update_priv, Delete_priv,
etc.
© 2014 - Prof. Marcos Alexandruk
MySQL
Tabela host
 A tabela host é consultada quando o MySQL encontra uma entrada em branco
na coluna Host da tabela db. Esta tabela contém as seguintes colunas:
 Colunas de escopo: Usadas para determinar quando uma linha é relevante.
Cada linha fornece informações para um único banco de dados acessar a
partir de um host. As colunas de escopo são: Host e Db.
 Colunas de privilégios: Especificam se a combinação Host e DB têm cada
um dos privilégios listados. Podem ter o valor Y ou N. Algumas colunas de
privilégios são: Select_priv, Insert_priv, Update_priv, Delete_priv, etc.
© 2014 - Prof. Marcos Alexandruk
MySQL
Tabela tables_priv
 A tabela tables_priv apresenta os privilégios de usuário com relação às tabelas
individuais. Esta tabela contém as seguintes colunas:
 Colunas de escopo: Usadas para determinar quando uma linha é relevante.
Cada linha fornece informações para um único banco de dados acessar a
partir de um host. As colunas de escopo são: Host, Db, User e Table_name.
 Colunas de concessão: Especificam que concedeu o privilégio e quando. As
colunas de concessão são: Grantor e Timestamp.
 Coluna Table_priv: Determina quais privilégios a combinação Host, Db e
User têm na tabela listada em Table_name. Pode conter os seguintes
valores: Select, Insert, Update, Delete, Create, Alter, Drop, Grant, References
e Index.
 Coluna Column_priv: Informa quais privilégios o usuário tem sobre as
colunas da tabela listada em Table_name. Pode conter os seguintes valores:
Select, Insert, Update e References.
© 2014 - Prof. Marcos Alexandruk
MySQL
Tabela columns_priv
 A tabela columns_priv apresenta os privilégios de usuário com relação às
colunas individuais. Esta tabela contém as seguintes colunas:
 Colunas de escopo: Usadas para determinar quando uma linha é relevante.
Cada linha fornece informações para um único banco de dados acessar a
partir de um host. As colunas de escopo são: Host, Db, User, Table_name e
Column_Name.
 Coluna Column_priv: Informa quais privilégios foram concedidos para a
combinação determinada nas colunas de escopa. Pode conter os seguintes
valores: Select, Insert, Update e References.
 Coluna Timestamp: Informa quando o privilégio foi concedido.
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Criando um usuário
 Para criar um usuário deve-se utilizar o seguinte comando:
CREATE USER FULANO WITH PASSWORD 'ABC123';
 Para criar um usuário com senha válida até 2015 deve-se utilizar o seguinte
comando:
CREATE USER FULANO WITH PASSWORD 'ABC123'
VALID UNTIL '2015-12-31';
 Para criar um com permissão de criar bancos de dados deve-se utilizar o
seguinte comando:
CREATE USER FULANO WITH PASSWORD 'ABC123'
CREATEDB;
© 2014 - Prof. Marcos Alexandruk
PostgreSQL
Alterando atributos do usuário
 Para renomear um usuário deve-se utilizar o seguinte comando:
ALTER USER FULANO RENAME TO BELTRANO;
 Para modificar a senha de um usuário deve-se utilizar o seguinte comando:
ALTER USER FULANO WITH PASSWORD 'XYZ123';
 Para permitir que um usuário crie usuários e bancos de dados deve-se utilizar o
seguinte comando:
ALTER USER FULANO CREATE USER CREATEDB;
© 2014 - Prof. Marcos Alexandruk
Aula 5: Use AVA
MariaDB, SGBD criado por um dos fundadores do MySQL, Michael
Widenius, está sendo adotado por grandes empresas como o
Google. Faça uma pesquisa sobre o MariaDB destacando aos
colegas os pontos que você achou interessante principalmente no
que diz respeito à segurança.
© 2014 - Prof. Marcos Alexandruk
Sistemas Gerenciadores de Banco de Dados
Aula 6
Prof. Marcos Alexandruk
Aula 6
Desenvolvendo um pequeno banco de dados
Oracle
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Tablespaces
•
O Oracle apresenta três tipos principais de tablespaces:
• Permanente: contém segmentos que persistem além da duração de
uma transação ou sessão;
• Undo: armazenam valores anteriores a uma inclusão, atualização
ou exclusão. Um banco de dados pode ter mais de um tablespace
de undo, mas apenas um pode estar ativo em um dado momento.
• Temporário: contém dados transitórios que só existem enquanto
durar a sessão, alocando, por exemplo, espaço para concluir uma
classificação de dados que não cabe na memória.
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Nomes dos tablespaces
• Consultar nomes dos tablespaces:
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
TABLESPACE_NAME
-----------------------------SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Tipos de gerenciamento de tablespaces
•
Os tablespaces podem ser gerenciados por dicionário ou localmente.
•
Se o tablespace SYSTEM for gerenciado localmente todos os outros
tablespaces, exceto os que sejam somente de leitura (read only),
devem obrigatoriamente ser gerenciados localmente.
•
Verificar o tipo de gerenciamento de extensões do tablespace SYSTEM:
SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT FROM
DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'SYSTEM';
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Criando um tablespace permanente gerenciado localmente
•
Criar um TABLESPACE gerenciado localmente:
CREATE TABLESPACE TESTE1
DATAFILE 'E:\DADOS1.DBF' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
•
UNIFORM SIZE indica que todas as extensões terão o mesmo tamanho.
O tamanho (SIZE) default é 1MB.
•
Consultar os nomes dos tablespaces:
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Criando um tablespace permanente gerenciado localmente
•
Se a cláusula UNIFORM SIZE for omitida, será assumido o valor default:
AUTOALLOCATE (alocação das extensões gerenciada pelo Oracle).
•
Criar o tablespace TESTE2 com a opção AUTOALLOCATE:
CREATE TABLESPACE TESTE2
DATAFILE 'E:\DADOS2.DBF' SIZE 10M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
•
Consultar os nomes dos tablespaces:
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Criando tablespaces de UNDO e TEMPORÁRIOS
•
Criar um TABLESPACE de UNDO:
CREATE UNDO TABLESPACE UNDOTBS2
DATAFILE 'E:\UNDOTBS02.ORA' SIZE 10M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
•
Para criar um TABLESPACE TEMPORÁRIO:
CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE 'E:\TEMP2.ORA' SIZE 10M
EXTENT MANAGEMENT LOCAL;
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Alterando um tablespace
• Incluir mais um arquivo de dados (datafile) em um tablespace:
ALTER TABLESPACE TESTE2
ADD DATAFILE 'E:\DADOS3.DBF' SIZE 10M;
© 2014 - Prof. Marcos Alexandruk
Tablespaces
Eliminando um tablespace
• Eliminar um tablespace:
DROP TABLESPACE TESTE1
INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
•
INCLUDING CONTENTS: o tablespace será eliminado mesmo que contenha
segmentos. A operação fracassará se houverem segmentos de undo ou
temporários ativos.
•
AND DATAFILES: utilizado com INCLUDING CONTENTS, força a eliminação
física dos arquivos de dados (datafiles) que compõem o tablespace.
•
CASCADE CONSTRAINTS: Elimina constraints relacionadas a tabelas do
tablespace que está sendo eliminado que estejam em outro tablespace.
© 2014 - Prof. Marcos Alexandruk
Datafiles
Datafiles
•
Cada arquivo de dados (datafile) do Oracle corresponde a um arquivo
físico do sistema operacional.
•
Um tablespace pode ser composto por vários arquivos de dados,
porém um arquivo de dados é membro de somente um tablespace.
•
Para alterar o tamanho de um datafile deve-se utilizar o comando
ALTER DATABASE.
•
Alterar o tamanho do arquivo DADOS1.DBF para 20 MB:
ALTER DATABASE
DATAFILE 'E:\DADOS2.DBF' RESIZE 20M;
© 2014 - Prof. Marcos Alexandruk
Segmentos
Segmentos
•
Um segmento é composto por um grupo de extensões e abrange um objeto (tabela, índice, etc.).
•
Criar uma tabela no tablespace USERS:
CREATE TABLE CLIENTE (
CODIGO NUMBER(4),
NOME VARCHAR2(30))
TABLESPACE USERS;
Foi alocado um segmento que recebeu o mesmo nome da tabela (coluna SEGMENT_NAME):
SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
WHERE TABLE_NAME = 'CLIENTE';
TABLE_NAME
TABLESPACE_NAME
------------------- -----------------CLIENTE
USERS
SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM USER_SEGMENTS
WHERE TABLESPACE_NAME = 'USERS';
SEGMENT_NAME
SEGMENT_TYPE
TABLESPACE_NAME
------------------ ------------------- ------------------CLIENTE
TABLE
USERS
© 2014 - Prof. Marcos Alexandruk
Extensões
Extensões
•
Extensões são formadas por um ou mais blocos de dados. Quando um objeto
do banco de dados é expandido são alocadas mais extensões.
•
Verificar o tipo de gerenciamento de extensões em cada tablespace:
SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT FROM DBA_TABLESPACES;
TABLESPACE_NAME
-----------------------------SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
EXTENT_MAN
---------LOCAL
LOCAL
LOCAL
LOCAL
LOCAL
LOCAL
© 2014 - Prof. Marcos Alexandruk
Blocos
Blocos
•
Blocos são as menores estruturas de armazenamento no banco de dados Oracle.
•
Um bloco de dados pode ser constituído de um ou mais blocos do sistema
operacional.
•
Verificar o tamanho dos blocos em cada tablespace:
SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES;
TABLESPACE_NAME
BLOCK_SIZE
------------------------------ ---------SYSTEM
8192
SYSAUX
8192
UNDOTBS1
8192
TEMP
8192
USERS
8192
EXAMPLE
8192
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Autenticação de usuários
• Os usuários podem ser autenticados no banco de
dados através de três métodos diferentes:
• através do banco de dados;
• através do sistema operacional;
• através da rede.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Criação de usuários
• Quando um usuário cria um novo objeto no banco de dados
(uma tabela, por exemplo) este objeto fará parte de um schema
(esquema) que tem o mesmo nome do usuário.
• Um usuário de banco de dados somente poderá ser criado pelo
DBA ou por outro usuário com o privilégio de sistema CREATE
USER. Além de informar o nome e a senha (provisória), é
possível
determinar
também
quais
tablespaces
estarão
disponíveis e até mesmo quanto espaço de armazenamento o
novo usuário poderá utilizar em cada tablespace.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Criando um usuário
•
Criar um novo usuário:
CREATE USER FULANO IDENTIFIED BY ABC123
ACCOUNT UNLOCK
PASSWORD EXPIRE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
ACCOUNT UNLOCK: A conta estará desbloqueada (default).
PASSWORD EXPIRE: Usuário deverá alterar a senha no primeiro login.
DEFAULT TABLESPACE: Tablespace padrão do usuário.
TEMPORARY TABLESPACE: Tablespace temporária para este usuário.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Alterando um usuário
•
Pode-se alterar os privilégios e outros atributos dos usuários do banco
de dados com o comando ALTER USER.
•
Estabelecer para o usuário uma cota de 100 MB no tablespace USERS:
ALTER USER FULANO
QUOTA 100M ON USERS;
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Concedendo privilégios a um usuário
•
Para conceder privilégios a um usuário deve-se utilizar a instrução
GRANT.
•
O exemplo a seguir apresenta a concessão do privilégio CONNECT ao
novo usuário que permitirá a ele conectar-se ao banco de dados:
GRANT CONNECT TO FULANO;
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Eliminando um usuário
•
Para eliminar usuários utiliza-se o comando DROP USER.
•
Eliminar o usuário e seus objetos (tabelas, visões, etc.):
DROP USER FULANO CASCADE;
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Apresentando os usuários
•
Nomes dos usuários podem ser obtidos através da visão DBA_USERS:
SELECT USERNAME FROM DBA_USERS;
•
Consultar o status de cada usuário (conta):
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS;
OPEN: A conta está disponível para uso;
LOCKED: A conta foi bloqueada pelo DBA;
EXPIRED: A senha expirou. O usuário deverá redefini-la;
EXPIRED & LOCKED: A conta foi bloqueada e a senha expirou.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
Conectando-se como outro usuário
•
Há casos em que o DBA precisará conectar-se como outro usuário para
resolver determinados problemas.
EXERCÍCIO
•
Utilize o roteiro a seguir e observe como isso pode ser feito.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de usuários
1. O DBA (conectado como SYSTEM) obtém a senha conforme gerada por um algoritmo HASH:
SELECT NAME, PASSWORD FROM SYS.USER$ WHERE NAME = 'FULANO';
NAME
PASSWORD
------------------------- ----------------FULANO
22DD56A22A50F1B8
2. O DBA copia a senha em um arquivo texto.
3. O DBA (conectado como SYTEM) altera temporariamente a senha do usuário:
ALTER USER FULANO IDENTIFIED BY SENHA_TEMP;
4. O DBA conecta-se ao banco utilizando a senha temporária:
CONNECT FULANO/SENHA_TEMP;
5. O DBA realiza as operações necessárias na conta do usuário:
6. O DBA (conectado como SYSTEM) 'repõe' a senha original do usuário:
ALTER USER FULANO IDENTIFIED BY VALUES '22DD56A22A50F1B8';.
© 2014 - Prof. Marcos Alexandruk
Perfis de usuários
Criando um perfil de usuário
•
Alteração necessária para que os limites relacionados ao recursos de
sistema possam ser controlados através de perfis de usuários:
ALTER SYSTEM SET resource_limit = TRUE;
•
Criar um PROFILE denominado LIMITE_LOGIN para limitar o número de
vezes consecutivas que um login pode falhar antes de bloquear a senha
do usuário:
CREATE PROFILE LIMITE_LOGIN
LIMIT FAILED_LOGIN_ATTEMPTS 3;
•
Criar um PROFILE denominado LIMITE_CONEXAO para limitar o tempo
de conexão a um usuário em trinta minutos:
CREATE PROFILE LIMITE_CONEXAO
LIMIT CONNECT_TIME 30;
© 2014 - Prof. Marcos Alexandruk
Perfis de usuários
Criando um perfil de usuário
•
Quando não for especificado nenhum perfil para um novo usuário do
banco de dados o Oracle aplica a este o perfil DEFAULT.
•
Para conhecer quais são os limites do perfil DEFAULT deve-se utilizar a
seguinte consulta ao dicionário de dados:
SELECT * FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT';
© 2014 - Prof. Marcos Alexandruk
Perfis de usuários
Controle de recursos
•
Parâmetros relacionados aos recursos que poderão ser utilizados pelos usuários:
© 2014 - Prof. Marcos Alexandruk
Perfis de usuários
Controle de senhas dos usuários
•
Parâmetros relacionados à administração de senhas de usuários.
© 2014 - Prof. Marcos Alexandruk
Perfis de usuários
Alterando um perfil de usuário
• Alterar o parâmetro FAILED_LOGIN_ATTEMPTS:
ALTER PROFILE LIMITE_LOGIN
LIMIT FAILED_LOGIN_ATTEMPTS 5;
•
Alterar o tempo de conexão de um usuário para sessenta minutos:
ALTER PROFILE LIMITE_CONEXAO
LIMIT CONNECT_TIME 60;
© 2014 - Prof. Marcos Alexandruk
Perfis de usuários
Associando um usuário a um perfil
•
Associar um usuário a um determinado perfil:
-- Ao criar o usuário:
CREATE USER FULANO IDENTIFIED BY 'ABC123'
PROFILE NOME_PERFIL;
-- Para usuário criado anteriormente:
ALTER USER FULANO PROFILE NOME_PERFIL;
•
Consultar a que perfil um usuário está relacionado:
SELECT USERNAME, PROFILE FROM DBA_USERS
WHERE USERNAME = 'FULANO';
© 2014 - Prof. Marcos Alexandruk
Privilégios de sistema
•
Privilégios de sistema envolvem os direitos de realizar determinadas
ações sobre objetos do banco de dados e alteração de parâmetros de
sistema, dentre outros.
•
A versão 11.2 do Oracle Database oferece 208 privilégios de sistema
que
são
apresentados
na
tabela
SYSTEM_PRIVILEGE_MAP.
© 2014 - Prof. Marcos Alexandruk
de
dicionário
de
dados
Privilégios de sistema
SELECT NAME FROM SYSTEM_PRIVILEGE_MAP;
NAME
-------------------ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE
CREATE USER
ALTER USER
DROP USER
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
...
...
CREATE INDEX
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
SYSDBA
SYSOPER
CREATE VIEW
ALTER DATABASE
CREATE PROCEDURE
CREATE ANY PROCEDURE
...
© 2014 - Prof. Marcos Alexandruk
Privilégios de sistema
PRIVILÉGIO DE SISTEMA
DESCRIÇÃO
SYSDBA | SYSOPER
Criar uma nova entrada no arquivo externo de senhas, inicializar ou interromper uma
instância, criar, alterar e recuperar um banco de dados, etc.
ALTER SYSTEM
Alterar os parâmetros no arquivo SPFILE. Emitir instruções ALTER SYSTEM.
AUDIT SYSTEM
Fazer auditoria no banco de dados.
CREATE SESSION
Conectar com o banco de dados.
ALTER DATABASE
Alterar o estado de um banco de dados. Exemplo: alterar o estado de MOUNT para OPEN.
CREATE TABLESPACE
Criar novos tablespaces.
ALTER TABLESPACE
Alterar parâmetros dos tablespaces.
DROP TABLESPACE
Eliminar tablespaces.
CREATE USER
Criar novos usuários.
ALTER USER
Alterar privilégios e outros dados dos usuários.
DROP USER
Eliminar usuários.
CREATE TABLE
Criar novas tabelas em seu próprio esquema.
CREATE ANY TABLE
Criar novas tabelas em qualquer esquema.
ALTER ANY TABLE
Alterar as tabelas em qualquer esquema.
DROP ANY TABLE
Eliminar tabelas em qualquer esquema.
CREATE PROCEDURE
Criar uma procedure, função ou pacote em seu próprio esquema.
CREATE ANY PROCEDURE
Criar uma procedure, função ou pacote em qualquer esquema.
...
...
© 2014 - Prof. Marcos Alexandruk
Privilégios de sistema
Concedendo privilégios de sistema
•
Privilégios são concedidos aos usuários utilizando-se o comando GRANT.
•
Conceder ao usuário FULANO os privilégios necessários para criar tabelas
e visões em seu próprio esquema:
GRANT CREATE TABLE, CREATE VIEW TO FULANO;
•
Privilégios também podem ser concedidos a todos os usuários do banco
através do grupo especial denominado PUBLIC:
GRANT CREATE TABLE TO PUBLIC;
© 2014 - Prof. Marcos Alexandruk
Privilégios de sistema
Concedendo privilégios de sistema
•
Conceder privilégios ao usuário e permitir que ele, por sua vez, também
conceda para outros os mesmos privilégios que está recebendo:
GRANT CREATE TABLE TO FULANO WITH ADMIN OPTION;
•
Se a permissão do usuário FULANO para conceder seus privilégios a
outros for revogada, os usuários aos quais ele concedeu os privilégios
continuarão a retê-los (não serão revogados).
© 2014 - Prof. Marcos Alexandruk
Privilégios de sistema
Revogando privilégios de sistema
•
Revogar o privilégio CREATE VIEW concedido anteriormente ao
usuário FULANO:
REVOKE CREATE VIEW FROM FULANO;
© 2014 - Prof. Marcos Alexandruk
Privilégios de sistema
Consultando privilégios de sistema
•
O dicionário de dados apresenta algumas visões que podem ser
consultadas para obter-se os privilégios concedidos aos usuários do
banco de dados.
•
Consultar os privilégios de sistema atribuídos diretamente para o
usuário SCOTT:
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'SCOTT';
© 2014 - Prof. Marcos Alexandruk
Privilégios de sistema
Visões de dicionário de dados de privilégios de sistema
VISÃO
DESCRIÇÃO
DBA_SYS_PRIVS
Privilégios de sistema atribuídos a usuários e papéis.
SESSION_PRIVS
Privilégios de sistema concedidos ao usuário na sessão atual
diretamente ou através de um papel.
ROLE_SYS_PRIVS
Privilégios de sistema concedidos ao usuário na sessão atual através
de um papel.
© 2014 - Prof. Marcos Alexandruk
Privilégios de objetos
•
Privilégio de objetos é o direito de realizar um tipo específico de ação sobre determinados
objetos de um banco de dados (tabelas, visões, procedures, etc.) que não fazem parte do
esquema do usuário.
•
Como ocorre com os privilégios de sistema, para conceder privilégios sobre objetos de
banco de dados utiliza-se o comando GRANT e para revogá-los utiliza-se o comando
REVOKE.
•
Os privilégios de objetos podem ser concedidos a todos os usuários do banco de dados
através do grupo especial PUBLIC.
•
Pode-se também conceder privilégios de objetos a determinado usuário e permitir que ele,
por sua vez, também conceda-os para outros. Exemplo:
GRANT SELECT ON NOME_TABELA TO FULANO WITH GRANT OPTION;
•
Diferente do que acontece quando se concede permissões de sistema, se os privilégios de
objetos do usuário forem revogados, serão também revogados os privilégios de todos os
usuários da cadeia, isto é, que receberam seus privilégios através deste usuário (FULANO
no exemplo acima).
© 2014 - Prof. Marcos Alexandruk
Privilégios de objetos
Privilégios de tabela
•
Os privilégios de tabelas podem ser concedidos para operações de DDL (Data
Definition Language) e de DML (Data Manipulation Language):
•
Operações de DDL: adicionar, modificar ou descartar colunas das tabelas
ou ainda criar índices nas tabelas.
•
Operações de DML: SELECT, INSERT, UPDATE e DELETE. É possível
restringir os privilégios a determinadas colunas das tabelas.
GRANT UPDATE (ID, NOME) ON FULANO.FUNCIONARIO TO SCOTT;
•
Revogar o privilégio concedido anteriormente:
REVOKE UPDATE ON FULANO.FUNCIONARIO FROM SCOTT;
© 2014 - Prof. Marcos Alexandruk
Privilégios de objetos
Consultando privilégios de tabela
•
O dicionário de dados apresenta algumas visões que podem ser
consultadas para obter-se os privilégios de objetos concedidos aos
usuários do banco.
•
Consultar os privilégios de tabela concedidos diretamente ao usuário
SCOTT:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'SCOTT';
© 2014 - Prof. Marcos Alexandruk
Privilégios de objetos
Visões de dicionário de dados de privilégios de objetos
VISÃO
DESCRIÇÃO
DBA_TAB_PRIVS
Privilégios de tabelas concedidos a usuários e papéis.
DBA_COL_PRIVS
Privilégios de colunas concedidos ao usuário na sessão atual
diretamente ou através de um papel.
SESSION_PRIVS
Privilégios de sistema concedidos ao usuário na sessão atual
diretamente ou através de um papel.
ROLE_TAB_PRIVS
Privilégios de tabelas concedidos ao usuário na sessão atual através de
um papel.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Roles
•
Papéis ou roles são grupos identificados de privilégios que podem
incluir tanto privilégios de sistema como privilégios de objetos.
•
A utilização de papéis facilita a administração dos privilégios
concedidos aos usuários do banco de dados. Pois, em vez de conceder
diversos privilégios individualmente aos usuários, é possível concedêlos a um papel e este, por sua vez, ser concedido aos usuários.
•
Caso seja necessária alguma alteração, esta poderá ser feita no role e,
consequentemente, os privilégios de todos os usuários que utilizam
este papel serão automaticamente alterados. Isto pode reduzir
significativamente os números de comandos GRANT e REVOKE
necessários para a administração dos privilégios dos usuários do
banco de dados.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Papéis predefinidos
•
A tabela a seguir apresenta alguns dos principais papéis predefinidos:
PAPEL
PRIVILÉGIO
CONNECT
ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK,
CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM,
CREATE TABLE, CREATE VIEW.
RESOURCE
CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR,
CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE,
CREATE TRIGGER, CREATE TYPE.
DBA
Todos os privilégios de sistema WITH ADMIN OPTION.
SELECT_CATALOG_ROLE
Privilégio SELECT nos objetos do dicionário de dados.
EXP_FULL_DATABASE
Privilégio para exportar todos os objetos do banco de dados.
IMP_FULL_DATABASE
Privilégio para importar todos os objetos do banco de dados.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Criando um role
•
Criar um novo role:
CREATE ROLE TESTE;
•
É necessário possuir o privilégio de sistema CREATE ROLE que
geralmente é concedido aos administradores do banco de dados.
Descartando um papel
•
Descartar um role:
DROP ROLE TESTE;
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Concedendo privilégios a um role
•
Privilégios são concedidos a um role através do comando GRANT.
•
Conceder um privilégio de objeto na tabela FUNCIONARIOS ao role
GERENTE_RH. (O role GERENTE_RH deve ser criado antes.)
GRANT SELECT ON FUNCIONARIOS TO GERENTE_RH;
•
Conceder um privilégio de sistema ao role GERENTE_RH.
GRANT CREATE TRIGGER TO GERENTE_RH;
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Atribuindo um role a um usuário
•
Atribuir o role GERENTE_RH ao usuário FULANO:
GRANT GERENTE_RH TO FULANO;
•
Caso sejam concedidos outros privilégios ao role GERENTE_RH estes
serão imediatamente atribuídos ao usuário FULANO.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Atribuindo um role a outro role
•
Roles podem também ser atribuídos a outros roles permitindo assim que o DBA tenha a sua
disposição uma hierarquia de papéis.
•
No exemplo a seguir, em vez de atribuir-se privilégios de objetos individuais ao role
TODOS_DEPT, preferiu-se atribuir os roles MM_DEPT, RH_DEPT, FI_DEPT e SD_DEPT ao
role TODOS_DEPT.
GRANT MM_DEPT, RH_DEPT, FI_DEPT, SD_DEPT TO TODOS_DEPT;
•
Portanto, o role TODOS_DEPT poderia, por exemplo, ser atribuído ao presidente da empresa
e este teria acesso às tabelas de todos os departamentos.
GRANT TODOS_DEPT TO USUARIO_PRESIDENTE;
•
O role TODOS_DEPT poderia ter também outros privilégios de sistema ou de objetos que
não seriam atribuídos aos outros (MM_DEPT, RH_DEPT, FI_DEPT e SD_DEPT).
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Revogando um role
•
Revoga-se um role através do comando REVOKE:
REVOKE GERENTE_RH FROM FULANO;
•
Caso outros roles atribuídos ao usuário FULANO tiverem alguns dos
privilégios concedidos ao role GERENTE_RH, o usuário (FULANO)
continuará a retê-los até que sejam explicitamente revogados.
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Ativando um role protegido por senha
•
O DBA poderá atribuir uma senha a um role, aumentando com esta medida a segurança.
CREATE ROLE TESTE_SENHA IDENTIFIED BY ABC123;
•
O papel TESTE_SENHA deve ser concedido normalmente através do comando GRANT.
GRANT TESTE_SENHA TO FULANO;
•
Quando o usuário FULANO conectar-se ao banco de dados deverá fornecer o nome do
papel e a sua respectiva senha para que possa "receber" os privilégios.
SET ROLE TESTE_SENHA IDENTIFIED BY ABC123;
© 2014 - Prof. Marcos Alexandruk
Gerenciamento de papéis (roles)
Visões de dicionário de dados referentes aos papéis
VISÃO
DESCRIÇÃO
DBA_ROLES
Apresenta todos os papéis e se eles requerem senha.
DBA_ROLE_PRIVS
Apresenta os papéis concedidos a outros usuários ou a outros papéis.
ROLE_ROLE_PRIVS
Apresenta os papéis concedidos a outros papéis.
ROLE_SYS_PRIVS
Apresenta os privilégios de sistema que foram concedidos aos papéis.
ROLE_TAB_PRIVS
Apresenta os privilégios de tabelas e colunas de tabelas que foram
concedidos aos papéis.
SESSION_ROLES
Apresenta os papéis que estão em efeito na sessão atual.
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
O Oracle apresenta três métodos para fazer backup de um banco de
dados:
• exportação/importação (backup lógico);
• backup off-line (backup físico);
• backup on-line (backup físico).
Uma boa estratégia de backup para um banco de dados envolve tanto
backups lógicos como físicos.
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
O backup lógico envolve a leitura de um conjunto de registros do banco
de dados e a gravação destes registros em um determinado arquivo.
A leitura dos registros ocorre independentemente das suas localizações
físicas.
Apesar de utilitários mais antigos como o Import e o Export ainda
estarem disponíveis, é recomendável a utilização do utilitário Data Pump
Export (disponível a partir da versão 10g) para realização do backup e do
Data Pump Import para recuperação dos mesmos.
O Data Pump Export consulta o banco de dados, inclusive o dicionário
de dados, e grava os registros em um arquivo padrão XML chamado
"arquivo dump de exportação". Podem ser exportados para este arquivo
todo o banco de dados, determinados usuários, tablespaces ou tabelas.
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Após a exportação dos dados através do Data Pump Export, estes
poderão ser recuperados através do utilitário Data Pump Import.
É possível recuperar todos os dados ou apenas uma parte dos dados
exportados.
Caso seja importado todo o arquivo gerado a partir de uma exportação
completa, então todos os objetos do banco (tablespaces, arquivos de
dados e usuários) serão criados durante a importação.
Por outro lado, caso sejam importados apenas uma parte dos dados, os
tablespaces, arquivos de dados e usuários, deverão ser devidamente
configurados antes da importação.
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando o DATA PUMP EXPORT
O exemplo que será apresentado tem como base a exportação dos
objetos do usuário FULANO que deverá ser criado e deverá receber
alguns privilégios, conforme segue:
• CREATE USER FULANO IDENTIFIED BY ABC123;
• GRANT CONNECT, RESOURCE TO FULANO;
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando o DATA PUMP EXPORT
O próximo passo apresenta a criação de uma tabela simples
denominada TAB1 para teste. A tabela será criada pelo usuário
FULANO:
• CREATE TABLE TAB1 (
• COL1 NUMBER(2));
•
A seguir serão inseridas duas linhas na tabela TAB1:
• INSERT INTO TAB1 VALUES (1);
• INSERT INTO TAB1 VALUES (2);
• COMMIT;
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando o DATA PUMP EXPORT
Cria-se a seguir um diretório para armazenar os arquivos de dados e de
controle que o Data Pump irá criar (na exportação) e ler (na importação).
É necessário que o usuário que emitirá o comando tenha o privilégio de
sistema CREATE ANY DIRECTORY.
Portanto, o DBA criará um diretório denominado TESTE e concederá os
privilégios de leitura e escrita ao usuário FULANO:
• CREATE DIRECTORY TESTE AS 'C:\TESTE';
• GRANT READ, WRITE ON DIRECTORY TESTE TO FULANO;
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando o DATA PUMP EXPORT
O utilitário EXPDP serve como interface com o Data Dump.
A tabela seguir apresenta alguns dos principais parâmetros utilizados
com o EXPDP.
PARÂMETRO
DESCRIÇÃO
DIRECTORY
Especifica o diretório de destino para os arquivos de log e para os
arquivos de dump.
DUMPFILE
Especifica os nomes dos arquivos de dump.
CONTENT
Especifica o que será exportado: DATA_ONLY, METADATA_ONLY ou ALL.
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando o DATA PUMP EXPORT
Para utilizar o EXPDP deve-se digitar EXPDP em uma janela de prompt de
comando do sistema operacional.
O exemplo abaixo apresenta a utilização do EXPDP pelo usuário FULANO.
Foi passado o valor METADATA_ONLY ao parâmetro CONTENT. Portanto, apenas
a estrutura da tabela TAB1 (único objeto do usuário FULANO) será exportada.
Para exportação da estrutura e dos dados da tabela o valor deste parâmetro
(CONTENT) deveria ser alterado para ALL.
C:\> EXPDP DIRECTORY=TESTE DUMPFILE=DATA.DMP CONTENT=METADATA_ONLY
Será solicitado o nome do usuário e sua senha. Após fornecê-los o EXPDP
passará à criação do arquivo de log e do arquivo dump no diretório TESTE.
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando o DATA PUMP EXPORT
A etapa seguinte envolverá a importação dos dados com base na
exportação que acaba de ocorrer.
Porém, antes de executar o utilitário de importação, será eliminada a
tabela TAB1 criada no exemplo apresentado.
• DROP TABLE TAB1;
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando o DATA PUMP IMPORT
Deve-se utilizar o Data Pump Import para importação de arquivos
exportados via Data Pump Export.
Para importação dos objetos do usuário FULANO, deve-se utilizar o
comando:
•
C:\> IMPDP DIRECTORY=TESTE DUMPFILE=DATA.DMP CONTENT=METADATA_ONLY
•
Voltando ao SQL Plus ao utilizar o comando DESCRIBE (ou sua
abreviação DESC) pode-se observar que o objeto anteriormente
excluído (a tabela TAB1) foi recuperado.
•
DESC TAB1
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
No exemplo apresentado apenas a estrutura da tabela foi exportada.
Para que os dados também fossem exportados seria necessário passar o valor
ALL ao parâmetro CONTENT.
Caso a tabela a ser importada já existir e a opção CONTENT receber o valor
METADATA_ONLY esta será simplesmente ignorada.
Porém, se a tabela já existir e a opção CONTENT receber o valor DATA_ONLY os
"novos" dados serão acrescentados aos dados já existentes na tabela. Para
alterar isso, deve-se utilizar a opção TABLE_EXISTS_ACTION atribuindo-lhe um
dos seguintes valores:
•
SKIP: Conserva apenas os valores que já se encontravam na tabela, nada
será importado;
•
APPEND: Acrescenta os "novos" valores aos que já se encontram na tabela;
•
TRUNCATE: Corta (elimina) os valores da tabela e insere os dados contidos
no arquivo de importação;
•
REPLACE: Substitui toda a tabela (estrutura e dados). Para esta opção deve
ser especificado o valor ALL ao parâmetro CONTENT na exportação e na
importação.
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
© 2014 - Prof. Marcos Alexandruk
Exportação e Importação
Utilizando as opções EXCLUDE, INCLUDE e QUERY
É possível excluir ou incluir conjuntos de tabelas ou ainda apenas
determinadas linhas de tabelas em uma exportação utilizando as
opções EXCLUDE, INCLUDE e QUERY.
O exemplo a seguir exclui a tabela TAB1 do arquivo de exportação
(todas as outras tabelas seriam exportadas, exceto TAB1):
C:\>EXPDP DIRECTORY=TESTE DUMPFILE=DATA.DMP
EXCLUDE=TABLE:\"=\'TAB1\'\"
CONTENT=ALL
Caso não seja especificado nenhum nome, todos os objetos do tipo
especificado não serão incluídos na exportação.
O exemplo a seguir não importará nenhum índice:
C:\>EXPDP DIRECTORY=TESTE
EXCLUDE=INDEX
DUMPFILE=DATA.DMP
© 2014 - Prof. Marcos Alexandruk
CONTENT=ALL
Exportação e Importação
Utilizando as opções EXCLUDE, INCLUDE e QUERY
O próximo exemplo inclui apenas a TAB1 no arquivo de exportação
(todas as outras tabelas não seriam exportadas):
C:\>EXPDP DIRECTORY=TESTE DUMPFILE=DATA.DMP CONTENT=ALL
INCLUDE=TABLE:\"=\'TAB1\'\"
Pode-se exportar apenas determinadas linhas de uma tabela. O
exemplo a seguir apresenta a exportação das linhas cujos valores da
COL1 (da tabela TAB1) sejam menores que 5:
C:\>EXPDP DIRECTORY=TESTE DUMPFILE=DATA.DMP CONTENT=ALL
INCLUDE=TABLE:\"=\'TAB1\'\" QUERY=TAB1:\"WHERE COL1 < 5\"
Para importação dos dados utiliza-se, por exemplo, o seguinte
comando:
C:\>IMPDP DIRECTORY=TESTE DUMPFILE=DATA.DMP CONTENT=ALL
TABLE_EXISTS_ACTION=APPEND
© 2014 - Prof. Marcos Alexandruk
Backups Físicos
Backups Físicos
Podem ser realizados com utilitários dos principais sistemas operacionais:
• Windows (copy ou winzip32);
• Unix (cp ou tar).
O Oracle disponibiliza dois tipos de backups físicos:
• Off-line (também chamado fechado ou a frio);
• On-line (também chamado aberto ou a quente).
© 2014 - Prof. Marcos Alexandruk
Backups Físicos
Opções disponíveis
• Integral ou parcial
• Integral: conjunto inteiro de arquivos de dados e de controle;
• Parcial: apenas alguns arquivos de dados e/ou de controle.
• Total ou incremental
• Total: autocontido, utilizável por si próprio;
• Incremental: apenas os blocos que foram alterados desde o
último backup. (Realizados somente através do RMAN)
© 2014 - Prof. Marcos Alexandruk
Backups off-line
Requisitos
O banco deverá ser "desligado" através de um destes tipos de shutdown:
• shutdown normal;
• shutdown immediate;
• shutdown transactional.
Não se deve realizar um backup off-line após um shutdown abort para
que não ocorram inconsistências nos dados.
Caso seja necessário fazer um shutdown abort, para realização de um
backup off-line consistente, deve-se reiniciar o banco de dados e realizar
novamente um shutdown com uma das três opções acima.
© 2014 - Prof. Marcos Alexandruk
Backups off-line
Deve-se fazer backup de todos os arquivos dos seguintes grupos:
• Arquivos de dados;
• Arquivos de controle;
• Arquivos de logs de redo on-line;
• Arquivo init.ora e spfile (se utilizado).
© 2014 - Prof. Marcos Alexandruk
Backups off-line
1. Criar o diretório para arquivar os backups (Exemplo: C:\BACKUP)
2. Criar (no SQL*Plus) o shell script para executar o backup:
SPOOL E:\BACKUP\BACKUP_OFFLINE.BAT
SELECT 'COPY '||NAME||' E:\BACKUP' FROM V$DATAFILE
UNION ALL
SELECT 'COPY '||NAME||' E:\BACKUP' FROM V$CONTROLFILE
UNION ALL
SELECT 'COPY '||MEMBER||' E:\BACKUP' FROM V$LOGFILE;
CREATE PFILE='E:\BACKUP\SPFILE_BACKUP.ORA' FROM SPFILE;
SPOOL OFF
Para criar o script é preciso fazer login como sysdba (ou equivalente).
© 2014 - Prof. Marcos Alexandruk
Backups off-line
Arquivo gerado
SQL>
2
3
4
5
SELECT 'COPY '||NAME||' C:\BACKUP' FROM V$DATAFILE
UNION ALL
SELECT 'COPY '||NAME||' C:\BACKUP' FROM V$CONTROLFILE
UNION ALL
SELECT 'COPY '||MEMBER||' C:\BACKUP' FROM V$LOGFILE;
'COPY'||NAME||'C:\BACKUP'
-------------------------------------------------------------------COPY C:\APP\MASTER\ORADATA\ORCL\SYSTEM01.DBF C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\SYSAUX01.DBF C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\UNDOTBS01.DBF C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\USERS01.DBF C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\EXAMPLE01.DBF C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\CONTROL01.CTL C:\BACKUP
COPY C:\APP\MASTER\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\REDO03.LOG C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\REDO02.LOG C:\BACKUP
COPY C:\APP\MASTER\ORADATA\ORCL\REDO01.LOG C:\BACKUP
10 linhas selecionadas.
SQL> CREATE PFILE='C:\BACKUP\SPFILE_BACKUP.ORA' FROM SPFILE;
Arquivo criado.
SQL> SPOOL OFF
© 2014 - Prof. Marcos Alexandruk
Backups off-line
Para executar o backup é preciso realizar o shutdown no banco de
dados (como sysdba):
SHUTDOWN IMMEDIATE
O backup é realizado através do sistema operacional com a 'execução'
do script anteriormente criado (BACKUP_OFFLINE.BAT).
© 2014 - Prof. Marcos Alexandruk
Backups on-line
Backups on-line (também denominados abertos ou "a quente") são
aqueles realizados enquanto o banco de dados Oracle encontra-se
aberto.
Não é possível fazer um backup on-line no modo NOARCHIVELOG.
Neste modo todas as transações encerradas com COMMIT, mas que
ainda não foram gravadas nos arquivos de dados ficam disponíveis
nos arquivos de redo log online.
Por outro lado, quando o modo ARCHIVELOG está ativado, o processo
em background ARCn (Archiver Process) faz uma cópia de cada
arquivo de redo log antes de sobrescrevê-lo.
© 2014 - Prof. Marcos Alexandruk
Backups on-line
VERIFICANDO O ARCHIVELOG MODE
Consulta a seguir para verificar se o archivelog mode está ativado:
SELECT LOG_MODE FROM V$DATABASE;
Se o archivelog mode estiver desativado a consulta retornará o
seguinte:
LOG_MODE
-----------NOARCHIVELOG
© 2014 - Prof. Marcos Alexandruk
Backups on-line
ATIVANDO O ARCHIVELOG MODE
Antes de ativar o archivelog mode é preciso parar o banco:
SHUTDOWN IMMEDIATE
A seguir, deve-se subir o banco para o estado mount:
STARTUP MOUNT;
Para alterar archivelog mode deve-se utilizar o seguinte comando:
ALTER DATABASE ARCHIVELOG;
O próximo passo será abrir o banco de dados:
ALTER DATABASE OPEN;
© 2014 - Prof. Marcos Alexandruk
Backups on-line
BACKUP DOS ARQUIVOS DE CONTROLE
Alternativa 1:
ALTER DATABASE BACKUP CONTROLFILE TO '/BACKUP/CONTROL1.BKP';
Realiza uma cópia binária do arquivo de controle, isto é, uma cópia idêntica
byte-a-byte do arquivo de controle.
Alternativa 2:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/BACKUP/CONTROL2.BKP';
Cria um novo arquivo de controle, um arquivo ASCII, que poderá ser executado
enquanto a instância estiver no modo NOMOUNT para criar um novo arquivo de
controle com conteúdo idêntico ao original.
© 2014 - Prof. Marcos Alexandruk
Backups on-line
BACKUP DOS ARQUIVOS DE UM TABLESPACE
Determinar quais são os arquivos associados ao tablespace (ex. SYSAUX):
SELECT FILE_NAME FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSAUX’;
Colocar o tablespace (ex. SYSAUX) no modo backup:
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
Fazer o backup do(s) arquivo(s) de dados utilizando um utilitário do sistema
operacional. (No caso do Microsoft Windows pode-se utilizar o utilitário copy.)
C:\>COPY C:\APP\MASTER\ORADATA\ORCL\SYSAUX01.DBF C:\BACKUP\SYSAUX01.DBF;
Encerrar o modo backup no tablespace SYSAUX:
ALTER TABLESPACE SYSAUX END BACKUP;
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
O RMAN é muito mais do que um simples utilitário que pode ser
utilizado do lado cliente para realizar backups.
Apresenta muitos recursos que não estão disponíveis em
outros métodos de backup.
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
EFETUANDO BACKUP COM O RMAN
Para efetuar um backup com o RAMAN deve-se primeiramente verificar
se o ARCHIVELOG está habilitado:
SELECT LOG_MODE FROM V$DATABASE;
Se o archivelog mode estiver desativado a consulta retornará o
seguinte:
LOG_MODE
-----------NOARCHIVELOG
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
ATIVANDO O ARCHIVELOG MODE
Antes de ativar o archivelog mode é preciso parar o banco:
SHUTDOWN IMMEDIATE
A seguir, deve-se subir o banco para o estado mount:
STARTUP MOUNT;
Para alterar archivelog mode deve-se utilizar o seguinte comando:
ALTER DATABASE ARCHIVELOG;
O próximo passo será abrir o banco de dados:
ALTER DATABASE OPEN;
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
INICIANDO O RMAN
A seguir, deve-se entrar no prompt (do sistema operacional) e digitar o
seguinte comando:
RMAN TARGET SYS/SENHA_DO_SYS
O comando anterior produzirá a seguinte saída:
Conectado ao banco de dados de destino: ORCL (DBID=1178846893)
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
BACKUP DOS ARQUIVOS DE DADOS
Criar o diretório para backup dos arquivos.
Configurar o diretório para backup:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\BACKUP\%U';
Realizar o backup dos arquivos de dados:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
BACKUP DO ARQUIVO DE CONTROLE
Configurar o diretório para backup do arquivo de controle:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE
DISK TO 'C:\BACKUP\%F';
Realizar o backup do arquivo de controle:
RMAN> BACKUP CURRENT CONTROLFILE;
É possível consultar os backups realizados através da seguinte consulta:
RMAN> LIST BACKUP;
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
RESTORE DO ARQUIVO DE CONTROLE
Entrar no utilitário RMAN:
RMAN TARGET SYS/SENHA_DO_SYS;
Iniciar o banco de dados no modo NOMOUNT:
RMAN> STARTUP FORCE NOMOUNT;
Fazer o restore do arquivo:
RMAN> RESTORE CONTROLFILE FROM 'C:\BACKUP\NOME_ARQ_CONTROLE';
Nota: O arquivo de controle foi feito por último, portanto, provavelmente ele deve
ser o último arquivo do conjunto de backup.
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
RESTORE DOS ARQUIVOS DE DADOS
Montar a base com o arquivo de controle recuperado:
RMAN> STARTUP FORCE MOUNT;
Restaurar os arquivos de dados:
RMAN> RESTORE DATABASE;
Recuperar (recover) a base de dados:
RMAN> RECOVER DATABASE;
Abrir o banco de dados (com o parâmetro RESETLOGS que cria novos
arquivos de REDOLOG):
RMAN> ALTER DATABASE OPEN RESETLOGS;
© 2014 - Prof. Marcos Alexandruk
RMAN (Recovery Manager)
APAGAR ARQUIVOS DE BACKUP
Excluir os arquivos de backup:
RMAN> DELETE BACKUP;
© 2014 - Prof. Marcos Alexandruk
Sistemas Gerenciadores de Banco de Dados
Aula 7
Prof. Marcos Alexandruk
Aula 7
Desenvolvendo um pequeno banco de dados
SQL Server
SQL Command
Management Studio
© 2014 - Prof. Marcos Alexandruk
Microsoft SQL Server
SQL COMMAND
© 2014 - Prof. Marcos Alexandruk
SQL Command
ACESSAR O SQL COMMAND
• Executar o Prompt de Comando como Administrador
• Chamar o utilitário SQLCMD:
SQLCMD –S .\SQLEXPRESS
ou
SQLCMD –S SAMSUNG-5\SQLEXPRESS
• SAMSUNG-5 = Servidor
• SQLEXPRESS = Instância
© 2014 - Prof. Marcos Alexandruk
SQL Command
CRIAR LOGIN E USER
• Criar um novo LOGIN:
CREATE LOGIN FULANO WITH PASSWORD = 'ABC123'
GO
• Criar um novo USER:
CREATE USER FULANO FOR LOGIN FULANO
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
CONSULTAR LOGINS E USERS
• Apresentar os nomes de LOGINS:
SELECT NAME
FROM SYS.SERVER_PRINCIPALS
GO
• Apresentar os nomes de USERS:
SELECT NAME
FROM SYS.DATABASE_PRINCIPALS
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
LOGIN
• Login como usuário FULANO:
SQLCMD –S .\SQLEXPRESS – U FULANO –P ABC123
© 2014 - Prof. Marcos Alexandruk
SQL Command
CRIAR UM DATABASE
• Criar um novo database denominado DBTESTE:
CREATE DATABASE DBTESTE
GO
• Criar o database como usuário Administrador ou outro com
privilégio CREATE DATABASE.
© 2014 - Prof. Marcos Alexandruk
SQL Command
SELECIONAR UM DATABASE
• Selecionar o database DBTESTE:
USE DBTESTE
GO
•
Criar uma tabela no database DBTESTE:
CREATE TABLE TESTE (
CODIGO INT)
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
SP_GRANTDBACCESS
• Utilizar a Stored Procedure SP_GRANTDBACCESS para permitir
que o usuário FULANO acesse o database DBTESTE:
USE DBTESTE
GO
SP_GRANTDBACCESS FULANO
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
SP_REVOKEDBACCESS
• Utilizar a Stored Procedure SP_REVOKEDBACCESS para retirar
do usuário FULANO o privilégio de acessar o database DBTESTE:
USE DBTESTE
GO
SP_REVOKEDBACCESS FULANO
GO
EXIT
© 2014 - Prof. Marcos Alexandruk
SQL Command
PRIVILÉGIO GRANT SELECT
• O usuário FULANO, após receber a permissão para acessar o
database DBTESTE, consegue acessá-lo. Porém, não consegue
consultar os dados dos objetos do database DBTESTE, pois não
recebeu permissão para isso.
• Conceder privilégio para que o usuário FULANO possa realizar
consultas no database DBTESTE:
USE DBTESTE
GO
GRANT SELECT TO FULANO
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
PRIVILÉGIO CREATE DATABASE
• Conceder privilégio para usuário FULANO criar databases:
GRANT CREATE DATABASE TO FULANO
GO
•
Retirar privilégio do usuário FULANO criar databases:
REVOKE CREATE DATABASE FROM FULANO
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
SCHEMA
• Criar um schema para o usuário FULANO:
CREATE SCHEMA FULANO AUTHORIZATION FULANO
GO
EXIT
• Usuário FULANO criando uma tabela no schema acima:
CREATE TABLE FULANO.TESTE
CODIGO INT)
GO
• A tabela será criada no database master, caso não seja
especificado outro database.
© 2014 - Prof. Marcos Alexandruk
SQL Server
SQL COMMAND
PL/T-SQL
© 2014 - Prof. Marcos Alexandruk
SQL Command
STORED PROCEDURE
• Criar uma Stored Procedure simples denominada SP_TESTE:
CREATE PROCEDURE SP_TESTE AS
DECLARE @MENSAGEM CHAR(20)
SET @MENSAGEM = 'TESTE PROCEDURE'
PRINT @MENSAGEM
GO
• Testar a Stored Procedure SP_TESTE:
EXEC SP_TESTE
GO
• Eliminar a Stored Procedure SP_TESTE:
DROP PROCEDURE SP_TESTE
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
STORED PROCEDURE
•
Criar procedure SP_SOMA que recebe dois números e apresenta o valor
da soma:
SQLCMD -S .\SQLEXPRESS
CREATE PROCEDURE SP_SOMA @A FLOAT, @B FLOAT AS
DECLARE @X FLOAT
SET @X = @A + @B
PRINT @X
GO
•
Executar a procedure SP_SOMA:
EXEC SP_SOMA 3.5, 5.4
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
FUNCTION
•
Criar a função SF_SOMA que recebe dois números e retorna a soma:
CREATE FUNCTION SF_SOMA (@A FLOAT, @B FLOAT)
RETURNS FLOAT AS
BEGIN
DECLARE @X FLOAT
SET @X = @A + @B
RETURN (@X)
END
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
FUNCTION
• Chamar a função SF_SOMA:
SELECT DBO.SF_SOMA (3, 7)
GO
• Eliminar a função SF_SOMA:
DROP FUNCTION DBO.SF_SOMA
GO
•
dbo = Database Owner (schema default para membros do role sysadmin)
© 2014 - Prof. Marcos Alexandruk
SQL Command
TRIGGER
•
Criar um trigger, TR_AUDITORIA, para disparar quando ocorrerem
atualizações na tabela PRODUTO:
CREATE TABLE PRODUTO (
CODPROD INT,
NOMEPROD VARCHAR(20))
GO
CREATE TABLE AUDITORIA (
USUARIO VARCHAR(20),
DATA DATETIME,
CODPROD INT)
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
TRIGGER
•
Criar um trigger, TR_AUDITORIA, para disparar quando ocorrerem
atualizações na tabela PRODUTO:
CREATE TRIGGER TR_AUDITORIA ON PRODUTO FOR UPDATE AS
INSERT INTO AUDITORIA SELECT
SUSER_SNAME(), GETDATE(), CODPROD
FROM INSERTED
GO
© 2014 - Prof. Marcos Alexandruk
SQL Command
TRIGGER
• Testar o trigger TR_AUDITORIA:
INSERT INTO PRODUTO VALUES (1, 'PRODUTO 1')
GO
UPDATE PRODUTO SET NOMEPROD = 'PRODUTO 2'
WHERE CODPROD = 1
GO
SELECT * FROM PRODUTO
GO
SELECT * FROM AUDITORIA
GO
© 2014 - Prof. Marcos Alexandruk
SQL Server
MANAGEMENT STUDIO
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Management Studio
© 2014 - Prof. Marcos Alexandruk
Aula 7: Use AVA
Recentemente a Microsoft lançou o SQL Server 2014. Faça uma
pesquisa e comente com seus colegas quais foram as principais
novidades desta nova versão.
© 2014 - Prof. Marcos Alexandruk