Vises e Autorizao de Acesso Vania Bogorny Vises

Visões e Autorização de Acesso Vania Bogorny

Visões n Dois objetivos principais das visões: ¨ Simplificar consultas ¨ Autorização de acesso (segurança)

Conceito Visão: é um meio de prover ao usuário um “modelo personalizado” do banco de dados n é uma relação que não armazena dados, composta dinamicamente por uma consulta que é previamente analisada e otimizada. n

Visão Um SGBD pode dar suporte a um grande número de visões sobre qualquer conjunto de relações n O SGBD armazena a definição da visão, mas ela é instanciada quando uma consulta sobre ela for executada n n ¨ Toda visão pode ser consultada mas nem toda visão pode ser atualizada

Criação e consultas em visões

n Em SQL uma visão é definida como; Create view nome. Da. Visao <expressão de consulta>, Onde <expressao de consulta> é qualquer expressão de consulta válida em SQL n n n Projeto (cod. Proj, tipo, descricao) Projeto. Empregado (cod. Proj, cod. Emp, data. Inicial, data. Final) Empregado (cod. Emp, nome, categoria, salario) Create view v. Alto. Escalao as select cod. Emp, nome, salario from empregado where salario>10000 n Esta visão terá os atributos especificados na consulta Tabela base

Visão sobre uma relação n Outra forma Create view v. Alto. Escalao (a, b, c) as select cod. Emp, nome, salario from empregado where salario>10000 n Esta visão terá os atributos a, b, c, que serão instanciados com os respectivos valores recuperados pela consulta (cod. Emp, nome, salario)

Visão com várias relações n Projetos do alto escalão ( visão com várias tabelas) n Projeto (cod. Proj, tipo, descricao) Projeto. Empregado (cod. Proj, cod. Emp, data. Inicial, data. Final) Empregado (cod. Emp, nome, categoria, salario) n n Create view v. Projeto. Alto. Escalao as select e. cod. Emp, e. nome, e. salario, p. descricao from empregado e, projeto p, projeto. Empregado pe where e. salario>10000 AND e. cod. Emp=pe. cod. Emp AND pe. cod. Proj=p. cod. Proj

Visão: recursividade (visão sobre visão) n Projetos do alto escalãoo (visão sobre visão) n Projeto (cod. Proj, tipo, descricao) Projeto. Empregado (cod. Proj, cod. Emp, data. Inicial, data. Final) Empregado (cod. Emp, nome, categoria, salario) v. Alto. Escalao (cod. Emp, nome, salario) n n n Create view v. Projetos. Alto. Escalao as select e. cod. Emp, a. nome, a. salario, p. descricao from alto. Escalao a, projeto p, projeto. Empregado pe where a. cod. Emp=pe. cod. Emp AND pe. cod. Proj=p. cod. Proj

Consultas em Visões n Consultas SQL podem ser especificadas sobre a visão select nome from v. Projetos. Alto. Escalao Where descricao = “Projeto A” n n Uma visão está sempre atualizada: Ao modificar tuplas nas tabelas envolvidas na visão, a visão vai automaticamente refletir as alterações A visão não é realizada quando é criada mas quando executamos uma consulta sobre ela

Consultas em Visões n Quando uma visão não é mais necessária podemos eliminá-la, usando o comando drop view DROP VIEW nome. Da. Visao

Autorização de Acesso

Autorização de Acesso Objetivo proteção contra acessos mal intencionados ¨ controlar quais dados um usuário/grupo de usuários pode ter acesso ¨ controlar quais operações um usuário/grupo de usuários pode realizar sobre estes dados ¨

