Need information on creation of Read Only user to vertica DB for SAS integration

Document ID : KB000125177
Last Modified Date : 23/01/2019
Show Technical Document Details
Introduction:
Currently we are running CA PM 3.6 on linux machine and we have a requirement that the capacity team need to access the vertica DB so that they can fetch the data and perform the operations.
Is there a Database connection that CA PM can create such as username (read Only) , password so that capacity team can access our DB(database Name) as ReadOnly to fetch the data.
 
Background:
CAPM uses the Vertica database.
 
Environment:
CAPM 3.x on linux
Instructions:
First we need to create the a readonly role.
  1. Launch vsql as the database admin user (example: dradmin)
cd /opt/vertica/bin
Log into vsql
./vsql -Udradmin -wdbpass
  1. Run the following queries to create the role and give it permissions.
Create a Read Only role:
​CREATE ROLE RO_role;
Grant usage on a particular schema to the Read only role:
GRANT USAGE ON SCHEMA dauser TO RO_role;
​Grant select privileges on some or all tables of a particular schema to the Read only role:
GRANT SELECT ON ALL TABLES IN SCHEMA dauser TO RO_role;
  1. After the role has been created, use the following queries to create a new readonly user and give it access to RO_role.
​​CREATE USER <readonly username> IDENTIFIED BY '<password>';
GRANT RO_Role TO <readonly username>;
ALTER USER <readonly username> DEFAULT ROLE RO_role;
ALTER USER <readonly username> SEARCH_PATH <dauser schema name>;
Additional Information:
The schema is named dauser by default, but this can be changed during the install.
Please be sure to use the correct schema name.