[SOLVED] If I give a user table privileges do i need to give them a database role as well

If I give a user table privileges do i need to give them a database role as well

I have a database and want to give out roles and privileges.

My aim is to allow multiple users - all have the same privileges - to be able to create, edit and view the tables (That's it).

I understand there are user table privileges that allow a user to Create, alter, drop and delete items in tables. But I also understand that there is a resource user pre-defined role that allows a user to do the same thing.

Would I offer both of the roles? The table-level privileges and predefined roles? What would happen if I do offer both? Can I?

Solution

That is more a database administration question, as you have users that develop their own tables. And the heart of your question, "one size fits all prepacked" role RESOURCE or better a bespoke solution for your set of users is really one of administrative style, taste, and your special case.

Personally, I don't like the role RESOURCE as it lacks the privilege CREATE VIEW.

Please note that the privilege CREATE TABLE (granted directly or via a role) allows the user to create tables in his/her own schema. To create tables in other schemas (or "all the tables on the database"), you need the privilege CREATE ANY TABLE. See documentation for details.