Permissão nas Tabelas via DBSCHEMA - Informix

Permissão nas Tabelas via DBSCHEMA - Informix

  junho 10, 2014    Claudemar Martins de Sá

---

Esta semana estava rodando um dbschema e vi que usando ele dá para retirar algumas informações bem interessantes do banco, como por exemplo (*) ver as permissões de um usuário em um banco (*) ver as permissões de um usuário mais com revoke para retirar essas permissões dele (*) ver guais são os usuários que tem acesso a uma tabela do banco, o Informix uma ferramenta disponível chamada dup-auth que faz as mesmas coisas, mais resolvi brincar um pouco o dbschema.

Download do Script Shell Completo - Clique Aqui

Checando os Grants de um Usuário

Vamos ver os acessos que um usuário tem no banco - para fazer isso vamos acessar a opção 1 (enter)

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:00:54
-------------------------------------------------------------------------------------------------
                                            c099 - informix

        Code  Function                                    
        ----  ------------------------------------------- 
         1    Relatorio de Grants de Usuario              
         2    Relatorio de Revoke de Usuario              
         3    Relatorio de Tabela por Usuarios            
         0    Sair da Aplicacao                           
        ----  ------------------------------------------- 

     Digite um Codigo - 1

Vamos digitar o nome do usuário e o nome do banco;

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:01:28
-------------------------------------------------------------------------------------------------


Digite o USERNAME do USUARIO ........:  csa

Digite o NOME DO BANDO DE DADOS .....:  aa01055

Feito, tenho as permissões do usuário csa no banco aa01055

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:01:56
-------------------------------------------------------------------------------------------------

grant connect to "csa";
grant select on "dba".cae_grau_resp to "csa" as "dba";
grant select on "dba".cae_grupo_iplan to "csa" as "dba";
grant select on "dba".cae_his to "csa" as "dba";
grant select on "dba".cae_setor to "csa" as "dba";
grant select on "dba".cae_subgrupo to "csa" as "dba";
grant select on "dba".cfop to "csa" as "dba";
grant select on "dba".coordenadoria to "csa" as "dba";
grant select on "dba".gnre_produto to "csa" as "dba";
grant select on "dba".motivo_atualizacao to "csa" as "dba";
grant select on "dba".tipo_movimento to "csa" as "dba";
grant select on "dba".tributos to "csa" as "dba";
grant select on "dba".tributos_grupo to "csa" as "dba";
grant select on "dba".uf to "csa" as "dba";
grant select on "dba".uferms to "csa" as "dba";
grant select on "dba".ufir to "csa" as "dba";
grant select on "dba".ods_parametros to "csa" as "dba";
grant select on "dba".cae to "csa" as "dba";
grant select on "dba".agenfa to "csa" as "dba";
grant select on "dba".ods_situacao_carga to "csa" as "dba";
grant select on "dba".cfop_grupo to "csa" as "dba";
grant select on "dba".cnae to "csa" as "dba";
grant select on "dba".cae_cotepe_grupo to "csa" as "dba";
grant select on "dba".cae_cotepe_subgrupo to "csa" as "dba";
grant select on "dba".cae_iagro_grupo to "csa" as "dba";
grant select on "dba".orgao_arrecadador to "csa" as "dba";
grant select on "dba".pnd_tipo to "csa" as "dba";
grant select on "dba".trb_local_nf to "csa" as "dba";
grant select on "dba".trb_local_nf_detalhe to "csa" as "dba";
grant select on "dba".trb_tabela to "csa" as "dba";
grant select on "dba".pnd_mvto_ppd_simples_nacional to "csa" as "dba";
grant select on "dba".dap_anos_revalidados to "csa" as "dba";
grant select on "dba".dap_anos_revalidados_his to "csa" as "dba";
--- corte ----

Pressione qualquer tecla para continuar...

Checando os Revoke de um Usuário

Para ter os revokes para futuramente rodar e tirar as permissões de um usuário em um banco - para fazer isso uso a opção 2 (enter)

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:02:34
-------------------------------------------------------------------------------------------------
                                            c099 - informix

        Code  Function                                    
        ----  ------------------------------------------- 
         1    Relatorio de Grants de Usuario              
         2    Relatorio de Revoke de Usuario              
         3    Relatorio de Tabela por Usuarios            
         0    Sair da Aplicacao                           
        ----  ------------------------------------------- 

     Digite um Codigo - 2

