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?