Atenç�o: esse artigo tem uma palestra complementar. Clique e assista!

De que se trata o artigo:

O artigo aborda os conceitos relacionais com gerenciamento de usu�rios e permiss�es de acesso em SGBD�s, focando o PostgreSQL. Neste artigo, foi implementado um estudo de caso para demonstrar na pr�tica as funç�es do PostgreSQL no que se refere ao gerenciamento de usu�rios.

Para que serve:

O conte�do deste artigo visa solucionar problemas inerentes do acesso a dadosem um SGBD, buscando formas eficientes de controlar as permiss�es dos usu�rios.

Em que situaç�o o tema � �til:

Em toda aplicaç�o com conex�o a banco de dados � importante o controle de acesso diferenciado para cada papel de usu�rio no sistema. Tendo em vista isto, este artigo torna-se uma boa opç�o de fonte de pesquisa.

A segurança dos sistemas de informaç�o engloba um n�mero elevado de �reas que poder�o estar sob a responsabilidade de uma ou v�rias pessoas. Entre estas �reas encontram-se a segurança de redes, a segurança f�sica, a segurança de computadores, a segurança das aplicaç�es, a segurança da informaç�o etc.

O respons�vel pela implementaç�o da segurança dos sistemas de informaç�o em uma organizaç�o possui como primeira miss�o, e mais importante, a garantia da segurança da informaç�o que protege. Esta garantia � conseguida mediante a utilizaç�o de v�rios instrumentos.

Uma pol�tica de backup e recuperaç�o de dados adequadamente elaborada e executada proteger� a organizaç�o contra a perda de informaç�o devido a falhas de hardware, defeitos de software, erros humanos, intrusos, sabotagem e desastres naturais. Entretanto, esta n�o � a �nica maneira existente de proteç�o das informaç�es. Afinal, precisamos mais do que uma soluç�o relativa ao acontecimento de falhas.

Quando se trata de proteger informaç�es guardadas em banco de dados, nos preocupamos em que dados proteger e como proteg�-los. Os SGBD�s atuais nos fornecem poderosas ferramentas que auxiliam em tais tarefas. Sendo os bancos de dados sistemas de armazenamento de informaç�o, e sendo esta um elemento de elevado valor, quer seja financeiro, quer seja estrat�gico, � necess�rio que haja algum tipo de controle de acesso a essa informaç�o.

Para mantermos a integridade das informaç�es e realizarmos auditoria das mesmas, podemos utilizar os recursos dos gatilhos. Al�m disso, podemos fazer uso de uma linguagem especial para controle de acesso e permiss�o aos dados. Esta linguagem � a DCL (Data Control Language� Linguagem de Controle de Dados). Ela � um subconjunto da SQL para o controle de permiss�es dos usu�rios aos objetos do banco de dados.

Neste artigo, ser� demonstrado o funcionamento do controle de permiss�es de acesso aos objetos do banco de dados PostgreSQL.

Administrando Usu�rios e Privil�gios no PostgreSQL

Todo agrupamento de bancos de dados possui um conjunto de usu�rios de banco de dados. Estes usu�rios s�o distintos dos usu�rios gerenciados pelo sistema operacional onde o servidor executa. Eles possuem objetos de banco de dados (por exemplo, tabelas, vis�es etc.), e podem conceder privil�gios nestes objetos para outros usu�rios controlando, assim, quem pode acessar qual objeto.

A DCL controla os aspectos de autorizaç�o de dados e licenças de usu�rios para controlar quem tem acesso para ver ou manipular dados dentro do banco de dados. Os nomes dos usu�rios de banco de dados s�o globais para todo o agrupamento de bancos de dados (e n�o apenas pr�prio de cada banco de dados).

No decorrer deste artigo, veremos conceitos relacionados a usu�rios, grupos e �roles� (pap�is), ao passo que aprenderemos como cri�-los e quando utiliz�-los em nossas aplicaç�es.

Criaç�o de Usu�rio de Banco de Dados � CREATE USER

Como foi mencionado, existem usu�rios que s�o independentes do sistema operacional, que servem para manipular objetos do SGBD.

No PostgreSQL existe o comando CREATE USER que adiciona um novo usu�rio no bancos de dados. Este comando j� foi muito utilizado, mas mesmo ainda funcionando, hoje � apenas umaliaspara o comando CREATE ROLE que veremos mais adiante. O comando gen�rico de criaç�o de um usu�rio est� exibido na Listagem 1.

Listagem 1. Comando de Criaç�o de um gatilho no PostgreSQL

CREATE USER nome [ [ WITH ] opç�o [ ... ] ]

As opç�es que acompanham este comando est�o listadas na Tabela 1.

