Privileges required for Table operations in HP Vertica

In the last article we talked about user privileges and their type, in this article we will continue to talk about Table based privileges.

To CREATE a table in a schema the user must be the owner of the schema or have the CREATE privileges along with USAGE privilege on the schema.

Example: 
dbadmin= create table tbl(id int);
ROLLBACK 4367:  Permission denied for schema public
Grant the CREATE privileges to the test_user 
dbadmin= c - dbadmin
Password:
You are now connected as user "dbadmin".
dbadmin= grant create on schema public to test_user;
GRANT PRIVILEGE
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= create table tbl(id int);
CREATE TABLE
dbadmin= d tbl;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | tbl   | id     | int  |    8 |         | f        | f           |
(1 row)
  • we see that the user is allowed to create a new table called tbl in the public schema.

To DROP a table the user must be the owner of the schema.

Example: 
-- create new schema
dbadmin= create schema ss;
CREATE SCHEMA
-- create a new table
dbadmin= create table ss.tbl1(id int);
CREATE TABLE
-- grant on to test_user on the table
dbadmin= grant all on schema ss to test_user;
GRANT PRIVILEGE
-- connect to test_user
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
-- try to drop the table
dbadmin= drop table ss.tbl1;
ROLLBACK 3989: Must be owner of relation tbl1
 Like we said the user must be the owner of the table in order to drop that table.
  • so let's change the tables owner
dbadmin= alter table ss.tbl1 owner to test_user;
ALTER TABLE

-- query the tables table to now
dbadmin= select table_schema,table_name,owner_name from tables where table_name='tbl1';
-[ RECORD 1 ]+----------
table_schema | ss
table_name   | tbl1
owner_name   | test_user
Now let's try to drop the table 
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= drop table ss.tbl1;
DROP TABLE
  • table was dropped.

To SELECT data from a table in Vertica

dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= select * from public.one;
ERROR 4367:  Permission denied for relation one
  • in order for user to have the privileges to select/query data out of a table the user must have the SELECT privilege given to him.
dbadmin= grant select on public.one to test_user;
GRANT PRIVILEGE
Try again now 
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= select * from public.one;
 id
----
  1
(1 row)

To DELETE data from a table in Vertica

  • for this task the user must have the DELETE privilege granted to him
dbadmin= grant delete on public.one to test_user;
GRANT PRIVILEGE
To REFERENCE some column in a table in Vertica
  • you will need the REFERENCES privilege on the specific table.
dbadmin= create table ref(id int references one);
ERROR 4367:  Permission denied for relation one
  • see that an error appears, to fix this you need to grant the REFERENCE privileges to test_user on the public.one table.
dbadmin= grant references on public.one to test_user;
GRANT PRIVILEGE
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= create table ref(id int references one);
CREATE TABLE

Copyright 2020. All Rights Reserved