Thread: How to find out about user rights

Hi all:

I have just started to work with SQL, so maybe the answer to my question is really simple - but I can't find the answer
toit in my books or in Google.  

Is it possible to get a list of all tables within a database for which a certain user has rights? Of course, I could
lookthrough the table list in pgAdmin - but maybe there is something easier?  

Or - another question - how could you possibly copy the rights of a certain user and assign them to another user?

Maybe some background information is helpful.  I have two users working with the same topic - one has update, insert,
delete-rightsfor several tables, the other has only select-rights. But there is no complete list. So I either have to
findout all the tables for which the first user has update, insert, delete-rights (and then grant those rights to the
seconduser) or I have to copy all the rights of the first user and assign them to the second. But I couldn't find
informationon how to do this. Can you help me?  

Thanks in advance.

Annegret

---
Annegret Sauer
Universität Hohenheim
Kommunikations-, Informations- und Medienzentrum (630)
IT-Dienste | Verwaltungssysteme

Schloss Westhof-Ost | 70599 Stuttgart
Tel.:  +49 711 459-23381 | Fax: +49 711 459-24224
Email: sauer@verwaltung.uni-hohenheim.de
https://kim.uni-hohenheim.de

Sauer Annegret <sauer@Verwaltung.uni-hohenheim.de> writes:
> Is it possible to get a list of all tables within a database for which a certain user has rights? Of course, I could
lookthrough the table list in pgAdmin - but maybe there is something easier?  

You could look into the information_schema views, or you could write a
query using the has_table_privilege() function.

> Or - another question - how could you possibly copy the rights of a certain user and assign them to another user?

There's no mechanism for doing that.  Usually the recommended solution
for "sharing" rights is to assign the rights to a role created for the
purpose, and then to grant use of that role to the individual users.
However this approach requires a bit of foresight ...

            regards, tom lane

Hi Tom,

> You could look into the information_schema views, or you could write a query using the has_table_privilege()
function.
Could you give an example for such a query? So far I have never heard of this function.

>> Or - another question - how could you possibly copy the rights of a certain user and assign them to another user?

> There's no mechanism for doing that.  Usually the recommended solution for "sharing" rights is to assign the rights
toa role created for the  
> purpose, and then to grant use of that role to the individual users.
> However this approach requires a bit of foresight ...
... which my predecessors didn't have...

I think I might try the has_table_privilege() function. Thanks!

Annegret

Hello

On Thu, 8 Nov 2012 10:59:49 +0000
Sauer Annegret <sauer@Verwaltung.uni-hohenheim.de> wrote:

> Hi all:
>
> I have just started to work with SQL, so maybe the answer to my question is really simple - but I can't find the
answerto it in my books or in Google.  
>
> Is it possible to get a list of all tables within a database for which a certain user has rights? Of course, I could
lookthrough the table list in pgAdmin - but maybe there is something easier?  

I've asked the same question a few month ago and wrote a little VIEW across
some pg_catalog tables as PostgreSQL does not provide something suitable:
  http://archives.postgresql.org/pgsql-novice/2012-07/msg00014.php


> Or - another question - how could you possibly copy the rights of a certain user and assign them to another user?

No idea.

bye,

-christian-