(From John Malconian, Index Data)
We have small groups of users who will need access to read and write to their own functional area local schemas.
If these users have database accounts that were created via Metadb, then they will automatically have their own local schema to read and write to. The schema name will be the same as name of user/role.
We also need to create a schema for our legacy system data to which users only have read access
This is outside the scope of Metadb, but can, of course, co-exist in the same database. In this case you could create a didicated role with ‘inherit’ which would function more or less as a group, create the schema, grant read-only privs to the role on the schema, and then add users to the role. Example:CREATE my_ro_schema_role INHERIT;
CREATE SCHEMA my_ro_schema;
GRANT USAGE ON SCHEMA my_ro_schema TO my_ro_schema_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_ro_schema to my_ro_schema_role;
GRANT my_ro_schema_role to $USER1;
GRANT my_ro_schema_role to $USER2;
etc.
In addition, we need to create a schema that only our small reporting team can write to, but allows for read access for all users.
Similar to above, but, in addition, create a role for read-write:CREATE my_rw_schema_role INHERIT;
CREATE SCHEMA my_rw_schema;
GRANT USAGE ON SCHEMA my_rw_schema TO my_rw_schema_role;
GRANT USAGE ON SCHEMA my_rw_schema TO my_ro_schema_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_rw_schema TO my_rw_schema_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_rw_schema TO my_ro_schema_role;;
GRANT CREATE ON SCHEMA my_rw_schema TO my_rw_schema_role;
GRANT ALL ON ALL TABLES IN SCHEMA my_rw_schema TO my_rw_schema_role;GRANT my_rw_schema_role to $USER1;
GRANT my_rw_schema_role to $USER2;
etc.Note that when a user with rw privs creates a table in the rw schema, they will need to explicitly grant privs on their table in order for others to use it. For example,GRANT SELECT ON my_rw_schema.mytable TO my_ro_schema_role;GRANT SELECT, INSERT, UPDATE, DELETE ON my_rw_schema.mytable TO my_rw_schema_role;Nassib might have other suggestions, but that’s how I’ve done it.