Configuring the DBMS account for work with MySQL and MariaDB

Prerequisites

Before you assign rights to the DBMS account, perform the following actions:

1. Make sure that you log in to the system under the local administrator account.
2. Install an environment for working with MySQL or MariaDB.

Configuring the DBMS account to install Administration Server

To configure the DBMS account for the Administration Server installation:

1. Run an environment for working with MySQL or MariaDB under the root account that you created when you installed the DBMS.
2. Create an internal DBMS account with a password. The Administration Server installer (hereinafter also referred to as the installer) and the Administration Server service will use this internal DBMS account to access DBMS.

   To create a DBMS account with a password, execute the following command:

   /* Create a user named KSCAdmin and specify the password for KSCAdmin */

   CREATE USER 'KSCAdmin' IDENTIFIED BY '<password>';

   If you use MySQL 8.0 or earlier as a DBMS, note that for these versions the "Caching SHA2 password" authentication is not supported. Change the default authentication from "Caching SHA2 password" to "MySQL native password":

   • To create a DBMS account that uses the "MySQL native password" authentication, execute the following command:

     CREATE USER 'KSCAdmin'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';

   • To change the authentication for an existing DBMS account, execute the following command:

     ALTER USER 'KSCAdmin'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';

3. Grant the following privileges to the created DBMS account:
   • Schema privileges:
     • Administration Server database: ALL (excluding GRANT OPTION)
     • System schemes (mysql and sys): SELECT, SHOW VIEW
     • The sys.table_exists stored procedure: EXECUTE
   • Global privileges for all schemes: PROCESS, SUPER

   To grant the required privileges to the created DBMS account, run the following script:

   /* Grant privileges to KSCAdmin */

   GRANT USAGE ON *.* TO 'KSCAdmin';

   GRANT ALL ON kav.* TO 'KSCAdmin';

   GRANT SELECT, SHOW VIEW ON mysql.* TO 'KSCAdmin';

   GRANT SELECT, SHOW VIEW ON sys.* TO 'KSCAdmin';

   GRANT EXECUTE ON PROCEDURE sys.table_exists TO 'KSCAdmin';

   GRANT PROCESS ON *.* TO 'KSCAdmin';

   GRANT SUPER ON *.* TO 'KSCAdmin';

   If you use MariaDB 10.5 or earlier as a DBMS, you do not need to grant the EXECUTE privilege. In this case, exclude the following command from the script: GRANT EXECUTE ON PROCEDURE sys.table_exists TO 'KSCAdmin'.

4. To view the list of privileges granted to the DBMS account, execute the following command:

   SHOW grants for 'KSCAdmin';

5. To create an Administration Server database manually, run the following script (in this script, the Administration Server database name is kav):

   CREATE DATABASE kav

   DEFAULT CHARACTER SET ascii

   DEFAULT COLLATE ascii_general_ci;

   Use the same database name that you specify in the script that creates the DBMS account.

6. Install Administration Server.

After the installation finishes, the Administration Server database is created and Administration Server is ready to use.