Informix Guide to SQL: Syntax SQL Statements

Use the CREATE SCHEMA statement to issue a block of CREATE and GRANT statements as a unit. The CREATE SCHEMA statement allows you to specify an owner of your choice for all database objects that the CREATE SCHEMA statement creates.

Use this statement with DB-Access and the SQL Editor.

You cannot issue the CREATE SCHEMA statement until you create the affected database.

Users with the Resource privilege can create a schema for themselves. In this case, user must be the name of the person with the Resource privilege who is running the CREATE SCHEMA statement. Anyone with the DBA privilege can also create a schema for someone else. In this case, user can identify a user other than the person who is running the CREATE SCHEMA statement.

You can put CREATE and GRANT statements in any logical order within the statement, as the following example shows. Statements are considered part of the CREATE SCHEMA statement until a semicolon or an end-of-file symbol is reached.

CREATE  SCHEMA  AUTHORIZATION  sarah

	CREATE  TABLE  mytable  (mytime  DATE,  mytext  TEXT)

	GRANT  SELECT,  UPDATE,  DELETE  ON  mytable  TO  rick

	CREATE VIEW myview AS

		SELECT * FROM mytable WHERE mytime > '12/31/1997'

	CREATE  INDEX  idxtime  ON  mytable  (mytime); 

All database objects that a CREATE SCHEMA statement creates are owned by user, even if you do not explicitly name each database object. If you are the DBA, you can create database objects for another user. If you are not the DBA, and you try to create a database object for an owner other than yourself, you receive an error message.

You can only grant privileges with the CREATE SCHEMA statement; you cannot revoke or drop privileges.

If you create a database object or use the GRANT statement outside a CREATE SCHEMA statement, you receive warnings if you use the -ansi flag or set DBANSIWARN.

Related statements: CREATE INDEX, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, and GRANT

For a discussion of how to create a database, see the Informix Guide to Database Design and Implementation.