...
------------------------------------------
Hi @Sharon MarkusWanted to reach out to you regarding SI#3411429: Request for additional user role on Metadb Test - CORNELL UNIV
We UNIVWe have added 2 roles for schema local_shared -- following same guidelines as noted -- https://confluence.cornell.edu/display/folioreporting/Creating+Additional+Metadb+Schemas
Read only role is ro_local_shared
Read Write Role is rw_local shared
Roles have been defined and granted as in the SQL at end of message.
Please note (similar to what is noted in Johns slack conversation) - you will need to explicitly grant privs in order for other users to use newly added tables.
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;`
...
-- Read-only role CREATE ROLE ro_local_shared INHERIT; GRANT USAGE ON SCHEMA local_shared TO ro_local_shared; GRANT SELECT ON ALL TABLES IN SCHEMA local_shared TO ro_local_shared; ALTER DEFAULT PRIVILEGES IN SCHEMA local_shared GRANT SELECT ON TABLES TO ro_local_shared;
-- Read/write role CREATE ROLE rw_local_shared INHERIT; GRANT USAGE, CREATE ON SCHEMA local_shared TO rw_local_shared; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA local_shared TO rw_local_shared; ALTER DEFAULT PRIVILEGES IN SCHEMA local_shared GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_local_shared; GRANT USAGE ON ALL SEQUENCES IN SCHEMA local_shared TO rw_local_shared; ALTER DEFAULT PRIVILEGES IN SCHEMA local_shared GRANT USAGE ON SEQUENCES TO rw_local_shared;
-- Grant r/w privileges to users GRANT rw_local_shared TO z_slm5; GRANT rw_local_shared TO z_ama8; GRANT rw_local_shared TO z_jl41; GRANT rw_local_shared TO z_lm15; GRANT rw_local_shared TO z_jmp8; GRANT rw_local_shared TO z_np55; GRANT rw_local_shared TO z_vp25; -- Grant r/o privileges to users GRANT ro_local_shared TO z_ab18; GRANT ro_local_shared TO z_alc28; GRANT ro_local_shared TO z_dlh19; GRANT ro_local_shared TO z_fbw4; GRANT ro_local_shared TO z_jrc88; GRANT ro_local_shared TO z_map6; GRANT ro_local_shared TO z_mb327; GRANT ro_local_shared TO z_pd36; GRANT ro_local_shared TO z_pm66; GRANT ro_local_shared TO z_tit1;
Please let us know how testing goes and if any adjustments are needed