Vamos digitar o nome do usuário e o nome do banco;

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:02:52
-------------------------------------------------------------------------------------------------


Digite o USERNAME do USUARIO ........:  csa

Digite o NOME DO BANDO DE DADOS .....:  aa01055

Feito, relatorio de todas as tabelas do usuário no banco de dados - mais em revoke;

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:03:14
-------------------------------------------------------------------------------------------------


revoke connect from "csa";
revoke select on "dba".cae_grau_resp from "csa" as "dba";
revoke select on "dba".cae_grupo_iplan from "csa" as "dba";
revoke select on "dba".cae_his from "csa" as "dba";
revoke select on "dba".cae_setor from "csa" as "dba";
revoke select on "dba".cae_subgrupo from "csa" as "dba";
revoke select on "dba".cfop from "csa" as "dba";
revoke select on "dba".coordenadoria from "csa" as "dba";
revoke select on "dba".gnre_produto from "csa" as "dba";
revoke select on "dba".motivo_atualizacao from "csa" as "dba";
revoke select on "dba".tipo_movimento from "csa" as "dba";
revoke select on "dba".tributos from "csa" as "dba";
revoke select on "dba".tributos_grupo from "csa" as "dba";
revoke select on "dba".uf from "csa" as "dba";
revoke select on "dba".uferms from "csa" as "dba";
revoke select on "dba".ufir from "csa" as "dba";
revoke select on "dba".ods_parametros from "csa" as "dba";
revoke select on "dba".cae from "csa" as "dba";
revoke select on "dba".agenfa from "csa" as "dba";
revoke select on "dba".ods_situacao_carga from "csa" as "dba";
revoke select on "dba".cfop_grupo from "csa" as "dba";
revoke select on "dba".cnae from "csa" as "dba";
revoke select on "dba".cae_cotepe_grupo from "csa" as "dba";
revoke select on "dba".cae_cotepe_subgrupo from "csa" as "dba";
revoke select on "dba".cae_iagro_grupo from "csa" as "dba";
revoke select on "dba".orgao_arrecadador from "csa" as "dba";
revoke select on "dba".pnd_tipo from "csa" as "dba";
revoke select on "dba".trb_local_nf from "csa" as "dba";
revoke select on "dba".trb_local_nf_detalhe from "csa" as "dba";
revoke select on "dba".trb_tabela from "csa" as "dba";
revoke select on "dba".pnd_mvto_ppd_simples_nacional from "csa" as "dba";
revoke select on "dba".dap_anos_revalidados from "csa" as "dba";
--- corte ----

Checando quem tem acesso em uma tabela

Vamos ver quem tem acesso em uma tabela - para fazer isso vamos acessar a opção 3 (enter)

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:03:53
-------------------------------------------------------------------------------------------------
                                            c099 - informix

        Code  Function                                    
        ----  ------------------------------------------- 
         1    Relatorio de Grants de Usuario              
         2    Relatorio de Revoke de Usuario              
         3    Relatorio de Tabela por Usuarios            
         0    Sair da Aplicacao                           
        ----  ------------------------------------------- 

     Digite um Codigo - 3

Vamos digitar o nome da tabela e o nome do banco;

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:04:28
-------------------------------------------------------------------------------------------------


Digite o nome da TABELA .............:  cae_grau_resp 

Digite o NOME DO BANDO DE DADOS .....:  aa01055

Feito, temos as permissões dos usuários em uma tabela;

-------------------------------------------------------------------------------------------------
10/06/2014                      ***** INFORMIX BASIC SERVICES *****                      17:04:54
-------------------------------------------------------------------------------------------------

grant select on "dba".cae_grau_resp to "csa" as "dba";
grant select on "dba".cae_grau_resp to "ods" as "dba";
grant select on "dba".cae_grau_resp to "odsleitura" as "dba";
grant select on "dba".cae_grau_resp to "pbeolchi" as "dba";
grant select on "dba".cae_grau_resp to "pbonifacio" as "dba";
grant select on "dba".cae_grau_resp to "pdiniz" as "dba";
grant select on "dba".cae_grau_resp to "public" as "dba";
grant update on "dba".cae_grau_resp to "ugig" as "dba";
grant insert on "dba".cae_grau_resp to "ugig" as "dba";
grant delete on "dba".cae_grau_resp to "ugig" as "dba";
grant index on "dba".cae_grau_resp to "ugig" as "dba";
grant select on "dba".cae_grau_resp to "vloinaz" as "dba";

Pressione qualquer tecla para continuar...