Postgres Pro
Downloads
Home   >   mailing lists

Thread: grants

grants

From
Edgar Mares
Date:
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

thnx  for  your  time

Re: grants

From
Christopher Kings-Lynne
Date:
> 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

Re: grants

From
Andreas Pflug
Date:
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

Re: grants

From
Kris Jurka
Date:

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

Re: grants

From
Andreas Pflug
Date:
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

Re: grants

From
Kris Jurka
Date:

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