Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

Has function privilege automation

Posted by Diego em Maio 6, 2014


Follows a couple of useful functions to test if a user has a certain privilege on a function\schema\table:

 

select has_function_privilege(‘user’, schema.function()’,‘execute’)

 

select has_schema_privilege(‘user’,‘schema’,‘create’)

 

select has_table_privilege(‘user’, ‘schema.table’, ‘select’)

 

The other day I had to look for any table on pg_catalog that a certain user did not have access (there are 268)

So I wrote the following script:

 

 

select ‘select ”’ || relname || ”’ as table_name, has_table_privilege(”<my_schema>”, ”’|| relname ||”’, ”select”) as permission union all –‘||relname

from pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace

where relkind = ‘r’ and nspname in (‘pg_catalog’)

 

that outputs  all the “has_table_privilege” functions on the following format:

 

select ‘pg_shadow’ as table_name, has_table_privilege(‘dim’, ‘pg_shadow’, ‘select’) as permission union all –pg_shadow

select ‘stl_save’ as table_name, has_table_privilege(‘dim’, ‘stl_save’, ‘select’) as permission union all –stl_save

select ‘stl_scan’ as table_name, has_table_privilege(‘dim’, ‘stl_scan’, ‘select’) as permission union all –stl_scan

and then I removed the last “union all “ and wrapped everything on a query like this

 

 

 

 

select *

from (

) X

where permission = false

then voilà:

 

table_name

permission

pg_shadow

False

pg_statistic

False

 

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: