Managing the MySQL privileged ('root') and unprivileged ('gateway') user accounts

Products

• CA API Gateway

Issue/Introduction

There are two accounts that are used to configure and access the MySQL database used by the API Gateway application:

1. Privileged user (root)
2. Unprivileged user (gateway)

These accounts provide the Gateway appliance with a method of configuring a Gateway database as well as providing jailed and restricted access through a privileged and unprivileged user account. The default accounts are root and gateway, respectively, but can be reconfigured by a system administrator.

Additionally, for replication purposes the REPLUSER account is used.

Environment

API Gateway all supported releases. (11.x onwards)

Resolution

Managing the privileged ('root') MySQL user account

1. Connect to the API Gateway via a serial cable, direct console access, or SSH.
2. Log in as the ssgconfig user.
3. Select 3) Use a privileged shell (root).
4. Stop the API Gateway service: service ssg stop
5. Access the MySQL prompt: mysql
6. Execute the following query, ensuring that the password value ('7layer') is substituted with the desired value:
7. For MySQL 5.7.5 and earlier: UPDATE `mysql`.`user` SET `password`=PASSWORD('7layer') WHERE `user`='root'; FLUSH PRIVILEGES;
8. For MySQL 5.7.6 and later: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
9. Exit the MySQL prompt: exit
10. Restart the MySQL service: service mysql restart
11. Modify the MySQL client configuration file to reflect the new password: /root/.my.cnf
12. Start the API Gateway service: service ssg start

Managing the unprivileged ('gateway') MySQL user accounts

Modifying the credentials for the unprivileged MySQL user involves two steps: Changing the credentials and re-configuring the Gateway application to use those new credentials. Both processes are documented below.

1. Changing the credentials

1. Connect to the API Gateway via a serial cable, direct console access, or SSH.
2. Log in as the ssgconfig user.
3. Select 3) Use a privileged shell (root).
4. Access the MySQL prompt: mysql
5. Execute the following query, ensuring that the user and password values ('gateway' and '7layer', respectively) are substituted with the desired values:
   For MySQL 5.7.5 and earlier: UPDATE mysql.user set authentication_string=password('7layer') where user='gateway'; FLUSH PRIVILEGES; 
6. For MySQL 5.7.6 and later: ALTER USER 'gateway'@'localhost' IDENTIFIED BY 'MyNewPass';  *

           Note: You can confirm the host the Gateway user has access to by executing the query:

           select user,host from mysql.user;

           The ALTER USER statement should be executed against the host where the grants exist.
            
        7. Exit the MySQL prompt: exit
        8. Exit the privileged shell: exit

2. Re-configuring the Gateway application

1. Select 2) Display Layer 7 Gateway configuration menu.
2. Select 3) Configure the Layer 7 Gateway.
3. Select 1) Database Connection.
4. Specify all aspects of the database connection information, ensuring that the database username and database password are updated to the correct values.
5. Save the changes and exit.
6. Restart the API Gateway appliance.

 

Managing the replication user ('repluser') MySQL user account

1. Connect to the API Gateway via a serial cable, direct console access, or SSH.
2. Log in as the ssgconfig user.
3. Select 3) Use a privileged shell (root).
4. Stop the API Gateway service: service ssg stop
5. Access the MySQL prompt: mysql
6. Execute the following query, ensuring that the password value ('7layer') is substituted with the desired value:
7. For MySQL 5.7.5 and earlier: UPDATE `mysql`.`user` SET `password`=PASSWORD('7layer') WHERE `user`='repluser'; FLUSH PRIVILEGES;
8. For MySQL 5.7.6 and later: ALTER USER 'repluser'@'<REMOTE_HOST>' IDENTIFIED BY 'MyNewPass';
         Where <REMOTE_HOST> wit be the opposite node in your replicating pair. You can confirm these details by running the below query at the mysql prompt:
         mysql>select user,host from mysql.user;

    9. Exit the MySQL prompt: exit
   10. Restart the MySQL service: service mysql restart
   11. Start the API Gateway service: service ssg start
   12. Run the same procedure on the secondary node.

Once this is complete you will need to restart replication on the pair following the details in the documentation.

Follow the instructions under the heading: When Secondary Node Slave Fails 

Additional Information

Privileged Shell for Root Commands - Documentation