User admin for MariaDB overview

User admin for MariaDB overview

Please not you cannot backup Pre 10.4.1 to replace the mysql.user table and restore mysql.global_priv table:

The mysql.global_priv table in MariaDB contains information about users who have permission to access the MariaDB server and their global privileges. This table was introduced in MariaDB 10.4.1 to replace the mysql.user table in earlier versions. Here are the key fields in the mysql.global_priv table:

• Host: The host from which the user is allowed to connect.

• User: The username of the account.

• Priv: A JSON field containing the global privileges granted to the account and other account properties. This field includes:

  • access: The privileges granted to the user.

  • plugin: The authentication plugin used.

  • authentication_string: The hashed password for the user.

Example of a record in the mysql.global_priv table:

+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+

| Host      | User        | Priv                                                                                                                                  |

+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+

| localhost | root        | {"access": 18446744073709551615,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |

| 127.%     | msandbox    | {"access":1073740799,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"}            |

+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+

Managing Users in MariaDB

1. Create User:

2. CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Example:

1. CREATE USER 'minal'@'localhost' IDENTIFIED BY 'password';

   1. Change User Name and Host:

2. RENAME USER 'old_user'@'old_host' TO 'new_user'@'new_host';

Example:

1. RENAME USER 'donald'@'localhost' TO 'duck'@'localhost';

   1. Alter User to Limited Number of Connections:

2. ALTER USER 'username'@'host' WITH MAX_USER_CONNECTIONS count;

Example:

1. ALTER USER 'minal'@'localhost' WITH MAX_USER_CONNECTIONS 5;

   1. Disable User Account:

2. ALTER USER 'username'@'host' ACCOUNT LOCK;

Example:

1. ALTER USER 'minal'@'localhost' ACCOUNT LOCK;

   1. Delete User Account:

2. DROP USER 'username'@'host';

Example:

1. DROP USER 'minal'@'localhost';

=======================================================================