User Management - DBA's only Please

Isacc is correct, use public synonyms.  That is the way for any/every user in an Oracle database to refer to the same table in your "MAIN" schema with having to specify the schema name.

For example, if your MIAN schema has a table named: customers,
The syntax is simply:
create public synonym customers for main.customers;

Then any user in the database can refer to that table simply as: customers.  Note that synonyms (whether public or private) do not replace or supercede grants.  Each user will still have to granted the rights they need on that table by MAIN, either directly or via roles.

Althought Public synonyms is one methods, there are organization that do not allow for this.

You can Dynamically generate grants and synonyms for the users.  Have a sql scripts as follows

spool &1_grants.sql
Select 'Grant select on MAIN.'||table_name ||' for &1 ; '
   from DBA_tables
   where owner = 'MAIN'
/

Select 'Grant select on MAIN.'||view_name ||' for &1 ; '
   from DBA_views
   where owner = 'MAIN'
/

spool off
spool &1_synonyms.sql
select 'Create synonym &1..'||table_name||' for MAIN.'||table_name||'; '
  from dba_tab_privs
  where grantor = 'MAIN'
      and grantee = '&1'
/

select 'exit' from dual;
spool off
@&1_grants.sql
@&1_synonyms.sql

You would run as follows for each user

sqlplus "sys/SYS_PASSWORD@YourSid as sysdba" @gen-grants-syns USER1

So just to clarify -

My thoughts on how to do this is correct then?  Create user, grant role, create public (or user) synonyms.

Is this how most companies do this type of thing?  We are migrating from a old system with text file tables, and terminal sessions to an Oracle solution, and I got thrown into an assistant DBA role.  So obviously I am not very confident in my grasp of the situation.

You want to use the MAIN schema only (for objects)  and do not want to fully specify owner.table.

It's a very standard way of doing things.

For more security, you can create private synonyms (for every users) for every objects owned by MAIN.
You can grant proper privileges on objects to roles (or directly to users if using some features that needs direct acces to the objects), I would suggest a role to start with, and decide afterwards for the exceptions.

Another way to put more security or restrict data access, is to create a view (same name as the table) in the user schema.
This can be done instead of creating private synonym (because at this point , you will have to specify the owner name in the select part of the create view).
The user will be able to select data using the view name (which will be the same as the table view), so nothing has to be modified in the apps coding. Also, with this method you can restrict data access managed by the where clause in the select.

Check also , when creating procedures if you need to use the AUTHID CURRENT_USER clause, this is used to get info from the user using the procedures instead of the user owning the procedure.

Only the DBA or Apps Owner should have the password on schema MAIN, to alter table and so on. Nobody else.

Good luck...

Like you said it would depend on the the level of security you need, must have.

You could created Roles based on privileges needed.  Ex.

All_Privs_Role - could be a role that has Select, Update, Delete, Insert
Execute_Role
Select_Role
Update_Role
Sel_Upd_Role
etc...

Then grant the role to the users and create private synonyms based on the objects the user has access to
Another script that I have that dynamically generates the synonyms based on ROLE is

spool &1_synonyms.sql
select distinct 'create or replace synonym &1..'||table_name ||' for '||owner||'.'||table_name||' ;'
  from dba_roles a, dba_tab_privs b
  where a.role = b.grantee
    and a.role in ( select granted_role
                      from dba_users u, dba_role_privs r
                      where u.username = r.grantee
                        and u.username = '&1' )
/
select 'exit' from dual;
spool off;

@&1_synonyms.sql

In an ideal world you could create synonyms that applied to a role.  I have not found a way of doing that !

The danger of creating public synonyms is that the default is the production database.

Quite often you want to train staff on a non production system.  Thus you must create local synoyms for each user that is using the non production database.
Problem is if you omit to do that the trainee could be experimenting with the production system!

Safe way is to not use public synonyms and have scripts that create the required private synonyms

After creating synonyms (public. private) you have to grant rights for operations on the tables named by synonyms to the user that will use the synonyms.

After creating synonyms (public. private) you can grant rights for operations on the tables, views & stored functions named by synonyms to the ROLEs that will use the synonyms.