Gere Automaticamente seus Comandos GRANT e REVOKE!

Os comandos GRANT e REVOKE concedem e retiram permissões de acesso dos usuários aos objetos do banco de dados relativas à inserção, exclusão e alteração de dados, entre outras possibilidades. Neste post, vamos gerar automaticamente comandos GRANT e REVOKE utilizando SQL. Este tipo de procedimento não é muito comum porque ambos os comandos apresentam sintaxes simples que permitem a concessão de acessos sem a necessidade de automação.

Para construir scripts para automatizar a concessão e revogação destes acessos, o primeiro passo é saber quais são os usuários cadastrados no servidor.

1. Quais são os usuários cadastrados?

Para conceder ou revogar privilégios aos usuários, é interessante saber quantos e quais usuários estão cadastrados no seu SGBD, e uma consulta a PG_USER .

select * from pg_user;

usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig

----------+----------+-------------+----------+-----------+----------+----------+-----------

postgres |       10 | t           | t        | t         | ******** |          |

gisuser  |    17141 | f           | f        | f         | ******** |          |

A próxima etapa é identificar as tabelas para as quais será concedido acesso.


2. Quais são as tabelas criadas no banco?



Uma consulta aos metadados de PG_TABLES retorna o nome das tabelas utilizadas. Observe que na consulta, selecionamos apenas as  tabelas do schema public, ignorando as tabelas de sistema.

pf=# select tablename from pg_tables where schemaname = 'public';
 tablename
-----------
 pfdet2011
 pf2011
 ns2011
 nsdet2011
 cliente
(5 registros)
3. Concedendo Acessos em Massa

Com o comando GRANT, posso conceder permissões de inclusão, alteração e exclusão nas tabelas do banco para um determinado usuário. Basta executar este select e utilizar o resultado da consulta como entrada para o postgresql:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tablename || ' TO 

postgres'  from pg_tables where schemaname = 'public';
                           ?column?                           
---------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres
(5 registros)

Uma pequena alteração no script faz o produto cartesiano entre tabelas e usuários, gerando todas as combinações:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' TO ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                             comando                             
------------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO hacker ;
(20 registros)
4. Revogando permissões de acesso


Com o comando REVOKE, as permissões  para todos os usuários podem ser revogadas instantaneamente:



pf=# select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' FROM ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                               comando                              
---------------------------------------------------------------------
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM hacker ;
(20 registros)

5. Considerações Práticas


Como já foi mencionado neste post, a concessão de acesssos com GRANT e REVOKE raramente demanda alguma automação. Sintaxes poderosas e simples resolvem o problema sem maiores problemas, geralmente sendo executadas diretamente pelo DBA:

GRANT ALL ON DATABASE postgres TO hacker;

REVOKE ALL ON DATABASE postgres FROM hacker;


Este post é mais um exercício do que um exemplo prático, mas pode ser útil em situações em que se deseje maior controle.

Consulte as especificações dos comandos GRANT e REVOKE para ver a grande diversidade de opções disponíveis!