How to Manage users accounts in HP Vertica Database

HP Vertica database has one or more users as per each application needs. Users will require a type of authentication when they will log into the database.

Database users can create/own objects in the database or just have grants to view/select other users objects.

• Dbadmin
• Object Owner
• Everyone else
• Management Console user

How can we create an user in Vertica ?

dbadmin= create user test_user identified by 'secret';
CREATE USER

• the user account was create.

dbadmin= c - test_user
Password:
You are now connected to database "d" as user "test_user".
d= dt
No relations found.

d= create table bla(id int);
ROLLBACK 4367:  Permission denied for schema public
d=

• but as you can see the user account has no privileges but to connect to the database and also a new created user will only have the right to create temporary tables.

Next you need to grant USAGE to the PUBLIC schema to the user.

• the USAGE privilege allows the user to access the objects in that schema but also individual access to the objects must be granted.

dbadmin= grant usage on schema public to test_user;
GRANT PRIVILEGE

How to alter an user password in Vertica

dbadmin= alter user test_user identified by '123';
ALTER USER

 How to lock or unlock an user account in Vertica database

dbadmin= alter user test_user account lock;
ALTER USER
Time: First fetch (0 rows): 3.289 ms. All rows formatted: 3.299 ms
dbadmin= c - test_user
FATAL 4974:  The user account "test_user" is locked
HINT:  Please contact the database administrator
Previous connection kept

dbadmin= alter user test_user account unlock;
ALTER USER
Time: First fetch (0 rows): 2.700 ms. All rows formatted: 2.709 ms
dbadmin= c - test_user
Password:
You are now connected as user "test_user".

 How to drop an user account in Vertica database

dbadmin= drop user test_user cascade;
DROP USER