wood burning stoves 2.0*
The moose likes Oracle/OAS and the fly likes When Should we create a Schema Object ? Is dividing the Database into schema a good practice ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "When Should we create a Schema Object ? Is dividing the Database into schema a good practice ?" Watch "When Should we create a Schema Object ? Is dividing the Database into schema a good practice ?" New topic
Author

When Should we create a Schema Object ? Is dividing the Database into schema a good practice ?

shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
When should we create a schema Object ?
How does the creation of a schema object benefit us?
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

I don't understand your question, and I think it is because of some jargon misuse 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 ?

[My Blog]
All roads lead to JavaRanch
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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).
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
Thanks Martin, I undertand what you mean, i get the situation under which we must create a separate schema to group database objects. My further question related to the same is, does separating objects into schema have any performance benefit just like we have a performance benefit from separating table groups into different tablespaces.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

No, performance is not affected by putting tables into different schemas. Schema does not affect physical organization of the tables in any way (apart perhaps from default tablespace, but you can override that).
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
thanks alot..and sorry for delayed response
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: When Should we create a Schema Object ? Is dividing the Database into schema a good practice ?
 
Similar Threads
To pass file object to webservice
XML and Object already defined. JAXB type questions
XSL/XSLT or DTD?
database schema
JAXB namespace