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
• DX NetOps

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.
 

Create read only user in Vertica 

Create read-only user in Vertica 

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.