Baixe o app para aproveitar ainda mais
Prévia do material em texto
CCDD – Centro de Criação e Desenvolvimento Dialógico 1 Sistema Gerenciador de Banco de Dados Aula 1 Profª. Martin Morães CCDD – Centro de Criação e Desenvolvimento Dialógico 2 Conversa Inicial Os índices e as views são os primeiros recursos que surgem em uma base de dados após as tabelas e os dados. Os índices, quando bem utilizados, dão ganho de performance nas operações com a base de dados. Já as views propiciam facilidades na disponibilização dos dados. Acesse o material online e conheça o professor Martin Morães, que ministrará esta disciplina. Contextualizando Imagine uma tabela com milhões de registros. Agora imagine que você precise encontrar alguns registros de uma determinada especificação. Será que para isto será necessário avaliar um por um todos os registros desta tabela? Que trabalho, hein? Para diminuir o tempo dessa operação é melhor você utilizar os índices, já que por meio deles podemos utilizar métodos de buscas mais eficientes. Mas hoje é o seu dia de sorte, pois você aprenderá a fazer exatamente isso. Outra situação que iremos abordar são as views, que são métodos que facilitam a disponibilização de dados para a consulta. Pronto para começar? Confira a contextualização do professor Martin Morães sobre este conteúdo no material online. CCDD – Centro de Criação e Desenvolvimento Dialógico 3 Pesquise Os Índices “Os índices são utilizados para encontrar registros com um valor específico de uma coluna rapidamente, ou seja, eles agilizam o processo de localização de registros com base no valor indexado. ” (Oracle Corporation e/ou suas afiliadas, 2015). Sem os índices o processo de encontrar um valor em um campo de uma tabela ocorre de forma sequencial, ou seja, todos os registros da tabela precisam ser testados para verificar se o valor do registro atende aos requisitos da busca. Nessa condição, considere o tempo de busca. Quanto maior a base de dados, quanto mais registros na tabela, maior é o tempo de busca. Os índices reduzem esse tempo de busca, pois consistem em uma tabela auxiliar em que os valores estão ordenados de tal forma que possibilitam uma busca binária ou outro sistema de busca mais eficiente que o método sequencial. No SQL os índices são utilizados, principalmente para: Localizar os registros que atendem a cláusula where; Recuperar registros de outras tabelas ao realizar joins; Encontrar o valor MIN () ou MAX () quando a coluna indicada está indexada; Ordenar ou agrupar uma tabela, se a ordenação ou agrupamento for feito com campos indexados; Outros. Para exemplificar o papel dos índices, considere a tabela de dados (alunos) a seguir, formada pelos campos: identificação, nome, data de nascimento e telefone. Os registros estão organizados nesta ordem de inserção. CCDD – Centro de Criação e Desenvolvimento Dialógico 4 Esta tabela recebe contínuos processos de consulta pelo campo “nome”. Se quisermos listar os registros cujos nomes comecem pela letra “M” é necessário que se percorra todos os registros da tabela, e quanto maior a tabela maior o tempo para concluir essa operação. Se para percorrer mil (1000) registros é despendido um tempo X, para percorrer dois mil (2000) registros teremos a necessidade de aproximadamente 2X de tempo. Além disso, acrescente a espera na lista de requisições de consulta, aguardando para ser atendida. Casos com grandes bases de dados e com muitas consultas gera uma demora de resposta insustentável no sistema. Nesse exemplo, podemos reduzir o tempo desta busca e espera, implementando um índice para o campo “nome”, o campo que tem uma expressiva quantidade de consultas. Este índice ficaria como a figura a seguir, em que teríamos na coluna “Nome” os nomes ordenados por ordem alfabética e na coluna “Registro” o número da posição do respectivo registro em sua tabela de origem, ou seja, o CCDD – Centro de Criação e Desenvolvimento Dialógico 5 Sr. Fleur Cardenas é o 4º registro na tabela de dados (origem), “Germane Spears” é o 11º registro na tabela de dados, e assim sucessivamente. Agora que temos uma ideia de como é a estrutura de um arquivo de índice, vamos ver como ele auxilia na redução do tempo de busca. Para encontrar os registros cujos nomes começam com a letra “M”, o SGDB vai fazer uma busca binária no arquivo de índice do campo “Nome”. Isto vai ser muito rápido pois a pesquisa binária é muito mais rápida do que a pesquisa sequencial. Ao encontrar os nomes que satisfaçam o quesito da busca, o SGDB pega o valor da coluna “Registro”, que indica a posição na tabela, e faz uma leitura diretamente na posição do respectivo registro na tabela de dados. CCDD – Centro de Criação e Desenvolvimento Dialógico 6 Veja como isso acontece: Criar Índices Com os SGDBs, a criação de um índice é muito simples. É apenas uma instrução SQL do tipo DDL – CREATE. A instrução para criar índice pode ser aplicada durante ou após a criação da tabela. A instrução é aplicada a cada campo que se queira tornar um índice. Para tornar um campo indexado na criação da tabela utiliza-se a instrução: “INDEX(nome_da_coluna)” Por exemplo: CCDD – Centro de Criação e Desenvolvimento Dialógico 7 Para tornar um campo indexado após a criação da tabela, utiliza-se o comando: O nome_do_index é um identificador para o índice que está sendo criado. Usualmente é colocado um prefixo que distingue como um identificador para um índice. É utilizado para remover, desabilitar ou habilitar o referido índice. O nome_tabela é a tabela que contem a coluna que será indexada. O nome_coluna é a coluna que será indexada. Exemplo: Remover índices Para remover um índice utiliza-se o comando: O nome_do_index é o nome que foi dado ao índice na sua criação. O nome_tabela é o nome da tabela de onde o referido índice está sendo removido. É necessário indicar o nome da tabela no processo de exclusão do índice, pois o nome do índice pode ser repetido em diferentes tabelas, mas não pode ser repetido na mesma tabela. CCDD – Centro de Criação e Desenvolvimento Dialógico 8 A atualizações dos índices são realizadas pelo próprio SGDB, não podemos interagir no processo. Cuidados Os índices dão um grande ganho na performance das consultas, mas degradam a performance nas operações de inserção. Isto se dá pela necessidade de indexar os novos valores. Não existe um ponto ideal definido para a criação ou não de um índice. Nesse contexto é que surge o projeto físico de banco de dados, onde os estudos e planejamentos determinarão as necessidades da organização quanto a performance. Em geral os índices são criados para ganhar performance na geração de relatórios, consultas, atualizações e exclusão. Quando a performance na inserção de dados fica comprometida faz-se o estudo, ou seja, entra em ação o projeto físico. Em um processo, em que uma massa significativa de dados será inserida na base de dados é válido considerar a desativação dos índices até a conclusão do referido processo. Isto reduzirá o tempo da inserção de dados. O que será que o professor Martin Morães tem a dizer sobre esse assunto? Confira no material online As Views As views são tabelas virtuais que disponibilizam os dados de uma base mediante determinadas especificações. Elas são bastante úteis no universo de bancos de dados, pois facilitam a solução de várias necessidades. Considere o seguinteconceito para view: Uma view (visão) é uma tabela derivada de outras tabelas. Essas outras tabelas podem ser tabelas de base ou views previamente definidas. Uma view não existe, necessariamente, em forma física. É considerada uma tabela virtual, ao contrário das tabelas da base, cujas tuplas sempre estão armazenadas fisicamente no banco de dados. Isso limita as operações de atualização que lhes CCDD – Centro de Criação e Desenvolvimento Dialógico 9 podem ser aplicadas, mas não limita sua consulta. (Claudia Vicci Amadeu, 2014, p. 163). Uma fonte de dados As views tem como característica principal a disponibilização de dados como se fosse uma tabela. Os dados que uma view disponibiliza são oriundos de outras views e/ou de tabelas. Na prática é uma instrução SQL de select que se transforma em uma view, ou seja, em uma fonte permanente de consultas SQL. Amadeu (2014, p. 164) reforça que: “Podemos especificar consultas SQL em uma view da mesma forma como fazemos consultas envolvendo tabelas da base”. Para podermos tratar uma view como uma tabela, a mesma tem de ter uma estrutura semelhante. Amadeu (2014, p. 163) detalha a estrutura de uma view ao dizer que: “A view recebe um nome de tabela (virtual), ou nome de view, uma lista de nomes de atributo e uma consulta para especificar seu conteúdo”. Veja mais uma definição bem interessante de view. “Pensamos em uma view como um modo de especificar uma tabela que precisamos referenciar com frequência, embora ela possa não existir fisicamente”. ELMASRI ET. AL. (2011, p. 89) Atualização da view O SGDB implementa os mecanismos para que as views forneçam os dados atualizados, sem a necessidade de intervenção. Amadeu explica este mecanismo ao dizer: Supõe-se que uma view esteja sempre atualizada. Se modificarmos as duplas nas tabelas da base sobre as quais a view é definida, esta precisa refletir automaticamente as mudanças. Portando, a view não é materializada no momento de uma definição, mas quando especificamos uma consulta na view. É função do SGDB, e não do usuário, manter a view atualizada. (Claudia Vicci Amadeu, 2014, p. 165) CCDD – Centro de Criação e Desenvolvimento Dialógico 10 Toda vez que consultarmos uma view os dados que ela fornecer estarão atualizados, pois o SGDB faz a verificação a cada utilização da mesma. View em linha Você também pode definir uma tabela de view na cláusula FROM de uma consulta SQL. Amadeu (2014, p. 168) esclarece que essa operação é conhecida como view em linha. Nesse caso, a view é definida na própria consulta. Este processo também é conhecido como sub-query no from. Neste formato, a view é anônima, o que implica que a mesma não será reutilizada em outros processos. Entenda melhor o conceito e aplicação das views assistindo à explicação do professor Martin Morães no material online. Trocando ideias Compartilhe com os seus colegas, por meio do fórum, sua percepção sobre a necessidade de criação de índices e views em diversos setores de trabalho, principalmente em relação à performance no tratamento de dados. Na Prática A principal aplicação de uma view é disponibilizar dados para consulta. Porém, alguns fatores motivam a consulta de dados em uma view. Amadeu (2014, p. 164) refere-se a esses aspectos ao dizer: “Uma das principais vantagens de uma view é simplificar a especificação de certas consultas. As views também são usadas como um mecanismo de segurança e autorização”. Simplificar Consultas: é comun que conjuntos de dados formados por várias tabelas sejam filtrados e tratados para serem utilizados em diferentes relatórios ou consultas. Uma view pode ser inserida neste contexto para que a instrução de consulta SQL fique simples e possa ser reaproveitada em outras aplicações. CCDD – Centro de Criação e Desenvolvimento Dialógico 11 Integridade dos Dados: um grupo de usuários pode receber privilégios de leitura em uma view e não receber privilégios nas tabelas de origem dos dados da mesma. Isto cria um bom nível de segurança na integridade dos dados. Autorização de Acesso: pensando em confidencialidade dos dados, por meio das views é possível criar visões de dados distintas para cada grupo de usuários. Criar uma view A criação de uma view é uma instrução SQL utilizando CREATE VIEW, veja a sintaxe a seguir: Na sintaxe apresentada, de criação de uma view, indicamos entre colchetes o que é opcional. “OR REPLACE” é uma instrução opcional na criação de uma view e quando utilizada serve para substituir uma view já existente. Conheça outros conceitos: view_name: um nome para a view. Assim como as tabelas, as views necessitam de um nome exclusivo. Se já existir uma view com o mesmo nome, crie uma nova com o nome “OR REPLACE”, assim a que já existe será substituída pela nova view. column_list: uma lista com os nomes da colunas que a view terá. Esse serviço é opcional, se não for fornecido, a view manterá os nomes das colunas das tabelas de origem. select_statement: esta parte é uma instrução “SELECT” que será a fonte de dados para a view. CCDD – Centro de Criação e Desenvolvimento Dialógico 12 Vamos considerar as seguintes tabelas: alunos, professores e aula. Uma instrução SQL que liste os nomes dos professores e respectivos alunos seria: CCDD – Centro de Criação e Desenvolvimento Dialógico 13 Confira o resultado. Para transformar este SELECT em uma view com nome lista_nomes, basta acrescentar as instruções de criação da view. Além disso, temos que indicar nomes distintos para as colunas. Uma opção é indicar os nomes das colunas como no código a seguir: CCDD – Centro de Criação e Desenvolvimento Dialógico 14 Outra opção é indicar o nome para as colunas resultantes do SELECT, como no código a seguir. Excluindo uma view Para excluir uma view, utiliza-se a instrução DROP indicando que é uma VIEW e o nome da view. O “IF EXISTS” é opcional e tem o efeito de enviar uma mensagem de erro se a view não existir. Mais de uma view pode ser excluída na mesma instrução, basta separar o nome de cada uma com vírgula. Neste exemplo estamos excluindo a view lista_nomes. Consultando uma view Para incluir uma view em uma consulta SQL basta tratá-la como uma tabela. No exemplo a seguir, a instrução SELECT está listando os cinco primeiros registros, resultantes da seleção ordenada pela primeira coluna, da view lista_nomes. Observe: CCDD – Centro de Criação e Desenvolvimento Dialógico 15 Uma view não tem seus campos indexados. Pensando nisso, pode ser que uma consulta que requeira filtros seja mais lenta em uma view do que em uma tabela que tenha índices apropriadamente. Para determinar a melhor opção é necessário implementar as opções e fazer a tomada de tempo para cada situação. Síntese Com os índices tem-se um ganho considerável na performance digital, principalmente quando as instruções SQL se utilizam da cláusula WHERE nos campos que estão indexados. Dessa forma, não é mais necessário percorrer todos os conteúdos de uma base de dados para encontrar os registros que satisfaçam um determinado requisito. Porém, o excesso de índices pode comprometer outras operações na base de dados, daí a necessidade de avaliar a performance das demais operações após a criação deles. As views facilitam a disponibilização de dados podendo ser utilizadas para diminuir a utilização de uma instrução SQL repetidas vezes, para restringir acesso a determinados dados epara implementar mais uma camada de segurança na integridade dos mesmos. Vamos às considerações finais do professor Martin Morães sobre o conteúdo abordado? Acesse o material online. CCDD – Centro de Criação e Desenvolvimento Dialógico 16 Referências AMADEU: Claudia Vicci (Org.). Banco de Dados. São Paulo: Pearson Education do Brasil, 2014. Coleção Bibliografia Universitária Pearson. ELMASRI, Ramez; NAVATHE, Shamkant B.; VIEIRA, Daniel; SERAPHIM, Enzo; SERAPHIM, Thatyana de Faria Piola (Orgs.). Sistemas de Banco de Dados. 6. ed. São Paulo: Pearson Education do Brasil, 2011.xviii, 788 p. ORACLE CORPORATION e / ou suas afiliadas. Como o MySQL Utiliza Índices. Disponível em: <https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html>. Acesso em: 17 nov. 2015. CCDD – Centro de Criação e Desenvolvimento Dialógico 1 Sistema Gerenciador de Banco de Dados Aula 02 Prof. Martin José Fagonde Morães CCDD – Centro de Criação e Desenvolvimento Dialógico 2 Conversa inicial Segurança em Sistemas de Gerenciamento de Banco de Dados (SGDB) é assunto amplo e contínuo. Nesta aula, vamos discutir as principais ameaças a uma base de dados, a responsabilidade do DBA, controle de acesso como medida de segurança imprescindível, considerações pós-sinistro e o processo de auditoria. Veja o vídeo do professor Martin em que ele comenta mais o que vamos estudar nesta aula. Acompanhe na rota! Contextualizando É um tema amplo considerando o vasto mateiral e situações a serem consideradas e é um tema contínuo por que a todo instante surgem novas tecnologias, possibilidades, riscos etc. A segurança de uma base de dado tem de ser tratada com diversas visões. Decidir como projetar considerações de privacidade na tecnologia para o futuro incluem dimensões filosóficas, legais e práticas. Existe uma sobreposição considerável entre questões relacionadas ao acesso a recursos (segurança) e questões relacionadas ao uso apropriado da informação (privacidade). (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 566) A segurança não está desassociada da privacidade e vice-versa. No documentário “Sujeito a Termos e Condições” do diretor Cullen Hoback (HOBACK, 2013), percebe-se o impacto de uma sobre a outra. As causas das ameaças a uma base de dados são muito variadas, podendo ser decorrentes de desastres como tsunami, rompimento de barragens, desmoronamentos etc. e também podem ser decorrentes de ações intencionais de danificar, induzir ao erro, causar prejuízo, apropriar-se de informações etc., como também podem ser decorrentes do inadvertido de comandos, operações etc. As bases de dados são fundamentais para a continuidade das CCDD – Centro de Criação e Desenvolvimento Dialógico 3 operações das organizações. As operações das organizações estão fortemente embasadas nas bases de dados, de tal forma que a indisponibilidade das mesmas, invariavelmente, impossibilitará vários processos. Neste estudo, entre outras questões, trabalharemos nas seguintes questões: Quais os recursos e melhores práticas para proteger a base de dados? O SGDB dá conta sozinho? Qual o papel do DBA? Antes de prosseguir, assista ao vídeo do professor Martin em que ele contextualiza o assunto desta aula. Confira! Pesquise Tema 01: Ameaças ao Banco de Dados Navathe et al. (2011, p. 563) ressalta que “as ameaças aos bancos de dados podem resultar na perda ou degradação de alguns ou de todos os objetivos de segurança comumente aceitos: integridade, disponibilidade e confidencialidade”. Em outras palavras, um banco de dados seguro disponibiliza dados íntegros garantindo confidencialidade. Vamos entender melhor. Confidencialidade Podemos pensar em confidencialidade de forma ampla como tratado por Navathe et al. (2011): Perda de confidencialidade. A confidencialidade do banco de dados refere-se à proteção dos dados contra exposição não autorizada. O impacto da exposição não autorizada de informações confidenciais pode variar desde a violação do Data Privacy Act até o comprometimento da segurança nacional. A exposição não autorizada, não antecipada ou não intencional poderia resultar em perda de confiança pública, constrangimento ou ação legal contra a organização. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 563) O Data Privacy Act é equivalente a nossa Lei n.º 12.965, o marco civil da Internet. A confidencialidade está relacionada ao sigilo e à privacidade. Quanto ao sigilo, no Artigo 7º do marco civil da Internet, encontramos que aos CCDD – Centro de Criação e Desenvolvimento Dialógico 4 usuários estão assegurados alguns direitos quanto aos seus dados. Art. 7º O acesso à internet é essencial ao exercício da cidadania, e ao usuário são assegurados os seguintes direitos: I - inviolabilidade da intimidade e da vida privada, sua proteção e indenização pelo dano material ou moral decorrente de sua violação; II - inviolabilidade e sigilo do fluxo de suas comunicações pela internet, salvo por ordem judicial, na forma da lei; III - inviolabilidade e sigilo de suas comunicações privadas armazenadas, salvo por ordem judicial; (BRASIL, 2014) Quanto à privacidade o artigo 8º do marco civil da Internet diz: “A garantia do direito à privacidade e à liberdade de expressão nas comunicações é condição para o pleno exercício do direito de acesso à Internet.” (BRASIL, 2014). A exposição de dados não autorizados, ou seja, a perda de confiabilidade impacta, entre outros, os fornecedores, clientes, funcionários e a própria organização. Saiba mais: Para reforçar este aspecto, veja a matéria sobre o vazamento de dados da Sony, clicando no link a seguir: http://exame.abril.com.br/tecnologia/noticias/10-celebridades-afetadas-pelo- vazamento-de-dados-da-sony Disponibilidade A perda de disponibilidade implica na interrupção de uma ou mais atividades por não poder acessar algum dado vital na atividade. Navathe et al. (2011, p. 563) trata deste conceito da seguinte forma. Perda de disponibilidade. A disponibilidade do banco de dados refere-se a tornar os objetos disponíveis a um usuário humano ou a um programa ao qual eles têm um direito legítimo. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 563) A indisponibilidade interrompe a continuidade do processo, implica degradação na qualidade do serviço prestado. Integridade A integridade implica que os dados disponibilizados estão correto, ou seja, não foram adulterados. Navathe, et al. (2011, p. 563) definiu da seguinte CCDD – Centro de Criação e Desenvolvimento Dialógico 5 forma. Perda de integridade. A integridade do banco de dados refere-se ao requisito de que a informação seja protegida contra modificação imprópria. A modificação de dados inclui criação, inserção, atualização, mudança do status dos dados e exclusão. A integridade é perdida se mudanças não autorizadas forem feitas nos dados por atos intencionais ou acidentais. Se a perda da integridade do sistema ou dos dados não for corrigida, o uso continuado do sistema contaminado ou de dados adulterados poderia resultar em decisões imprecisas, fraudulentas ou errôneas. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 563) Vamos aprender mais com o professor Martin, assista ao vídeo em que ele explica mais detalhes sobre as ameaças ao banco de dados. Tema 02: Responsabilidades Garantir confidencialidade, disponibilidade e integridade em uma base de dados é uma tarefa constante e, conforme Navate, et al. (2011, p. 566), “éresponsabilidade do administrador de banco de dados e do administrado de segurança impor coletivamente as políticas de segurança de uma organização”. As políticas de segurança devem ser criadas e trabalhadas por um comitê, envolvendo os diversos setores e níveis da organização, envolve decisões em que os gestores devem estar envolvidos e as quais devem aprovar. Uma política de segurança deve ser implementada para que aja processos, critérios e legalidade nas ações adotadas. Uma das medidas imprecindíveis, segundo Navate, et al. (2011, p. 566), diz que o “acesso deve ser permitido a certo atributo do banco de dados (também conhecido como coluna da tabela ou um elemento de dados) ou não para usuários individuais ou para categorias de usuários”, eles resaltam que: As responsabilidades do administrador do banco de dados (DBA) incluem conceder privilégios aos usuários que precisam usar o sistema e classificar os usuários e dados de acordo com a política da organização. O DBA tem uma conta de DBA no SGDB, também conhecida como conta do sistema ou conta de superusuário, que oferece capacidade poderosas que não estão disponíveis às contas e usuários comuns do banco de dados. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 564). CCDD – Centro de Criação e Desenvolvimento Dialógico 6 O controle de acesso neste ponto está tratando do acesso lógico, ou seja, aos dados propriamente dito. Embora seja evidente a responsabilidade do DBA, outros personagens da organização também devem ser envolvidos e responsabilizados. O DBA tem responsabilidades em implementar as restrições de acesso, seguindo as deifnições das políticas de segurança da organização. O controle de acesso lógico envolve o coordenador de recursos humanos (RH), os diretores e encarregados de cada área, pois nos processos estão envolvidos os dados, os sistemas, os colaboradores. Todo o trabalho de controle de acesso lógico é perdido se não tiver controle do acesso físico e outras medidas que discutiremos mais adiante. Agora, o professro Martin vai comentar mais sobre as responsabilidades da segurança do banco de dados. Tema 03: Controle de Acesso O controle de acesso consiste em conceder ou revogar privilégios, classificar e/ou definir um grupo de permissões. Pode-se começar definindo o pressuposto do controle de acesso, ou seja: No acesso é tudo permitido exceto se for expressamente proibido. No acesso é tudo proibido exceto se for expressamente permitido. Em “tudo permitido” a implementação é menos trabalhosa, recomendado para um grupo pequeno de colaboradores, e em “tudo proibido” é mais recomendado para grupos com muitos colaboradores ou com dados mais sensíveis. O controle de acesso pode ser implementado por diversas estratégias, vamos analisar as estratégias discricionário, view, obrigatório e baseado em papéis. Discricionário Do inglês “discretionary access control” (DAC), controle de acesso discricionário, é bastante comum e bases de dados com poucos colaboradores. CCDD – Centro de Criação e Desenvolvimento Dialógico 7 Nesta estratégia de “controle de acesso trata-se de conceder, revogar e propagar privilégios de acesso aos dados por campos (colunas), registros (tuplas), tabelas, views etc.” (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 568). O método típico para impor o controle de acesso discricionário em um sistema de banco de dados é baseado na concessão e revogação de privilégios. A ideia principal é incluir declarações na linguagem de consulta que peritam que o DBA e usuários selecionados concedam e revoguem privilégios. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 567) Navathe (2011, p. 567) refere-se a dois níveis discricionário “dois níveis para atribuição de privilégios na utilização do sistema de banco de dados: Nível de conta; Nível de relação (ou tabela)”. Considera-se importante aplicar os dois níveis discricionários em uma base de dados. Nível de conta Este nível de controle de acesso discricionário está voltado às permissões de utilizar os comandos DDL (linguagem de definição de dados) do SQL. Navathe (2011, p. 567) os descreve assim: “no nível de conta se aplicam às capacidades fornecidas à própria conta e podem incluir o privilégio de CREATE SCHEMA ou CREATE TABLE... ALTER... DROP... MODIFY... SELECT”. É importante ressaltar que este nível de controle não está definido no SQL2. “Os privilégios em nível de conta não são definidos como parte da SQL2; eles são deixados para os implementadores de SGDB definirem.” (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 567). Sendo assim, esta é uma parte fundamental a ser incluída na hora de definir o SGDB. Nível de relação Faz parte do SQL2 os recursos para implementar o controle de acesso por nível de relação. Devemos entender por relação, nas próprias palavra de Navathe (2011, CCDD – Centro de Criação e Desenvolvimento Dialógico 8 p. 567): “relação pode se referir a uma relação da base ou a uma visão” (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 567), exemplificando tabelas, registros, colunas, views e outros. Efetivamente, neste nível de controle de acesso, é dado ou não permissão de leitura, gravação, exclusão ou alteração sobre um dado ou um conjunto de dado. Uma tabela ou uma view são exemplos de conjuntos de dados. Navathe (2011, p. 568) refere-se a este assunto dizendo “os tipos de privilégios podem ser de recuperação ou leitura (SELECT), privilégios de modificação das tuplas (UPDATE, DELETE e INSERT)” (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 568). Sendo assim, se um usuário receber somente permissão de leitura em uma tabela, ele não conseguirá inserir ou alterar dados. Navathe (2011, p. 567) explica este mecanismo dizendo: A concessão e a revogação de privilégios costuma seguir um modelo de autorização para privilégios discricionários conhecido como modelo de matriz de acesso, no qual as linhas de uma matriz M representam sujeitos (usuários, contas, programas) e as colunas representam objetos (relações, registros, colunas, visões, operações). Cada posição M(i,j) na matriz representa os tipos de privilégios (leitura, gravação, atualização) que o sujeito i mantém sobre o objeto j. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 567) Destaca-se o fato de que “o proprietário de uma relação recebe todos os privilégios sobre essa relação” (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 568). View As views podem ser utilizadas como mecanismos de segurança. Navathe (2011, p. 568) apoia este mecanismo ao dizer: “O mecanismo de visões (views) é um importante mecanismo de autorização discricionário por si só”. A view, por sua natureza, não permite inserção, alteração ou exclusão de dados, garantindo, assim, integridade nos dados, e a confidencialidade pode CCDD – Centro de Criação e Desenvolvimento Dialógico 9 ser implementada com a seleção somente das colunas e registro que são permitidos aos que têm permissão de leitura na view. Obrigatório Os recursos para implementar esta técnica não são nativos no SQL2. Podem ser implementados por pacotes adicionais aos SGDB ou por meio de programação (trigger, procedure etc.), no SGDB. Esta técnica é recomendada para dados altamente sensíveis, ou seja, dados que têm muitas variações de permissões. A técnica conhecida como controle de acesso obrigatório, do inglês mandatory Access Control (MAC), apresenta classificação para os dados e para os colaboradores, seguindo uma classe de segurança (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011,p. 570). Típicas classes de segurança são: Altamente confidencial ou top secret – TS; Secreta ou secret – S; Confidencial ou confidential – C e Não classificada ou unclassified – U. Os dados ou objetos são registros, colunas, tabelas, views etc. Os usuários/colaboradores e os objetos são classificados com uma das classes de segurança, Navathe (2011, p. 570) exclarece. O modelo normalmente utilizado para segurança multinível, conhecido como modelo de Bell-LaPadula, classifica cada sujeito (usuário, conta, programa) e objeto (relação, tupla, coluna, visão, operação) em uma das classificações de segurança. Com os objetos e os colaboradores classificados, são aplicadas regras ao controle de acesso, Navathe (2011, p. 570) exemplifica: Duas restrições são impostas no acesso aos dados com base nas classificações de sujeito/objeto: 1. Um sujeito S não tem permissão para acesso de leitura a um objeto O a menos que classe(S) >= classe(O). Isso é conhecido como propriedade de segurança simples. 2. Um sujeito S não tem permissão para gravar um objeto O a menos CCDD – Centro de Criação e Desenvolvimento Dialógico 10 que classe(S) <= classe(O). Isso é conhecido como propriedade estrela (ou propriedade *). Baseado em papéis Controle de acesso baseado em papéis é o mais comum, Navathe (2011, p. 572) diz: “os privilégios e outras permissões são associados a papéis organizacionais, em vez de a usuários individuais. Tais usuários recebem então os papéis apropriados”. Exemplificando, cria-se um grupo denominado “programador”, a este grupo atribui-se as permissões conforme visto na técnica discricionário. Todos os colaboradores que devam ter as permissões de acesso de um “programador” devem receber a permissão “programador”. No SQL2, conforme Navathe (2011, p. 572), os comandos são: “os papéis podem ser criados usando os comandos CREATE ROLE e DESTROY ROLE. Os comandos GRANT e REVOKE... podem, então, ser utilzados para atribuir e revogar privilégios dos papéis, bem como para usuários individuais, quando necessário”. Para esclarecer e se aprofundar um pouco mais no assunto, leia o tópico “24.3 Controle de acesso obrigatório e controle de acesso baseado em papel para segurança multinível.” Navathe (2011, p. 570). Assista ao vídeo do professor Martin em que ele fala mais sobre a classificação dos controles de acesso. Acompanhe na rota! Tema 04: Outras Medidas Para proteger os bancos de dados, é necessário implementar diversos mecanismos de segurança buscando proteger a confidencialidade, a disponibilidade e a integridade. Deve-se avaliar se existe a necessidade de implementar o controle de fluxo, a criptografia, o log de operações, o controle de acesso físico e outros. Vamos entender cada uma delas. Controle de fluxo O controle de fluxo trata de garantir que o destino de um dado atenda as CCDD – Centro de Criação e Desenvolvimento Dialógico 11 regras implementadas de proteção. Veja as considerações de Navathe (2011, p. 579) a seguir: O controle de fluxo regula a distribuição ou fluxo de informações entre objetos acessíveis. Um fluxo entre o objeto X e o objeto Y ocorre quando um programa lê valores de X e grava valores em Y. Os controles de fluxo verificam que a informação contida em alguns objetos não flui explícita ou implicitamente para objetos menos protegidos. [...] a transferência de informações de um emissor para um receptor só é permitida se a classe de segurança do receptor for pelo menos tão privilegiada quanto a do emissor. Criptografia A criptografia pode ser aplicada tanto no dado que está armazenado na base de dados quanto na transmissão do mesmo. Veja as considerações de Navathe: A criptografia é a conversão de dados para um formato, chamado texto cifrado, que não pode ser facilmente entendido por pessoas não autorizadas. Ela melhora a segurança e a privacidade, pois em casos de perda ou roubo de dados, aqueles criptografados não podem ser facilmente entendidos por pessoas não autorizadas. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 580) Log de operações Os logs são os registros das operações realizadas contendo, entre outras informações, a data, a hora, o colaborador, o processo de origem e outras informações úteis. Veja as considerações de Navathe: Podemos expandir as entradas de log de modo que também incluam o número de conta do usuário e o computador on-line ou ID de dispositivo que aplicou cada operação registrada no log. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 565) Os logs não implementam medidas de segurança, eles são úteis para identificar a origem de uma falha na segurança. Isso possibilitará uma análise para implementar uma medida de segurança. Controle de acesso físico É fundamental que alguns locais sejam de acesso restrito. Alguns desses locais podem estar ao longo do caminho da rede de dados, da rede elétrica, dos servidores, dos nobreaks etc. CCDD – Centro de Criação e Desenvolvimento Dialógico 12 Determinar o nível de restrição do acesso físico a alguns locais implica determinar o grau do risco quanto à confidencialidade, disponibilidade e integridade. Backup Os backups, ou cópias de segurança, são fundamentais no processo de implementar segurança na base de dados. É a principal fonte de restauração após um desastre. São variadas as propostas de backups de base de dados. Essas propostas são inerentes ao SGDB adotado, os SGDB oferecem ferramentas diferenciadas para fazer os backups. Em geral, todos os SGDB fornecem a possibilidade de fazer os backups de toda a base, só dos dados ou só da estrutura. As principais questões nas rotinas de backups são a frequência e o armazenamento. Para determinar a frequência dos backups é de se considerar o volume de operações realizadas, a interrupção dos serviços e o tempo de backup. Ao determinar o armazenamento, deve ser considerado o local e a mídia, para que o backup também esteja protegido das ameaças quanto à confidencialidade, disponibilidade e integridade. Vamos ver o que mais o professor Martin fala sobre outras medidas a serem tomadas para o controle de acesso e de fluxo ao banco de dados. Tema 05: Ocorrência Uma breve ponderação sobre necessidades na eventual ocorrência de uma violação da segurança. Auditoria As auditorias, nas medidas de segurança, devem ser realizadas periodicamente atendendo as políticas de segurança que foram adotadas. É recomendado que auditorias sejam realizadas toda vez que houver suspeita de violação da integridade dos dados. Veja as considerações de Navathe: CCDD – Centro de Criação e Desenvolvimento Dialógico 13 Se houver suspeita de qualquer adulteração no banco de dados, é realizada uma auditoria do banco de dados, que consiste em rever o log para examinar todos os acessos e operações aplicadas ao banco de dados durante certo período. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 565) Recuperação Na ocorrência de uma indisponibilidade, é válido considerar as sugestões de Navathe: Os sistemas de banco de dados precisam operar e continuar suas funções, mesmo com capacidades reduzidas [...] além de realizar todos os esforços para impedir um ataque e detectar um, caso ocorra, deve ser capaz de fazer o seguinte: Confinamento. Tomar ação imediata para eliminar o acesso do atacante ao sistema e isolar ou conter o problema para impedir que se espalhe mais. Avaliação de danos. Determina a extensão do problema, incluindo funções que falharam e dados adulterados. Reconfiguração. Reconfigurar para permitir que a operação continue em um modo reduzido enquantoa recuperação prossegue. Reparo. Recuperar dados adulterados ou perdidos e reparar ou reinstalar funções do sistema que falharam, para restabelecer um nível de operação normal. Tratamento de falha. Ao máximo possível, identificar os pontos fracos explorados no ataque e tomar medidas para impedir uma nova ocorrência. (ELMASRI; NAVATHE; VIEIRA; SERAPHIM; SERAPHIM, 2011, p. 583–584) Veja o vídeo em que o professor Martin comenta a ocorrência de uma situação indesejada. CCDD – Centro de Criação e Desenvolvimento Dialógico 14 Trocando ideias Quais são o(s) aspecto(s) que você considera relevante para implementar a segurança em uma base de dados? Reflita! Síntese Propiciar segurança a uma base de dados é não se descuidar em garantir confidencialidade, disponibilidade e integridade. São necessárias várias técnicas e diferentes métodos para mitigar os riscos. O controle de acesso é uma das medidas para implementar segurança na base de dados, mas só o controle de acesso não é suficiente, outras medidas de segurança são necessárias para garantir a disponibilidade e a confidencialidade. O DBA é o principal responsável pela segurança a base de dados, mas não o único envolvido nas definições das políticas de segurança. Só os recursos fornecidos pelos SGDB não são suficientes para garantir segurança a base de dados. Ao considerar outras técnicas, é necessário agregar ao processo outras ferramentas e recursos. Assista ao último vídeo do professor Martin em que ele faz uma síntese do que estudamos nesta aula. CCDD – Centro de Criação e Desenvolvimento Dialógico 15 Referências BRASIL. Lei nº 12.965, de 23 abril de 2014. Estabelece princípios, garantias, direitos e deveres para o uso da Internet no Brasil. Disponível em: <http://www.planalto.gov.br/ccivil_03/_ato2011-2014/2014/lei/l12965.htm>. Acesso em: 21 jan. 2016. ELMASRI, Ramez; NAVATHE, Shamkant B.; VIEIRA, Daniel; SERAPHIM, Enzo; SERAPHIM, Thatyana de Faria Piola (Orgs.). Sistemas de banco de dados. 6. ed. São Paulo: Pearson Education do Brasil, 2011.xviii, 788 p. HOBACK, Cullen. Sujeito a Termos e Condições: Terms and Conditions May Apply. Estados Unidos, 2013. Sistema Gerenciador de Banco de Dados Aula 3 Professor Martin José Fagonde Morães Conversa Inicial A aplicação de procedures e trigger agregam muita performance nos sistemas e segurança aos dados. Estudaremos como criá-los e utilizá-los, bem como as particularidades que os diferenciam. No vídeo disponível no material on-line, você confere os comentários iniciais do professor Martin. Contextualizando Por meio de uma trigger, podemos implementar a atualização do estoque toda vez que uma venda for realizada. As procedures e as trigger reduzem o tráfego na rede e disponibilizam dados atualizados em menor tempo em relação aos aplicativos. Na videoaula disponível no material on-line, o professor Martin faz uma contextualização dos temas dessa aula. Tema 1 - Introdução Os procedimentos, as funções e os gatilhos são códigos criados dentro do banco de dados, utilizando uma linguagem de procedural do SGDB juntamente com os comandos SQL, visando automatizar operações. Eles são referenciados como programas armazenados, ou seja, quando se queira falar dos três de forma genérica utiliza-se a expressão programas armazenados. O objetivo é trazer para dentro do banco de dados operações que não precisam ser realizadas nos aplicativos, diminuindo o tráfego de dados na rede, diminuindo o processamento nas estações de trabalho, entre outros benefícios. Cada SGDB implementa a sua própria linguagem de programação, comumente denominadas de PL, acrônimo para “Procedural Language”. Esta linguagem implementa laços de repetição, controle de fluxo e outros recursos. A variação desta linguagem entre os principais SGDB é pequena. Para desenvolvimento desse estudo, é importante conhecer os conceitos a seguir: Procedures: são uma sequência de códigos acionadas pelos aplicativos ou por console de instruções SQL. Elas podem receber parâmetros para serem utilizadas em seu processamento; Functions: se assemelham às procedures e podem ser chamadas de locais, que a procedures não podem. Triggers: se a semelham às procedures e são executadas automaticamente antes ou depois de um dos comandos de DML (insert, update e delete), conforme definido na sua criação. Neste estudo, tomaremos por base o SGDB MySQL para nossos exemplos. No vídeo disponível no material on-line, o professor Martin traz mais informações sobre os programas armazenados. Tema 2 - Delimitadores As procedures, functions e triggers (programas armazenados) são constituídas de instruções SQL. Toda instrução SQL tem que terminar com um caractere delimitador, geralmente está definido o caractere ponto e vírgula (;). A criação de um destes programas armazenados também é uma instrução SQL do tipo CREATE e precisa ser encerrado com um delimitador. Se for usado o mesmo delimitador na criação de um programa armazenado e na definição das instruções de cada programa armazenado, o interpretador tentará executar todos e gerará um erro no interpretador. Para evitar esta situação e conseguir criar os programas armazenados, é necessário alterar o delimitar na criação do recurso. Depois do recurso criado, retorna o delimitador para o caractere padrão. Nas instruções SQL que farão parte do programa armazenado, tem que ser aplicado o caractere delimitar, que ficará como o padrão. Para ficar mais claro, vamos considerar a criação de uma procedure. A procedure se chamará “totaliza” e executará três instruções SQL. O delimitador padrão é o ponto e vírgula (;) e vamos usar como delimitador temporário o caractere asterisco (*). seq Código Explicação 1 Delimitador * Primeiramente, alteramos o delimitador padrão para o delimitador temporário. 2 Cria procedure totaliza() INÍCIO Instrução SQL 1; Instrução SQL 2; Instrução SQL 3; FIM* Escreve-se o código de criação da procedure utilizando em cada instrução SQL o delimitador padrão. Para terminar a instrução de criação da procedure, utiliza-se o delimitador temporário. 3 Delimitador ; Volta-se a definir o delimitador com o caractere padrão. Ficou com alguma dúvida a respeito dos delimitadores? No vídeo disponível no material on-line, o professor Martin traz mais informações. Tema 3 - Variáveis As variáveis em programas armazenados são utilizadas para armazenar valores de tipos definidos e são aplicadas como se utiliza variáveis em outras linguagens de programação. Nos programas armazenados, pode-se definir variáveis utilizando a instrução “DECLARE” e a variável tem de ter um nome e um tipo válido. Isso é feito dentro do bloco de instruções, ou seja, entre o BEGIN e o END dos programas armazenados. A sintaxe é: DECLARE var_name [, var_name] ... type [DEFAULT value] var_name: é o nome da variável. Pode ser definido mais de uma variável na mesma declaração, separando-as por vírgula; type: é o tipo da variável. Tem de ser um dos tipos válidos do SGDB. DEFAULT value: definir um valor inicial para a variável é opcional. value: é o valor. DEFAULT: é uma instrução. Exemplos: 1. Para declarar uma variável do tipo inteiro de nome “vlr”, faz-se o seguinte: DECLARE vlr int; 2. Para declarar uma variável do tipo inteiro de nome “vlr” com valor inicialde 5, faz-se o seguinte: DECLARE vlr int DEFAULT 5; Atribuição As variáveis criadas que não tenham definido um valor na sua criação são de valor NULL, até ter um valor atribuído. Para atribuir um valor a uma variável após sua criação, utiliza- se a instrução SET; o operador de atribuição é o caractere igual (=). Para atribuir o valor 10 à variável “vlr”, faz-se o seguinte: SET vlr = 10; Atribuição no SELECT Um valor retornado por um SELECT pode ser atribuído a uma variável utilizando a instrução INTO. Os seguintes cuidados também têm de serem tomados: A instrução SELECT só pode retornar um registro; A quantidade de variáveis tem de ser a mesma de colunas indicadas no SELECT; A instrução INTO tem de estar logo após aos nomes das colunas selecionadas. Exemplo: No exemplo a seguir, está sendo selecionado o menor e o maior valor do ID dos alunos e atribuído respectivamente as variáveis num1 e num2: SELECT MIN(id), MAX(id) INTO num1, num2 FROM alunos; Retornando o valor de uma variável Para retornar o valor de uma ou mais variáveis de dentro de um programa de armazenamento, basta chamar o SELECT e as respectivas variáveis. Exemplo: No exemplo anterior, as variáveis num1 e num2 ficaram, respectivamente, com o valor do menor e do maior ID dos alunos. Para fazer esses valores retornarem, faz-se da seguinte forma: SELECT num1, num2; Ao executar um SELECT de retorno de variáveis, o programa de armazenamento encerra para sua atividade. Para mais informações sobre as variáveis, confira no material on-line a videoaula do professor Martin. Tema 4 - Controle de fluxo Os controles de fluxo funcionam como nas demais linguagens de programação. Existem outros além dos que veremos aqui, estes são os mais usados e os mais comuns em todos os SGDB. Você pode aprofundar seu conhecimento a respeito do MySQL clicando no botão a seguir: http://dev.mysql.com/doc/refman/5.5/en/flow-control- statements.html IF Com o IF, testa-se uma condição com os operadores condicionais e lógicos. Se a condição for verdadeira (THEN), há uma instrução, ou conjunto delas, a ser executada. Se a condição for falsa (ELSE / ELSEIF), pode-se ter um novo teste de condição com o ELSEIF ou aplicar uma instrução, ou um conjunto delas, após o ELSE. A instrução IF termina com um END IF. Sintaxe: IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF Exemplo: No seguinte exemplo de IF, temos dois testes: IF num1 = 1 THEN SELECT "UM"; ELSEIF num1 > 1 THEN SELECT "MAIOR"; END IF; Explicação do código: Código Explicação IF num1 = 1 THEN testando se a variável num1 é igual a 1. SELECT "UM"; se for verdadeiro, esta instrução será executada. ELSEIF num1 > 1 THEN se num1 não for igual a 1, então é feito um outro teste, se num1 é maior que um. SELECT "MAIOR"; se for verdadeiro, esta instrução será executada. END IF; Fim da instrução IF. CASE O CASE parte de um valor que é procurado em suas possibilidades, se encontrado um correspondente, as instruções são executadas e o bloco CASE é encerrado. Sintaxe: CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE case_value: é o valor a ser encontrado nas possibilidades; when_value: é o valor de possibilidade. Se for igual ao case_value (valor procurado), então (THEN) são executadas as respectivas instruções. Exemplo: No exemplo seguinte, caso a variável num1 tenha o valor 0, será executado SELECT “ZERO” e assim sucessivamente. CASE num1 WHEN 0 THEN SELECT "ZERO"; WHEN 1 THEN SELECT "UM"; WHEN 2 THEN SELECT "DOIS"; END CASE; A instrução CASE encerra com o END CASE. O controle de fluxo CASE pode conter a possibilidade ELSE. Ou seja, se nenhum valor correspondente ao procurado for encontrado, serão executadas as instruções contidas na opção ELSE. WHILE O WHILE é um controle de fluxo onde uma instrução, ou conjunto delas, é repetida se a condição for verdadeira. Sintaxe: WHILE search_condition DO statement_list END WHILE search_condition: teste de condição, se o resultado for verdadeiro as instruções que estão entre o DO e o END WHILE serão executadas. Exemplo: No exemplo a seguir, o WHILE testa se num2 é maior que 10. Se for, ele executa a instrução de fazer com que seja subtraído 100 de num2 e testa novamente. Realiza esta operação até que num2 seja menor ou igual a 10. WHILE num2 > 10 DO SET num2 = num2 - 100; END WHILE; REPEAT O REPEAT é um controle de fluxo onde uma instrução, ou um conjunto delas, é repetida até que a condição do teste seja verdadeira. Sintaxe: REPEAT statement_list UNTIL search_condition END REPEAT search_condition: teste de condição, se a condição não for satisfeita as instruções serão executadas novamente. Exemplo: No exemplo a seguir, o REPEAT subtrairá 10 da variável num2 e testará se num2 é menor que 10, se for menor o REPEAT será encerrado, do contrário o processo será executado mais uma vez. REPEAT SET num2 = num2 - 10; UNTIL num2 < 10 END REPEAT; Para mais informações sobre o controle de custos, confira no material on-line a videoaula do professor Martin. Tema 5 - Programas armazenados Os programas armazenados são rotinas que ficam armazenadas (salvas) dentro da base de dados. Esses programas (rotinas) utilizam os recursos do SGDB como o SQL e os recursos da base de dados, como os dados que estão nas tabelas, as próprias tabelas, os índices etc. Os programas armazenados podem ser as procedures, as triggers e as functions. Na sequência, vamos estudar as procedures e as triggers, analisando as vantagens em cada situação. Procedimentos - Procedure As procedures são executadas por chamadas, ou seja, no aplicativo ou em uma trigger se faz a chamada da procedure e ele executa o código programado. Sintaxe: CREATE PROCEDURE sp_name ([proc_parameter[,...]]) routine_body Utiliza-se o comando CREATE PROCEDURE seguido de um nome para a procedure e entre os parênteses as definições dos parâmetros (proc_parameter), que são opcionais. Após este cabeçalho, usa-se o corpo da procedure (routine_body) com a sequência de comandos a serem executados. Estas instruções ficam dentro de um bloco BEGIN ... END. No código seguinte, temos um exemplo de criação da procedure lista_prof_aluno. Essa procedure lista os professores e os respectivos alunos. Ela não recebe parâmetros e nem utiliza variáveis. CREATE PROCEDURE lista_prof_aluno() BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id; END Executar a procedure Para executar, ou seja, chamar uma procedure, é necessário fazer a instrução CALL. Sintaxe: Instrução CALL mais o nome da procedure, se tiver parâmetros incluí-los dentro de parêntese. CALL sp_name([parameter[,...]]) CALL sp_name[()] Exemplo: Para chamar a procedure lista_prof_aluno(), pode ser utilizada uma das linhas abaixo: CALL lista_prof_aluno(); CALL lista_prof_aluno; Não tendo parâmetro, não há necessidade de utilizar os parênteses. Especificando parâmetros Para as procedures, existem três opções de parâmetros: IN: são utilizadossomente para introduzir valores a serem utilizados na procedure; OUT: são utilizados somente como armazenamento temporário de valores externos à procedure (isso vai ficar mais claro); INOUT: podem ser utilizados tanto para receber valores na procedure como para disponibilizar valores fora da procedure. Parâmetros IN Os parâmetros definidos como IN são utilizados somente para entrada de dados que serão utilizados na procedure. Vamos usar parâmetros IN. No nosso cenário da procedure lista_prof_aluno(), está listando todos os professores e os respectivos alunos relacionados na tabela “aula”. Vamos alterar esta procedure para que liste os alunos do professor que for indicado no parâmetro. A aplicação deste parâmetro é só de entrada de dados na procedure, então definiremos do tipo IN. Definir um parâmetro do tipo IN consiste em especificar na criação da procedure, entre os parênteses, que o referido parâmetro é IN, ou seja, antes do nome do parâmetro coloca- se a diretiva IN, depois o nome do parâmetro e o tipo do dado que ele terá. No nosso caso o tipo do dado é inteiro, pois se refere ao tipo de dado que está sendo armazenado o idprofessor. Exemplo: CREATE PROCEDURE `lista_prof_aluno`(in idProf int) BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf; END Na cláusula WHERE, estamos restringindo a seleção ao valor recebido no parâmetro – idProf. Para chamar esta procedure, é necessário indicar um valor do tipo inteiro que será utilizado na cláusula WHERE. Exemplo: call aula. Lista_prof_aluno(101); Quando na criação de um parâmetro não é explicitado como IN, OUT ou INOUT, por default é IN. Parâmetros OUT Os parâmetros definidos como OUT são utilizados somente para disponibilizarem valores em variáveis externas a procedure, que poderão ser recuperados por outros selects. Eles utilizam recursos de variáveis de ambiente do usuário. Estas variáveis ficam ativas durante a sessão do usuário e poderão ser acessadas por todos os recursos que fizerem referência a ela. Para definir/criar uma variável na sessão do usuário, basta utilizar a instrução SET seguido do nome da variável que tem de estar precedido de arroba e atribuir um valor para a variável, veja os exemplos da próxima página. Exemplo: No seguinte exemplo, está sendo criado duas variáveis no ambiente do usuário uma denominada @NUM e a outra @nome e estão recebendo valores: SET @NUM = 10, @nome = "MARTIN"; Observe que essas variáveis de ambiente não têm o tipo do dado definido, mas a característica indispensável delas é o arroga como prefixo do nome. Para acessar o valor de uma destas variáveis, basta utilizar a instrução SELECT e o nome da variável. Exemplo: SELECT @NUM, @nome; A resposta ao SET anterior e a este SELECT será como no quadro abaixo: @NUM, @nome 10 MARTIN Vamos aplicar o parâmetro OUT na procedure lista_prof_aluno_TOTAL, que é uma variação de exemplo que já utilizamos. Essa procedure lista todos os alunos de um professor, determinado pelo parâmetro IN “idProf”. Estamos acrescentando o parâmetro OUT de nome “total” e do tipo int. O parâmetro “total” receberá a quantidade de alunos, que são do professor, cujo “idProf” veio por parâmetro: CREATE PROCEDURE `lista_prof_aluno_TOTAL`(in idProf int, out total int) BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf; SELECT count(*) into total from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf; END Vamos executar a procedure “lista_prof_aluno_TOTAL”. Observe o código a seguir: 1. Primeiramente, está sendo definido e inicializado com valor zero a variável de sessão “@total_registros”; 2. A próxima instrução está chamando a procedure “lista_prof_aluno_TOTAL” e passando os parâmetros referentes ao ID do professor. O outro parâmetro é a variável de ambiente “@total_registros” para onde será atribuído o total de alunos do respectivo professor; 3. A instrução “select @total_registros;” está mostrando o valor guardado na variável “@total_registros”. set @total_registros = 0; call aula.lista_prof_aluno_TOTAL(101, @total_registros); select @total_registros; 1 º SELEC T 2 º SELECT Parâmetro INOUT Os parâmetros definidos como INOUT indicam que podem ser utilizados como valor de entrada e podem guardar novos valores. Utilizando o código do exemplo anterior, vamos alterar para utilizar uma única variável, que será do tipo INOUT. Criamos o parâmetro “inout idProf_total int”, que começará com um valor definido na chamada e na segunda select receberá um novo valor. CREATE PROCEDURE `lista_prof_aluno_TOTAL`(inout idProf_total int) BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf_total; SELECT count(*) into idProf_total from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf_total; END A chamada da procedure “lista_prof_aluno_TOTAL” fica assim: 1. Na primeira instrução, criamos e inicializamos a variável de ambiente “@total_registros” com o valor do ID do professor; 2. Na segunda instrução, fizemos a chamada da procedure passando no parâmetro a variável de ambiente; 3. Na terceira instrução, pedimos para mostrar o novo valor da variável de ambiente. Neste ponto, ela contém a quantidade de registros e não mais o valor do ID do professor. 1 º SELEC T 2 º SELEC T set @total_registros = 101; call aula.lista_prof_aluno_TOTAL(@total_registros); select @total_registros; Para mais informações sobre os procedimentos (procedures), confira no material on-line a videoaula do professor Martin. Tema 6: Gatilhos - triggers Os gatilhos são programas armazenados que são executados conforme o que for definido na sua criação. Os gatilhos podem ser programados para serem executados ou antes ou depois de uma instrução SQL de manipulação de dados – DML (insert, update ou delete) de uma tabela específica. Os gatilhos não têm parâmetros e não retornam valores após sua chama, mas se utilizam de todos os demais conceitos e recursos que vimos até aqui. São muito úteis para disparar eventos, registrar log, fazer tratamento nos dados e para outras situações decorrentes de operações DML em uma tabela específica. Na criação de uma trigger define-se: Um nome: trigger tem de ter um nome único, seguindo as mesmas regras dos nomes de tabelas, procedures etc.; Ocasião: definir quando ela deve ser executada, se antes ou depois do(s) evento(s) ao(s) qual (quais) está vinculada. Para definir que seja executada antes da ocorrência de um determinado evento, usa-se a palavra “BEFORE”; para que seja executada depois da ocorrência do evento usa-se a palavra “AFTER”; Eventos: definir que ela será executada na ocorrência das instruções “insert” e/ou “update” e/ou “delete”; Tabela: definir à qual tabela está vinculado a trigger. A definição é feita indicando o nome da tabela; Ordem: é opcional, mas podemos definir a ordem em queas triggers serão definidas. Isto é para as situações em que temos mais de uma trigger definida para a mesma tabela, o mesmo evento e a mesma ocasião. A seguir, temos a sintaxe da instrução de criação de uma trigger: CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body Exemplo: A trigger denominada “contador”, após cada inserção na tabela “professores”, soma mais um (1) na variável de ambiente “@Total”. DELIMITER // CREATE TRIGGER contador AFTER INSERT ON professores FOR EACH ROW BEGIN SET @Total = @Total+1; END// DELIMITER ; Com a trigger criada, ao inserirmos valores na tabela professores como a instrução seguinte, a variável @Total vai sendo incrementada. Nesta instrução, estamos inserindo três novos professores: INSERT INTO professores(nome) VALUES("Marcos"),("Maicon"),("Adriana"); Com “SELECT @Total;” podemos acompanhar o valor da variável @Total. OLD e NEW Os operadores “OLD” e “NEW” nos auxiliam a acessar os dados dos campos envolvidos nas operações DML. Considerando a nossa trigger “contador”, podemos acessar o valor dos nomes que estão sendo inseridos na tabela professores utilizando o operador NEW seguido do nome da coluna, ficando assim: NEW.nome. Vamos alterar nossa trigger para utilizar o operador NEW, guardaremos os novos nomes em uma variável de ambiente, separados por ponto e vírgula. Exemplo: Observe a nova linha. Estamos atribuindo a variável de ambiente “@NOMES” à concatenação que a função “CONCAT_WS” está retornando. Estamos dizendo para a função “CONCAT_WS”, concatenar o que tem na variável @NOMES com o dado que veio no NEW.nome e é para separá-los utilizando o primeiro parâmetro ‘;’. CREATE TRIGGER contador AFTER INSERT ON professores FOR EACH ROW BEGIN SET @Total = @Total+1; SET @NOMES = CONCAT_WS(';', @NOMES, NEW.nome); END Ao executar as seguintes instruções, estamos definindo uma variável de ambiente com o valor “A” e executando uma instrução INSERT com três registros a serem inseridos e, por fim, estamos pedindo para mostrar o conteúdo da variável @NOMES; SET @NOMES = "A"; INSERT INTO professores(nome) VALUES("Aurélio"),("David"),("João"); SELECT @NOMES; Ao executar estas instruções, teremos como retorno o conteúdo de @NOMES e os nomes inseridos e tudo separado por ponto e vírgula. # @NOMES 'A;Aurélio;David;João' Em uma operação de INSERT só tem um novo registro (NEW), não tem um antigo registro (OLD); Em uma operação de DELETE só tem um antigo registro (OLD), não tem um novo registro (NEW); Em uma operação de UPDATE, temos um novo registro (NEW) e um antigo registro (OLD). Para mais informações sobre gatilhos (triggers), confira no material on-line a videoaula do professor Martin. Trocando Ideias Compartilhe no fórum suas ideias de quando utilizar procedure e de quando usar trigger! Na prática No contexto de variáveis de ambiente e dos parâmetros de uma procedure, qual a melhor alternativa para o seguinte cenário: O desenvolvedor (programador) precisa do valor resultante de uma das operações realizadas em uma procedure, que realiza várias operações. Como deve ser a procedure e como deve proceder o programador para ter acesso ao referido valor? Depois de responder à questão, veja o comentário do professor Martin: A procedure deve receber um parâmetro do tipo OUT ou INOUT, e atribuir a este parâmetro o valor da referida operação no enunciado, ou seja, o valor esperado pelo programador. O programador deve fazer a chamada da procedure por meio da instrução CALL e passar na posição do parâmetro OUT ou INOUT uma referência a uma variável de ambiente. Para acessar o valor da variável, após a chamada da procedure na forma apropriada o programador deve executar um SELECT na referida variável de ambiente, que ele mesmo forneceu. Síntese Ter rotinas rodando dentro da base de dados traz alguns benefícios, principalmente os envolvendo performance e segurança. Os programas armazenados, que são as procedures, triggers e functions, estão disponíveis nos principais SGDB que dispõem de facilidades diferentes entre eles. Variáveis e controle de fluxo são os recursos mais utilizados e comuns nos SGDB. Confira no vídeo disponível no material on-line a síntese do professor Martin. Referências AMADEU, Claudia Vicci (Org.). Banco de dados. São Paulo: Pearson Education do Brasil, 2014. (Coleção Bibliografia Universitária Pearson). ELMASRI, Ramez et al. (Orgs.). Sistemas de banco de dados. 6. ed. São Paulo: Pearson Education do Brasil, 2011. ORACLE Corporation e / ou suas afiliadas. Como o MySQL Utiliza Índices. Disponível em: <https://dev.mysql.com/doc/refman/5.7/en/mysql- indexes.html>. Acesso em: 17 nov. 2015. Sistema Gerenciador de Banco de Dados Aula 04 Prof. Martin Moraes CONVERSA INICIAL Olá, seja bem-vindo(a) à Aula 4 de Sistema Gerenciador de Banco de Dados. Neste encontro, vamos conhecer sobre as transações e suas implicações em uma base operacional – conhecimento necessário a todos os profissionais (programadores, analistas, DBA, testadores e outros) do entorno de um banco de dados. Conhecendo transações, o planejamento, a implementação e a manutenção de sistemas e de bases de dados serão mais consistentes, com resultados na medida certa e segurança de robustez para cada tipo de sistema. CONTEXTUALIZANDO O conceito de transação é fundamental para muitas técnicas de controle de concorrência e recuperação de falhas. (ELMASRI; et al, 2011, p. 505 e 506). Imagine um sistema de vendas online de carros que confirme a venda de um mesmo veículo para dois compradores distintos que confirmaram a compra ao mesmo tempo. Sistemas de vendas de passagens que não tenham um bom controle de transação podem permitir que o mesmo lugar seja vendido para mais de uma pessoa. Nos sistemas financeiros, os saldos e limites dependem de um controle de transações da operações bem-feitas. Os computadores, principalmente os servidores, têm a capacidade de executar várias tarefas ao mesmo tempo, isto é possível pelo escalonamento de tarefas no processador e também por terem mais de um processador. Isso é o que possibilita a alta performance percebida pelos usuários. Por meio dos aplicativos, os inúmeros usuários de um sistema enviam requisições ao banco de dados, o SGDB forma uma lista das requisições recebidas e as trata na ordem em que vão chegando. Com as nossas atuais capacidades computacionais, os SGDB podem ter mais de uma das requisições recebidas sendo atendidas ao mesmo tempo e isto pode causar inconsistência nos dados e outros tipos de falhas, se as requisições estão trabalhando com os mesmos conjuntos de dados. Quando o SGDB é monousuário, é fácil para o programador e/ou para o SGDB garantir que só uma requisição será tratada por vez. Veja como Elmasri et. al. (2011, p. 501) conceitua SGDB monousuário e multiusuário: Um SGDB é monousuário se no máximo um usuário de cada vez pode utilizar o sistema, e é multiusuário se muitos usuários puderem fazê-lo. [...] Em um SGDB multiusuário, os itens de dados armazenados são os recursos principais que podem ser acessados simultaneamente por usuários ou programas de aplicação interativos, que estãosempre recuperando informações e modificando o banco de dados. (ELMASRI; et al, 2011, p. 501). O ponto-chave deste contexto é que os dados podem ser acessados simultaneamente. Devemos entender por acessados as operações de deletar, alterar, selecionar e inserir. Este controle não depende só do programador, pode- se ter grande benefícios utilizando os controles de transações dos SGDB. Quanto mais crítico for o senário de concorrência, mais sofisticado o controle de transações do SGDB deve ser. Tema 1 – Implementando a transação No geral, o termo transação, em banco de dados, é utilizado para expressar um conjunto de instruções que o SGDB recebe, e depois as trata. Para exemplificar, vamos considerar os usuários A, B e C: o usuário A fez uma requisição de consulta para o SGDB; o usuário B fez uma requisição de inserção de dados; e o usuário C fez uma requisição de alteração. O SGDB executou três instruções, e cada uma foi uma transação. Problemas de transações Com este sentido da palavra transação, Elmasri et. al. (2011, p. 503), tratou de cinco problemas que os dados podem sofrer quando as transações não têm controle. São eles: ”O problema da atualização perdida. Esse problema ocorre quando duas transações que acessam os mesmos itens do banco de dados têm suas operações intercaladas de modo que isso torna o valor de alguns itens do banco de dados incorreto.”. Como exemplo de um problema de atualização perdida, vamos considerar o nosso cenário dos usuários A, B e C. Imaginemos que a requisição de alteração do usuário C seja para que todos os alunos recebam uma bonificação de 5% na mensalidade. São 40 alunos; a transação começou e incluiu a bonificação para 10 alunos; neste ponto, a requisição do usuário B, de incluir um novo aluno, é atendida, e o sistema volta a atender a requisição do usuário C, e conclui a inserção da bonificação para os 30 alunos restantes. Neste processo, um aluno ficou sem bonificação, ocorrendo, então, uma atualização perdida. “O problema de atualização temporária (ou leitura suja). Esse problema ocorre quando uma transação atualiza um item do banco de dados e depois a transação falha por algum motivo. Nesse meio tempo, o item atualizado é acessado (lido) por outra transação, antes de ser alterado de volta para seu valor original.” (ELMASRI; et al, 2011, p. 503). Como exemplo de problema de atualização temporária, podemos considerar o cenário dos usuários A, B e C. Imaginemos que a requisição de alteração do usuário C seja para que todos os alunos recebam uma bonificação de 5% na mensalidade. A requisição do usuário A é o valor da mensalidade de um aluno que está no caixa para pagar, a consulta é realizada e o valor é com o desconto. Antes que a requisição do usuário C fosse concluída, por uma falha qualquer, foi desfeita a operação que consentia a bonificação. Neste ponto, o valor que está para ser pago, dar baixa, é menor que ficou no registro do aluno. “O problema do resumo incorreto. Se uma transação está calculando uma função de resumo de agregação em uma série de itens de banco de dados, enquanto outras transações estão atualizando alguns desses itens, a função de agregação pode calcular alguns valores antes que eles sejam atualizados e outro, depois que eles forem atualizados.” (ELMASRI; et al, 2011, p. 504). Um exemplo de problema do resumo incorreto podemos considerar o senário dos usuários A, B e C. Imaginemos que a requisição de alteração do usuário C seja para que todos os alunos recebam uma bonificação na mensalidade de 5% e a requisição do usuário A, é uma relação dos alunos com os respectivos valores das mensalidades. Com as duas transações ocorrendo simultaneamente teremos na relação de alunos valores das mensalidades corretos e incorretos. “O problema de leitura não repetitiva. Outro problema que pode acontecer é chamado de leitura não repetitiva, em que uma transação T lê o mesmo item duas vezes e o item é alterado por outra transação T´ entre as duas leituras. Logo, T recebe valores diferentes para suas duas leituras do mesmo item.” (ELMASRI; et al, 2011, p. 504). Como exemplo de problema de leitura não repetitiva, podemos considerar o senário dos usuários A, B e C. Imaginemos que a requisição de alteração do usuário C seja para que todos os alunos recebam uma bonificação de 5% na mensalidade. A requisição do usuário A é o valor da mensalidade de um aluno que está no caixa para pagar, a consulta é realizada e o valor retornado é sem o desconto. A operadora, no caixa, executa o débito no cartão pelo valor total, autentica o recibo pelo valor total e na hora que vai dar baixa da mensalidade o valor é a menor, pois o processo do usuário C foi concluído. Implementando a transação Em um sentido técnico, em banco de dados, o termo transação é um conjunto de instruções com garantia de tratamento, seguindo regras que não impliquem em problemas para os dados. O controle das transações é realizado por software que fazem parte do SGDB. Elmasri et al (2011, p. 501) diz que “Uma transação normalmente é implementada por um programa de computador, que inclui comandos de banco de dados como recuperação, inserções, exclusões e atualizações”. Quando Elmasri et al (2011, p. 500) diz que “Uma transação é um programa em execução que forma uma unidade lógica de processamento de banco de dados”, está fazendo uma analogia ao princípio de funcionamento do processador do computador (UCP), para onde convergem todas as requisições. Diz-se “implementar transação” como um modo de tratar a concorrência das requisições de forma a não incorrer em um dos problemas já estudado. Para isso, informa- se ao software que trata de todas as requisições ao banco de dados o início e o fim de uma transação, para que a mesma seja executada de forma a não sofrer interferências de outras requisições. Elmasri et al. (2011, p. 502) indica como isso é feito ao dizer “Um modo de especificar os limites da transação é determinando pelas instruções explícitas begin transaction e end transaction”. Desse modo, é factível a afirmação de Elmasri et al. (2011, p. 506) em que diz: “Uma transação é uma unidade atômica de trabalho, que deve ser concluída totalmente ou não ser feita de forma alguma”, ou seja, quando um conjunto de instrução é colocado em uma transação, todos os comandos são realizados ou nenhum é realizado. Com isso, tem-se a garantia de que os dados originais não foram comprometidos. Tema 2 - Granularidade e operações Granularidade Quando é criada uma transação, itens de dados são isolados para que a transação efetue suas operações (instruções). Elmasri et al (2011) trata sobre itens de dados ao dizer: Um banco de dados é basicamente representado como uma coleção de itens de dados ... O tamanho de um item de dados é chamado de granularidade. Um item de dados pode ser um registro de banco de dados, mas também pode ser ... um bloco de disco..., ou mesmo uma unidade menor, como um valor de campo (atributo). (ELMASRI; et al, 2011, p. 502) Operações Uma instrução enviada ao banco de dados se transforma em várias operações. Repare no exemplo a seguir (ELMARSRI; et al, 2011, p. 502), que em uma instrução SQL ocorrem várias operações no computador entre o sistema operacional e o hardware. Entendendo este contexto facilitará sua percepção de custos de tempo ao implementar transações. Vamos O exemplo a seguir contém um exemplo para o processo de leitura de dados e para o processo de gravação (escrita): [...] um modelo simplificado de operações básicas de acesso ao banco de dados que uma transação
Compartilhar