Opç�o	Significado
Nome	O nome do usu�rio
Id_do_usu�rio	A cl�usula SYSID pode ser utilizada para escolher o identificador de usu�rio do PostgreSQL do novo usu�rio.
CREATEDB \| NOCREATEDB	Estas cl�usulas definem a permiss�o para o usu�rio criar banco de dados. Se for especificado CREATEDB, o usu�rio sendo definido ter� permiss�o para criar seus pr�prios bancos de dados. Se for especificado NOCREATEDB, nega-se ao usu�rio a permiss�o para criar banco de dados. Se nenhuma destas cl�usulas for especificada, o padr�o � NOCREATEDB.
CREATEUSER \| NOCREATEUSER	Estas cl�usulas determinam se o usu�rio pode ou n�o criar novos usu�rios. CREATEUSER tamb�m torna o usu�rio um super-usu�rio, o qual pode passar por cima de todas as restriç�es de acesso. Se nenhuma destas cl�usulas for especificada, o padr�o � NOCREATEUSER.
Nome_do_grupo	O nome de um grupo existente onde o usu�rio ser� inclu�do como um novo membro. Podem ser especificados nomes de v�rios grupos.
Senha	Define a senha do usu�rio. Se n�o se pretende utilizar autenticaç�o por senha, esta opç�o pode ser omitida, mas o usu�rio n�o poder� mais se conectar se for decidido mudar para autenticaç�o por senha. A senha poder� ser definida ou mudada posteriormente atrav�s do comando ALTER USER.
ENCRYPTED \| UNENCRYPTED	Estas cl�usulas controlam se a senha ser� armazenada criptografada, ou n�o, nos cat�logos do sistema.
Data_e_hora	A cl�usula VALID UNTIL define uma data e hora ap�s a qual a senha do usu�rio n�o � mais v�lida. Se esta cl�usula for omitida, a conta ser� v�lida para sempre.

Tabela 1. Opç�es do comando CREATE USER.

Removendo um Usu�rio no Banco de Dados � DROP USER

Uma vez criado no banco de dados, o comando DROP USER remove o usu�rio especificado. Por�m, esse comando n�o remove as tabelas, vis�es ou outros objetos pertencentes ao usu�rio. Se o usu�rio possuir algum banco de dados, uma mensagem de erro ser� gerada.

O comando que remove um usu�rio do PostgreSQL est� mostrado na Listagem 2.

Listagem 2. Comando que remove um usu�rio no PostgreSQL

DROP USER nome_usu�rio

Neste caso,nome_usu�riorefere-se ao usu�rio que est� sendo removido. Para remover um usu�rio que possui um banco de dados, primeiro remove-se o banco de dados ou muda-se o dono do mesmo.

Alterando um Usu�rio � ALTER USER

Em alguns casos, � conveniente modificar um usu�rio, seja para alterar sua senha ou validade da mesma ou para incluir a permiss�o de criaç�o de usu�rios ou retir�-la, caso exista etc. O comando para modificar um usu�rio � o ALTER USER e est� demonstrado na Listagem 3.

Listagem 3. Comando que altera um usu�rio no PostgreSQL

ALTER USER nome [ [ WITH ] opç�o [ ... ] ]

As opç�es desse comando s�o exibidas na Tabela 2.

Opç�o	Significado
Nome	O nome de usu�rio cujos atributos est�o sendo alterados.
CREATEDB \| NOCREATEDB	Estas cl�usulas definem a permiss�o para o usu�rio criar banco de dados. Se CREATEDB for especificado, o usu�rio ter� permiss�o para criar seus pr�prios bancos de dados. Especificando NOCREATEDB nega-se ao usu�rio a permiss�o para criar banco de dados (se o usu�rio for um super-usu�rio ent�o esta definiç�o n�o tem efeito pr�tico).
CREATEUSER \| NOCREATEUSER	Estas cl�usulas determinam se o usu�rio ter� permiss�o para criar novos usu�rios. CREATEUSER torna o usu�rio um super-usu�rio, n�o sujeito a restriç�es de acesso.
Senha	A nova senha a ser utilizada para esta conta.
ENCRYPTED \| UNENCRYPTED	Estas palavras chave controlam se a senha � armazenada criptografada, ou n�o, em pg_shadow (Consulte o comando CREATE USER para obter informaç�es adicionais sobre esta opç�o).
data_e_hora	A data (e, opcionalmente, a hora) de expiraç�o da senha do usu�rio. Para fazer com que a senha nunca expire deve ser utilizado 'infinity'.
Novo_nome	O novo nome de usu�rio.
Par�metro valor	Define o valor fornecido como sendo o valor padr�o para o par�metro de configuraç�o especificado. Se valor for DEFAULT ou, de forma equivalente, se RESET for utilizado, a definiç�o da vari�vel espec�fica para o usu�rio � removida, e o valor padr�o global do sistema ser� herdado nas novas sess�es do usu�rio. Use RESET ALL para remover todas as definiç�es espec�ficas do usu�rio.

Tabela 2. Opç�es do comando ALTER USER

Uma variaç�o do comando ALTER USER exibido na Listagem 4, muda o nome de um usu�rio. � importante comentar que apenas um super-usu�rio pode mudar o nome de outro usu�rio.

Listagem 4. Comando para alterar o nome de um usu�rio

ALTER USER nome RENAME TO novo_nome

Conceitos de Grupos de Usu�rios no PostgreSQL

Assim como nos sistemas operacionais baseados em Unix, os grupos s�o uma forma l�gica de juntar usu�rios para facilitar o gerenciamento de privil�gios. Tais privil�gios podem ser concedidos, ou revogados, para o grupo como um todo. Para criar um grupo, deve ser utilizado o comando CREATE GROUP, como mostrado na Listagem 5.

Listagem 5. Comando para criar um grupo no PostgreSQL

CREATE GROUP nome_do_grupo;

Podemos adicionar ou remover usu�rios em um grupo existente utilizando o comando ALTER GROUP da Listagem 6, respectivamente.

Listagem 6. Comandos para adicionar e remover usu�rios em um grupo

