Schemas contain metadata that describes the formal structure of base tables, indexes, views and routines, and metadata that describes the privileges.
Create a schema
The database administrator grants the special privilege CREATE SCHEMA to SQL users with the SQL statement GRANT. SQL users with this privilege can then create schemas and, thus, objects for those schemas (see section “Special privileges” ). These authorized users create schemas with the SQL statement CREATE SCHEMA.
When creating a schema, users authorized to use CREATE SCHEMA can define SQL objects (base tables, views, indexes and routines) and can grant table and EXECUTE privileges by including the SQL statements CREATE TABLE, CREATE VIEW, CREATE INDEX, GRANT and CREATE PROCEDURE in CREATE SCHEMA.
Using the SQL statement GRANT, schema owners can pass on table and EXECUTE privileges for objects in their schemas to other users (see section “Table privileges” ).
Alter a schema
The owner of the schema can adjust it to current requirements:
Create, modify or delete base tables.
Schema owners can create base tables with the SQL statement CREATE TABLE, and can modify and delete existing base tables with the SQL statements ALTER TABLE and DROP TABLE respectively. For further details on creating, modifying and deleting base tables, see section “Base table”.Create or delete views.
Schema owners can create views with the SQL statement CREATE VIEW, and can delete views with the SQL statement DROP VIEW. For further details on creating and deleting views, see section “View”.
- Create or delete indexes.
Schema owners can create indexes with the SQL statement CREATE INDEX, and can delete existing indexes with the SQL statement DROP INDEX. For further details on creating and deleting indexes, see section “Index”.
- Create or delete routines.
Schema owners create routines with the SQL statements CREATE PROCEDURE and CREATE FUNCTION, and delete existing routines with the statements DROP PROCEDURE and DROP FUNCTION. For further details on creating and deleting procedures, see section “Routine”.
Grant or revoke table and EXECUTE privileges.
Schema owners grant privileges for tables and routines in their schemas with the SQL statement GRANT. Privileges are revoked with the SQL statement REVOKE. For further details on granting and revoking privileges, see section “Access protection based on privileges in SQL”.
Delete a schema
Schema owners can delete a schema with the SQL statement DROP SCHEMA.