what is the best internal structure for setting up users, and tables?
We are trying to set up a small development (3 developers) environment using Oracle. This is what we have done so far:
1.- Installed Oracle and created a database
2.- Created tablespace and users (3) for that database while signed on using SYS as SYSDBA
Our question is should one of these users have DBA privileges so he can create the tables for the database then grant priviliges to these tables to the other 2 developers? OR should we just continue signing on using SYS as SYSDBA and create the tables??
create another user, never use SYS or SYSTEM, you dont even need to gran DBA to the new user. As long as the new user is the owner of the tables he can grant the privileges on his tables to other users, you dont need DBA to create objects
After you create the new user simply do
grant connect, resource to newuser;
connect and resource are roles (group or set of privileges whatever you want to call them, DBA is also a role), mostly these two roles are more than enough, if the user requires more privilege you can grant the privileges individually
And make sure you create tablespaces before create the users and assign these new tablespaces to the new user or users so when they create the objects the new objects reside in these new tablespaces