ALTER GROUP nome_do_grupo ADD USER nome_do_usu�rio;
  ALTER GROUP nome_do_grupo DROP USER nome_do_usu�rio;

Uma vez criado um grupo, ainda podemos remov�-lo. Tal operaç�o pode ser feita usando o comando DROP GROUP, conforme Listagem 7.

Listagem 7. Comando para remover um grupo no PostgreSQL

DROP GROUP nome_do_grupo;

O comando DROP GROUP remove os grupos, mas n�o remove os usu�rios membros do grupo.

Uma coisa importante que deve ser comentada � que n�o existe o comando CREATE GROUP no padr�o SQL. O conceito de "pap�is" (roles) que veremos adiante � semelhante ao de grupos e ser� o foco principal deste artigo.

Alterando Grupos no PostgreSQL � ALTER GROUP

Assim como podemos modificar um usu�rio criado no banco de dados, podemos, da mesma forma, modificar um grupo. Para fazermos isso, utilizamos o comando ALTER GROUP. A Listagem 8 adiciona, remove e altera usu�rios em um grupo, respectivamente.

Listagem 8. Comandos para alterar um grupo no PostgreSQL

ALTER GROUP nome_do_grupo ADD USER nome_do_usu�rio;
  ALTER GROUP nome_do_grupo DROP USER nome_do_usu�rio;
  ALTER GROUP nome_do_grupo RENAME TO novo_nome;

Criando Pap�is (roles) no PostgreSQL � CREATE ROLE

O comando CREATE ROLE adiciona um novo papel (role) ao agrupamento de bancos de dados do PostgreSQL. O papel � uma entidade que pode possuir objetos do banco de dados e possuir privil�gios do banco de dados. Ele pode ser considerado como sendo um "usu�rio", um "grupo", ou ambos, dependendo de como � utilizado.

O comando CREATE ROLE � o substituto dos comandos CREATE USER e CREATE GROUP por possuir mais recursos que os mesmos. A Listagem 9 demonstra o comando de criaç�o de um ROLE, enquanto a Tabela 3 explica as opç�es que podem ser utilizadas neste comando.

Listagem 10. Comando para criar um role no PostgreSQL

CREATE ROLE nome [ [WITH] opç�o [...]];

Opç�o	Significado
Nome	Nome do papel
SUPERUSER \| NOSUPERUSER	Estas cl�usulas determinam se o novo papel � um "super-usu�rio", o qual pode passar por cima de todas as restriç�es de acesso dos bancos de dados. Se nenhuma destas duas cl�usulas for especificada, o padr�o � NOSUPERUSER.
CREATEDB \| NOCREATEDB	Estas cl�usulas definem a permiss�o para o papel criar bancos de dados. Se nenhuma destas duas cl�usulas for especificada, o padr�o � NOCREATEDB.
CREATEROLE \| NOCREATEROLE	Estas cl�usulas determinam se o papel ter� permiss�o para criar novos pap�is (ou seja, executar o comando CREATE ROLE).
CREATEUSER \| NOCREATEUSER	Estas cl�usulas s�o formas obsoletas, mas ainda aceitas, das cl�usulas SUPERUSER e NOSUPERUSER.
INHERIT \| NOINHERIT	Estas cl�usulas determinam se o papel "herda" os privil�gios dos pap�is dos quais � membro. Um papel com o atributo INHERIT pode utilizar, automaticamente, todos os privil�gios de banco de dados que foram concedidos a todos os pap�is dos quais � um membro direto ou indireto.
LOGIN NOLOGIN	Estas cl�usulas determinam se o papel pode estabelecer uma conex�o (log in), ou seja, se o papel pode ser fornecido como nome de autorizaç�o inicial da sess�o durante a conex�o do cliente. Um papel com o atributo LOGIN pode ser considerado como sendo um usu�rio. Se nenhuma destas duas cl�usulas for especificada, o padr�o � NOLOGIN, exceto quando CREATE ROLE for chamada atrav�s de sua forma alternativa CREATE USER.
CONNECTION LIMIT limite_de_conex�es	Se o papel puder estabelecer uma conex�o, esta cl�usula especifica quantas conex�es simult�neas este papel pode estabelecer. -1 (o padr�o) significa sem limite.
PASSWORD senha	Define a senha do papel (A senha s� � �til para os pap�is que possuem o atributo LOGIN, mesmo assim pode ser definida uma senha para os pap�is sem este atributo).
ENCRYPTED UNENCRYPTED	Estas cl�usulas controlam se a senha ser� armazenada criptografada, ou n�o, nos cat�logos do sistema;
VALID UNTIL 'carimbo_do_tempo'	A cl�usula VALID UNTIL define uma data e hora ap�s a qual o papel n�o � mais v�lido. Se esta cl�usula for omitida, a senha ser� v�lida para sempre.
IN ROLE nome_do_papel	A cl�usula IN ROLE relaciona um ou mais pap�is existentes, aos quais o novo papel ser� adicionado imediatamente como sendo um novo membro.
IN GROUP nome_do_papel	A cl�usula IN GROUP � uma forma obsoleta de IN ROLE.
ROLE nome_do_papel	A cl�usula ROLE relaciona um ou mais pap�is existentes a serem automaticamente adicionados como membros do novo papel (Isto tem como efeito tornar o novo papel um "grupo").
ADMIN nome_do_papel	A cl�usula ADMIN � como a cl�usula ROLE, mas os pap�is especificados s�o adicionados ao novo papel WITH ADMIN OPTION, dando aos mesmos o direito de permitir que outros pap�is se tornem membros deste grupo.
USER nome_do_papel	A cl�usula USER � uma forma obsoleta da cl�usula ROLE.
SYSID id_usu�rio	A cl�usula SYSID � ignorada, mas � aceita para manter a compatibilidade com as vers�es anteriores.

