Help Databases and objects
User Manager
The User Manager lists the server’s user accounts and lets you create, rename and delete them, set their
passwords and limits, grant or revoke privileges at every level, and, on servers that have them, manage
roles. Everything it does is ordinary CREATE USER, GRANT, REVOKE and related statements, run with your
own connection’s rights.
Opening it
Press Ctrl+U, or choose Tools > User Manager.
Your database user needs to read the privilege tables in the mysql database, and to have the rights to
change accounts. Without the first, bonSQL says it cannot retrieve the information it needs and the User
Manager does not open.
The layout
- User, top left: pick an account, shown as
name@host. - The tree below it: the account (marked
(role)if it is a role), then Global Privileges, Object Level Privileges (the databases, and inside them tables, views, columns, stored procedures and functions) and, on servers with roles, Roles. - The right-hand side changes with what is selected in the tree. A line at the top says what you can do there.
- Show only privileged objects, bottom left: shrinks the tree to the objects the account has privileges on.
Account details
Select the account itself in the tree to see:
| Field | What it is |
|---|---|
| Username / Host | The account name and the host it may connect from. % means any host. Changing either and saving renames the account. |
| Password / Retype Password | Type a new password in both to change it. Leave them as they are to keep the current one. |
| Plugin | The authentication plugin, from the plugins active on the server. It can be changed on MySQL newer than 5.7.0 and MariaDB newer than 10.4.0; on MySQL 5.6 it can be chosen only when creating an account. Elsewhere it is greyed out. |
| Role (no password) | Ticked for a role. It can only be set when creating an account. |
| Four Maximum number of … fields | Queries, updates and connections per hour, and simultaneous connections. 0 means no limit. Shown when the server supports them. |
Privileges
Select Global Privileges for privileges on the whole server, or an object under Object Level Privileges for privileges on that database, table, view, column or routine. Tick a privilege to grant it, untick it to revoke it. Select/Deselect All does the whole list. Selecting a folder rather than an object shows Note - Please select an object to GRANT/REVOKE privileges.
The list comes from the server itself (SHOW PRIVILEGES), so it shows exactly what your server has: MariaDB’s
privileges such as DELETE HISTORY and BINLOG ADMIN, and MySQL 8’s dynamic ones such as BACKUP_ADMIN.
Each object level offers only the privileges that apply to it. An account’s current global privileges are
read with SHOW GRANTS. GRANT in the list is the grant option. SQLyog Community used a fixed list, which
missed privileges added in newer servers.
Roles
On MariaDB 10.0.5 and later and MySQL 8.0 and later, the tree has a Roles node. It lists every role on the server; tick a role to grant it to the selected account, untick it to revoke it. A role can be granted to another role.
After granting a role to an account that is not itself a role, bonSQL makes the role usable at once:
- MySQL: it runs
SET DEFAULT ROLE ALL TOthe account, because MySQL leaves granted roles inactive until they are default roles. - MariaDB: if the account has no default role yet, the newly granted role becomes its default.
To create a role, click Add New User, tick Role (no password) and enter a name. On MariaDB a role has no host. MySQL has no separate role flag, so bonSQL counts a locked account with no password as a role.
Buttons
| Button | Does |
|---|---|
| Add New User | Clears the fields for a new account; Save Changes becomes Create and Cancel Changes becomes Cancel. The host starts as %. |
| Delete User | Deletes the selected account after asking you to confirm. |
| Save Changes | Applies everything changed since the last save. |
| Cancel Changes | Puts the fields back as they were. |
| Close | Closes the User Manager, asking first if there are unsaved changes. |
| Help | Opens this page. |
After deleting or renaming an account, bonSQL runs FLUSH PRIVILEGES.
When it does not work
- Passwords do not match: the two password fields differ.
- You have not specified a username. Do you want to create an anonymous user?: the username is empty. Answer No to go back and enter one.
- A server error when saving usually means your own account lacks the right to make that change.