Using Database Schemas

Schemas allow you to create tables and to view and grant permissions for objects in a single transaction. Schemas are supported by SQL Server, Oracle, and DB2, and they're created with the CREATE SCHEMA command, which looks like this:

CREATE SCHEMA AUTHORIZATION <schema owner>
<schema body>

The schema body can contain CREATE TABLE, CREATE VIEW, and GRANT statements. If all statements in the body execute successfully, the transaction is committed. Otherwise, the changes are rolled back.

The individual statements inside the schema body aren't separated by delimiters, such as semicolons. Here's a typical CREATE SCHEMA statement, which creates two data tables for the user Chris and grants access to them to the user Mike:

CREATE SCHEMA AUTHORIZATION Chris
CREATE TABLE Friend (
   FriendID INT PRIMARY KEY NOT NULL, Name VARCHAR(50))
CREATE VIEW FriendsNames AS SELECT Name FROM Friend
GRANT SELECT ON FriendsNames TO Alice;

Within a schema, the created objects don't have to appear in logical order. To ensure this is true, drop the Friend table and the FriendsNames view and reverse the order of the statements inside the schema body:

CREATE SCHEMA AUTHORIZATION System
GRANT SELECT ON FriendsNames TO Alice
CREATE VIEW FriendsNames AS SELECT Name FROM Friend
CREATE TABLE Friend (
   FriendID INT PRIMARY KEY NOT NULL, Name VARCHAR(50));

This executes correctly, even though these statements would have generated errors if executed from within a trigger or a stored procedure; you grant access on a view that wasn't created yet, and you create the view "before" creating the table.