Tabela 3.Opç�es do comando CREATE ROLE.

Deve ser tomado cuidado com o privil�gio CREATEROLE. N�o existe o conceito de herança para os privil�gios do papel com CREATEROLE. Isto significa que mesmo que o papel n�o possua um determinado privil�gio, mas tenha permiss�o para criar outros pap�is, poder� facilmente criar um papel com privil�gios diferentes do pr�prio papel (exceto criar pap�is com privil�gio de super-usu�rio).

Removendo um Role � DROP ROLE

O comando DROP ROLE remove os pap�is especificados. A Listagem 10 demonstra como o comando DROP ROLE funciona.

Listagem 10. Comando para remover um role no PostgreSQL

DROP ROLE [ IF EXISTS ] nome [, ...]

O papel n�o poder� ser removido se ainda estiver sendo referenciado em qualquer banco de dados do agrupamento. Assim ser� lançado um erro caso tente-se remov�-lo. Antes de remover o papel, � necess�rio remover todos os objetos pertencentes ao mesmo (ou mudar o dono), e revogar todos os privil�gios concedidos pelo papel.

Alterando um Role � ALTER ROLE

O comando ALTER ROLE altera os atributos de um papel do PostgreSQL.

AListagem 11demonstra o comando gen�rico de alteraç�o de um role.

Listagem 11.Comando para alterar um role no PostgreSQL

ALTER ROLE nome [ [ WITH ] opç�o [ ... ] ]

O comando ALTER ROLE pode modificar um role, utilizando as mesmas opç�es mostradas na Tabela 3.

Concedendo e Revogando Privil�gios no PostgreSQL

Quando um objeto do banco de dados � criado, � atribu�do um dono ao mesmo. O dono � o usu�rio que executou o comando de criaç�o do objeto. Para mudar o dono de uma tabela, �ndice, seq��ncia ou vis�o deve ser utilizado o comando ALTER TABLE. A Listagem 12 mostra a modificaç�o de um dono de uma tabela de um banco de dados.

Listagem 12. Comando para alterar um dono de uma tabela no PostgreSQL

ALTER TABLE nome_da_tabela OWNER TO novo_dono

Por padr�o, somente o dono (ou um super-usu�rio) pode fazer qualquer coisa com o objeto. Para permitir o uso por outros usu�rios, devem ser concedidos privil�gios aos mesmos. Em SQL, existe o comando GRANT. Ele possui duas funcionalidades b�sicas: conceder privil�gios para um objeto do banco de dados (tabela, vis�o, seq��ncia, banco de dados, funç�o, linguagem procedural, esquema e espaço de tabelas) e conceder o privil�gio de ser membro de um papel.

Existem v�rios privil�gios distintos: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE e ALL PRIVILEGES. A Tabela 4 exibe os privil�gios poss�veis e os seus significados.

Privil�gio	Significado
SELECT	Permite consultar qualquer coluna da tabela, vis�o ou seq��ncia especificada. Tamb�m permite utilizar o comando COPY TO.
INSERT	Permite inserir novas linhas na tabela especificada. Tamb�m permite utilizar o comando COPY FROM.
UPDATE	Permite modificar os dados de qualquer coluna da tabela especificada.
DELETE	Permite excluir linhas da tabela especificada.
RULE	Permite criar regras para uma tabela ou para uma vis�o.
REFERENCES	Para criar uma restriç�o de chave estrangeira � necess�rio possuir este privil�gio, tanto na tabela que faz refer�ncia quanto na tabela que � referenciada.
TRIGGER	Permite criar gatilhos na tabela especificada
CREATE	Para bancos de dados, permite a criaç�o de novos esquemas no banco de dados. Para esquemas, permite a criaç�o de novos objetos no esquema. Para mudar o nome de um objeto existente � necess�rio ser o dono do objetoepossuir este privil�gio no esquema que o cont�m. Para espaços de tabelas (TABLESPACE), permite a criaç�o de tabelas e �ndices no espaço de tabelas, e permite a criaç�o de bancos de dados possuindo este espaço de tabelas como seu espaço de tabelas padr�o (Deve ser observado que revogar este privil�gio n�o altera a colocaç�o dos objetos existentes).
TEMPORARY TEMP	Permite a criaç�o de tabelas tempor�rias ao usar o banco de dados.
EXECUTE	Permite utilizar a funç�o especificada e qualquer operador implementado utilizando a funç�o. Este � o �nico tipo de privil�gio aplic�vel �s funç�es (Esta sintaxe funciona para as funç�es de agregaç�o tamb�m).
USAGE	Para as linguagens procedurais, permite o uso da linguagem especificada para criar funç�es nesta linguagem. Este � o �nico tipo de privil�gio aplic�vel �s linguagens procedurais. Para os esquemas, permite acessar os objetos contidos no esquema especificado (assumindo que os privil�gios requeridos para os pr�prios objetos estejam atendidos). Essencialmente, concede a quem recebe o direito de "procurar" por objetos dentro do esquema.
ALL PRIVILEGES	Concede todos os privil�gios dispon�veis de uma s� vez. A palavra chave PRIVILEGES � opcional no PostgreSQL, embora seja requerida pelo SQL estrito.

