How to globally modify Data Source access for Users

Products

• CA Performance Management - Usage and Administration
• DX NetOps

Issue/Introduction

In CAPC, we added NFA as a data source.  As a result, the user permissions are set to None by default.  How do we do a mass update on over 1000 users to grant User access?

How to globally modify Data Source access for Users without using the DX NetOps Performance Management Performance Center Portal.

Environment

All supported DX NetOps Performance Management releases

Cause

This is by design. When a new Data Source (DS) is added all users are set to None by default for access to the new DS. The exception is the default admin user.

Resolution

Some DS's allow users with the None access level to view data in reports from the DS. Some require a minimum of User access level to the DS to view data in reports from that DS.

NFA is one that does allow users with the None access level to view data in reports for NFA Data. If that is sufficient the following changes are not necessary.

A new feature to add a new option was created in engineering which will add the ability to mass select users and add/change the DS access level at one time.

This command will update any User that with a Role other than Administrator assigned. It will update the Users NFA Data Source (DS) access level to 'User'.

• replace into data_source_rights select userid, <NFA_DS_ID>, 4 from user_definitions where userid in (select userid from user_definitions where RoleID != 3 and Enabled='Y');

We can see how many users there are, and how many would be changed with these commands:

• select count(distinct userid) from user_definitions where RoleID != 3 and Enabled='Y';
• select count(distinct userid) from data_source_rights where sourceid=5 and userlevel=4;

The count from the data_source_rights table query (second one listed) should be >= to the count from the user_definitions table query (first one listed). It will depend on whether any Users with the Administrator Role were given User level NFA access.

Note: In the DB table data_source_rights that stores DS rights. The following UserLevel values are available.

• 4 = User
• 2 = PowerUser
• 1 = Administrator
• 0 = None

To make this change via database commands complete the following steps.

1. Open a terminal to the PC host.
2. Go to the (default path) /opt/CA/MySql/bin directory.
3. Run the command:
   • ./mysql -uroot -p netqosportal
4. Enter the complex MySql DB password when prompted.
5. Once in the mysql prompt run this to find the NFA DS ID needed for the replace command in step 7 below.
   • select sourceid,consolename from data_sources2;
6. Run these to get the current counts:
   • select count(distinct userid) from user_definitions where RoleID != 3 and Enabled='Y';
   • select count(distinct userid) from data_source_rights where sourceid=5 and userlevel=4;
7. Run this to update the Users access levels. Replace <NFA_DS_ID> with the SourceID value from the query in step 5.
   • replace into data_source_rights select userid, <NFA_DS_ID>, 4 from user_definitions where userid in (select userid from user_definitions where RoleID != 3 and Enabled='Y');
8. Run the count commands again to see how they've changed.
   • select count(distinct userid) from user_definitions where RoleID != 3 and Enabled='Y';
   • select count(distinct userid) from data_source_rights where sourceid=5 and userlevel=4;
9. Exit the MySql prompt with the command:
   • exit