posted 14 years ago
I assume you want to discuss separating objects of different applications (or possibly of one application) into different schemas.
In Oracle, a schema is automatically created for every database user. Any database object the user creates (table, view, index, trigger and so on) implicitly belongs to his schema. In short, you use schemas to simplify administration and enhance data protection of your application.
It is practically a must to separate objects of different applications into different schemas (that is create them using different users), otherwise there could be numerous clashes between object names chosen by the applications. Never put objects of two unrelated applications into one schema, even if the applications use unique prefix to distinguish them. It is probably even advisable to create a separate tablespace for every application; some applications might even utilize multiple tablespaces. This is getting quite beyond the original question, though.
I personally have good experience with the following setup:
1) All object of the application are created in a single schema (let's say MYAPP). User MYAPP is used only to do maintenance in this schema, that is application administrator(s) log in as MYAPP to create or upgrade the schema. Users of the application never log in as MYAPP.
2) A separate user (or users) are created for application users. In three tier application it will often be a single user. In client-server architecture, if used, there might be one database user for every real world user. These users are granted minimal set of privileges they need. For example, if there is an audit table, they will be granted only INSERT and SELECT into the audit table. That way the application cannot erase audit table neither by malice nor by accident.
3) To avoid the need to prefix every view or table accessed by the application with MYAPP. prefix, two solutions are possible: you can either create a private synonym for every object in the schema of every user from the above step, or you can issue ALTER SESSION SET CURRENT_SCHEMA=MYAPP immediately after you obtain the connection (you don't need the ALTER SESSION privilege for that).
This setup offers additional protection against the most destructive SQL injection attacks (DROP TABLE cannot succeed in this setup - though you must definitely protect yourself by using binds!). And if you use the SET CURRENT_SCHEMA solution, it allows you to have several versions of the application's objects installed on one database (say development and production version) and easily configure the application to use one or the other simply by changing the schema name in the SET CURRENT_SCHEMA statement (eg. place the schema name into a configuration file).
In Oracle, schema objects are used to refer to database data. There are several kinds of schema objects, including tables, indexes, views, clusters, synonyms... Are you sure you wanted to ask about schema objects ? If so, which schema object in particular ?