Tabela 4.Todos os poss�veis privil�gios de acesso

Em alguns casos tamb�m se torna necess�rio revogar alguns privil�gios de acesso a usu�rios ou grupos de usu�rios. Para que isso seja feito, utiliza-se o comando REVOKE.

Deve ser observado que os super-usu�rios do banco de dados podem acessar todos os objetos, independentemente dos privil�gios definidos para o objeto. Assim, n�o � aconselh�vel operar como um super-usu�rio a n�o ser quando for absolutamente necess�rio.

Se um super-usu�rio decidir submeter o comando GRANT ou REVOKE, o comando ser� executado como se tivesse sido submetido pelo dono do objeto afetado. Em particular, os privil�gios concedidos atrav�s destes comandos aparecer�o como se tivessem sido concedidos pelo dono do objeto.

Atualmente, o PostgreSQL n�o suporta conceder ou revogar privil�gios para as colunas da tabela individualmente. Uma forma simples de contornar isso seria criar uma vis�o contendo apenas as colunas desejadas e, ent�o, conceder os privil�gios para a vis�o. Os privil�gios especiais do dono da tabela (ou seja, o direito de DROP (remover), GRANT (conceder), REVOKE (revogar), etc.) s�o sempre impl�citos ao fato de ser o dono, n�o podendo ser concedidos ou revogados.

Estudo de Caso � Hotel

Para demonstrar o controle de permiss�es de acesso aos objetos de um banco de dados no PostgreSQL, foi criado um banco de dados de um hotel fict�cio. Este banco de dados possui as tabelascliente, reserva, hospedagem, quarto, tipo_quarto, atendimento e serviço.

No exemplo, foi considerado que cada cliente pode realizar v�rias reservas e v�rias hospedagens. Al�m de, a cada hospedagem, solicitar v�rios serviços.

Ainda � interessante notar que os quartos podem ser de tipos diferentes (apartamento simples, su�te casal, su�te luxo etc.).

A Figura 1 apresenta o diagrama Entidade-Relacionamento do banco de dados em estudo.

Figura 1.Diagrama Entidade-Relacionamento de um hotel.

Neste banco de dados, al�m das tabelas foram criadas tr�s funç�es e uma vis�o. A primeira funç�o � chamada deadicionaReservae serve para realizar a reserva de um determinado quarto para um cliente. Uma outra funç�o, chamadaadicionaHospedagem,cria uma hospedagem para o cliente em um quarto. A �ltima funç�o, denominadarealizaPedido,ser� respons�vel por registrar os pedidos feitos pelos clientes, enquanto estiverem hospedados.

Al�m das funç�es mencionadas, existe a vis�olistaClientes, que exibe apenas os nomes e os sexos dos clientes, descartando o RG e o telefone dos mesmos. A vis�olistaClientesfoi criada para demonstrar a permiss�o de acessar apenas algumas colunas de uma determinada tabela.

Tamb�m foram criados alguns roles e foram dadas permiss�es diferentes de acesso aos dados para os mesmos. A Tabela 5 mostra os tr�s roles (neste caso, servem para definir o perfil de alguns grupos de usu�rios) que t�m acesso ao banco e que tipo de permiss�o cada um deles possui.

Role	Permiss�o
Gerente	� o grupo de usu�rio que pode modificar todos os registros de todas as tabelas e utilizar as funç�esadicionaReserva,adicionaHospedagem e realizaPedidos,al�m de poder dar direitos de acesso aos demais usu�rios.
Atendente	� o grupo de usu�rios que pode manipular apenas as funç�esadicionaReserva, adicionaHospedagemerealizaPedido.
Estagi�rio	� o grupo de usu�rio que s� tem acesso a vis�olistaClientes.

Tabela 5.Roles do banco de dados hotel

Implementaç�o do Banco de Dados Hotel no PostgreSQL

O PostgreSQL ser� utilizado para criar o banco de dados, as funç�es, a vis�o e os roles citados na seç�o anterior. A ferramenta gr�fica PgAdmin III foi utilizada para a criaç�o do banco de dados do estudo em caso.

A Listagem 13 exibe o comando SQL para criaç�o do banco de dados chamadohotel.

Listagem 13. Comando SQL para criaç�o do bancohotel

CREATE DATABASE hotel;

As tabelas do banco de dados hotel s�o criadas pelos comandos da Listagem 14.

Listagem 14. Comandos SQL para criaç�o das tabelas do bancohotel

