Обсуждение: 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-