Question:
What is the correct procedure for allowing team members to develop applications and create tables in only a specific schema?
I am running Oracle 12C. If I create a schema – say MYAPP1 – how should I grant permissions to Tom, Dick & Harry only to create tables in that schema?
If I do:
grant create any table to user tom
would that automatically grant him the right to create tables in any other schema? Furthermore, how would he get select, insert, update, delete rights to the table that he created?
Can this be done using roles so I can dynamically add / remove users to roles and not need to grant permissions every time the team changes?
Answer:
You can create a ROLE for your developers and after they log in, they can alter session to this user who will be the owner of the development objects, for example:
1 – Your developers will log in to the DB.
2 – Run the command: alter session set current_schema=DEVELOPER_USER;
3 – From now on, every object created will be owned by DEVELOPER_USER.
4 – Regarding permissions, you can create a trigger or job that grants permissions when needed.
It is better to grant create any table, as this allows them to create tables for ANY user, which can be a big problem.