// Tabela de CLIENTES
  CREATE TABLE cliente
  (
  rg NUMERIC NOT NULL,
       nome VARCHAR(40) NOT NULL,
  sexo CHAR(1) NOT NULL,
  telefone NUMERIC(10,0),
  PRIMARY KEY (rg)
  ) WITHOUT OIDS;
   
  // Tabela TIPO_QUARTO
  CREATE TABLE tipo_quarto
  (
  id_tipo SERIAL NOT NULL,
  descricao VARCHAR(40) NOT NULL,
  valor NUMERIC(9,2) NOT NULL,
  PRIMARY KEY (id_tipo)
  ) WITHOUT OIDS;
   
  // Tabela QUARTO
  CREATE TABLE quarto
  (
  num_quarto INTEGER NOT NULL,
  andar CHAR(10),
  id_tipo INTEGER NOT NULL,
  status CHAR(01) NOT NULL DEFAULT 'D',
  PRIMARY KEY (num_quarto),
  FOREIGN KEY (id_tipo) REFERENCES tipo_quarto (id_tipo)
  ON UPDATE RESTRICT ON DELETE RESTRICT
  ) WITHOUT OIDS;
   
  // Tabela SERVIÇO
  CREATE TABLE servico
  (
  id_servico SERIAL NOT NULL,
  descricao VARCHAR(60) NOT NULL,
  valor NUMERIC(9,2) NOT NULL,
  PRIMARY KEY (id_servico)
  ) WITHOUT OIDS;
   
  // Tabela RESERVA
  CREATE TABLE reserva
  (
  id_reserva SERIAL NOT NULL,
  rg NUMERIC NOT NULL,
  num_quarto INTEGER NOT NULL,
  dt_reserva DATE NOT NULL,
  qtd_dias INTEGER NOT NULL,
  data_entrada DATE NOT NULL,
       status CHAR(1) NOT NULL DEFAULT 'A',
   PRIMARY KEY (id_reserva),
   FOREIGN KEY (rg) REFERENCES cliente (rg)
   ON UPDATE RESTRICT ON DELETE RESTRICT,
   FOREIGN KEY (num_quarto) REFERENCES quarto (num_quarto)
   ON UPDATE RESTRICT ON DELETE RESTRICT
  ) WITHOUT OIDS;
   
  // Tabela HOSPEDAGEM
  CREATE TABLE hospedagem
  (
       id_hospedagem SERIAL NOT NULL,
       rg NUMERIC NOT NULL,
       num_quarto INTEGER NOT NULL,
       data_entrada DATE NOT NULL,
       data_saida DATE,
       status CHAR(1) NOT NULL,
   PRIMARY KEY (id_hospedagem),
   FOREIGN KEY (rg) REFERENCES cliente (rg)
   ON UPDATE RESTRICT ON DELETE RESTRICT,
   FOREIGN KEY (num_quarto) REFERENCES quarto (num_quarto)
   ON UPDATE RESTRICT ON DELETE RESTRICT
  ) WITHOUT OIDS;
   
  // Tabela ATENDIMENTO
  CREATE TABLE atendimento
  (
       id_atendimento SERIAL NOT NULL,
       id_servico INTEGER NOT NULL,
       id_hospedagem INTEGER NOT NULL,
   PRIMARY KEY (id_atendimento),
   FOREIGN KEY (id_servico) REFERENCES servico (id_servico)
   ON UPDATE RESTRICT ON DELETE RESTRICT,
   FOREIGN KEY (id_hospedagem) REFERENCES hospedagem (id_hospedagem)
   ON UPDATE RESTRICT ON DELETE RESTRICT
  ) WITHOUT OIDS;

As funç�es adicionaHospedagem, adicionaReserva e realizaPedido s�o criadas com os comandos das Listagens 15,16 e 17, respectivamente.

Listagem 15. Comando de criaç�o da funç�oadicionaHospedagem

CREATE OR REPLACE FUNCTION adicionaHospedagem(rg_cliente numeric, numero_quarto int) RETURNS void AS
  $
    begin
      perform * from cliente where rg = rg_cliente;
      if found then
        perform * from quarto where upper(status) = 'D' and num_quarto = numero_quarto;
        if found then
          insert into hospedagem values (default, rg_cliente, numero_quarto, current_date, null, 'A');
          update quarto set status = 'O' where num_quarto = numero_quarto;
          RAISE NOTICE 'Hospedagem realizada com sucesso!';
        else
          RAISE EXCEPTION 'Quarto indisponivel para hospedagem!'; 
        end if;
      else
        RAISE EXCEPTION 'Cliente nao consta no cadastro!';      
      end if;    
    end;
  $
  LANGUAGE plpgsql SECURITY DEFINER;

Listagem 16. Comando de criaç�o da funç�oadicionaReserva

CREATE OR REPLACE FUNCTION adicionaReserva(rg_cliente numeric, numero_quarto int, dias int, data_entrada date) RETURNS void AS
  $
    begin
      perform * from cliente where rg = rg_cliente;
      if found then
        perform * from quarto where upper(status) = 'D' and num_quarto = numero_quarto;
        if found then
          insert into reserva values (default, rg_cliente, numero_quarto, current_date, dias, data_entrada, 'A');
          update quarto set status = 'R' where num_quarto = numero_quarto;
          RAISE NOTICE 'Reserva realizada com sucesso!';
        else
          RAISE EXCEPTION 'Quarto indisponivel para reserva!'; 
        end if;
      else
        RAISE EXCEPTION 'Cliente nao consta no cadastro!';      
      end if;    
    end;
  $
  LANGUAGE plpgsql SECURITY DEFINER;

Listagem 17. Comando de criaç�o da funç�orealizaPedidos

CREATE OR REPLACE FUNCTION realizaPedido(hosp int, serv int) RETURNS void AS
  $
    begin
      perform * from hospedagem where upper(status) = 'A' and id_hospedagem = hosp;
      if found then
        perform * from servico where id_servico = serv;
        if found then
          insert into atendimento values (default, serv, hosp);
          RAISE NOTICE 'Pedido realizado com sucesso!';
        else
          RAISE EXCEPTION 'Servico indisponivel!'; 
        end if;
      else
        RAISE EXCEPTION 'Hospedagem nao consta no cadastro ou ja foi desativada!';     
      end if;  
    end;
  $
  LANGUAGE plpgsql SECURITY DEFINER;

