June 22, 2006 at 1:46 pm
I am trying to understand what is a schema in Oracle and in SQL Server.
In Oracle:
a) A schema is a collection of DB Objects that are owned by a DB User.
b) A DB may have multiple schemas.
c) The words User, Schema and Owner are used interchangeable.
In SQL Server:
a) Each DB has one schema or application (I.e. Human Resources).
Please help clarify my interpretation of a schema in these two databases.
Thanks in advance, Kevin
June 22, 2006 at 2:50 pm
Sigh. This is extremely difficult to explain.
In general, you have it correct. In Oracle there is a schema that is associated with a login. You can not change ownership of a schema. And in Oracle terms you would consider the entire instance the database.
In SQL 2005, each user/role in the database implies a schema, but you can also create arbitrary schemas (like Human Resources). Schemas are used logically organize objects, primarily for the means of easing security assignment. A schema has an owner, but it can be changed. The owner of a schema is NOT necessarily the owner of the objects contained in the schema. You can group objects under a schema, and users are then granted object priveledges on the *schema*, not the individual objects. For example, if add the user "James" to the Human Resources schema, I grant him Select, or Insert, or Update, etc. That's it. No table or view names. Those priveledges apply to all objects in the schema. If an object is removed from the Human Resources schema, "James" would no longer have any priveledges.
You don't have to use schemas in SQL 2005 in this way -- you can still just organize everything under DBO. If you do choose to use artificial schemas (like HR, Sales, etc.), group objects based on common security scenarios.
I teach custom SQL 2005 for Oracle DBAs, and this is one of the toughest topics. I hope that helped a bit.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply