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