Managing permissions for multiple users to Create and Drop tables
We have one database with several schemas. We have several groups of developers that
have the need to be able to collaborate including creating and dropping tables.
I noticed it became difficult to manage because when one
developer creates a table, he is now the owner.
All the other developers need to be given permissions to select from (or
drop) those tables he created. I was
looking for a way to where all members of a group can select from everybody’s
tables, drop them if needed, etc. Where
they can all work collaboratively.
I found this site that had a suggestion: https://blog.hagander.net/setting-owner-at-create-table-237/
You basically create a “common_role” as the table owner and grant all the users
access to tables owned by common_role. However,
for this to work automatically, you need to create an event trigger so that
each time a developer creates a table, it alters the table owner after the
table is created.
I was wonder how other DBA’s handle this permission issue
for collaborative environments? Any best
practices or advise?