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 includesWITH GRANT OPTION
. This enablesCarol
to grant the same privileges on this table to other users —in this case, SELECT privileges to userTom
:
=> 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