Database Users and Privileges

This feature is available for: MySQL/MariaDB Replication, MySQL/MariaDB Galera Cluster, PostgreSQL/TimescaleDB, MariaDB Server and Redis Cluster.

This feature shows a summary of database users and privileges for the cluster. All of the changes are automatically synced to all database nodes in the cluster.

Access the database users management section

ClusterControl GUIClusterControl CLI

Log in to your ClusterControl GUI → choose a database cluster → Manage → DB Users. This will bring up a panel where you can view and edit DB Users and privileges.

You can filter the list by Active and Inactive users. Click on Actions → Edit to update the existing user or Actions → Delete to remove the existing user.

For CLI, you can view users, create and delete users, and grant privileges.

• Lists the DB Users for cluster ID 1.

  s9s account \
      --list \
      --long \
      --cluster-id=1 
  

Create database user

ClusterControl GUIClusterControl CLI

Click on Create DB User to open the user creation wizard:

Field	Description
Configuration
Username	- Database username.
Password	- Password for Username. The minimum requirement is 4 characters.
Hostname	- Hostname or IP address range of the user or client. For the IP address range, use the IP address/subnet format, e.g, 192.168.100.0/24.
Privileges
Privileges	- Specify the privilege for this user. If the Privileges text box is active, it will list all possible privileges of the server. - Specify the database or table name. It can be in *.*,{database_name}, {database_name}.* or {database_name}{table_name} format.
Add Privileges	- Add another Privileges statement builder entry for this user.
Advanced
Max Queries Per Hour	- Maximum queries this user can perform in an hour. Default is 0 (unlimited).
Max Updated Per Hour	- Maximum update operations this user can perform in an hour. Default is 0 (unlimited).
Max Connections Per Hour	- Maximum connections allowed for this user in an hour. Default is 0 (unlimited).
Max User Connections	- Maximum connections allowed for this user ever. Default is 0 (unlimited).
Requires SSL	- On – The user must be authenticated using SSL. - Off – This configuration setting will be skipped.

• Create a new MySQL user account myuser with password secr3tP4ss, and allow it to have ALL PRIVILEGES on database shop_db while SELECT on the table account_db.payments:

  s9s account \
      --create \
      --cluster-id=1 \
      --account="myuser:[email protected]" \
      --privileges="shop_db.*:ALL;account_db.payments:SELECT"
  

• Create a new PostgreSQL user account called mydbuser, and allowed the host in the network subnet 192.168.0.0/24 to access the database mydbshop:

  s9s account \
      --create \
      --cluster-id=50 \
      --account='mydbuser:k#[email protected]/24' \
      --privileges="mydbshop.*:ALL"
  

Delete database user

ClusterControl GUIClusterControl CLI

Click on the action button ... → Delete to remove the existing user.

• Delete a database user called joe:

  s9s account \
      -delete \
      --cluster-id=1 \
      --account="joe"