Baixe o app para aproveitar ainda mais
Prévia do material em texto
Introdução a Banco de Dados150 OBJETIVOS DO CAPÍTULO • Desenvolver código transacional de dados em um banco de dados. • Aplicar o princípio da integridade e confiabilidade nas informações manipuladas. CAPÍTULO 7 Linguagem de Banco de dados SQL: DCL e TCL Adriana Bastos da Costa Jackson Luis Schirigatti TÓPICOS DE ESTUDO 1 Transações 3 Linguagem DCL – Linguagem de Controle de Dados • Conceitos de Transações. • Propriedades de uma transação. • Estado de uma transação. • Concorrência e deadlock. • Tipos de Falhas. • Usuário e privilégios. • Defi nição de Linguagem DCL. • Gerenciamento de permissões com os comandos Grant, Revoke e Deny. 2 Linguagem TCL – Linguagem TCL de Controle de Transações 4 Rules (Regras de Banco de Dados) • Defi nição de uma Linguagem TCL. • Commit e Rollback. • Defi nições de Regras (Rules). • Criando Regras em tabelas e visões – CREATE RULE. 151Introdução a Banco de Dados Contextualizando o cenário Nos negócios uma transação é uma interação entre dois ou mais indivíduos. Em banco de dados uma transação envolve um conjunto de operações que devem ser processadas como uma unidade de trabalho. Elas devem ser processadas confiavelmente para que nenhum dado se perca, ou se omita, em decorrência de múltiplos usuários ou falhas de sistema. Nesse contexto, como as linguagens SQL/TCL/DCL podem apoiar os programadores de aplicações e os administradores de Banco de Dados no controle de transações e dados? Introdução a Banco de Dados152 7.1. Transações Em organizações modernas como instituições financeiras, indústrias, companhias aéreas, de logística, comércio eletrônico e todas as que envolvem tecnologia e utilizam armazenamento de dados, o gerenciamento de transações é importante para o funciona- mento do negócio. Tais operações utilizam sistemas de controle operacional e gerencia- mento de informações, executam transações operacionais e, consequentemente, utilizam transações computacionais que envolvem armazenamento seguro. É essencial compreendermos como as transações funcionam em um gerenciador de banco de dados e as suas propriedades de atomicidade, durabilidade, consistência e iso- lamento das transações. Só a partir delas será possível garantir a confiabilidade, consis- tência, segurança e integridade das informações trocadas entre o usuário e o sistema de armazenamento de dados. Também é importante entendermos os estados de comportamento das transações, para assim compreendermos seus efeitos, falhas e frequências, para que sejam tratadas de maneira conveniente. Portanto, neste estudo, especificamente, abordaremos os esta- dos das transações, suas causas e efeitos no Sistema Gerenciador de Banco de Dados e nas transações de negócios impactando drasticamente no desempenho do processamento e armazenamento de informações. Vamos discutir também sobre os diferentes tipos de falhas, gerência de buffer e trata- mento de cursos, entendendo o que são e como tratá-los. Veremos que com a compreensão do comportamento das transações é possível reali- zar determinadas tratativas por meio da programação e reduzir ou eliminar os efeitos cau- sados pelas falhas elétricas, de hardware, lógicas ou por uma intervenção do usuário. 7.1.1. Conceito de Transações O processamento de transações envolve o lado operacional de um banco de dados enquanto o gerenciamento de operações descreve como os bens materiais são produzidos e como os serviços são realizados. O gerenciamento de transações, assim como os bens informacionais ou as transações são controlados (MANNINO, 2008, p. 516). Segundo Silberschatz, Korth e Sudarshan (2006, p. 409), uma transação é uma unidade do programa que acessa e possivelmente atualiza vários itens de dados. Normalmente, uma transação é iniciada por um programa de usuário escrito em uma linguagem de manipulação de dados ou em uma linguagem de programação de alto nível, como SQL, C++ ou Java. 153Introdução a Banco de Dados Também Okuyama, Miletto e Nicolao (2014, p. 213) comentam que a transação repre- senta uma unidade lógica de processamento do banco de dados que deve ser concluída por inteiro para garantir sua exatidão. Uma transação geralmente é implementada por um programa de computador que inclui comandos de banco de dados como recuperar, inserir, excluir e atualizar. Para Ramarkrishnan e Gehrke (2011, p. 823), o conceito de transação tem ampla apli- cação para uma variedade de tarefas de computação distribuídas, como reservas de voos, gerenciamento de inventário e comercio eletrônico. Mannino (2008), acrescenta que o processamento de transações envolve o lado operacional de um banco de dados, ou seja, como os bens informacionais são controlados. Por isso, é de extrema importância para os bancos e os respectivos caixas automáticos, por exemplo. O conceito de transação é definido por Bryla e Loney (2009, p. 232) como uma cole- ção de instruções SQL DML que são tratadas com uma unidade lógica. A falha de alguma das instruções na transação implica que nenhuma outra alteração feita ao banco de dados será salva permanentemente no banco de dados. Para Silberschatz, Korth e Sudarshan (2006, p. 409), a transação “é delimitada pelas instruções (ou chamadas de funções) na forma de begin transaction e end transaction”, início e fim de uma transação. Resumindo, uma transação pode ser entendida como uma unidade de trabalho exe- cutada dentro de um sistema de gerenciamento de banco de dados, tratada de maneira coerente e confiável, independente de outras transações. Uma transação, geralmente, representa qualquer alteração em um banco de dados, podendo envolver inclusão, altera- ção ou exclusão de dados. Pseudocódigo de uma transação de reserva aérea. START TRANSACTION EXIBIR saudações OBTER preferências de reserva SELECT registros de vôos de partida e retorno SE a reserva for aceitável ENTÃO UPDATE assentos restantes no registro de vôo de partida UPDATE assentos restantes no registro de vôo de retorno INSERT registro de reservas Imprima passagens se solicitado FIM SE ERROR: ROLLBACK COMMIT Fonte: MANNINO, 2018, p. 518. Introdução a Banco de Dados154 Na figura acima, Mannino mostra o pseudocódigo de uma transação de reserva aérea. A transação começa com as instruções START TRANSACTION (Begin transaction) e termina com o comando COMMIT, que torna as transações permanentes. A coleção de instruções no seu interior é composta por diversas instruções de visualização, seleção (leitura), condição, alterações (update) e inserção de registro de reservas (Insert) e depois há a finalização da transação (end transaction), neste caso após o COMMIT. Um comando de COMMIT torna permanente uma determinada transação. Ou seja, um COMMIT é o ato de enviar – fazer um UPLOAD dos códigos para um banco de dados –, efetivando ações no banco de dados. Caso ocorra algum erro, a instrução Rollback (cancelamento) desfaz todas as alterações realizadas pelos comandos DML (update e insert) desde o início e encerra a transação. Curiosidade SQL usa uma sintaxe simples, fácil de utilizar. Existem cinco tipos de instru- ções SQL: Instruções de consulta, instruções DML (Data Manipulation Language), Instruções DDL (Data Definition Language), Instruções TC (Transaction Control) e Instruções DCL (Data Control Language) (PRICE, 2009, p. 32). Portanto, em um sistema de banco de dados real, a operação write, não necessaria- mente resulta na atualização imediata dos dados no disco. Ela pode ser armazenada tem- porariamente na memória e executada no disco mais tarde (SILBERSCHATZ, KORTH E SUDARSHAN, 2006, p. 409). Este local temporário na memória chama-se de tablespace de Undo e dá suporte a vários outros recursos, incluindo consistência de leitura e opera- ções de recuperação de dados com a função de Flashback (BRYLA e LONEY, 2009, p. 233). Silberschatz, Korth e Sudarshan (2006, p.410) apresentam um exemplo (1) de transa- ção bancária que transfere R$ 50,00 daconta A para a conta B: Ti: read (A); A∶= A – 50; write (A); read (B); B≔ B + 50; write(B). 155Introdução a Banco de Dados Considere que Ti é uma transação financeira na qual é realizada a leitura das contas A e B e são feitas as devidas gravações dos novos valores. Neste exemplo, a gravação é realizada diretamente na base de dados, mas o ideal é que a transferência do valor do banco de dados seja feita para um buffer local, pertencente à transação que executou a operação read. Mas vamos entender melhor isso? O que é buffer, afinal? O buffer é uma ferramenta eficiente para entradas e saídas (Input e Output) de informa- ções no banco de dados, economiza recursos e aumenta a velocidade. Um buffer precisa ser gerenciado para garantir um melhor desempenho do banco de dados e do software como um todo, e é uma página de memória do servidor, incluindo índices e páginas de dados. O pool de buffers, também chamado de cache de buffer, consiste em vários buffers que permitem gerenciar um número maior de informações. Se um software precisa de informações sobre um dos buffers, o gerenciador de buffer libera os dados a partir das pági- nas disponíveis, em vez de entrar em contato com o disco do servidor, o que torna a aplica- ção mais rápida. 7.1.2. Propriedades de uma transação As propriedades de uma transação são os seus requisitos que elas asseguram e impactam nas transações de banco de dados. Para Mannino (2018, p.518) os Sistemas Gerenciadores de Banco de Dados (SGBD) asseguram que as transações obedeçam a determinadas propriedades. As propriedades mais importantes e mais difundidas são as propriedades ACID (atomicidade, consistência, isolamento e durabilidade). podemos obser- var essas propriedades a partir do exemplo: Ti: read (A); A∶= A – 50; write (A); read (B); B≔ B + 50; write(B). Para Silberschatz, Korth e Sudarshan (2006, p.410), a atomicidade implica no sis- tema de banco de dados acompanhar, em disco, os valores antigos de quaisquer dados em que uma transação realiza uma escrita. Se a transação não completar sua execução, o sis- tema de banco de dados restaura os valores antigos como se nenhuma alteração houvesse ocorrido. Para Mannino (2018, p. 518) essa propriedade significa que as mudanças parciais realizadas por uma transação devem ser desfeitas se a transação abortar. Introdução a Banco de Dados156 Ou seja, caso seja necessário, é fundamental cuidar do ROLLBACK de qualquer tran- sação, para deixar o banco de dados íntegro e pronto para uso. Quando falamos em falhas relacionadas a bancos de dados, podemos listar: falha de transação; falha de sistema; e falha de mídia. Em resumo, uma falha de transação pode ser causada por: erros lógicos, quando uma transação não pode continuar sua exe- cução normal devido a uma falha interna do sistema, como dados não encontrados, over- flow (estouro de memória) ou programação incorreta do sistema; ou por erros de sistema, quando o sistema entra em um estado inadequado (deadlock) e, com isso, a transação não consegue continuar sua execução normalmente. Outro tipo de falha é a falha de sistema, que, normalmente, não danifica fisicamente o banco de dados, mas pode afetar todas as transações que estiverem em execução, per- dendo, assim, o conteúdo em memória. Por fim, temos, ainda, a falha de mídia, que pode causar danos ao banco de dados, ou em parte dele, prejudicando as transações que estive- rem acessando o banco de dados. Em todos os tipos de falhas, é fundamental ter condições de recuperar os dados e os bancos de dados como um todo, fornecendo, assim, mais confiabilidade ao software. Vamos discutir alguns exemplos: Falhas que impedem que a transação complete a sua exe- cução podem ocorrer devido à falta de energia, falhas de hardware, erros de software ou qualquer erro por uma execução anormal. Vejamos no exemplo 2 que se A = 1.000,00 e B = 2.000,00 e a falha ocorreu após a instrução write (A), o valor das contas é A = 950 e B = 2000, ou seja, há uma diferença de 50,00, devido à falha (SILBERSCHATZ, KORTH E SUDARSHAN, 2006). A transação deverá desfazer esta operação no caso da falha. Caso a transação já tenha sido realizada, não há como realizar a restauração. Para Mannino (2018, p. 518), consistência significa que se as restrições aplicáveis forem verdadeiras antes de a transação ter início, elas serão verdadeiras depois que a tran- sação for concluída. Conforme explicam Silberschatz, Korth e Sudarshan, a soma de A e B deve permanecer inalterada pela execução da transação. “Sem o requisito da consistência, o dinheiro poderia ser criado ou destruído pela transação” (2006, p. 410). Para Ramakrih- nan e Gehrke (2011, p.435), cada transação, executada sozinha, sem nenhuma execução concorrente de outras transações, deve preservar a consistência do banco de dados. O SGBD presume que a consistência é preservada pela transação. Para Silberschatz, Korth e Sudarshan (2006, p.410), a propriedade de durabilidade garante que, quando uma transação é executada com sucesso, todas as atualizações que ela executou no banco de dados persistem, mesmo que haja uma falha no sistema após a transação terminar sua execução. “Por exemplo, se o computador de um banco falhar, cinco minutos após a conclusão da transação, o efeito da transação ainda estará registrado no banco de dados” (MANNINO, 2018, p. 519). 157Introdução a Banco de Dados Silberschatz, Korth e Sudarshan (2006) comentam que é possível garantir a durabili- dade por meio de atualizações gravadas em disco antes do término da transação. Eles afir- mam que as informações sobre as atualizações executadas pela transação e gravadas em disco devem ser suficientes para a reconstrução das atualizações após falha. Para Ramarkrishnan e Gehrke (2011, p. 823), “os usuários devem ser capazes de enten- der uma transação sem considerar o efeito de outras transações em execução concorrente, mesmo que o SGBD intercale as ações de várias transações por motivos de desempenho”. Dessa forma as “transações são isoladas (ou protegidas) dos efeitos do plano de execução concorrente de outras transações” (RAMARKRISHNAN E GEHRKE, 2011, p. 823). Silberschatz, Korth e Sudarshan (2006, p. 411) comentam que, se várias transa- ções forem executadas simultaneamente, suas operações podem se intercalar de alguma maneira indesejável, resultando em um estado inconsistente. O estado inconsistente se dá pela explicação do exemplo de Silberschatz, Korth e Sudarshan (2006, p. 411) que pode ser observado na tabela a seguir. Duas transações, T1 e T2, estão sendo executadas ao mesmo tempo. No momento da execução das instruções de transferência dos fundos da conta A para a conta B na Transa- ção 1 (retirado o valor 50 da conta “A” e acrescido 50 na conta “B”) são executadas as ins- truções de leitura e soma das contas A e B na Transação 2 (C = A + B). Contudo, o valor gerado é considerado inconsistente, ou seja, se houver uma falha no sistema ou queda de energia no momento da consulta C = A + B (transação T2), C será um valor inconsistente, pois a transferência será desfeita na transação T1 . Poderíamos exemplificar numericamente um cenário onde a Transação T1, contém inicialmente o valor de A = 1000 e B = 2000 (saldo inicial). Em segundo momento realiza-se a retirada da conta A (A:= A – 50), ou seja, A = 950 que é gravada no banco de dados temporariamente (operação durante a transação). E em um mesmo momento outra transação é executada (T2) e realiza a leitura de A e B (consulta o extrato da soma de duas contas), neste caso A = 950 e B = 2000, somando os dois valores = 2950. Perceba que a consulta T2 está sendo realizada no momento em que a transação T1 transfere valores. Se houver uma falha na transação T1 o cálculo já foi realizado na transação T2, infor- mando assim um valor inconsistente de consulta, já que os valores dos fundos continuam os mesmo antes de iniciar a transaçãoT1. Lembrando que uma transação é desfeita por uma falha de hardware, energia ou erro de programação e, com isso, as operações den- tro da transação são desfeitas. Portanto, se algum cliente realizou uma consulta antes da falha, os valores serão inconsistentes. Introdução a Banco de Dados158 Exemplo de inconsistência temporária e concorrência de transações Sequência de instruções T1 Ação de T1 Sequência de instruções T2 Ação de T2 Ti: read (A); A∶= A – 50; write (A); read (B); B≔ B + 50; write(B). Ação de transferência de fundos de A para B, deduzindo gravado em A e aumentado gravado em B. T2: read (A); read (B); C≔ A + B; Leitura de A e B e calcula A + B. Fonte: adaptado de SILBERSCHATZ, KORTH E SUDARSHAN (2006). Uma solução dada por Silberschatz, Korth e Sudarshan (2006, p. 411) é executar tran- sações de modo serial, ou seja, uma após a outra. Porém, a execução simultânea de tran- sações oferece benefícios de desempenho significativos. A garantia da propriedade de isolamento é responsabilidade de um componente do sistema de banco de dados chamado de componente de controle de concorrência. Curiosidade Os componentes do SGBD associados com o controle de concorrência e recu- peração incluem o gerenciador de transações, o gerenciador de bloqueio e o gerenciador de recuperação (RAMARKRISHNAN; GEHRKE, 2011). O mecanismo de Banco de Dados oferece recursos de: • bloqueio, que preservam o isolamento da transação; • log que garantem a durabilidade da transação. Mesmo que o hardware do servidor, o sistema operacional ou a instância do mecanismo de Banco de Dados falhem, a instância usa logs da transação incompleta até o ponto da falha do sistema; • administração de transação que impõem a atomicidade e a consistência da tran- sação. Depois que uma transação for iniciada, ela deve ser concluída com êxito ou desfará as modificações de dados desde que a transação foi iniciada (MICROSOFT, 2012a, online). Para a Microsoft (2012a, online), é de responsabilidade de um sistema de banco de dados empresarial, enquanto Banco de Dados, oferecer mecanismos que assegurem a inte- gridade física de cada transação. 159Introdução a Banco de Dados 7.1.3. Estado de uma transação Silberschatz, Korth e Sudarshan (2006) comentam que se uma transação não pode ser completada com sucesso, essa transação é considerada abortada. E cumprindo a pro- priedade de Atomicidade, qualquer alteração que a transação tenha feito no banco de dados, deverá ser desfeita (rolled back). Já uma transação que teve sucesso nas suas atualizações e não foi abortada é consi- derada confirmada (commited). Uma transação confirmada não poderá desfazer seus efei- tos, . a não ser que execute uma transação de compensação, cuja tratativa é realizada pelo usuário, e não pelo sistema. Portanto, é necessário estabelecer um modelo abstrato simples de transação, no qual a transação precisa estar em um dos estados: (1) ativa; (2) parcialmente confirmada; (3) falha; (4) abortada; (5) confirmada, e (6) completa como mostra a figura abaixo: Diagrama de estado de uma transição. Ativa Begin Transaction End Transaction Read e Write Parcialmente confirmada Falha Confirmada Abortada Completa Fonte: Adaptado de Silberschatz, Korth e Sudarshan (2006, p. 412). © D TC O M Introdução a Banco de Dados160 O quadro abaixo apresenta a definição e características dos estados de uma transa- ção: ativa, parcialmente confirmada, falha, abortada, confirmada e completa. Definição e características dos estados de uma transação Estado Definição Ativa O estado ativo é o estado inicial logo que é dada a instrução Begin Transaction. A transação permanece nesse estado enquanto está executando as instruções ou até concluir sua última instrução e executar o End Transaction. Parcialmente confirmada Depois que a instrução final foi executada. Neste estado a transação pode ainda estar na memória e uma falha de energia, hardware ou software pode ocorrer e impedir o término bem sucedido. Falha Depois de descoberta que a execução normal não pode mais prosseguir. Abortada Depois que a transação foi revertida e o banco de dados foi restaurado ao estado anterior ao início da transação. Neste estado é impossível prosseguir com a transação. A transação é reiniciada e é considerada uma nova transa- ção ou pode ser encerrada devido a algum erro lógico interno. Confirmada Após o término bem-sucedido. Completa A transação é completada confirmando o sucesso ou não sucesso depois de confirmada ou abortada. Fonte: Adaptado de Silberschatz, Korth e Sudarshan (2006). Existe a possibilidade de um SGBD realizar a escrita externa, além da escrita interna ao banco de dados, que pode ser realizada em uma impressora, terminal, etc. (por exem- plo, um terminal de caixa eletrônico). Se o sistema falhar após a transação ser confirmada, mas antes que pudesse confirmar as escritas externas, o SGBD executará as escritas exter- nas usando os dados de armazenamento não volátil (interno). Quando o sistema for reini- ciado, é possível visualizar a consulta ou transação de uma escrita qualquer, como mostra o cenário da figura abaixo: 161Introdução a Banco de Dados Escrita externa após falha depois de um estado confirmado. Ativa Begin Transaction End Transaction Consulta de extrato Interno FalhaConfirmada Ativa Nova Transação - sistema reiniciado End Transaction Terminal Confirmada Outra situação comentada por Silberschatz, Korth e Sudarshan (2006) é quando um caixa eletrônico é utilizado para saque e o sistema falha imediatamente antes que o dinheiro seja entregue. Não faz sentido o dinheiro ser entregue quando o sistema for rei- niciado, pois o usuário pode sair das proximidades da máquina. Neste caso, uma transação de compensação poder der executada, como realizar o depósito novamente na conta do correntista quando o sistema for reiniciado, como mostra a figura abaixo: Ativa Begin Transaction End Transaction Débito da conta Liberar dinheiro da máquina Dados FalhaConfirmada Ativa Nova Transação - sistema reiniciado End Transaction Confirmada Completa Crédito na conta © D TC O M © D TC O M Introdução a Banco de Dados162 Aplicar o conceito de transação em um sistema de banco de dados é permitir que as transações sejam realizadas de forma segura em bases de dados, distribuídas ou não, garan- tindo a integridade das informações tratadas, sem a necessidade de controle explícito deta- lhado no nível de programação. Para preservar a integridade das informações deve-se permitir que ou todos os resultados sejam efetivados (estado confirmado) ou, caso, isso não seja possível, todos os resultados obtidos deverão ser desprezados (estado abortado). Pausa para refletir Imagine você realizando uma transação de compra pela internet e após a inserção do código de segurança do seu cartão de crédito ocorreu um travamento na sua máquina. A transação foi realmente efetivada? 7.1.4. Concorrência e deadlock Segundo Silberschatz, Korth e Sudarshan (2006, p. 414), “os sistemas de processa- mento de transações normalmente permitem que várias transações sejam executadas simul- taneamente”. No entanto várias transações, ao serem executadas simultaneamente, podem causar várias complicações, como inconsistência de dados, exigindo um trabalho extra do sistema gerenciador de banco de dados. O gerenciamento da concorrência dos dados se faz fundamental, em todos os tipos de software, para garantir a confiabilidade da aplicação. Uma alternativa é utilizar técnicas de execução seriada, ou seja, as transações serão executadas uma de cada vez, cada uma começando apenas depois que a anterior for con- cluída. Mas existem vantagens em permitir a concorrência de transações, como um melhor throughput (número de transações executadas em determinada quantidade de tempo) e a utilização de recursos com um tempo de espera reduzido (SILBERSCHATZ, KORTH E SUDARSHAN, 2006).Para Price: O software de banco de dados Oracle [por exemplo,] suporta muitos usuários intera- gindo com um banco de dados, e cada usuário pode executar suas próprias transações simultâneas. Essas transações são conhecidas como transações concorrentes. Se os usuários estão executando transações que afetam a mesma tabela, os efeitos dessas transações são esperados uns dos outros até que uma instrução COMMIT seja execu- tada (2009, p. 294). 163Introdução a Banco de Dados A tabela a seguir apresenta um exemplo de concorrência entre transações. As transa- ções T1 e T2 entram em concorrência, mas a consistência somente é dada quando: a) a instrução COMMIT efetiva a transação T1 e; b) após uma nova consulta à tabela for realizada pela transação T2. Essa é a aplicação da propriedade de isolamento e de consistências das informações padrão entre as transações, ou seja, a transação é isolada até que seja efetivada e que seus dados forem consultados após a efetivação com COMMIT: Transações concorrentes Transação 1 Transação 2 1. T1 executa instrução SELECT que recupera da tabela. 2. T2 executa instrução SELECT que recu- pera da tabela. 3. T1 executa uma instrução INSERT para adicionar uma linha na tabela, mas não executa uma instrução COMMIT. 4. T1 Executa atualizações com UPDATE. 5. T1 Executa consultas à tabela. 6. T2 executa outra instrução SELECT e recupera as mesmas linhas do passo 1. T2 não “vê” a nova linha adicionada por T1. 7. T1 executa finalmente a instrução COM- MIT para registrar permanentemente a nova linha adicionada no passo 2. 8. T2 executa outra instrução SELECT e finalmente “vê” a nova linha adicionada por T1. Fonte: adaptado de PRICE, 2009. Veremos que, para eliminar as inconsistências geradas entre as transações simultâ- neas, é necessário compreender o mecanismo de travas ou bloqueios pessimistas de tran- sações, que serializam as transações em um estado correto. Quanto mais conservador for o tratamento das travas e bloqueios, melhor será o resultado obtido para a segurança da transação e dos dados. Segundo Coulouris et al. (2013, p. 692) as transações devem ser programadas de modo que seus efeitos sobre os dados compartilhados sejam serialmente equivalentes. Um servi- dor pode obter equivalência serial das transações, dispondo em série o acesso aos objetos. Fowler (2007) comenta que usar técnicas que resultem em uma equivalência seriável, ou seja, em uma sequência de execuções de instruções na qual não haja conflito entre tran- sações se tiver que fazê-lo e que se puder ajustar em transações maiores e renunciando a escalabilidade, deve ser feito. Introdução a Banco de Dados164 As técnicas que Fowler comenta são mecanismos de travas utilizados em transações maiores, nas quais ocorre o impedimento de acesso (consulta ou edição) aos valores de uma tabela quando esta está sendo editada ou gravada. Um exemplo simples de mecanismo para disposição em série é o uso de travas exclusivas (exclu- sive locks). [...]. A ideia é fazer com que o servidor impeça o acesso a qualquer objeto que esteja sendo usado por qualquer operação de transação de um cliente. Se um Cliente solicitar o acesso a um objeto que já está travado devido a transação de outro cliente, a requisição será suspensa e o cliente deverá esperar até que o objeto seja destravado (COULOURIS et al., 2013, p. 692). Para Silberschatz, Korth e Sudarshan (2006), a propriedade de isolamento é funda- mental para transações seriadas ou aquelas nas quais é aplicado o critério da equivalência seriada, trocando as instruções de acesso a fontes de dados diferentes com instruções de escrita e leitura, write (A), read (B). Já para transações simultâneas, a propriedade de isola- mento pode não ser mais preservada e, para garantir o isolamento, é necessário que o sis- tema utilize mecanismos de controle de concorrência. A seriação ou serialização de transações, como alguns autores denominam, é o prin- cipal critério de correção das transações concorrentes. Ou seja, o escalonamento de tran- sações deve ser sequencial, sem sobreposição no tempo. A Seriabilidade é o critério de correção geralmente aceito para a execução intercalada de determinado conjunto de tran- sações, portanto, tal execução é considerada correta se for seriável (DATE, 2003, p. 407). Para Silberschatz, Korth e Sudarshan (2006, p. 417), como as transações são progra- mas, é computacionalmente difícil determinar exatamente que operações uma transação realiza e como as operações de várias transações interagem. Assim as únicas operações significativas de uma transação, são as instruções read e write. O quadro a seguir mostra as regras de conflito das operações de leitura e escrita. Para Coulouris et al. (2013, p. 686), quando dizemos que duas operações estão em conflito, queremos dizer que seus efeitos combinados dependem da ordem em que elas são executadas. A operação read acessa o valor de um objeto e write altera o seu valor. 165Introdução a Banco de Dados Regras de conflito das operações de leitura e gravação. Operações de diferentes transações Conflito Motivo Leitura Leitura Não Porque o efeito das duas operações de lei- tura não depende da ordem em que elas são executadas. Leitura Escrita Sim Porque o efeito de uma operação de leitura e de uma operação de escrita depende da sua execução. Escrita Escrita Sim Porque o efeito de duas operações de escrita depende da ordem de sua execução. Fonte: Coulouris, Dollimore, Kindberg e Blair (2013, p. 686). Ainda Coulouris et al. (2013, p. 686), comentam que o efeito de uma operação se refere ao valor de um objeto configurado por uma operação write ao resultado retornado por uma operação read. Para quaisquer transações, é possível determinar a ordem dos pares da operação conflitante nos objetos acessados por ambas. Portanto, um dos modos de garantir a seriação é exigir que os itens de dados sejam acessados de uma maneira mutuamente exclusiva, ou seja, enquanto uma transação está acessando item de dados, nenhuma outra transação pode modificar este item de dados. Para Milani (2008, p. 88), “em algumas situações, geralmente em transações ou alte- rações em grandes volumes, inclusive na exportação de backups, é necessário garantir o estado consistente do banco de dados, o qual é possível por meio de comando”, trata-se do comando específico de Lock. As travas transacionais são mecanismos importantes para a aplicação do princípio do isolamento nas transações simultâneas, resolvendo os problemas de inconsistência de dados. Mesmo assim, comportamentos indesejados podem ocorrer na concorrência dos acessos de leitura e gravação nas bases de dados, como é o caso do Deadlock, ou impasses nos bloqueios das transações. Afirmação Para a Microsoft (2012b, online), o Deadlock é uma condição que pode ocor- rer em qualquer sistema com vários threads. Ele pode ocorrer para outros recursos, além de gerar o bloqueio de objetos em banco de dados. Os Deadlocks às vezes são chamados de abraço mortal. Introdução a Banco de Dados166 Segundo Date (2003, p. 406), o impasse de transações é uma situação na qual duas ou mais transações estão em estado de espera simultânea, que envolve a escrita no banco de dados de alguma forma. Cada uma espera que uma das outras libere um bloqueio antes de poder prosseguir. A figura a seguir mostra um impasse envolvendo as transações A e B. Transação A Tempo Transação B – t1 t2 t3 t4 – – – LOCK r1 EXCLUSIVE – – – – LOCK r2 EXCLUSIVE – – LOCK r2 EXCLUSIVE – wait – wait LOCK r1 EXCLUSIVE wait wait wait wait Fonte: Date (2003, p. 406). Podemos observar que r1 (recurso 1) é bloqueado na transação A no tempo t1 por meio de um bloqueio exclusivo de leitura e escrita. Depois r2 (recurso 2) é bloqueado na transação B com o exclusive lock. No mesmo tempo t2, o r2 aguarda solicitação de blo- queio na transação A e logo no tempo t4 o r1 aguarda solicitação de bloqueiona transação B, ocorrendo assim um impasse, chamado deadlock. Para Date (2003, p. 406), interromper o impasse envolve escolher uma das transações participantes como vítima e desfazer a situação, liberando assim seus bloqueios e, portanto, permitir o prosseguimento de alguma outra transação. Ainda segundo o autor, na prática nem todos os sistemas detectam os impasses. Alguns utilizam apenas um mecanismo de tempo de espera e assumem, simplesmente, que uma transação que não tenha realizado qualquer trabalho durante um período de tempo predefinido está em situação de impasse. 167Introdução a Banco de Dados 7.2. Linguagem TCL – Linguagem de Controle de Transações A linguagem TCL (Transaction Control Language) é uma linguagem de controle de transações utilizada para gerenciar as mudanças feitas por instruções DML (instruções de inserção, consulta, atualização e eliminação de dados), ou seja, a TCL faz com que as ins- truções DML sejam agrupadas em transações lógicas que preservam a integridade e a con- sistência dos dados. Exemplo da utilização das instruções TCL. Transação Ti Transação utilizando Instruções TCL/SQL Ti: read (A); A∶= A – 50; write (A); read (B); B≔ B + 50; write(B). Begin Transaction Update from contas set A = A – 50 where conta.cliente = 001; Update from contas set B = B + 50 where conta.cliente = 002; Commit else Rollback “mensagem” End Transaction Para agrupar as instruções em transações lógicas utilizamos a sintaxe como ilustra a tabela, que associa um exemplo de transação Ti, por meio dos comandos Begin Transac- tion e Commit. O comando Begin Transaction inicia uma transação e Commit e Rollback finalizam a transação. 7.2.1. Definição da Linguagem TCL A Linguagem TCL, também abreviada de DTL, é uma linguagem de controle de tran- sações na forma SQL que realiza um agrupamento lógico de integridade das instruções DML (Data Manipulation Language) ou da DDL (Data Definition Language), como vimos no exemplo da tabela anterior. As instruções DML ficam protegidas (íntegras) entre os comandos Begin Transaction ou Start transaction e Commit ou Rollback, ou seja, caso ocorra alguma inconsistência, problema no servidor ou por outro fator que possa impedir a execução completa da transa- ção Ti, as atualizações dos Updates serão desfeitas e nenhuma alteração na base será reali- zada. A MICROSOFT (2016, online) comenta que a instrução Begin Transaction: Marca o ponto inicial de uma transação local explícita e representa um ponto no qual os dados são referenciados por uma conexão lógica e fisicamente consistentes. Se forem encontrados erros, todas as modificações de dados feitas depois do Begin Transaction poderão ser revertidas para voltar os dados ao estado conhecido de consistência. Introdução a Banco de Dados168 Uma característica importante da linguagem TCL é quanto ao registro de log das tran- sações: após o início do comando Begin Transaction ainda não será efetuado o registro no log de transações, mas somente quando as instruções DML, como a Insert, Update ou Delete, forem executadas (MICROSOFT, 2016, online). Na tabela sobre utilização das instruções TCL vimos a atualização do campo A da tabela contas a partir da execução da instrução: “Update from contas set A = A – 50”. Somente neste momento será executado o log de transações. Conforme comenta Milani (2008, p. 207): As transações podem ser criadas e/ou gerenciadas em tempo real pela aplicação a qual está conectada ao banco de dados. Neste caso os códigos SQL deverão estar de alguma forma dis- ponível pela aplicação em questão, e a mesma deve manipular os comandos de transação e tratar os possíveis erros. Portanto, outra característica da linguagem TCL é que ela pode estar presente nos código das aplicações para execuções em tempo real. Um outro conceito bastante utilizado atualmente em programas de computado- res é o que chamamos de CRUD – CRUD, que é acrônimo do inglês Create, Read, Update and . Essas são as quatro operações básicas: criação, consulta, atualização e destruição de dados, e são utilizadas em bases de dados relacionais. Ou seja, são operações fundamen- tais de transações em bancos de dados. 7.2.2. Commit e Rollback Após a execução dos comandos DML dentro de uma transação Begin Transaction/ Commit/Rollback, o comando Commit finaliza a transação efetivando os dados na Base de dados. Já o comando Rollback aborta a transação em andamento impedindo que as altera- ções realizadas pelos comandos DML sejam gravadas no banco de dados. Apenas quando os comandos Commit e Rollback forem executados os dados estarão íntegros. Estes comandos são denominados de “comando de efetivação e de abordagem”. A tabela a seguir apresenta os valores das contas antes da execução do comando commit da transação do exemplo 3, que efetiva as transferências de R$50 entre as contas A e B. O exemplo 4 realiza uma consulta na tabela contas. 169Introdução a Banco de Dados Exemplo 3 Begin Transaction; Update from contas set A = A – 50 where conta.cliente = 0001; Update from contas set B = B + 50 where conta.cliente = 0002; Commit; // efetiva a transação End Transaction; Exemplo 4 Select * from contas //consulta a tabela contas Valores relativos ao cliente A Cliente A Antes Depois 0001 200 150 002 300 350 Após executado o comando commit, não será mais possível desfazer as alterações na base através do comando Rollback. Exemplo 5 Begin Transaction; Delete from contas; Rollback; End Transaction; Um exemplo de execução do comando Rollback é apresentado no exemplo 5 utilizando os mesmos dados da tabela anterior. Delete elimina todos os registros da tabela contas, con- tudo Rollback reverte a transação e retorna ao estado inicial dos registros na tabela conta. Introdução a Banco de Dados170 7.3. Linguagem DCL – Linguagem de Controle de Dados Ramakrishnan e Gehrke (2011, p. 578) comentam que um banco de dados de uma empresa contém muitas informações e vários grupos de usuários, sendo que estes preci- sam de acessos aos bancos de dados para realizar as suas tarefas por meio das aplicações. Contudo, é através das aplicações que é realizado o acesso ao Banco de Dados e informado qual o usuário que está realizando determinado acesso. Portanto, o SGBD deve fornecer mecanismos para controlar o acesso aos dados. A linguagem DCL (Linguagem de Controle de Dados) é uma linguagem do SQL uti- lizada pelos administradores de segurança de Banco de Dados para controlar o acesso do usuário aos objetos do banco de dados e seu conteúdo. O administrador, por meio do usuá- rio administrador, autoriza outros usuários, concedendo privilégios de acesso aos objetos do Banco de Dados (IBM, online). 7.3.1. Definição de Linguagem DCL A linguagem DCL/SQL trabalha com os principais comandos Grant e Revoke que con- cedem e revogam privilégios de segurança ao usuário por meio de acesso discricionário. Para Ramakrishnan e Gehrke (2011, p. 578), o controle de acesso obrigatório (mandatory acess control) é baseado em políticas em nível de sistema que não podem ser alteradas por usuários individuais. Nessa estratégia, cada objeto do banco de dados é atribuído uma classe de segurança, cada usuário recebe uma liberação para uma classe de segurança e são impostas regras para a leitura e gravação de objetos do banco de dados por parte do usuário. O SGBD define se determinado usuário pode ler ou gra- var cada objeto, mas com base em certas regras que envolvem o nível de segurança do objeto e a liberação do usuário. A Linguagem DCL faz com que estes controles sejam realizados por meio de ins- truções que concedem e revogam privilégios nas tabelas base, visões que compõem o discricionário. 7.3.2. Gerenciamento de permissões com comandos Grant, Revoke e Deny No gerenciamento de permissões aos objetos do Banco de Dados, apenas o admi- nistrador poderá controlar a criação de objetos para a administração do sistema. 171Introduçãoa Banco de Dados Subentende-se por objetos: backup, banco de dados, tabela externa, funções definidas pelo usuário, grupos de usuários, índices, bibliotecas, visualizações, esquemas, tabelas, usuários, etc. Já os privilégios concedidos e removidos aos usuários seriam os de interrupção, alte- ração, exclusão, descarte, exclusão, limpeza, inserção, listagens, atualizações, etc. O exemplo 6 abaixo ilustra como conceder ao usuário User_1 todos os privilégios de acesso à tabela contas do banco de dados banco_financ por meio da execução do comando GRANT: Exemplo 6 GRANT all ON banco_financ.contas TO User_1; Já o exemplo 7 faz com que o usuário User_1 tenha acesso a realizar consultas SQL na tabela contas do banco de dados banco_financ: Exemplo 7 GRANT select ON banco_financ.contas TO User_1;. A sintaxe do comando GRANT é: GRANT <privilégios> (colunas) ON <banco>.<tabela> TO <usuário> IDENTIFIED BY <senha>;. O primeiro parâmetro simboliza quais os direitos de acesso. O segundo parâmetro, composto por banco e tabela, reúne os objetos aos quais o usuário poderá ter acesso. O terceiro parâmetro são os usuários e domínios de acesso específicos que receberão os privi- légios de acesso. Já o “comando REVOKE tem o efeito contrário do comando GRANT. Sua funcionali- dade é de retirar permissões de acessos em contas de usuários específicos” (MILANI, 2008, p.123). A sintaxe do comando REVOKE é: REVOKE <privilégios> (colunas) ON <banco>.<ta- bela> FROM <usuário>. O exemplo 8 ilustra a execução do comando Revoke para a remoção dos privilégios de escrita do usuário User_1 na tabela contas: Introdução a Banco de Dados172 Exemplo 8 REVOKE insert ON banco_financ.contas TO User_1; Outro comando que trata das permissões de privilégios dos usuários é o comando DENY, que nega uma permissão a uma entidade de segurança (usuário ou grupo de usuá- rios). O comando DENY impede a entidade de segurança de herdar a permissão através das suas associações de grupo ou de função (MICROSOFT, 2017, online). A sintaxe do comando Deny é: DENY <Permissões> ON <banco>.<tabela> TO <usuário>. A seguir termos o exemplo 9: Exemplo 9 DENY Select ON banco_financ.contas TO <banco de dados>. Ele ilustra a execução do comando DENY na negação explícita da permissão de reali- zar Select, sem afetar a permissão para os demais envolvidos no acesso a tabela contas. 7.4. Rules (Regras em Banco de Dados) As Rules (regras), também chamadas de Triggers (gatilhos) são uma técnica utilizada na maioria dos bancos de dados relacionais para a implementação de regras de negócios das aplicações. Para Pichiliani: “os Triggers padrão são Stored Procedures disparadas automaticamente quando uma operação com um INSERT, UPDATE ou DELETE é efetuado sobre uma tabela. Ou seja, logo após a aplicação cliente enviar alguns destes comandos para o banco o Trigger é disparado”. (2008, p. 13) Uma Rules ou Trigger é um tipo especial de procedimento armazenado, que é execu- tado sempre que houver uma tentativa de alteração de dados na tabela (DEVMEDIA, online. 7.4.1. Definição de Regras (Rules) Uma Regra de negócio (Rule) deve estar associada a uma tabela (tabelas de trigger) e são chamadas automaticamente quando as instruções DML de inserção, atualização ou 173Introdução a Banco de Dados eliminação forem acionadas, ou seja, não podem ser chamadas diretamente, pois não pas- sam e nem aceitam parâmetros. Algumas aplicações do uso das Rules são: • impor uma integridade de dados mais complexa como uma regra de negócio; • definir mensagens de erros; • comparar a consistência de dados; • utilização para replicação de dados para outras tabelas. Uma Rule ou Trigger é parte de uma transação, ou seja, a Trigger e a instrução que o aciona são tratadas com uma única transação, que poderá ser revertida em qualquer ponto do procedimento por meio do comando Rollback (DEVMEDIA, online). O grande benefício de se utilizar Rules e Triggers é a automatização de parte do pro- cessamento, facilitando e otimizando, assim, a gestão do software. Pausa para refletir A maioria das regras de negócio de uma aplicação podem ser definidas nos gatilhos de Rules ou Triggers? 7.4.2. Criando Regras em tabelas e visões – CREATE RULES Um exemplo de regra que pode ser utilizado em uma tabela ou em visões é a criada por meio do comando CREATE RULES ou CREATE TRIGGER, como no caso abaixo: Exemplo 10 CREATE TRIGGER T_cancela on Contas FOR Insert, Update, Delete AS Select ‘Cancela ação!’ Rollback GO Introdução a Banco de Dados174 Já a Trigger T_cancela, do exemplo 11, cancela qualquer instrução Insert, Update ou Delete sobre a tabela Contas. Uma mensagem de cancelamento é enviada para o terminal e o Rollback reverte a transação. Exemplo 11 CREATE TRIGGER T_inclui on Contas FOR Insert AS If (Select COUNT (*) FROM INSERTED) = 1 PRINT ‘Registro inserido com sucesso!’ GO Outro exemplo de Criação de regras em tabelas está contido também no exemplo (11), no qual é apresentada uma mensagem de sucesso de inserção de um registro toda vez que é executado um INSERT na tabela contas. Proposta de Atividade Reforce seu aprendizado com o exercício sugerido a seguir. A atividade não é avaliativa, mas é uma boa oportunidade para testar seus conhecimentos e fixar o conteúdo estudado no capítulo. Agora é hora de recapitular tudo o que você aprendeu nesse capítulo! Elabore um mapa mental destacando as principais ideias abordadas ao longo do capítulo. Ao produzir seu mapa mental, considere as leituras básicas e complementares realizadas. Dica: em um mapa mental, você deve ter uma expressão principal, inicie, por exemplo, por ‘Linguagem de Banco de Dados TCL e DCL e subdivida essa expressão ou termo em novas palavras, dando sentido ao texto, como por exemplo: Transações, efetivação de uma tran- sação, Gerenciamento de Permissões e Triggers. A partir destes termos subdivida em novas expressões ou termos, fracionando a ideia até o melhor entendimento possível. Apresente, ao longo d a fragmentação da ideia os principais comandos da Linguagem TCL e DCL. 175Introdução a Banco de Dados Recapitulando Neste capítulo compreendemos que uma transação de banco de dados é tão comum nos dias de hoje como as transações de negócios. As aplicações e, consequentemente, os Bancos de Dados realizam transações diariamente no mundo todo. Entretanto, esse geren- ciamento pelo SGBD só é possível se os programadores e administradores de Banco de Dados compreenderem e realizarem as devidas tratativas de transações nos códigos DML e DDL. Compreender como uma transação é realizada, suas propriedades, seus estados, bem como a concorrência transacional e os preocupantes deadlocks, é tão importante quando a própria programação das aplicações. Para isso, a linguagem SQL beneficia os programadores por meio da linguagem TCL e DCL. Vimos que Linguagem DCL apoia os programadores no controle das transações quando as falhas de sistemas ocorrem, e a linguagem DCL os apoia no controle de acesso aos dados, auxiliando na concessão dos privilégios de acesso de usuários e seus grupos. Já com relação às definições de regras em bancos de dados, compreendemos que as Rules ou Triggers ajudam na implementação de regras de negócios das aplicações. Entretanto, é importante ter cautela na quantidade de uso das Triggers, pois a maioria das regras está na camada de desenvolvimento e nas relações das bases de dados por meio da modelagem lógica. Deixar muitas regras definidas em tabelas de uma base de dados não é muito conveniente, devido a uma maior necessidade de organização e controle das mesmas. Na questão de travamentos ou falhas de hardware ou de software em máquinas locais, que estão realizando transações financeiras ou outras transações, as mesmas são efetivas. É importante compreender que a transação está sendo executada não em sua máquina local, mas sim em um servidor de banco de dados, centralizado em um DataCen- ter ou servidor qualquer. Quando o usuário usa o código de um cartão crédito para uma compra pela internet e envia os dados ao servidor, neste momento, mesmo que a máquina local falhe, o servidor já estará processando a transação de efetivação. O importante é sempre verificar o extrato do cartão de crédito ou se o pedido compra foi efetivado. Introdução a Banco de Dados178 OBJETIVOS DO CAPÍTULO • Elaborar codificação SQL a partir de visão externa, desenvolvendo assim consultas virtuais de um banco de dados. CAPÍTULO 8 Linguagem de Banco de Dados SQL: Nível externo – Visões (Views) Adriana Bastos da Costa Sidartha Azevedo Lobo de Carvalho TÓPICOS DE ESTUDO 1 Noções básicas 3 Consultando, alterando e excluindo Visões • Defi nição de visões em um Banco de Dados. • Importância e benefícios das visões. • Tipos de visão. • Consultando Visões e tabelas. • Modifi cando uma visão. • Excluindo visões. 2 Criando Visões • Como criar uma visão simples? • Privilégios e controle de acesso para visões. 179Introdução a Banco de Dados Contextualizando o cenário Neste capítulo vamos abordar consultas virtuais de um banco de dados utilizando as visões (views) em um banco de dados relacional. Iremos partir do princípio de resolução de proble- mas reais e, ao longo do capítulo, iremos detalhar os conceitos ministrados envolvendo pro- blemas do mundo real e vários exemplo práticos. Além disso, vamos detalhar os comandos na linguagem SQL para definição de visões, sua importância, benefícios e tipos de visão. De forma mais específica, detalharemos os comandos para criação de uma visão utilizando o CREATE VIEW, abordaremos a alteração das visões já criadas com o comando ALTER VIEW e CREATE OR REPLACE VIEW, e, por fim, analisaremos o comando DROP VIEW para exclusão de visões. A partir dos conceitos aprendidos nesse capítulo, o aluno será capaz de responder à seguinte pergunta norteadora: como manipular visões em um banco de dados relacional utilizando a linguagem SQL? Introdução a Banco de Dados180 8.1. Noções básicas Neste tópico iremos esclarecer os principais conceitos relacionados às visões do banco de dados. As visões são abstrações do banco de dados, ou seja, uma representação que não reflete o esquema físico do banco. Uma visão pode ser o resultado de uma con- sulta ao banco (esquema físico) que ficará reservada como uma visão. A seguir, vamos defi- nir o conceito de visão e demonstrá-lo por meio de exemplos. Teoricamente, no conceito de banco de dados, uma visão é o resultado de uma con- sulta sobre os dados, em que os usuários do banco de dados podem consultar simples- mente como eles fariam em um objeto de coleção de banco de dados persistente. Ou seja, o papel da visão é simplificar a consulta, oferecendo um bom tempo de resposta. Além disso, vamos detalhar a importância das visões, sua utilidade para os bancos de dados relacionais e os benefícios fornecidos pelo uso das visões em aplicações comerciais. Por fim, iremos classificar as visões criadas, podendo ser uma visão diretamente derivada do banco de dados, ou seja, uma consulta ao banco, ou uma visão de visões, que é uma consulta às visões criadas gerando uma nova visão. 8.1.1. Definição de visões em um Banco de Dados Segundo Silberschatz (2006, p. 99), visão em um banco de dados é qualquer relação que não faça parte do modelo lógico, mas que é visível para o usuário como uma relação virtual. Além disso, é possível dar suporte para um grande número de visões sobre qual- quer conjunto de relações reais. Na álgebra relacional usamos os operadores a nível lógico, todas as entidades e relações que estavam nas operações são dados armazenados no banco de dados. Não é interessante que todos os usuários do banco de dados tenham acesso a todos os dados armazenados. Imagine que um determinado atendente de um banco, que não deveria ter acesso aos valores disponíveis nas contas dos clientes, deseje consultar se o cliente do banco possui algum empréstimo ativo. Para esse funcionário, somente estaria disponível uma visão do banco de dados real, omitindo os valores em reais. Dessa forma, podemos também enten- der que o uso de visões melhora a segurança das informações, disponibilizando apenas a informação necessária para cada usuário. 181Introdução a Banco de Dados Dica As visões são abstrações do banco de dados e não geram custos de armazenamento de dados. Somente as referências são armazenadas para gerar a visão no banco de dados. Essa visão do banco de dados deve ser criada a partir do resultado de uma operação SQL que envolve projeção e omite a coluna da tabela que possui os valores em reais da conta do cliente. 8.1.2. Importância e benefícios das visões Como vimos no tópico anterior, as visões são imprescindíveis para bancos de dados complexos, nos quais diversos colaboradores têm acesso, mas nem todos devem ter acesso a todas as informações. No nosso próximo exemplo, ilustrado na figura a seguir, um cliente vai até sua agência bancária para ser atendido. Por questões de segurança, pode ser necessário criar uma coleção de relações perso- nalizadas que melhor atendem às demandas do usuário final do banco de dados. Digamos que um colaborador do departamento de publicidade queira ter acesso à relação dos clien- tes que têm conta ou empréstimo no banco e a agência que detém os dados desse cliente. A relação que poderia ser criada para esse colaborador é (SILBERSCHATZ, 2006, p. 99): π nome_agencia, número_empréstimo (depositante ⋈ conta) ∪ π nome_agencia, nome_cliente (devedor ⋈ empréstimo) Usando a álgebra relacional para resolver a consulta acima, serão retornados os valores dos clientes que possuem conta ou empréstimo ativo no banco, com nome do cliente e a agência na qual mantém seus dados. Afirmação A álgebra relacional é essencial para a construção do banco de dados, sendo uti- lizada para definir e consultar dados em um banco de dados relacional. Para facilitar o seu uso, as consultas são apoiadas na linguagem SQL. Introdução a Banco de Dados182 Esse resultado poderia ser usado para criar uma visão do banco de dados, facilitando a visualização e melhorando a segurança. 8.1.3. Tipos de visão As visões são um poderoso recurso para aumentar a flexibilidade dos bancos de dados, melhorando a segurança das informações, no que diz respeito a disponibilizar as informações adequadas para cada usuário. Com elas é possível criar várias outras relações a partir do modelo lógico do banco e também de visões. O nome da visão também é enten- dido como uma relação no banco de dados. Vamos definir visões em dois níveis: a partir do modelo lógico e a partir da visão criada a partir do modelo lógico. O modelo de diferentes visões apoiadas no mesmo modelo lógico está exemplificado na figura a seguir. Podemos ter várias visões criadas a partir do mesmo modelo lógico do banco de dados, que por fim se comunica com o modelo físico. Diferentes visões no banco de dados. Nível Lógico Nível Físico Visão 1 Visão 2 Visões do Bando de Dados Visão 3 Visão N No banco de dados PostgreSQL as visões são armazenadas de forma abstrata, sendo representadas por uma tabela lógica referenciando a uma consulta SQL previamente defi- nida. Uma visão pode ser materializada, sendo armazenada de forma física no banco de © D TC O M 183Introdução a Banco de Dados dados, tornando-se equivalente a uma tabela. Uma visão materializada armazena o resultado de uma consulta e então permite que os dados sejam alterados como uma tabela qualquer. Dando continuidade, temos uma tabela chamada Devedor e também temos uma tabela chamada Empréstimo, ambas fazem parte do modelo lógico do banco de dados e estão implementadas no modelo físico. Podemos criar a visão todos_clientes para ser utilizada como uma entidade do banco de dados que irá conter todos os clientes que possuem algum débito (tabela Devedor) ou algum empréstimo ativo (tabelaEmpréstimo). Podemos aplicar os operadores da álgebra relacional nessa nova relação do banco de dados criada como uma visão: • selecionar o nome de todos os clientes; • π nome (todos_clientes) • selecionar o nome e o CPF de todos os clientes; • π nome, CPF (todos_clientes) Da mesma forma, podemos também conseguir essa informação acessando direta- mente as tabelas do modelo lógico, mas de uma forma mais complexa. Além disso, podemos criar uma visão a partir da visão “todos_clientes” criada ante- riormente. Podemos utilizar o seguinte comando para a criação: create view cliente_recife as π nome (σ nome_agencia = “Recife” (todos_clientes)) O comando acima cria a nova visão chamada de cliente_recife, contendo todos os usuários do banco que possuem como sede a cidade de Recife, capital do estado de Pernam- buco. Essa visão foi criada a partir de uma visão previamente definida, chamada de todos_ clientes que contém todos os clientes que possuem algum saldo devedor ou empréstimo ativo no banco. O processo de criação das visões será discutido no próximo tópico. 8.2. Criando Visões Neste tópico veremos em detalhes a criação de visões no banco de dados e, a partir disso, vamos definir diferentes níveis de acesso para as visões criadas. Primeiramente, ire- mos explorar o comando de criação de visões: CREATE VIEW, usado para criar as visões a partir do modelo lógico do banco de dados ou de outras visões já definidas. Ademais, iremos detalhar o processo de criação de usuários no banco de dados e o uso do comando GRANT e TO, utilizados para concessão de privilégios diferenciados para Introdução a Banco de Dados184 os usuários do banco de dados. A partir da atribuição de privilégios é possível permitir que somente pessoas autorizadas tenham acesso a determinados comandos de criação ou alte- ração do banco de dados. 8.2.1. Como criar uma visão simples? Para criar uma nova visão é necessário atribuir a ela um nome e a consulta que gerará seus dados. O comando para criação de visões segue o seguinte modelo: CREATE VIEW nome_visao as <expressão de consulta> No modelo acima, o atributo nome_visao será substituído pelo nome a ser dado a uma nova visão, por exemplo, todos_clientes e a <expressão de consulta> é uma expressão válida em álgebra relacional ou SQL. Para criar a visão todos_clientes podemos utilizar a expressão abaixo: CREATE VIEW todos_clientes as π nome_agencia, nome_cliente (Depositante ⋈ Conta) ∪ π nome_agencia, nome_cliente (Devedor ⋈ Empréstimo) A cláusula acima pode ser reescrita em SQL da seguinte forma: CREATE VIEW todos_clientes as (SELECT nome_agência, nome_cliente from depositante, conta where depositante.número_conta = conta.número_conta) union (SELECT nome_agência, nome_cliente from devedor, empréstimo where devedor.número_empréstimo = empréstimo.número_empréstimo) 185Introdução a Banco de Dados Agora que já definimos a nossa visão, podemos utilizar o operador de seleção da álgebra relacional para selecionar todos os clientes da agência Recife, por exemplo: π nome_cliente (σ nome_agencia = “Recife” (todos_clientes)) A figura abaixo ilustra a execução de um comando SQL para criação da visão todos_ clientes_nome, que seleciona somente o nome de todos os clientes presentes na tabela todos_clientes usando o comando SQL SELECT. Podemos perceber na parte inferior o tempo que a consulta demorou para ser executada: 11 milisegundos. Criando uma visão no PGAdmin III. Lembre-se que os nomes das visões devem aparecer nos mesmos locais em que apare- ceriam os nomes das relações do banco de dados. Por fim, vale lembrar que as visões são rela- ções virtuais definidas por uma expressão de consulta em álgebra relacional ou SQL. Além disso, desenvolvemos consultas utilizando visões e os seus equivalentes em álgebra relacional. Dando continuidade, vamos criar uma outra visão, chamada de clientes_CPF, que irá conter somente os CPFs dos clientes, omitindo o nome. Essa visão poderá ser usada por algum outro papel, que não deveria ter acesso ao nome do cliente por motivos diversos. O comando para criação da visão é: CREATE VIEW clientes_CPF as SELECT CPF FROM todos_clientes Introdução a Banco de Dados186 A figura abaixo ilustra a execução do comando de criação da nova visão. A figura mostra a execução bem sucedida do comando para criação da visão clientes_ cpf com a operação de projeção SELECT cpf FROM todos_clientes. A nova visão já está dis- ponível para o uso após a execução bem sucedida do comando. Até agora vimos como criar as visões lógicas. Para criar as visões físicas, ou seja, que estão armazenadas de forma física no banco de dados, devemos utilizar a materialização de visões. A sintaxe é a mesma do exemplo anterior acrescida do termo MATERIALIZED. Segue um exemplo: CREATE MATERIALIZED VIEW clientes_nome as SELECT nome FROM todos_clientes 187Introdução a Banco de Dados Abaixo segue uma tela da ferramenta Pgadmin III após a criação dessa visão. Note na figura acima que temos duas visões criadas: a clientes_cpf e a clientes_ nome. Perceba que a visão clientes_nome possui um M em seu ícone, representando que essa visão está materializada no banco de dados. Dado que uma visão está materializada, não é possível alterar os dados dela de forma direta utilizando o comando INSERT INTO, por exemplo. Se os dados da tabela referen- ciada mudarem, deve-se utilizar o comando de atualização da visão para preencher a visão com os novos dados da tabela, utilizando: REFRESH MATERIALIZED VIEW nome_visão; Introdução a Banco de Dados188 Por fim, vimos a diferença da visão lógica e da visão física ou materializada no banco de dados PostgreSQL, sua criação, alteração e atualização a partir de comandos SQL. 8.2.2. Privilégios e controle de acesso para visões Agora que já entendemos as visões e como criá-las, vamos focar em como definir papéis para realizar o controle de privilégios e acesso às informações do banco de dados. Os papéis que criaremos serão uma expansão dos exemplos anteriores envolvendo o cená- rio de um banco com agências, gerentes e clientes. O comando CREATE ROLE vai permitir a criação de um novo papel no banco de dados, que será concedido a um determinado usuário. A tabela a seguir ilustra o comando para criação de cada papel do nosso exemplo prático. Comandos de definição de papéis. Papel Comando Gerente CREATE ROLE Gerente; Cliente CREATE ROLE Cliente; Atendente CREATE ROLE Atendente; A imagem abaixo mostra a execução dos comandos acima para criação dos papéis de Gerente, Cliente e Atendente. 189Introdução a Banco de Dados Vamos definir que somente o Gerente possui acesso a todos os dados, sem restri- ções. O Atendente somente pode consultar alguns dados do cliente, sem ter acesso a valo- res como empréstimos ou saldo devedor. A tabela abaixo contém a sequência de comandos para adequação das permissões aos papéis citados. Sequência de comandos para definição de permissões. Sequência Comando 1 GRANT SELECT, INSERT, UPDATE, DELETE ON TODOS_CLIENTES TO Gerente; 2 GRANT SELECT ON TODOS_CLIENTES TO Atendente; Interpretando os comandos anteriores, o comando GRANT atribui os privilégios de modificação e projeção concedidos ao Gerente, os comandos citados são: SELECT, INSERT, UPDATE e DELETE para especificamente a tabela “TODOS_CLIENTES”. Além disso, o Atendente somente pode consultar os dados, sendo atribuído a operação SELECT para a tabela TODOS_CLIENTES. A imagem acima mostra a execução dos comandos de permissão de acesso aos papéis de Gerente e Atendente na ferramenta Pgadmin III. Introdução a Banco de Dados190 Pausa para Refletir As visões e o controle de privilégios podem ser utilizados em conjunto para garantir uma maior segurança aos bancos de dados relacionais? O comando ON estipula a relação por meio da qual os privilégios serão concedidos, enquanto o TO deve ser seguido pelo nome da entidade para a qual será concedido o privilégio.Se for necessária a exclusão de algum papel já criado, deve-se utilizar o comando DROP Papel, por exemplo, DROP Cliente. A imagem abaixo mostra a execução do comando DROP, excluindo o papel cliente, que não será mais necessário nos próximos exemplos. Ademais, se quisermos consultar todos os papéis já criados no banco de dados, deve- mos utilizar o comando: SELECT rolname FROM pg_roles; A figura abaixo ilustra essa execução. É possível notar que somente o atributo rolname, ou seja, o nome do papel, será retornado, todos os demais atributos serão omitidos. Perceba que o papel cliente, criado anteriormente, já não está mais presente no banco de dados. 191Introdução a Banco de Dados Por fim, vimos os principais comandos para adequação dos privilégios aos diferentes usuários do banco de dados. Nos próximos tópicos vamos detalhar melhor como consultar, alterar e criar visões no banco de dados com exemplos práticos. 8.3. Consultando, alterando e excluindo Visões Nos tópicos anteriores, vimos as noções básicas envolvendo as visões (View) do banco de dados, a sua importância e os comandos de criação das visões no banco de dados rela- cional usando a ferramenta gráfi ca PgAdmin III agregada ao banco de dados PostgreSQL. Dica Sempre saiba qual tipo de banco de dados você está manipulando, os comandos apresentados podem variar de acordo com o banco de dados utilizado. Introdução a Banco de Dados192 Algumas vezes, as visões criadas precisam ser editadas ou excluídas de acordo com a demanda da organização e manutenção do banco de dados. Para a correta manutenção do banco de dados, é necessário consultar as visões ativas e realizar as exclusões devidas para controle da segurança. Nos próximos tópicos iremos detalhar como consultar as visões já criadas no banco de dados utilizando os comandos já aprendidos. Além disso, também vamos ver o processo de modificação e exclusão das visões presentes no banco de dados. Todos os processos serão detalhados com imagens reais do uso do comando SQL no aplicativo Pgadmin III e o banco de dados PostgreSQL. 8.3.1. Consultando Visões e tabelas A consulta às visões cadastradas no banco de dados são essenciais para a manuten- ção dos dados da organização. Algumas visões podem ficar obsoletas e necessitar de alte- rações ou até mesmo de exclusão. Da mesma forma, novas visões podem ser criadas com o amadurecimento da organização. A consulta de visões, por se tratar de uma consulta ao modelo físico do banco de dados, é dependente da estrutura utilizada, ou seja, cada banco de dados pode implemen- tar essa consulta de uma forma diferente. O comando para listar todas as visões do banco de dados PostgreSQL, incluindo todos os atributos de uma visão é: SELECT * FROM INFORMATION_SCHEMA.views; A figura abaixo ilustra a execução do comando de listagem de todas as visões cria- das no banco de dados PostgreSQL, retornando todos os atributos de uma visão. Note que vários atributos foram retornados, inclusive várias visões que não foram criadas para o nosso esquema do banco de dados, chamado de public na primeira linha dos resultados. 193Introdução a Banco de Dados Para retornar somente as visões que fazem parte do nosso esquema do banco de dados, devemos utilizar o comando WHERE, restringindo os resultados ao nosso esquema. SELECT * FROM INFORMATION_SCHEMA.views where table_schema = ‘public’; A figura abaixo ilustra a execução do comando acima, restringindo a busca das visões ao nosso esquema do banco de dados, chamado de public. Introdução a Banco de Dados194 Pudemos perceber que agora somente foi retornada uma linha na consulta, a qual contém a visão que criamos chamada de “todos clientes nome”, essa visão está criada para o esquema public e é uma visão para o comando SQL: SELECT nome FROM todos_clien- tes, no qual todos_clientes é uma tabela do nosso banco de dados. Pausa para refletir: Dado que as visões são representações abstratas do banco de dados, como podem ser úteis para o cotidiano de um administrador de banco de dados? Podemos restringir ainda mais nossa busca pelas visões cadastradas no banco de dados para selecionar somente o nome da visão e o comando associada à criação dela. A figura abaixo representa a ilustração da execução do comando SQL: “SELECT table_name, view_definition FROM INFORMATION_SCHEMA.views where table_schema = ‘public’;”. 195Introdução a Banco de Dados Na busca acima, modificamos nosso comando SQL para listar somente o nome da visão, guardada no atributo table_name e o comando de definição da visão, armazenado no atributo view_definition. 8.3.2. Modificando uma visão Algumas vezes é necessário realizar a alteração de uma visão por motivos de erro na criação, inclusão ou exclusão de algum atributo, mudança do comando SQL, dentre diver- sos outros fatores. Para esse procedimento, podemos utilizar o seguinte comando para alterar uma visão no banco de dados PostgreSQL: “ALTER VIEW todos_clientes_nome RENAME TO clientes_nome” Aqui alteramos o nome da visão todos_clientes_nome, para somente clientes_nome. A figura abaixo ilustra a execução desse comando. Introdução a Banco de Dados196 Além do nome, também podemos alterar os atributos da visão, o comando de gera- ção e todos os outros fatores que compõem uma visão. Por exemplo, para definição do comando SQL de criação da visão, devemos utilizar CREATE OR REPLACE VIEW, infor- mando o novo comando SQL e o nome da visão. Para os outros procedimentos de altera- ção de visão é utilizado o comando ALTER VIEW. A tabela abaixo sintetiza os comandos utilizados para alterar as visões. Prefixo Comando Descrição ALTER VIEW nomevisao ALTER Alterar as colunas da visão ALTER VIEW nomevisao OWNER TO Alterar o criador da visão ALTER VIEW nomevisao RENAME TO Alterar o nome da visão ALTER VIEW nomevisao SET SCHEMA Definir um novo esquema à visão ALTER VIEW nomevisao SET Alterar as opções da visão ALTER VIEW nomevisao RESET Resetar as opções da visão 197Introdução a Banco de Dados Os comandos da tabela acima devem ser precedidos do comando ALTER VIEW para indicar a intenção de realizar a alteração na tabela. Após isso e o nome da visão, o comando mais específico deve ser definido, por exemplo, ALTER para alterar as proprieda- des das colunas da visão, RENAME TO para alterar o nome da visão, e assim por diante. Além da mudança na estrutura da visão, também podemos alterar os dados de uma visão de forma que esses dados sejam refletidos na tabela à qual a visão está associada. Podemos utilizar o comando INSERT INTO para inserir dados em uma visão, assim como fazemos com uma tabela comum. As alterações feitas na visão serão refletidas na tabela referenciada alterando também os dados da tabela original. Porém há restrições quanto a isso, não é possível inserir um conjunto de valores que são obrigatórios na tabela original e não estão na visão. Outra restrição é quanto à alteração de valores obrigatórios, sendo necessário informar algum valor, mesmo que seja nulo. 8.3.3. Excluindo visões Se em um determinando momento da organização, alguma regra mudou e uma visão previamente criada não é mais necessária, podemos apagar essa visão do nosso banco de dados utilizando o seguinte comando SQL: “DROP VIEW nome_visão” No nosso exemplo, vamos apagar a visão previamente criada, chamada de clientes utilizando o comando: “DROP VIEW clientes” A figura abaixo ilustra a execução do comando de exclusão da visão. Introdução a Banco de Dados198 Podemos perceber, na imagem acima, que a execução demorou apenas 12 milisse- gundos e foi executada com sucesso, ou seja, a visão clientes foi apagada do esquema do banco de dados. Podemos verificar que a visão foi excluída de acordo com a figura abaixo, na qual não há mais nenhuma visão no nosso esquema do banco de dados chamado public. 199Introdução a Banco de Dados Por fim, vimos as principais operações em visões nos bancos de dadosrelacionais, mais especificamente usando o banco de dados PostgreSQL e a ferramenta auxiliar Pgadmin III. Proposta de Atividade Reforce seu aprendizado com o exercício sugerido a seguir. A atividade não é avaliativa, mas é uma boa oportunidade para testar seus conhecimentos e fixar o conteúdo estudado no capítulo. Agora é a hora de recapitular tudo o que você aprendeu nesse capítulo! Elabore várias con- sultas SQL para criação, alteração e exclusão de visões para um banco de dados que você criou. Você pode utilizar o esquema desenvolvido nos capítulos anteriores destacando as principais ideias abordadas ao longo do capítulo. Ao produzir suas consultas, aplique todas as operações de manipulação das visões vistas no capítulo utilizando a linguagem SQL e con- sidere as leituras básicas e complementares realizadas. Recapitulando Vimos no começo do capítulo que nossa pergunta norteadora foi: “como manipu- lar visões em um banco de dados relacional utilizando a linguagem SQL?”. Acredito que agora é possível entender melhor como podemos manipular as visões criando, alterando e excluindo-as em um banco de dados relacional utilizando a linguagem SQL, mais especifi- camente utilizando o PostgreSQL e o Pgadmin III. Vimos os principais comandos de operação de visões em um banco de dados relacio- nal utilizando a linguagem SQL, com exemplos dos principais comandos: CREATE VIEW, ALTER VIEW e DROP VIEW. Além disso, vimos como as visões podem ser úteis no cotidiano do administrador do banco de dados, permitindo a criação de relações abstratas que facilitam o acesso e permi- tem maior segurança ao acesso aos dados. Por fim, exemplificamos o uso do controle de privilégios e as visões em conjunto para melhorar a segurança no banco de dados, não permitindo que pessoas não autorizadas tenham acesso a todas as informações do banco. Utilizando as visões é possível restringir quais dados serão expostos a cada função/pessoa.
Compartilhar