Autorização de Acesso: Exemplos n Exemplo 1: Em um sistema bancário, um funcionário precisa saber os dados clientes, mas apenas os que tem conta poupança na agência 1899 -6 Cliente (#cod. Cli, nome, endereco, cod. Cidade) Agencia (#cod. Agencia, nome, descricao) Poupança (# num. Conta, # cod. Agencia, # cod. Cli, saldo) n Exemplo 2: um funcionário de uma empresa deve ter acesso aos nomes dos funcionários e aos seus projetos, mas não de ter acesso ao salário dos funcionários Empregado (#cod. Emp, nome, categoria, salario) Projeto (#cod. Proj, tipo, descricao) Projeto. Empregado (# cod. Proj, # cod. Emp, data. Inicial, data. Final)

Autorização de Acesso Um usuário do Banco de dados pode ter diversas formas de autorização a partes do BD: n O DBA é o superusuário do BD, que pode tudo alguns privilégios são exclusivos dele, como a recuperação do BD, a configuração de parâmetros do SGBD, etc. ¨ concede/retira (revoga) privilégios de acesso aos outros usuários ¨

Autorização de Acesso Duas formas principais de acesso: n Nível de conta (usuário): o administrador do BD pode dar permissões aos usuários para criar esquemas, modificar e criar tabelas e selecionar dados ¨O DBA estabelece permissões da conta, independente das relações do BD n Nível de relação/visão: o DBA pode controlar o privilégio de acesso de cada usuário a relações ou visões específicas do BD ¨ definidas para SQL ¨ Para cada usuário podem ser dadas permissões de leitura(seleção), modificação e referência

Autorização de Acesso n Autorização de leitura: permite apenas recuperar dados de uma relação ¨ n permissão select Autorização de modificação: privilégios para insert, delete e update ¨ Para insert e update é possível restringir os atributos n Autorização de referência: uma conta (de usuário) pode fazer referência a uma relação ao especificar restrições de integridade ¨ Pode ser por atributo

Autorização de Acesso em SQL n Lista basica: alter, delete, index, insert, select e update n Sintaxe: grant <lista de privilégios> on <nome da relação ou visão> to <lista de usuários> n Exemplo: grant select on cliente to U 1, U 2, U 3 grant update (saldo) on deposito to U 1, U 2

Autorização de Acesso: Exemplos n Exemplo 1: Em um sistema bancário, um funcionário precisa saber os dados clientes, mas apenas os que tem conta poupança na agência 1899 -6 Cliente (cod. Cli, nome, endereco, cod. Cidade) Agencia (cod. Agencia, nome, descricao) Poupança ( num. Conta, # cod. Agencia, # cod. Cli, saldo) n Solução: usar views Create view v. Cliente. Poupanca. Agencia as select c. cod. Cli, c. nome, p. num. Conta from cliente c, poupanca p where c. cod. Cli=p. cod_cli and p. cod. Agencia=“ 1899 -6” n grant select on v. Cliente. Poupanca. Agencia to U 10

Autorização de Acesso: Exemplos n Exemplo 2: um funcionário de uma empresa deve ter acesso aos nomes dos funcionários e aos seus projetos, mas não de ter acesso ao salário dos funcionários Create view v. Empregado. Projeto as select e. cod. Emp, e. nome, p. tipo, p. descricao, j. data. Inicial, j. data. Final from empregado e, projeto p, projeto. Empregado j where e. cod. Emp=j. cod. Emp and j. cod. Proj=p. cod. Proj n grant select on v. Empregado. Projeto to U 50 Empregado (cod. Emp, nome, categoria, salario) Projeto (#cod. Proj, tipo, descricao) Projeto. Empregado (# cod. Proj, # cod. Emp, data. Inicial, data. Final)

Autorização de Acesso Roles (Papéis): são interessantes quando um grupo de usuários tem as mesmas restrições de acesso: ex: vários caixas de um banco (várias agências) tem permissão para creditar e debitar valores na conta dos clientes. Ao invés de dar permissão de inclusão, alteração e exclusão para cada um dos caixas que tem este direito, cria-se o papel movimentação Create role movimentaçao E dá-se a permissão ao papel Grant insert, update, delete ON TABELA to movimentaçao Vincula todos os usuarios ao papel GRANT movimentação TO A, B, C, C

Exemplo Completo n n Suponha que o DBA crie 4 contas U 1, U 2, U 3 e U 4. 1) Somente U 1 deve criar relações no banco (privilégio de conta) ¨ grant n create. Tab to U 1 2) com essa autorização o usuário U 1 pode criar relações e terá TODOS os privilégios sobre elas ¨ Suponha que U 1 criou as relações Empregado (cod. Emp, nome, categoria, salario) Projeto (#cod. Proj, tipo, descricao) Projeto. Empregado (# cod. Proj, # cod. Emp, data. Inicial, data. Final)

Exemplo Completo n 3) Suponha que U 1 quer dar ao usuário U 2 permissão para incluir e remover tuplas em Empregado e Projeto ¨ n grant INSERT, DELETE ON EMPREGADO, PROJETO TO U 2 4) Suponha que U 1 quer dar ao usuário U 3 permissão para recuperar tuplas em Empregado e seja capaz de PROPAGAR este privilégio ¨ grant SELECT ON EMPREGADO TO U 3 WITH GRANT OPTION Empregado (cod. Emp, nome, categoria, salario) Projeto (#cod. Proj, tipo, descricao)

Exemplo Completo n 4) Agora U 3 pode conceder privilégio de seleção para U 4 sobre a relação Empregado grant SELECT ON EMPREGADO TO U 4 ¨ Obs: U 4 não pode propagar este privilégio ¨ n 5) Suponha que U 1 queira revogar a permissão de U 3 ¨ REVOKE SELECT ON EMPEGADO FROM U 3 Empregado (cod. Emp, nome, categoria, salario) Projeto (#cod. Proj, tipo, descricao)

Exemplo Completo n 6)Suponha que U 1 queira dar ao usuário U 3 permissão apenas para consultar empregados que trabalhem no projeto AATOM. Create view v. Empregado. Projeto as select e. cod. Emp, e. nome, e. categoria, e. salario from empregado e, projeto p, projeto. Empregado j where e. cod. Emp=j. cod. Emp and j. cod. Proj=p. cod. Proj and p. descricao=“AATOM” ¨ grant SELECT ON v. EMRPEGADOProjeto TO U 3 Empregado (cod. Emp, nome, categoria, salario) Projeto (cod. Proj, tipo, descricao) Projeto. Empregado (# cod. Proj, # cod. Emp, data. Inicial, data. Final)

n grant all to {lista. Usuários | public}

Lista de Permissões no Postgre. SQL GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [, . . . ] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, . . . ] TO { username | GROUP groupname | PUBLIC } [, . . . ] [ WITH GRANT OPTION ] GRANT { { USAGE | SELECT | UPDATE } [, . . . ] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, . . . ] TO { username | GROUP groupname | PUBLIC } [, . . . ] [ WITH GRANT OPTION ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, . . . ] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, . . . ] TO { username | GROUP groupname | PUBLIC } [, . . . ] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, . . . ] ] ) [, . . . ] TO { username | GROUP groupname | PUBLIC } [, . . . ] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, . . . ] TO { username | GROUP groupname | PUBLIC } [, . . . ] [ WITH GRANT OPTION ] GRANT { { CREATE | USAGE } [, . . . ] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, . . . ] TO { username | GROUP groupname | PUBLIC } [, . . . ] [ WITH GRANT OPTION ] GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, . . . ] TO { username | GROUP groupname | PUBLIC } [, . . . ] [ WITH GRANT OPTION ] GRANT role [, . . . ] TO username [, . . . ] [ WITH ADMIN OPTION ]

Exercícios Escreva uma visão que contenha o nome do medico, o nome do paciente e a data da consulta n Escreva uma visão que contenha o nome dos médicos que não atendem em nenhum ambulatório n Escreva uma visão que recupere os funcionários que nunca consultaram n

Exercícios - Permissões Crie 4 usuarios: um dba, medico, estagiario e secretaria n n n O usuário estagiário pode ver o nome do medico, o nome do paciente e a data da consulta, mas não podem ver a doença do paciente Somente o estagiario pode incluir novos funcionários Somente o medico tem acesso completo aos dados do paciente, tanto para cadastro quanto para alteração. Somente o usuário dba pode consultar e alterar o salário dos funcionários O usuário “secretaria” somente pode ter acesso aos médicos que trabalham no ambulatório do 4 andar Crie um papel chamado consultas. Este papel poderá consultar as tabelas paciente e medico. Crie 2 usuarios u 1 e u 2 que tenham o mesmo papel de consulta