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

Article Id: 125177
Status: Published
Updated On: 23-01-2019 08:09
Products:
CA Infrastructure Management , CA Infrastructure Management , CA Performance Management - Usage and Administration , CA Performance Management - Data Polling
Issue/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.
 

CAPM uses the Vertica database.
 

Environment:

CAPM 3.x on linux

Resolution:

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.