Ansible Upgrade : Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

Products

• CA API Gateway
• API SECURITY
• CA API Gateway Precision API Monitoring Module for API Gateway (Layer 7)
• CA API Gateway Enterprise Service Manager (Layer 7)
• STARTER PACK-7
• CA Microgateway

Issue/Introduction

After running the “GRANT” commands 

GRANT ALL ON ssg.* TO 'gateway'@'localhost' IDENTIFIED BY 'gatewaypass';

GRANT ALL ON mysql.* TO 'gateway'@'localhost' IDENTIFIED BY 'gatewaypass';

 

the “export database playbook” ran successfully. But in next step while running “import database playbook” we are running in issue give below:

[root@soarpoc gateway-ansible-playbook]# ansible-playbook -i inventories/qa/hosts.yml playbooks/gateway-database-import.yml

 PLAY [Execute mysql import in destination gateway] *********************************************************************************************************************

 TASK [Gathering Facts] *************************************************************************************************************************************************

ok: [<server name><domain>.com]

 

TASK [gateway_common : checking to see if gateway pid exists.] *********************************************************************************************************

fatal: [<server name><domain>.com]: FAILED! => {"changed": false, "cmd": "pgrep -f \"Gateway.jar\"", "delta": "0:00:00.011141", "end": "2020-09-14 10:39:26.934666", "msg": "non-zero return code", "rc": 1, "start": "2020-09-14 10:39:26.923525", "stderr": "", "stderr_lines": [], "stdout": "", "stdout_lines": []}

...ignoring

 

TASK [gateway_common : stopping Gateway application if the gateway pid exists, leave process controller on.] ***********************************************************

skipping: [<server name><domain>.com]

 

TASK [gateway_common : checking to see if gateway pid does not exist anymore after stopping Gateway application] *******************************************************

ok: [<server name><domain>.com]

 

TASK [gateway_common : gateway pid after stopping Gateway application] *************************************************************************************************

ok: [<server name><domain>.com] => {

    "msg": ""

}

 

TASK [gateway_import_database : create temp direcory for output files] *************************************************************************************************

ok: [<server name><domain>.com]

 

TASK [gateway_import_database : unzip mysql output to remote server] ***************************************************************************************************

ok: [<server name><domain>.com]

 

TASK [gateway_import_database : Delete gateway database] ***************************************************************************************************************

changed: [<server name><domain>.com]

 

TASK [gateway_import_database : create gateway database] ***************************************************************************************************************

changed: [<server name><domain>.com]

 

TASK [gateway_import_database : import gateway database from source gateway] *******************************************************************************************

fatal: [<server name><domain>.com]: FAILED! => {"changed": false, " \nERROR 1227 (42000) at line 3503: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation", "stderr_lines": ["mysql: [Warning] Using a password on the command line interface can be insecure.", "ERROR 1227 (42000) at line 3503:Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation"], "stdout": "", "stdout_lines": []}

 

PLAY RECAP *************************************************************************************************************************************************************

<server name><domain>.com    : ok=8    changed=2    unreachable=0    failed=1    skipped=1    rescued=0    ignored=1

 [root@soarpoc gateway-ansible-playbook]#

 

We have also run the below commands on gwsrv15-q but the issue still persist.

GRANT ALL ON ssg.* TO 'gateway'@'localhost' IDENTIFIED BY 'gatewaypass';

GRANT ALL ON mysql.* TO 'gateway'@'localhost' IDENTIFIED BY 'gatewaypass';

 

 

Environment

Release : 9.4

Component : API GATEWAY

Resolution

To Resolve the Error above Was needed to run the following :

GRANT ALL ON ssg.* TO 'gateway'@'localhost' IDENTIFIED BY '7layer';
GRANT ALL ON mysql.* TO 'gateway'@'localhost' IDENTIFIED BY '7layer';
GRANT SYSTEM_USER ON *.* TO 'gateway'@'localhost';
mysql> UPDATE mysql.user SET Super_Priv='Y' WHERE user='gateway' AND host='%';
mysql> UPDATE mysql.user SET Super_Priv='Y' WHERE user='gateway' AND host='gwserv15-domain.com';
mysql> UPDATE mysql.user SET Super_Priv='Y' WHERE user='gateway' AND host='gwserv16-domain.com';
mysql> UPDATE mysql.user SET Super_Priv='Y' WHERE user='gateway' AND host='localhost';
mysql> flush privileges;