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. You can also grant new users CREATE privileges on 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 objects in the schema, including renaming an object in the schema or moving an object into this schema.
The schema owner is typically the user who creates the schema. However, a superuser can assign schema ownership to another user on creation, and also later through .
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. |
You can also set privileges on a schema that are inherited by tables and views that you create in it. For details on inherited privileges, see GRANT (Table).
Note the following on error messages related to granting privileges on a schema or an object:
-
You attempt to grant a privilege to a schema, but you do not have USAGE privilege for the schema. In this case, you receive an error message that the schema does not exist.
-
You attempt to grant a privilege to an object within a schema, and you have USAGE privilege on the schema. You do not have privilege on the individual object within the schema. In this case, you receive an error denying permission for that 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:
|