grants

hi there i'm having troubles to find how to
GRANT SELECT ON all-tables-onmydb TO specificuser

There isn't any such command. You need to write a stored procedure to
do it for you in a loop.

Chris

Edgar Mares wrote:

hi there i'm having troubles to find how to
GRANT SELECT ON all-tables-onmydb TO specificuser

this is just to give the access to "specificuser" to query the
database and find troubles on it

pgAdmin II has a tool for that (Security wizard; pgAdmin III has it on
the todo-list)

Regards,
Andreas

On Wed, 10 Mar 2004, Andreas Pflug wrote:

Edgar Mares wrote:

hi there i'm having troubles to find how to
GRANT SELECT ON all-tables-onmydb TO specificuser

this is just to give the access to "specificuser" to query the
database and find troubles on it

pgAdmin II has a tool for that (Security wizard; pgAdmin III has it on
the todo-list)

The problem that cannot be solved with either this or a function that
loops and grants on each table is that it is not a permanent grant of what
the admin had in mind. If a new table is added or an existing table is
dropped and recreated, the grants must be done again. The real use of a
SELECT ANY TABLE permission is ignorance of schema updates.

Kris Jurka

Kris Jurka wrote:

On Wed, 10 Mar 2004, Andreas Pflug wrote:

Edgar Mares wrote:

hi there i'm having troubles to find how to
GRANT SELECT ON all-tables-onmydb TO specificuser

this is just to give the access to "specificuser" to query the
database and find troubles on it

pgAdmin II has a tool for that (Security wizard; pgAdmin III has it on
the todo-list)

The problem that cannot be solved with either this or a function that
loops and grants on each table is that it is not a permanent grant of what
the admin had in mind. If a new table is added or an existing table is
dropped and recreated, the grants must be done again. The real use of a
SELECT ANY TABLE permission is ignorance of schema updates.

Hm, does this exist in other DBMS?
As soon as roles are implemented, there might be a default role
('public') for this. Until then, using groups solves most of the
problems (well, you certainly still need to GRANT rights to your
preferred group).

Regards,
Andreas

On Wed, 10 Mar 2004, Andreas Pflug wrote:

Kris Jurka wrote:

On Wed, 10 Mar 2004, Andreas Pflug wrote:

The problem that cannot be solved with either this or a function that
loops and grants on each table is that it is not a permanent grant of what
the admin had in mind. If a new table is added or an existing table is
dropped and recreated, the grants must be done again. The real use of a
SELECT ANY TABLE permission is ignorance of schema updates.

Hm, does this exist in other DBMS?
As soon as roles are implemented, there might be a default role
('public') for this. Until then, using groups solves most of the
problems (well, you certainly still need to GRANT rights to your
preferred group).

Groups help, but only if you want to GRANT to more than one user, and you
still need to do it on after schema changes. I know this is implemented
in at least Oracle, SELECT ANY TABLE is in fact the permission
name used.

Kris Jurka