Embed Size (px)
Citation preview
Módulo 15 – Linguagem de Definição de Dados
Programação e Sistemas de Informação
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Revisões Agora que já sabe … … o que é uma base de dados.
(Ai sei ???)
… esquematizar uma base de dados. (A sério???)
… fazer tabelas e estabelecer relacionamentos.
(Também??? Não sabia que … sabia tanto)
2
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Revisões Bem, se calhar é melhor fazer umas revisões antes …
n Base de dados: Conjunto de dados organizados para servir um determinado propósito.
n Sistema de gestão de base de dados: É um programa capaz de relacionar, definir, manipular e gerir dados. 3
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
SGBD
4
Base de Dados
S G B D
Programa A
Programa B
Programa C
Programa D
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Arquitectura de um SGBD
5 • Base de • Dados
Nível Visualização
Nível Conceptual
Nível Físico
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
SQL n Significa: Structured Query Language
n Linguagem declarativa usada para definição e manipulação de dados em bases de dados relacionais
n SQL não é uma linguagem procedimental
n SQL é uma linguagem declarativa 6
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
SQL n Inicialmente desenvolvida pela IBM em 1974 – SEQUEL n Em 1982 o ANSI torna o SQL padrão oficial em ambiente
relacional n A 1ª versão surge em 1986 com a designação SQL-86 n Após algumas revisões - 1992 (SQL-92) e em 2003
(SQL-2003) n Nova revisão - SQL3 (suporte a modelos orientados a
objectos)
7
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
SQL
n SQL/DS e DB2 da IBM, ORACLE da Oracle Corporation, SYBASE da Sybase INC, e Microsoft SQL Server...
8
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Porquê SQL??? Vantagens … n Independência do fabricante
n Portabilidade entre computadores
n Redução dos custos
n Inglês estruturado de alto nível
n Consulta interactiva
n Definição dinâmica dos dados 9
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
SQL n Linguagem de definição de dados
§ DDL (Data Definition Language) § Instruções de definição do esquema da base
de dados n Linguagem de manipulação de dados
§ DML (Data Manipulation Language) § Instruções de programação de consultas e
transacções de manipulação da base de dados 10
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os Eu queria….
--- deixar de ser treinador
--- mudar de clube (SCP nunca)
--- ser treinador (mas só do grandioso )
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Como Programar … … em SQL??? Para isso, vamos utilizar um
SGBD
Lembram-se disto???
12
Na altura, falou-se na existência de vários, por qual optar???
No nosso caso, sem dúvidas … MySQL …
http://www.mysql.com/
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
MySQL - Vantagens Porquê optar por MySQL??? Para já, porque sim, apeteceu-nos … para além disso, apresenta um conjunto de vantagens, e à cabeça aparece logo:
n Gratuito (abrangido pelas licenças GPL);
n Bom desempenho;
n Estabilidade;
n Simplicidade de utilização;
n Compatibilidade (existem módulos para diversas linguagens de programação: PHP, C++, Java, C#, Perl, …);
13
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
MySQL - Vantagens Ainda não está convencido??? Quanta exigência, mas não seja por isso:
n Portabilidade (entre várias plataformas e sistemas);
n Leveza (não é por se pegar nele, mas por exigir poucos recursos de hardware);
n Existem diversos interfaces gráficos que permitem fazer a sua administração de uma forma simples;
n …
Ufaaaaaa … Agora sim, já chega … convencidíssimo …
14
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP … O quê??? Para trabalhar com MySQL, vai ser necessário instalar o
WAMP (existem outros programas similares, por exemplo: XAMPP)
Está disponível no site
http://www.wampserver.com/en/
Também já está disponível na Dropbox …
15
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP … O quê??? Mas, o que significa isso de WAMP??? Joguemos então ao jogo da adivinha …
W
A
M
P
16
… de Sistema Operativo Windows;
… de distribuição de Servidor Web Livre Apache;
… de SGBD MySQL;
… de linguagem de scripting muito utilizada para sites web dinâmicos: PHP;
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP … O quê??? O objetivo do WAMP é então instalar no Windows (em pacote offline), de forma:
§ Simultânea;
§ Simples;
§ Rápida …
… o Apache, o MySQL e o PHP … … desta forma, é possível elaborar um site dinâmico e experimentá-lo na sua máquina (localmente) como se estivesse a “executá-lo” num servidor web.
17
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Ficha de trabalho 9
Instale o WAMP no seu computador … 18
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP Já está instalado??? É a velha técnica do …
… Next … Next … Next …
Depois de efectuada a instalação, deve ter no Ambiente de Trabalho um atalho semelhante a:
19
Se clicar nele, automaticamente será lançado o WAMP … cuidado, baixem-se … e ficará disponível na barra de notificações:
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP Inicialmente os serviços estarão parados: Tem de os iniciar:
20 Todos …
… ou um a um.
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP Será que já está a funcionar??? Apesar de o verde nem sempre ser bom agoiro, parece que sim … Se não estiver, tente … Reiniciar todos os Serviços …
21
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP Outra forma é verificar a página de
localhost (127.0.0.1) no browser. 22
Em que se tudo estiver bem, deve aparecer a página:
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP - phpMyAdmin § Totalmente desenvolvido em PHP;
§ Interface Web para administração de MySQL. Torna automático o processo de:
§ Criar / Remover bases de dados;
§ Criar / Remover / Alterar tabelas;
§ Inserir / Remover / Editar campos;
§ Executar códigos SQL;
§ …
23
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP - phpMyAdmin
Tão simples … Tão simples …
Que vamos ver outra forma mais à frente …
24
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP – Diretório WWW Tanto o diretório WWW como o phpMyAdmin vão ser aprofundados na disciplina de RC …
25
Pasta onde ficam colocados os códigos / programas, nomeadamente em PHP …
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP – Consola MySql Que tal, como puristas e adeptos (… ferrenhos …) da programação, utilizar a consola do MySql para programar? Excelente ideia … não fosse ela nossa … 26
E olhem que coisa mais linda:
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP – Consola MySql Modo consola??? Então deixe adivinhar … hmmmm … São comandos … comandos … e, não sei se já disse, comandos.
27
Comecemos então a espreitar alguns comandos SQL ...
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
WAMP – Consola MySql Eis a nossa consola, não confundir com outras consolas … Inicie a sessão como root, não necessita de password … Mais tarde, falaremos de utilizadores e privilégios.
28
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Comandos – Consola MySQL Quais as bases de dados que tem criadas, ou que vêm por defeito com o MySQL??? Nada mais simples, a qualquer momento faça:
show databases;
Como resultado, obtém uma lista com o nome de todas as bases de dados …
29
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Comandos – Consola MySQL Quer utilizar uma das bases de dados da listagem anterior?
use [nome_da_BD];
30 Muda a ação para a BD que escolheu usar … pode fazer as mais variadas ações sobre ela …
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Comandos – Consola MySQL Quais as tabelas que a BD anterior contém??? show tables;
31
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Comandos – Consola MySQL Quais os atributos que defini para uma tabela e respectivos tipos???
describe [nome_da_tabela];
32
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Bases de Dados MySQL Até agora foram apenas comandos genéricos … não confundir com os medicamentos Sendo assim, é melhor começar pelo inicio, certo??? E uma coisa é certa … … como não se pode criar tabelas sem uma … … base de dados … 33
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Bases de Dados MySQL … comecemos então por criar uma base de dados:
create database [nome_base_dados]; (IMPORTANTE: Não esquecer o “;” no final das instruções …)
SUCESSO … O que faz a query anterior??? Agora precisa de dizer ao MySQL que quer utilizar essa BD, como???
34
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas Já sabe criar Bases de Dados … Já sabe passar a ação para dentro da BD que escolheu … O que falta??? Além de animar a malta??? Exatamente, criar tabelas dentro dessa BD. E como se diz criar tabela em Inglês??? Cultura geral:
create table 35
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas É isso mesmo, nada mais simples, criar uma tabela é só:
create table [nome_da_tabela] (
nome_atributo1 tipo_dados1, nome_atributo2 tipo_dados2 … );
36
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas Imagine o seguinte código para uma tabela Contactos:
37
Comente Linha create table Contactos Cria tabela Contactos
( Abre lista de atributos
p_nome varchar(40), Atributo primeiro nome com até 40 carateres
u_nome varchar(30), Atributo último nome com até 30 carateres
email varchar(50), Atributo email com até 40 carateres
aniversário date, Atributo aniversário que guarda valor da data
morada varchar(60), Atributo morada com até 60 carateres
telefone integer, Atributo telefone que é conjunto de números inteiros
interesses varchar(100) Atributo interesses com até 100 carateres
); Fecha lista de atributos e termina o comando com ;
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas A tabela anterior traduzir-se-ia na consola MySQL em: Como pode verificar: Qual o comando que usou para visualizar os atributos da tabela e respectivos tipos???
38 describe contactos;
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas - Tipos Depois de tanto se falar em tipos de dados, está na altura de os conhecermos:
39
INT ou INTEGER Números inteiros
positivos ou negativos
CHAR ou CHARACTER Conjunto de caracteres
com determinado tamanho (pouco flexível)
DATETIME ou TIMESTAMP
Regista a data e a hora
DEC ou DECIMAL Números decimais
VARCHAR Até 255 caracteres. Flexível porque se
adapta ao tamanho dos dados até ao tamanho.
DATE Guarda as datas
sem ligar às horas
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas - Tipos Os tipos anteriores são mais ou menos usuais. Mas imagine que quer guardar um ficheiro binário, por exemplo, uma imagem …
Utilizaria o tipo de dados…
… BLOB (Binary Large Object)
Que pode ser de 4 tipos: § TINYBLOB – 8 bits § BLOB – 16 bits § MEDIUMBLOB – 24 bits § LONGBLOB – 32 bits
40
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas Outras questões se alevantam … e se o atributo …
... for chave primária??? In English please: PRIMARY KEY
… for não nulo??? In English please: NOT NULL
… tiver valor por defeito??? In English please: DEFAULT
41
Que leitura faz desta tabela???
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Tabelas Veja as diferenças com o describe (DESC):
42
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Eliminação de BD / Tabelas Livramo-nos de uma tabela é mais simples que criá-la:
drop table [nome_da_tabela];
Assim como de uma base de dados, alguém quer tentar???
drop database [nome_da_BD];
Utilize este comando com muito cuidado … Vocês sabem do que estou a falar …
43
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Alteração de Tabelas Não me diga que se me enganar, ou esquecer algum atributo, tenho de reescrever tudo de novo. Até podia ser, mas … não era a mesma coisa … basta então utilizar:
alter table [nome_da_tabela] ADD / CHANGE / MODIFY / DROP COLUMN
[nome_do_atributo] [tipo] Permite definir a posição da coluna:
First, Last, Before [campo], After [campo] Nota: comando pode conter várias linhas de uma mesma forma(vários add), ou mesmo, a conjugação de diferentes formas (add + change).
44
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Alteração de Tabelas Quais são então as alterações possíveis? ALTER TABLE [Nome_Tabela] ADD à atributo à BD com o respetivo tipo;
ADD Nome_Atributo Definições_Atributo CHANGE à nome e tipo de um determinado atributo;
CHANGE Nome_Atributo Novo_Nome Definições_Novo_Atributo MODIFY à tipo de dados ou posição de um atributo;
MODIFY Nome_Atributo Tipo_Atributo DROP à um atributo da tabela (elimina);
DROP COLUMN Nome_Atributo RENAME à uma tabela
RENAME TO Novo_Nome
Vantagem: pode-se utilizar sem que as alterações afetem os dados. 45
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Alteração de Tabelas - Exemplo Vejamos um exemplo já conhecido, mas propositadamente modificado … Se quiser adicionar um atributo que seja chave primária, não nulo e de auto-incremento:
46
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Alteração de Tabelas - Exemplo O resultado final seria este: Fácil, barato … só não dá milhões €€€€€
47
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Ficha de trabalho nº 10
48
Criação de Base de Dados e Tabelas em MySQL
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Ficha de trabalho nº 11
49
Criação de Base de Dados e Tabelas em MySQL
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Utilização de Índices n Já foram falados no Módulo 13 será que vale a pena??? n E lá diz o poeta:
50
O que lhe faz lembrar a palavra índice??? E qual a sua função???
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Utilização de Índices
n Imagine uma BD com poucos registos
n Imagine que essa BD cresceu e tem agora milhares de
registos
51
neste caso, pesquisa será rápida e o desempenho da BD será óptimo.
agora, a pesquisa tornou-se mais lenta. Quem quer tentar???
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Utilização de Índices Definição formal de índice: n Índices ou estruturas de acesso secundárias são
estruturas de dados auxiliares que visam minimizar o tempo de acesso a registos em resposta a operações de procura sobre determinados atributos.
52
… ou então leio o índice….
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Utilização de Índices n O problema não está na quantidade de registos mas na
forma como se efetuam as consultas (Módulo 14 – a ver )
n A utilização de índices prende-se exatamente com questões: l de eficiência performance l Quais o Benefícios da utilização de índices??? l Aumento da performance; l Utilização de mais do que um índice; l Fácil implementação.
53
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Utilização de Índices Mas existem algumas contrapartidas: n É criado bloco de informações separado… n … que necessita de atualização
(optimize table [nome_da_tabela])
n A ideia é que sendo o ficheiro de índices mais pequeno que o ficheiro de registos, podemos tê-lo em memória e usá-lo para localizar mais depressa os registos nos blocos em disco. 54
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Utilização de Índices - Ilustração n Imagine uma tabela com os registos das compras efectuadas pelos
clientes e as respectivas datas, mas sem qualquer (coluna) atributo indexado.
55
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
q Agora já se obteve uma tabela de índices criada sobre os dados da coluna: Cod_Cliente
Utilização de Índices - Ilustração
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Consegue observar as vantagens??? Ainda não Hmmm n Imagine que quer saber os registos do cliente 13
l Sabe exatamente a localização desses registos; l Estão ordenados…. l …consequentemente, chega ao 14 e pára.
n Consegue imaginar a mesma tabela sem índices e com milhões de registos??? n Não se metam nisso, é melhor…
57
Utilização de Índices
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Designar chaves primárias cria índice nesse atributo; n Devem ser aplicados apenas quando necessários (facilita a
pesquisa / consulta, mas dificulta a atualização para além do aumento do espaço em disco )
n Pode haver mais do que um índice numa tabela, os chamados índices compostos;
n Escolhas dos atributos que serão índices deve ser criteriosa e incidir sobre as pesquisas (e respetivos atributos) mais comuns.
Nota: Por alguns dos motivos apresentados, muita atenção na escolha dos índices… em vez de estar a optimizar a BD, pode estar a torná-la ainda mais pesada.
58
Utilização de Índices - Observações
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Criação de Índices – CREATE INDEX n Agora que já está um mestre em Índices, que tal passar
da teoria à prática com mais uns… comandos SQL… n Quer Criar Índice, ou seja,
59
q UNIQUE não permite que existam valores duplicados. q FULLTEXT só podem indexar colunas CHAR, VARCHAR e TEXT.
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
• Criar Tabela e Índice:
Criação de Índices – INDEX
Utilizando o exemplo do cliente, criar uma tabela e índice:
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
• Criar Tabela e Índice:
Criação de Índices – CREATE INDEX n Utilizemos o mesmo exemplo, mas neste caso…
l Primeiro cria-se a tabela
l E só depois é que é criado o índice
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Utilização de Índices – DROP INDEX n O que falta??? Além de animar a malta, mais uma vez… … é eliminar um índice, o famoso DROP:
62
DROP INDEX índice_nome ON nome_tabela
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Ficha de trabalho nº 12
63
Trabalhar com índices em Tabelas
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Transações n O conceito de transação é-vos familiar, em que
situação???
Toca a puxar pela cabeça, mas devagarinho para não aleijar Claro, é o exemplo clássico da transferência bancária xiiiii, pois é… Individuo A Individuo B
64
pretende transferir 1000€ para (quem é o sortudo???)
Uma transação / transferência bancária, não é mais do que o depósito numa conta (B) e levantamento noutra (A), mas então … Qual o problema? Desde que o depósito seja na nossa …
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Transações n … mas não é bem assim. Quais as duas ações / consultas
que é preciso fazer (e cujos comandos iremos abordar no próximo módulo):
n Depositar o valor na conta de destino (individuo B);
n Retirar o mesmo valor da conta de origem (individuo A)
O que aconteceria se falhasse uma das condições: § A luz falhava antes de colocar o dinheiro na conta de destino § Dinheiro é colocado na conta de destino, mas não é atualizado na
origem Todas as ações têm de ser feitas para concluir a operação senão, não deve ser tida em conta nenhuma delas. 65
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n É isso mesmo. Em linguagem popular, pode-se definir transação como um
Tudo ou Nada n E isto sem exageros, uma vez que: n Uma transação consiste numa unidade lógica de trabalho. n Se for composta por um conjunto de passos,
ou se realizam todos (Tudo) ou não se realiza nenhum (Nada). 66
Transações
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Transações n Aplicando o conceito ao exemplo da transferência bancária: n Objetivo é ter certeza que as duas contas são movimentadas.
l Senão, nenhuma delas deverá ser mexida, l independentemente da ordem das operações (comandos).
n Em SQL, início de transação é definido: l De forma implícita, depois de uma outra ter sido terminada; l Através de comando de início de transação (pode diferir dependendo do sistema
que está a utilizar )
67
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n No nosso caso, define-se uma transação da seguinte forma:
n Independentemente de como se inicia, termina sempre com:
n Um Commit; n Ou um Rollback.
68
Transações – Definir em SQL
START TRANSACTION <COMANDOS E INSTRUÇÕES A EXECUTAR> COMMIT/ROLLBACK
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n E perguntam vocês: Qual a diferença entre commit e rollback? n Commit -> dados que são alterados durante uma transação são
guardados de forma temporária à espera que o utilizador passe as alterações a definitivas, precisamente com este comando;
n Rollback -> ignora todas as alterações desde o início da transação e
deixa a BD no estado em que se encontrava antes. Vai ver que é possível retornar a um ponto da transação para começar a partir daí.
n Atenção: depois de executar um commit não pode executar um
rollback 69
Transações – COMMIT e ROLLBACK
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Funciona como o Checkpoint de um jogo. Alguém falou em jogo? n Quem??? Quem???
n Mas, já que o assunto veio à baila: n O que é o checkpoint de um jogo? n Se perder a vida, depois de um determinado Checkpoint, existe a
possibilidade de retomar o jogo a partir desse ponto n O SAVEPOINT acaba por funcionar de forma similar: n Obriga na mesma à realização da totalidade das operações (ou nenhuma); n Permite que se faça ROLLBACK até um SAVEPOINT; n Podem-se colocar vários ao longo da transação.
70
Transações – SAVEPOINT
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Transações – SAVEPOINT n Em código, que é isso que todo o aluno de PSI gosta…
71
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Aplicam-se a comandos de manipulação de dados, como: l INSERT; l UPDATE; l DELETE.
n Serão estudadas no Módulo 14 é que é já, já, já a seguir…
n Se apagar uma tabela com drop table… só lhe resta uma alternativa… e só mesmo essa rezar…. 72
Transações
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Ficha de trabalho nº 13
73
Aplicação Teórica de Transações
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Neste caso, quando se fala em segurança, refere-se a: n Privilégios sobre os elementos da BD; n Controlo de acesso à BD.
n Com aquilo que aprendeu e fez até agora, acha que as
suas BD estão seguras??? n É fácil, basta pensar no seguinte: n Qual o login e a password que coloca para aceder às
BD??? 74
Segurança
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Dá que pensar, não é verdade??? Meeeeeeedo…. n Neste momento tem quantos utilizadores na BD???
l Um n Qual?
l root (utilizador priveligiado que controla tudo)
n Tem password?
l Não, por defeito é vazia. l Então que fazer???
75
Segurança
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Para começar, que tal atribuir uma password ao root??? n Proteger o root, mas que bela e pioneira ideia…
SET PASSWORD FOR ”root”@”localhost” = PASSWORD(”123456”);
n Nome do utilizador: root n Local onde está instalado o SQL: localhost (neste caso localmente)
poderia ser num servidor (exemplo: [email protected], não é um email) n Password: 123456, pass escolhida para o user root
76
Segurança – Atribuir Password
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Segurança - Experiência n Que tal experimentar??? Força… Sem medos…
1. Abra o MySQL e entre como habitualmente, sem password;
2. Utilize a forma do slide anterior para atribuir password ao utilizador root;
3. Depois de estar tudo OK, feche a consola, volte a abrir e tente entrar
sem password. O que aconteceu??? Espero que alguma coisa …
Exato, a consola fechou logo, é necessário colocar a password anterior.
4. Agora sim, conseguiu entrar no modo consola como root parece tudo
bem, que tal experimentar o phpMyAdmin()… mas o que aconteceu??? 77
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Erro??? Não é possivel aceder ao phpMyAdmin??? Mas porquê???
78
Segurança - Experiência
Toca a pensar o problema está onde??? q No acesso ao phpMyAdmin; q Por causa da password que acabei de criar; q Que deve estar num ficheiro de
configuração; Que raciocínio fantástico, ainda por cima correto. Outra forma de fazer alterar password
de root :
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Onde se encontram os ficheiros do phpMyAdmin()???
c:\wamp\apps\phpmyadmin 5. Entre na pasta anterior e abra o ficheiro config.inc.php;
6. Abra o ficheiro (Notepad++), encontre a linha:
$cfg['Servers'][$i]['password'] = ‘ ‘;
E modifique-a para
$cfg['Servers'][$i]['password'] = ‘password de root definida em 2'; 79
Segurança - Experiência
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Faça o refresh do phpMyAdmin ou abra-o a partir do WAMP; n Agora sim, já deve ter acesso ao phpMyAdmin… … se modificar a password volte a repetir o processo
80
Segurança - Experiência
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Segurança – Criar Utilizadores n Agora que é um pro em BD, cá vai uma pergunta, mas
sem rasteira: onde é que o SQL guarda a informação sobre os utilizadores???
n Essa é fácil, óbvio que é numa… aiiiii…. TABELA, isso….
n Para aceder a uma área pessoal precisa de ter:
81
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Isso é tudo muito bonito, mas não vi nada sobre os utilizadores mesmo desconfiados, continuando….
3. select * from user; (será abordado no Módulo 14)
Ainda está confuso??? Que tal ver os atributos da tabela: 4. desc user; n Quais os campos que pretende visualizar??? n Nome do utilizador (user) e a password 82
Segurança – Criar Utilizadores
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Visualizemos então a informação pretendida: n 5. select user, password from user;
83
Finalmente tanta coisa para isto, não há uma maneira mais fácil??? Bem, por acaso até há phpMyAdmin Privileges:
§ Adicione / Elimine utilizadores; § Conceda / Retire Privilégios; § Altere Passwords
Segurança – Criar Utilizadores
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Sintaxe
n Exemplos
84
Segurança – Criar Utilizadores
CREATE USER user_specification [, user_specification] ...
user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']
CREATE USER 'joaquim'@'localhost';
CREATE USER 'joaquim'@'localhost' IDENTIFIED BY 'mypass';
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Eliminar utilizadores é muito mais simples e exige muito menos ginástica mental.
n Qual o comando que tem utilizado para apagar??? n Exactamente, o drop então:
drop user ‘nome_utilizador’@’host’;
n Simples e eficaz exemplo: drop user ‘daniela’@’localhost’; 85
Segurança – Eliminar Utilizadores
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Mas… como posso aceder a uma sessão MySQL??? n Belíssima questão…. n Vamos falar agora de e linha de comandos:
Aceda a: MySql_HOME (mesmo que dizer pasta do MySQL)/bin E execute o comando: mysql –u [nome_utilizador] -p
86
Segurança – Sessão MySQL
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Não está a funcionar??? Será que definiu o utilizador em localhost???
87
Segurança – Sessão MySQL
É um pormenor, mas de classe e daqueles que faz toda a diferença…
Agora sim, está pronto para iniciar sessão mysql no localhost. Faça show databases e repare nas BD que aparece,
Qualquer host
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Segurança - Privilégios n Neste momento temos um utilizador que não tem
permissões para fazer nada, pouco útil, não é verdade??? n Apesar de todos serem utilizadores:
l Root tem permissão para executar qualquer comando SQL; l Utilizadores criados de novo não têm privilégios.
n Existe forma de alterar esta situação??? n Em informática tudo tem solução... ou não...
88
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Segurança – Privilégios … Grant n Por isso é que existe o comando… GRANT
Este comando permite… ... controlar, de forma precisa, aquilo que cada utilizador pode fazer com as
tabelas e respetivas colunas.
n Ou seja, l Apenas alguns utilizadores podem modificar determinadas tabelas; l Dados de uma tabela acessíveis apenas a determinados utilizadores; l Alguns utilizadores podem ver atributos que outros não podem.
Para atribuir privilégios faça um levantamento cuidadoso daquilo que cada utilizador precisa de fazer na BD.
89
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Imagine uma BD muito simples, sobre Encomendas e os seguintes utilizadores:
90
Segurança – Privilégios … Grant
Implemente-a, bem como os utilizadores
Rute… ou antes: root
Olegário Vanessa Óscar
Felisberta Ambrósio Albertina
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Neste ponto… … apenas o utilizador root pode efetuar operações sobre a BD.
…nenhum dos outros utilizadores tem permissões para fazer o que quer que seja na BD. n Imagine que a menina Felisberta trabalha no atendimento ao público e
precisa de selecionar (SELECT) informação da tabela (ON) Cliente…
n é preciso conceder (GRANT) permissão à (TO) Felisberta para que possa ter acesso à tabela Cliente e possa selecionar a informação necessária. 91
Segurança – Privilégios … Grant
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Segurança – Privilégios … Grant n Para isso, utiliza-se o comando:
GRANT SELECT ON Cliente -> concede permissão de select na tabela Cliente TO Felisberta -> para a utilizadora Felisberta
92
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Algumas particularidades do comando anterior:
n Atribuir mais que um privilégio ao mesmo tempo: GRANT SELECT, DELETE, INSERT, UPDATE ON Cliente TO Felisberta, Ambrósio, Albertina; n Atribuir mesmos privilégios numa tabela a múltiplos utilizadores: GRANT SELECT ON Encomenda TO Ambrósio, Albertina; n Atribuir privilégios para apenas um atributo de uma tabela: GRANT SELECT(Nome) ON Cliente TO Felisberta
93
Segurança – Privilégios … Grant
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Abreviar todas as permissões com ALL: GRANT ALL ON Produto TO Felisberta, Ambrósio, Albertina;
n Um utilizador pode atribuir as suas permissões a outro com: GRANT DELETE ON Cliente TO Felisberta WITH GRANT OPTION;
n Mesma permissão, para o mesmo utilizador, mas tabelas diferentes, obriga a descriminar um GRANT para cada uma das tabelas: GRANT SELECT ON Cliente TO Felisberta; GRANT SELECT ON Produto TO Felisberta;
94
Segurança – Privilégios … Grant
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Que tal dar uma espreitadela a uma breve lista de privilégios:
95
Segurança – Privilégios … Grant
Privilégio Descrição SELECT Permite selecionar todas as colunas de uma determinada tabela
SELECT (Lista) Permite selecionar as colunas especificadas em lista
INSERT
Permite inserir registos em todas as colunas da tabela
INSERT (Lista)
Permite inserir valores na lista das colunas de uma tabela
UPDATE
Permite alterar conteúdos em todas as colunas da tabela
UPDATE (Lista)
Permite alterar o conteúdo da lista das colunas da tabela
DELETE
Permite apagar registos de uma tabela
ALL [PRIVILEGES]
Permite atribuir todos os privilégios acima especificados
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Pergunta para queijinho, mas não é desses. n Mas será que já ninguém joga Trivial Pursuit??? n Assim está melhor (Dica: utilize o * sabendo que significa match com qualquer string, mesmo a vazia) n Como acha que poderia fazer para dar o privilégio SELECT, à Dona Felisberta, para todas as tabelas da BD Encomendas:
GRANT SELECT ON Encomendas.* TO Felisberta; 96
Segurança – Privilégios … Grant
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n E se houvesse um comando que:
Ao mesmo tempo que se criasse um utilizador: create user Felisberta identified by ‘semprefelisebela’;
Desse para atribuir / conceder os privilégios: grant select on Cliente to Felisberta;
n Bem, isso era genial chamaram??? Os seus desejos são ordens
grant select on Cliente to Felisberta identified by ‘semprefelisebela’;
97
Segurança – Privilégios … Grant
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Lá diz a velha máxima, quem dá também tira. n Que é como quem diz, quem dá privilégios…. ... tem de ter forma de os tirar e nem vamos falar da Troika.
n Vamos falar sim do comando, REVOKE:
98
Segurança – Privilégios … Revoke
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Imagine que afinal a Dona Felisberta foi promovida para outro setor e já não precisa da permissão de SELECT na tabela Cliente, muito simples:
REVOKE SELECT ON Cliente -> remove o privilégio SELECT FROM Felisberta; -> do utilizador Felisberta n Diferenças para o GRANT??? Mais visíveis é difícil:
l Em vez de GRANT tem REVOKE; l Em vez de TO (conceder a) tem FROM (remover de)
99
Segurança – Privilégios … Revoke
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Pergunta… Será que é possível remover WITH GRANT OPTION n Pode fazê-lo deixando o respetivo privilégio intacto. Como assim???
REVOKE GRANT OPTION ON DELETE ON Cliente FROM Felisberta;
n Com este comando: l Felisberta continua a poder utilizar o comando DELETE; l Mas deixa de poder atribuir esse privilégio a outros utilizadores
100
Segurança – Privilégios … Revoke
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n Que tal usar agora um brasileirismo, revokando do conhecido verbo revokar eu revoko, tu revokas, ele revoka
101
Segurança – Privilégios … Revoke
Imagine a seguinte situação:
Rute Vanessa Óscar
Atribui privilégio SELECT com GRANT OPTION na tabela Produto à
Que passa esse privilégio ao
Como fazer o REVOKE??? E qual o impacto na Vanessa e no Óscar… Isto vai dar problemas … ou não…
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
n “Revokando” com precisão, duas formas de abordar a questão: n REVOKE SELECT ON Produto FROM Vanessa CASCADE; Remove privilégios do utilizador alvo (Vanessa) e de todos aqueles a quem ele concedeu permissões (Óscar) efeito dominó (por defeito, se não colocar nada, é este ) n REVOKE SELECT ON Produto FROM Vanessa RESTRICT; Dá erro se quiser remover os privilégios de um utilizador que concedeu privilégio(s) a outro(s). n Neste caso, ocorreria um erro e ambos os utilizadores continuariam
com os seus privilégios, uma vez que a Vanessa concedeu privilégio. 102
Segurança – Privilégios … Revoke
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
Ficha de trabalho nº 14
103
Segurança, Utilizadores e Privilégios
Mód
ulo
15 –
Lin
guag
em d
e D
efin
ição
de
Dad
os
The End…
104