N�o � interessante que alguns usu�rios acessem todas as informaç�es sobre os clientes, como, por exemplo, o RG e o telefone. Por esse motivo, foi criada uma vis�o que permite apenas visualizar apenas o nome e o sexo dos clientes (veja Listagem 18).

Listagem 18. Criaç�o da vis�o para consultar o nome e o sexo dos clientes

CREATE VIEW listaClientes (nome_cliente,sexo) AS
  SELECT nome, sexo FROM cliente

Tendo definido os tr�s tipos de usu�rios que ter�o acesso aos objetos do banco de dados (veja Tabela 5), agora criaremos cada um (veja Listagem 19) e, mais adiante, daremos as permiss�es que ambos t�m direito.

Listagem 19. Criaç�o dos pap�is (roles) gerente, atendente e estagi�rio

CREATE ROLE gerente;
  CREATE ROLE atendente;
  CREATE ROLE estagiario;

Depois de criarmos todos os pap�is que ir�o agrupar todos os usu�rios com perfis semelhantes, temos que dar as permiss�es para cada tipo, de acordo com o que foi definido anteriormente.

Antes de dar as permiss�es sobre as funç�es � importante explicar a opç�o SECURITY DEFINER usada na criaç�o das tr�s funç�es. Esta opç�o serve para permitir que todos os usu�rios acessem essas funç�es com a mesma permiss�o de quem as criou. Isto � muito importante, pois os usu�rios dos grupos atendente e estagi�rio n�o t�m acesso �s tabelas do banco de dados hotel, ent�o n�o poderiam execut�-las. Por exemplo, os grupos atendente e estagi�rio n�o executariam a funç�oadicionaHospedagem,porque na sua definiç�o existem consultas que buscam valores que est�o em algumas tabelas como clientes e quartos e esses grupos de usu�rios n�o t�m permiss�o para acessa-las.

Para resolver este problema, foram criadas todas as funç�es com a opç�o SECURITY DEFINER, e antes de dar a permiss�o de execuç�o para os grupos gerente, atendente e estagi�rio, deve-se revogar o direito de qualquer usu�rio do banco executar tais funç�es.

Os comandos que revogam o execuç�o funç�esadicionaHospedagem, adicionaReserva e realizaPedidopelos usu�rios, est�o exibidos na Listagem 20.

Listagem 20. Revogando a execuç�o das tr�s funç�es para todos os usu�rios

REVOKE ALL ON FUNCTION adicionaReserva(numeric,int,int,date) FROM PUBLIC;
  REVOKE ALL ON FUNCTION adicionaHospedagem(numeric,int) FROM PUBLIC;
  REVOKE ALL ON FUNCTION realizaPedido(int,int) FROM PUBLIC;

Feito isso, podemos agora dar as permiss�es de acesso para cada um dos grupos de usu�rios. O papel gerente poder� modificar todos os registros de todas as tabelas (veja Listagem 21), al�m de acessar as funç�es adicionaReserva (veja Listagem 22), adicionaHospedagem (veja Listagem 23) e listaClientes (veja Listagem 24) e a vis�o listaClientes (veja Listagem 25).

Listagem 21. Concedendo permiss�o para o role gerente acessar todas as tabelas e conceder permiss�es para outros usu�rios

GRANT SELECT, INSERT ON cliente, reserva, hospedagem, quarto, tipo_quarto, atendimento, servico, listaClientes TO gerente WITH GRANT OPTION;

Listagem 22. Concedendo permiss�o para o role gerente para acessar a funç�o adicionaHospedagem

GRANT EXECUTE ON FUNCTION adicionaHospedagem(numeric,int) TO gerente;

Listagem 23. Concedendo permiss�o para o role gerente para acessar a funç�o adicionaReserva

GRANT EXECUTE ON FUNCTION adicionaReserva(numeric,int,int,date) TO gerente;

Listagem 24. Concedendo permiss�o para o role gerente para acessar a funç�o realizarPedido

GRANT EXECUTE ON FUNCTION realizaPedido(int,int) TO gerente;

Listagem 25. Concedendo permiss�o para o role gerente para acessar a view listaClientes

GRANT SELECT ON listaClientes TO gerente

O grupo de usu�rios atendente n�o pode ter acesso a nenhuma tabela. Ele pode apenas acessar as funç�es adicionaHospedagem, adicionaReserva e realizaPedidos.As permiss�es para acessar tais funç�es est�o dispon�veis nas Listagens 26, 27 e 28, respectivamente.

Listagem 26. Concedendo permiss�o para o role atendente para acessar a funç�o adicionaHospedagem

GRANT EXECUTE ON FUNCTION adicionaHospedagem(numeric,int) TO atendente;

Listagem 27. Concedendo permiss�o para o role atendente para acessar a funç�o adicionaReserva

GRANT EXECUTE ON FUNCTION adicionaReserva(numeric,int,int,date) TO atendente;

Listagem 28. Concedendo permiss�o para o role atendente para acessar a funç�o realizaPedidos

GRANT EXECUTE ON FUNCTION
  realizaPedido(int,int) TO atendente;

A Listagem 29 d� a permiss�o para o grupo de usu�rios do tipo estagi�rio acessar a vis�olistaClientes.

