Обсуждение: grant privileges across schemas

Hi,

I'm using a database that is splitted into a number of
schemas. In my local installation I'd like to have 3 users:
a dba (ALL privileges), a user with read-write
(INSERT/UPDATE/DELETE on all tables and views and SELECT on
all sequences) privileges and a user with read-only (SELECT
on all tables and views) privileges.

Then individual db users would be given the read-only or
read-write roles as appropriate.

Right now I'd like to have these set across all schemas
(later, for finer control we would turn to specific control
for individual schemas).

The problem is that I only seem to be able to set the privs
for the dba (ALL) since this is done at the database level:
GRANT ALL ON DATABASE mydb TO dba;

However the following doesn't work:
mydb=> GRANT SELECT ON DATABASE mydb TO readonly;
ERROR:  invalid privilege type SELECT for database
mydb=> GRANT SELECT ON SCHEMA myschema TO readonly;
ERROR:  invalid privilege type SELECT for schema
mydb=> GRANT SELECT ON TABLE myschema.* TO readonly;
ERROR:  syntax error at or near "TO" at character 34
LINE 1: GRANT SELECT ON TABLE myschema.* TO readonly;
                                         ^

The following works, but I'm not sure about the consequences
of granting USAGE to a schema, as the documentation is
not clear, IMO : "For schemas, allows access to objects
contained in the specified schema (assuming that the
objects' own privilege requirements are also met).
Essentially this allows the grantee to "look up" objects
within the schema."
mydb=> GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT

After doing this, how do I give SELECT privilege to this
user for all tables of this schema?

Thanks in advance,

Fernan

On Wed, Apr 19, 2006 at 09:29:50 -0300,
  Fernan Aguero <fernan@iib.unsam.edu.ar> wrote:
>
> The following works, but I'm not sure about the consequences
> of granting USAGE to a schema, as the documentation is
> not clear, IMO : "For schemas, allows access to objects
> contained in the specified schema (assuming that the
> objects' own privilege requirements are also met).
> Essentially this allows the grantee to "look up" objects
> within the schema."
> mydb=> GRANT USAGE ON SCHEMA myschema TO readonly;
> GRANT
>
> After doing this, how do I give SELECT privilege to this
> user for all tables of this schema?

Granting access to a schema allows use of that schema. It does not grant
access to objects contained in the schema. You will need to do a grant for
each object in addition to what you are already doing. You will probably want
to write a script or function to do it, as there isn't a built in command
to do grants to multiple objects.

+----[ Bruno Wolff III <bruno@wolff.to> (19.Apr.2006 14:39):
|
| On Wed, Apr 19, 2006 at 09:29:50 -0300,
|   Fernan Aguero <fernan@iib.unsam.edu.ar> wrote:
| >
| > The following works, but I'm not sure about the consequences
| > of granting USAGE to a schema, as the documentation is
| > not clear, IMO : "For schemas, allows access to objects
| > contained in the specified schema (assuming that the
| > objects' own privilege requirements are also met).
| > Essentially this allows the grantee to "look up" objects
| > within the schema."
| > mydb=> GRANT USAGE ON SCHEMA myschema TO readonly;
| > GRANT
| >
| > After doing this, how do I give SELECT privilege to this
| > user for all tables of this schema?
|
| Granting access to a schema allows use of that schema. It does not grant
| access to objects contained in the schema. You will need to do a grant for
| each object in addition to what you are already doing. You will probably want
| to write a script or function to do it, as there isn't a built in command
| to do grants to multiple objects.
|
+----]

Bruno,

thanks for your reply, yes I've found a couple of mentions
to 'write your own script/function' by searching Google.

I just hoped someone would have come across this before and
had a solution at hand :)


Fernan

On Thu, Apr 20, 2006 at 13:52:38 -0300,
  Fernan Aguero <fernan@iib.unsam.edu.ar> wrote:
> +----[ Bruno Wolff III <bruno@wolff.to> (19.Apr.2006 14:39):
> |
> |
> | Granting access to a schema allows use of that schema. It does not grant
> | access to objects contained in the schema. You will need to do a grant for
> | each object in addition to what you are already doing. You will probably want
> | to write a script or function to do it, as there isn't a built in command
> | to do grants to multiple objects.
> |
> +----]
>
> Bruno,
>
> thanks for your reply, yes I've found a couple of mentions
> to 'write your own script/function' by searching Google.
>
> I just hoped someone would have come across this before and
> had a solution at hand :)

I am pretty sure some sample ones have been posted to at least one of the lists
and should be in the archives. I wouldn't be surprised to hear that techdocs
had a sample there, if you can't find one in the archives.