What is the best practice for connecting to different schemas?
681400Sep 10 2009 — edited Sep 10 2009Hi all,
We are porting an application from SQL Server to oracle and would like to know what the best practices are in oracle for user connections to an Oracle instance.
More or less the question could be put like this:
1) The equivalent of a SQL Server Database in Oracle is a Schema. (more or less)
2) A specific application has it's own schema where it keeps all related objects (Tables, etc)
3) In SQL Server you grant access to the Database and its objects (Tables, etc) to all users of the application.
4) In Oracle do you grant access to the Schema and its objects (Tables, etc) to all users of the application also? Or do all users log
in as the schema owner?
So in Oracle if there existed [SchemaApplication].[table1], how would [userChris] and [userDave] query [SchemaApplication].[table1]?
Would Chris and Dave log in as [userChris] and [userDave], or would they normally log in as [userApplication]?
finally, is it good practice to log in as a unique user eg [userChris] and then issue the
alter session set current_schema = shemaApplication;
command to change the way references to tables are interpreted?