To give other users permission to see and use a table you have imported or created in a local schema in Metadb, you need to grant that user access to the new table. To do so, you can issue a command to alter the table permissions as a query in a DBeaver session connected to Metadb. Here is an example:


To grant the user z_slm5 access to the table vs_folio_physical_material_formats in the local_shared schema on Metadb, you would issue this command:


     GRANT SELECT ON local_shared.vs_folio_physical_material_formats TO z_slm5;



Re-issue the command for each user if there are multiple users to whom you need to grant access. Ask the user(s) to confirm seeing the table after you have granted permissions.


*Please note that if you replace or update the table, you will need to reset these permissions.


------------------------------------------


Carole Godfrey  6:53 PM

Hi @Sharon MarkusWanted to reach out to you regarding SI#3411429: Request for additional user role on Metadb Test - CORNELL UNIV

We 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;`


In your case - for example - if a new heartbeat table is added to local_shared shared:
GRANT SELECT ON local_shared.heartbeat TO ro_local_shared;
GRANT SELECT, INSERT, UPDATE, DELETE ON local_shared.heartbeat TO rw_local_shared;`

-- 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 (edited) 



  • No labels