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

Document ID : KB000009575
Last Modified Date : 29/06/2018
Show Technical Document Details
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.

Instructions:

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> with be the opposite node in your replicating pair. You can confirm these details by running the below query at the mysql                  prompt:
          
           select user,host from mysql.user;

        9. Exit the MySQL prompt: exit
       10. Restart the MySQL service: service mysql restart
       12. Start the API Gateway service: service ssg start
       13. 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: 

https://docops.ca.com/ca-api-gateway/9-3/en/install-configure-upgrade/configure-a-gateway-cluster/configuring-cluster-database-replication/restart-replication

Follow the instructions under the heading: 

When Secondary Node Slave Fails


 
Additional Information:

Privileged Shell for Root Commands - Documentation