0

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?

Annon
  • 123
  • 1
  • 9
  • Can you clarify what you mean by "alter" and "drop" in this context? `alter` and `drop` are DDL commands that let you modify the structure of a table or remove the table from the database. Those are not object-level permissions and do not relate to rows in the table. Do you really mean "select, insert, update, and delete rows in a table"? – Justin Cave May 08 '20 at 23:45
  • Different database products have different ways they organize things. In Oracle, each user owns a "schema" (all the tables, indexes, procedures, etc. they created themselves). A user is generally (but not always!) granted extensive privileges to their own schema, and very few privileges to other people's schemas. **Where** do you want "all" users to be able to edit and view tables? Each in their own schema, or do you want to create a "general" schema for "general" objects, and give users privileges on objects in that schema only? –  May 09 '20 at 00:11
  • But if i offer a resource user role to a user, wouldn't that allow the user to alter all the tables on the database? – Annon May 09 '20 at 00:14
  • Allowing a user to alter all the tables is a huge security risk. Why in the world would you even _think_ of allowing that to anyone but a DBA? – EdStevens May 09 '20 at 01:53
  • To be honest, the question was more of a 'can I' - like would it even make a different kind of question (or even should I). I just found it strange why there are roles and table privileges, so if I offer a resource user role to a user, wouldn't that allow the user to alter all the tables on the database, then what's the point of even having table-level privileges? – Annon May 09 '20 at 02:02
  • @JustinCave: I fell for the same confustion about alter and drop, but that is the description of the resource role in the 2d DBA guide: "Enables a user to create, modify, and delete certain types of schema objects in the schema associated with that user" – wolφi May 09 '20 at 11:50
  • @wolφi - Are you asking about the privileges associated with the predefined role `resource` (which should not be used in a real system)? Because that does contain a variety of system privileges which allow you to do things like create and drop tables in your own schema. It does not grant object-level privileges that would allow you to insert or delete rows in someone else's table. – Justin Cave May 09 '20 at 11:53
  • @JustinCave: no, I think it's a documentation bug. The word `DELETE` does not make sense in the description of the role `RESOURCE` in the [2 day DBA guide](https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/administering-user-accounts-and-security.html#GUID-289A4BF6-F703-4ED5-8357-89F651A6D1DE) – wolφi May 09 '20 at 12:02

1 Answers1

1

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.

wolφi
  • 8,091
  • 2
  • 35
  • 64