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

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.