ALTER SCHEMA

Changes one or more schemas in one of the following ways:

  • Enables or disables inheritance of schema privileges by tables created in the schemas.
  • Reassigns schema ownership to another user.
  • Renames schemas.

Syntax

ALTER SCHEMA [database.]schema DEFAULT {INCLUDE | EXCLUDE} SCHEMA PRIVILEGES
ALTER SCHEMA [database.]schema OWNER TO user‑name [CASCADE]
ALTER SCHEMA [database.]schema[,…] RENAME TO new‑schema‑name[,…]

Parameters

[database.]schema

The schema to modify. If you specify a database, it must be the current database.
DEFAULT {INCLUDE | EXCLUDE}
SCHEMA PRIVILEGES

Specifies whether to enable or disable default inheritance of privileges for new tables in the specified schema:

  • EXCLUDE SCHEMA PRIVILEGES (default): Disables inheritance of schema privileges.
  • INCLUDE SCHEMA PRIVILEGES: Specifies to grant tables in the specified schema the same privileges granted to that schema. This option has no effect on existing tables in the schema.

See also Enabling Schema Inheritance.
OWNER TO Reassigns schema ownership to the specified user: 
OWNER TO user‑name [CASCADE]

By default, ownership of objects in the reassigned schema remain unchanged. To reassign ownership of schema objects to the new schema owner, qualify the OWNER TO clause with CASCADE. For details, see Cascading Schema Ownership below.
RENAME TO

Renames one or more schemas:

RENAME TO new‑schema‑name[,…]

The following requirements apply:

  • The new schema name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, models, and schemas in the database.
  • If you specify multiple schemas to rename, the source and target lists must have the same number of names.

Renaming a schema referenced by a view causes the view to fail unless another schema is created to replace it.

Privileges

One of the following:

  • Superuser
  • Schema owner

Cascading Schema Ownership

By default, ALTER SCHEMA…OWNER TO does not affect ownership of objects in the target schema or the privileges granted on them. If you qualify the OWNER TO clause with CASCADE, Vertica acts as follows on objects in the target schema:

  • Transfers ownership of objects owned by the previous schema owner to the new owner.
  • Revokes all object privileges granted by the previous schema owner.

If issued by non-superusers, ALTER SCHEMA…OWNER TO CASCADE ignores all objects that belong to other users, and returns with notices on the objects that it cannot change.

For example:

  1. Schema ms is owned by user mayday, and contains two tables: ms.t1 owned by mayday, and ms.t2 owned by user joe:
    => \dt
                           List of tables
     Schema     |         Name          | Kind  |  Owner  | Comment
----------------+-----------------------+-------+---------+---------
 ms             | t1                    | table | mayday  |
 ms             | t2                    | table | joe     |
  2. User mayday transfers ownership of schema ms to user dbadmin, using CASCADE. On return, ALTER SCHEMA notifies user mayday that it cannot transfer ownership of table ms.t2 and its projections, which are owned by user joe: 
    => \c - mayday
You are now connected as user "mayday".
=> ALTER SCHEMA ms OWNER TO dbadmin CASCADE;
NOTICE 3583:  Insufficient privileges on ms.t2
NOTICE 3583:  Insufficient privileges on ms.t2_b0
NOTICE 3583:  Insufficient privileges on ms.t2_b1
ALTER SCHEMA
=> \c
You are now connected as user "dbadmin".
=> \dt
                           List of tables
     Schema     |         Name          | Kind  |  Owner  | Comment
----------------+-----------------------+-------+---------+---------
 ms             | t1                    | table | dbadmin |
 ms             | t2                    | table | joe     |
  3. User dbadmin transfers ownership of schema ms to user pat, again using CASCADE. This time, because dbadmin is a superuser, ALTER SCHEMA transfers ownership of all ms tables to user pat
    => ALTER SCHEMA ms OWNER TO pat CASCADE;
ALTER SCHEMA
=> \dt
                           List of tables
     Schema     |         Name          | Kind  |  Owner  | Comment
----------------+-----------------------+-------+---------+---------
 ms             | t1                    | table | pat     |
 ms             | t2                    | table | pat     |

Swapping Schemas

Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. For example, the following ALTER SCHEMA statement uses the temporary schema temps to facilitate swapping schema S1 with schema S2. In this example, S1 is renamed to temps. Then S2 is renamed to S1. Finally, temps is renamed to S2.

=> ALTER SCHEMA S1, S2, temps RENAME TO temps, S1, S2;

Examples

The following example renames schemas S1 and S2 to S3 and S4, respectively:

=> ALTER SCHEMA S1, S2 RENAME TO S3, S4;

This example sets the default behavior for new table t2 to automatically inherit the schema's privileges:

=> ALTER SCHEMA s1 DEFAULT INCLUDE SCHEMA PRIVILEGES;
=> CREATE TABLE s1.t2 (i, int);

This example sets the default for new tables to not automatically inherit privileges from the schema:

=> ALTER SCHEMA s1 DEFAULT EXCLUDE SCHEMA PRIVILEGES;

See Also