Update Permissions for otk_user to otk_user@%

Products

• CA API Gateway

Issue/Introduction

I need to update permissions for otk_user to permit connections from hosts other than localhost. The recommendation from support to run the following command:

 

My recommendation is to set the JDBC query url to a more suitable configuration for multi db environment similar to the following:

 

jdbc:mysql://HostName1:3306,HostName2:3306/otk_db?failOverReadOnly=false&autoReconnect=true&connectTimeout=1

 

 

grant all on otk_db.* to otk_user@'%';

UPDATE mysql.user SET password = PASSWORD('otk_user_password') WHERE user = "otk_user"; flush privileges;

 

However, that command results in the following error:

 

ERROR 1133 (42000): Can't find any matching row in the user table

 

 

 

Environment

Release : 9.3

Component : API GTW ENTERPRISE MANAGER

Resolution

If, 

# mysql 

mysql> select user, host from mysql.user;

Doesnt show a otk_user@% 

Just a otk_user@localhost 

The problem is the original user is defined as otk_user@localhost but you are trying to grant all on otk_user@%

You need to run a create user before doing the grant and update.

# mysql

mysql> create user 'otk_user'@'%'

mysql> grant
all on otk_db.* to otk_user@'%';

mysql> UPDATE
mysql.user SET password = PASSWORD('otk_user_password') WHERE user =
"otk_user"; flush privileges;

Or  instead  run the following to create and grant 
mysql> grant
all on otk_db.* to otk_user@'%' identified by ''; flush privileges;