Banco de Dados I
Aula 20
Agenda
• Notícia
• Comentários sobre os artigos trabalhados
• Prática no MySQL
Texto 1. Gerenciamento de Usuários
e Controle de Acessos do MySQL
• Controles de acessos do MySQL
• Criação de usuários e gerenciamento de seus
privilégios.
Limitação de Acesso
• O MySQL possui um mecanismo que permite limitar
o acesso de um usuário a apenas um banco, tabela
ou coluna, além de poder controlar o acesso de
acordo com o host a partir de onde está sendo feita a
conexão com o servidor.
• Pode-se ainda, conceder privilégios diferentes para
cada host de onde o usuário possa estabelecer a
conexão. Assim, é possível que determinados
comandos possam ser executados somente quando o
usuário estiver em um host específico, por exemplo
o mesmo host do servidor MySQL (localhost).
Sintaxe do Comando
• . A sintáxe resumida do comando GRANT é
exibida a seguir:
• GRANT priv [(colunas)] [, priv
[(colunas)]] ...
ON {*.* | db.* | db.tabela}
TO usuario [IDENTIFIED BY 'senha']
[, usuario [IDENTIFIED BY 'senha']] ...
Exemplo
• No exemplo a seguir é criado um usuário com o nome
teste que pode se conectar somente do host onde o
servidor está em execução (localhost), o usuário só
poderá fazer SELECT nas colunas nome e idade da
tabela pessoa, que se encontra no banco de dados rh. A
senha do usuário é 12345.
• mysql>GRANT SELECT (nome, idade) ON
rh.pessoa TO teste@localhost
IDENTIFIED BY "12345";
• Para listar os privilégios deste usuário utilize o comando:
• mysql>SHOW GRANTS FOR teste@localhost;
Dicas
• Remover ou alterar a senha dos usuários:
• Anônimo e root na instalação
Texto 2. Implementando Integridade
Referencial no MySQL
• Para adicionarmos restrições de integridade
(constraints) às chaves estrangeiras, é necessário
criar as tabelas como InnoDB.
Tipos de Restrições
• CASCADE: ao se remover um registro da tabela
referenciada pela chave estrangeira os registros
relacionados àquele removido serão eliminados em
todas as tabelas relacionadas.
• RESTRICT:não permite a remoção de registros
que possuam relacionamentos em outras tabelas.
• SET NULL e SET DEFAULT. atribuem os valores
DEFAULT ou NULL para as chaves estrangeiras
cujos registros relacionados foram excluídos.
Exemplo
• CREATE TABLE aluno (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome CHAR(30) NOT NULL
) TYPE=InnoDB;
• CREATE TABLE cursos (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome CHAR(30) NOT NULL
) TYPE=InnoDB;
• CREATE TABLE notas (
aluno_id INT NOT NULL,
cursos_id INT NOT NULL,
date DATE NOT NULL,
nota DOUBLE NOT NULL,
PRIMARY KEY(aluno_id, cursos_id, date),
INDEX i2 (cursos_id),
FOREIGN KEY (aluno_id) REFERENCES aluno(id) ON DELETE CASCADE,
FOREIGN KEY (cursos_id) REFERENCES cursos(id) ON DELETE
RESTRICT
) TYPE=InnoDB;
Explicação
• A tabela cursos que contém as disciplinas ministradas e a
tabela notas com os pontos dos alunos em todos os cursos
freqüentados por eles.
• No modelo é possível que um curso possua várias avaliações
em datas distintas. Neste caso, foram criadas as tabelas como
tipo InnoDB (TYPE=InnoDB), para que as regras de
integridade sejam respeitadas. As regras definidas foram: um
CASCADE para aluno, isto é, se for removido um registro da
tabela de aluno, todas as suas notas serão removidas
automaticamente.
• No caso da tabela de cursos, não será possível remover um
curso que possua notas cadastradas para ele.
• Além da restrição ON DELETE, o InnoDB permite também o
ON UPDATE, que aplica as restrições no caso de
atualizações dos campos ralacionados entre as tabelas.
Texto 3. Trabalhando com os Vários
Tipos de Tabelas do MySQL
• No MySQL é possível escolher o tipo da tabela
no momento da criação da mesma.
• O formato de armazenamento dos dados, bem
como alguns recursos do banco de dados são
dependentes do tipo de tabela escolhido.
• A definição do tipo de tabela é feito através do
comando CREATE TABLE, como mostrado a
seguir:
MyISAM:
• Este é o tipo de tabela padrão do MySQL. Caso
não seja informado o tipo de tabela, o MySQL
criará a tabela do tipo MyISAM.
• Problemas:
• Lock de tabela inteira
• Instável, não transacional ( não possui Commit
e Rollback)
• Vantagem:
• Desempenho
InnoDB:
• O InnoDB é um tipo de tabela transacional, desenvolvido
pela InnoDBase Oy. A partir da versão 4.0 do MySQL ele
passa a ser parte integrada das distribuições do MySQL.
• Transacional,
• Integridade referencial, com implementação dos
constraints SET NULL, SET DEFAULT, RESTRICT
e CASCADE;
• Ferramenta de backup on-line (ferramenta comercial,
não GPL);
• Lock de registro, como Oracle, DB2, etc;
• Níveis de isolamento;
• Armazenamentos de dados em tablespace.
Tablespace
• Tablespace é um termo em língua inglesa que
designa uma sub-divisão lógica de um banco de
dados utilizado para agrupar estruturas lógicas
relacionadas.
• As tablespaces apenas especificam a localização de
armazenamento do banco de dados e são
armazenadas fisicamente em datafiles, que alocam
imediatamente o espaço especificado na sua criação.
• Exemplo:
• A primeira tablespace criada pelo ORACLE é a
System.
Tablespace no Postgresql 8
• Local no sistema de arquivos onde serão armazenados os
objetos;
• Melhoria no gerenciamento de discos;
• Selecionar os sistemas de arquivos que irá armazenar as
informações (isso inclui esquemas, tabelas e índices
posso escolher uma determinada PASTA no servidor a
qual será utilizada para armazenar uma determinada
informação);
• No PostgreSQL, os objetos como tabela e índices são
arquivos sem extensão e nomeados numericamente –
OID – Object ID).
Utilidades
• Tuning: Podemos criar uma tablespace chamada
table_index a qual está vinculada ao segundo HD e
nesta tablespace armazenaremos TODOS os índices
de nosso banco de dados.
• Com isso, o acesso à informação é mais rápido e
diminui a sobrecarga do HD (e todo o conjunto
lógico/físico envolvido) para ler dados e índices;
• Gerenciamento de Espaço: Através deste
conceito é possível adicionar um outro HD e criar
uma nova tablespace para que as novas tabelas e
índices sejam colocadas neste novo HD com o
intuito de não sofrer com problemas de espaço em
disco.
Prática
• No MySQL
▫ 1. Criar tabelas do vistas no texto com restrições
de integridade;
▫ 2. Criar Expecificar permissões PARA
User1/Localhost com o comando GRANT.