This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Functional Reactive Programming and have Stephen Blackheath and Anthony Jones on-line!
See this thread for details.
Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
shukla raghav
Ranch Hand
Posts: 203
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When should we create a schema Object ?
How does the creation of a schema object benefit us?
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 203
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 203
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks alot..and sorry for delayed response
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic