Schema Privileges

By default, only a superuser and the schema owner have privileges to create objects within a schema. Additionally, only the schema owner or a superuser can drop or alter a schema. See DROP SCHEMA and ALTER SCHEMA.

You must grant all new users access to the PUBLIC schema by running GRANT USAGE ON SCHEMA PUBLIC. Then grant new users CREATE privileges and privileges to individual objects in the schema. This enables new users to create or locate objects in the PUBLIC schema. Without USAGE privilege, objects in the schema cannot be used or altered, even by the object owner.

CREATE gives the schema owner or user WITH GRANT OPTION permission to create new objects in the schema, including renaming an object in the schema or moving an object into this schema.

Note: The schema owner is typically the user who creates the schema. However, a superuser can create a schema and assign ownership of the schema to a different user at creation.

All other access to the schema and its objects must be explicitly granted to users or roles by the superuser or schema owner. This prevents unauthorized users from accessing the schema and its objects. A user can be granted one of the following privileges through the GRANT statement:

Privilege Description
CREATE

Allows the user to create new objects within the schema. This includes the ability to create a new object, rename existing objects, and move objects into the schema from other schemas.

USAGE

Permission to select, access, alter, and drop objects in the schema. The user must also be granted access to the individual objects in order to alter them. For example, a user would need to be granted USAGE on the schema and SELECT on a table to be able to select data from a table. You receive an error message if you attempt to query a table that you have SELECT privileges on, but do not have USAGE privileges for the schema that contains the table.

Note the following on error messages related to granting privileges on a schema or an object:

Schema Privileges and the Search Path

The search path determines to which schema unqualified objects in SQL statements belong.

When a user specifies an object name in a statement without supplying the schema in which the object exists (called an unqualified object name) Vertica has two different behaviors, depending on whether the object is being accessed or created.

Creating an object Accessing/altering an object

When a user creates an object—such as table, view, sequence, procedure, function—with an unqualified name, Vertica tries to create the object in the current schema (the first schema in the schema search path), returning an error if the schema does not exist or if the user does not have CREATE privileges in that schema.

Use the SHOW search_path command to view the current search path.

=> SHOW search_path;    name     |                      setting
-------------+---------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

Note: The first schema in the search path is the current schema, and the $user setting is a placeholder that resolves to the current user's name.

When a user accesses or alters an object with an unqualified name, those statements search through all schemas for a matching object, starting with the current schema, where:

  • The object name in the schema matches the object name in the statement.
  • The user has USAGE privileges on the schema in order to access object in it.
  • The user has at least one privilege on the object.