Access denied for user 'CR_user'@'<CABI_hostname>' in Spectrum Data Source connection test

Products

• Spectrum
• Network Observability

Issue/Introduction

After upgrading Spectrum from 23.3.13 to 24.3.12, unable to generate CABI/JasperReports reports. 

Spectrum Data Source connection test fails: spectrum_domain_ds

Could not connect to address=(host=<SRM_hostname>)(port=3306)(type=master) : Access denied for user 'CR_user'@'<CABI_hostname>' (using password: YES) Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding' Show details

Environment

DX NetOps Spectrum version 24.3.12 and CABI/JasperReports version 7.9.2.4

Cause

 It looks like there an issue with the 'CR_user'@'<CABI_hostname>' account in MySQL.

cd $SPECROOT/mysql/bin
./mysql --defaults-file=../my-spectrum.cnf -uroot -p reporting -A

 

mysql> SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;
+-------------------------------------------------------+
| CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')  |
+-------------------------------------------------------+
| SHOW GRANTS FOR 'root'@'%';                           |
| SHOW GRANTS FOR 'OC_admin'@'localhost';               |
| SHOW GRANTS FOR 'OC_user'@'localhost';                |
| SHOW GRANTS FOR 'SPEC_admin'@'localhost';             |
| SHOW GRANTS FOR 'mysql.infoschema'@'localhost';       |
| SHOW GRANTS FOR 'mysql.session'@'localhost';          |
| SHOW GRANTS FOR 'mysql.sys'@'localhost';              |
| SHOW GRANTS FOR 'root'@'localhost';                   |
| SHOW GRANTS FOR 'OC_admin'@'localhost.localdomain';   |
| SHOW GRANTS FOR 'OC_user'@'localhost.localdomain';    |
| SHOW GRANTS FOR 'SPEC_admin'@'localhost.localdomain'; |
| SHOW GRANTS FOR 'CR_user'@'<CABI_hostname>';              |
+-------------------------------------------------------+
12 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'CR_user'@'<CABI_hostname>';

ERROR 1141 (42000): There is not such grant defined for user 'CR_user' on host '<CABI_hostname>'

Resolution

Disable the Jasper Integration, dropped the 'CR_user'@'<CABI_hostname>' account in MySQL and enabled the Jasper Integration.

1) Disable the Jasper Integration

2) Drop the 'CR_user'@'<CABI_hostname>'  account.

mysql> DROP user 'CR_user'@'<CABI_hostname>';

Query OK, 0 rows affected (0.00 sec)

3) Enable the Jasper Integration

The the 'CR_user'@'<CABI_hostname>' account was recreated in MySQL.

 

4) Rerun the following MySQL queries and got the proper results:

mysql> SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

mysql> SHOW GRANTS FOR 'CR_user'@'<CABI_hostname>';

+-----------------------------------------------------------------------------------------+
| Grants for CR_user'@'<CABI_hostname>'                                                   |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `CR_user'@'<CABI_hostname>`                                       |
| GRANT SELECT, EXECUTE ON `reporting`.* TO `CR_user'@'<CABI_hostname>` WITH GRANT OPTION |
| GRANT SELECT, EXECUTE ON `srmdbapi`.* TO `CR_user'@'<CABI_hostname>` WITH GRANT OPTION  |
+-----------------------------------------------------------------------------------------+