Granting and revoking privileges

Vertica supports GRANT and REVOKE statements to control user access to database objects—for example, GRANT (schema) and REVOKE (schema), GRANT (table) and REVOKE (table), and so on. Typically, a superuser creates users and roles shortly after creating the database, and then uses GRANT statements to assign them privileges.

Where applicable, GRANT statements require USAGE privileges on the object schema. The following users can grant and revoke privileges:

• Superusers: all privileges on all database objects, including the database itself

• Non-superusers: all privileges on objects that they own

• Grantees of privileges that include WITH GRANT OPTION: the same privileges on that object

In the following example, a dbadmin (with superuser privileges) creates user Carol. Subsequent GRANT statements grant Carol schema and table privileges:

• CREATE and USAGE privileges on schema PUBLIC

• SELECT, INSERT, and UPDATE privileges on table public.applog. This GRANT statement also includes WITH GRANT OPTION. This enables Carol to grant the same privileges on this table to other users —in this case, SELECT privileges to user Tom:

=> CREATE USER Carol;
CREATE USER
=> GRANT CREATE, USAGE ON SCHEMA PUBLIC to Carol;
GRANT PRIVILEGE
=> GRANT SELECT, INSERT, UPDATE ON TABLE public.applog TO Carol WITH GRANT OPTION;
GRANT PRIVILEGE
=> GRANT SELECT ON TABLE public.applog TO Tom;
GRANT PRIVILEGE