Listagem 29. Concedendo permiss�o para o role estagi�rio acessar a vis�olistaCliente

GRANT SELECT ON listaClientes TO estagiario;

Com o banco de dados, as funç�es, a vis�o e os usu�rios criados, j� podemos realizar testes para verificar se o que foi permitido para cada tipo de usu�rio est� de acordo com cada um dos seus perfis.

Simulando Testes para Validar as Permiss�es

Com todos os pap�is criados, iremos realizar alguns testes para validar as permiss�es que foram concedidas para cada um dos pap�is.

Em um primeiro momento, ser�o criados tr�s usu�rios pertencentes a cada um dos pap�is criados (gerente, atendente e estagi�rio). A Listagem 30 exibe a criaç�o do um usu�rio chamadotonye que pertencer� ao grupo de usu�rios gerente. O par�metro �LOGIN� permite que o usu�rio possa logar no sistema e o par�metro �PASSWORD� atribui a senha �111� para o mesmo.

Listagem 30. Criaç�o de usu�rio com papel de gerente

CREATE ROLE tony LOGIN PASSWORD '111' IN ROLE gerente;

Por sua vez, a Listagem 31 cria o usu�riomariacom direito de logar no sistema, mas agora pertencendo ao grupo de atendentes.

Listagem 31. Criaç�o de usu�rio com papel de atendente

CREATE ROLE maria LOGIN PASSWORD '222' IN ROLE atendente;

O �ltimo usu�rio criado � avitoria.Ela pertencer� ao grupo de estagi�rios e sua criaç�o est� mostrada na Listagem 32.

Listagem 32. Criaç�o de usu�rio com papel de atendente

CREATE ROLE vitoria LOGIN PASSWORD '333' IN ROLE estagiario;

Com o usu�riotonylogado, pode-se realizar todas as operaç�es com as tabelas, exceto operaç�es pr�prias dos donos dos objetos, como por exemplo, remover uma tabela (DROP TABLE).

Quando este usu�rio tenta executar o comando de inclus�o de um novo cliente, conforme a Figura 2, o sistema executa o comando normalmente.

Figura 2. Usu�rio tony incluindo um novo cliente.

Se usu�rio tamb�m tentar realizar qualquer operaç�o (SELECT, DELETE, UPDATE, INSERT) com qualquer uma das tabelas, o sistema executar� o comando normalmente. Por�m, o mesmo n�o ocorrer�, caso ele tente remover uma tabela do banco de dados (veja Figura 3).

Figura 3. Usu�rio tony tentando apagar do banco de dados a tabela tipo_quarto

Agora com o usu�rio maria logado, tenta-se utilizar a funç�o adicionaReserva, conforme Figura 4.

Figura 4. Usu�rio maria acessando a funç�oadicionaReserva

O mesmo n�o aconteceria se esse usu�rio tentasse acessar diretamente a tabela reserva, porque o seu perfil n�o tem permiss�o para isto (veja Figura 5).

Figura 5. Usu�rio maria tentando selecionar os registros da tabela reserva

Por �ltimo, com o usu�rio vitoria, tenta-se consultar a vis�olistaClientes(veja Figura 6).

Figura 6. Usu�rio vit�ria acessando a vis�olistaClientes

Verifique que vitoria n�o teve problema porque o seu perfil tem permiss�o para consultar somente a vis�olistaClientes. O mesmo n�o ocorreria caso ele tentasse consultar diretamente a tabela cliente (veja Figura 7).

Figura 7. Usu�rio vit�ria tentando consultar diretamente a tabela cliente.

Conclus�o

Segurança � importante em todos os n�veis, principalmente a n�vel de informaç�o. Hoje o maior patrim�nio de uma empresa s�o seus dados e por isso � t�o importante preserva-los �ntegros.

Com o que foi visto neste artigo, percebe que podemos controlar de forma bastante eficaz os nossos dados, dando permiss�o de acesso �queles que possuem determinadas caracter�sticas e dar acesso diferenciado para outros.

O PostgreSQL � um SGBD bastante utilizado e como foi visto, � uma excelente opç�o por v�rios motivos, al�m de permitir meios eficazes de controle de acesso aos dados.

Refer�ncias

GONZAGA, J. L. Dominando o PostgreSQL. Rio d Janeiro: Editora Ci�ncia Moderna Ltda., 2007. p. 187-204.

ELMASRI, Ramez E.; NAVATHE, Shamkant. Sistemas de Banco de Dados. 4 ed. S�o Paulo: Pearson Addsison Wesley, 2005. p. 544-552.

DAMAS, Luis. SQL � Structered Query Language. 6 ed. S�o Paulo: LTC, 2007. 396p.

http://pgdocptbr.sourceforge.net/pg80/sql-grant.html

http://pgdocptbr.sourceforge.net/pg80/user-manag.html

http://pgdocptbr.sourceforge.net/pg80/sql-revoke.html

Gerenciando Usu�rios e Permiss�es no PostgreSQL

O artigo aborda os conceitos relacionais com gerenciamento de usuários e permissões de acesso em SGBD�s, focando o PostgreSQL. Neste artigo, foi implementado um estudo de caso para demonstrar na prática as funções do PostgreSQL no que se refere ao gerenciamento de usuários.

Atenç�o: esse artigo tem uma palestra complementar. Clique e assista!

Artigos relacionados