Table of Contents
This section shows how to administer existing users, add new users, and delete existing users. For more information on the MySQL user account management and privilege system, see MySQL User Account Management.
Existing users are listed in the lower left area of the
sidebar. If
there are many user accounts on your MySQL server, the field with
the magnifying glass icon may be handy for filtering the users you
are looking for. Typing in t
or
T
, for example, will set the filter to users
whose names begin with a t
. The filter works in
a case-insensitive fashion.
If you are an experienced MySQL user, you will notice that users
are not listed in the typical MySQL way
(
), but rather with their user names only.
If double clicking on a user name does not show subcategories for
that user, this means that the user's privileges are not
restricted to a specific host, or hosts. If there are
subcategories, the user's privileges are restricted to a specific
host, or set of hosts. You may assign various (and different) sets
of privileges, depending on the host from which the user connects
to the MySQL server. That concept is described in detail in
Access Control, Stage 1: Connection Verification.
user
@host
Note that MySQL Administrator has a different concept of
what a user
is than MySQL has. In MySQL, a user
is always identified by a
username
/host
combination.
This means that, for example, 'brian'@'%'
may
be a user completely different from
'brian'@'localhost'
. The former might be
Brian Miller
, while the latter might be
Brian Schultz
. That distinction does not hold
true for MySQL Administrator: User brian
is always
a particular user, no matter from which host he connects to the
MySQL server. That said, 'brian'@'%'
may still
have privileges different from
'brian'@'localhost'
.
For example, you might have a user called
superuser
. If double clicking on that user name
shows two subcategories, localhost
and
athena
, this means that the user has a specific
set of privileges if he/she connects from
localhost
, and a (probably different) set of
privileges if he/she connects from athena
.
Right clicking on a user name, or on one of the subcategories a user might have, provides access to the following commands:
New User
to the user list. You will have to fill in at least a user
name in the MySQL User field of the
User
Information tab, which, after applying this change,
will also rename that user in the user accounts list.
: Allows you to specify a host from which the user can connect. This command is unavailable if you highlight a subcategory.
: Allows you to remove a host from which the user can connect. If there are no sub-categories, the user will be removed (you will be prompted to confirm the removal).
New User
until you specify another name in
the MySQL User field of the
User
Information tab. This command is useful for creating
users with identical privileges.
Cloning is only available on Windows.
: Allows you to delete the selected user. You will be prompted to confirm that you really want to delete that user.
: Rereads the user list from the MySQL server. This is helpful when other users (on other connections) are editing user accounts. Note that selecting that command will collapse all subcategories.
The fields in this tab are unavailable unless you either click on a user name in the User Accounts area of the sidebar, or create a new user in that sidebar or using the button on this tab. In the former case, the fields are filled with the information stored for that user, in the latter case, all fields are empty.
Note that all fields will be stored only when you click the
button. If you don't want to save your changes, click the button.
The values you enter in the fields of this tab are stored on the
MySQL server. The fields in the Login
Information group of this tab are stored in the
user
table of the mysql
database, while the fields in the Additional
Information group are stored in the
user_info
table of the mysql
database. The latter table is created the first time you add a new
user, or change an existing user. Its contents may look like this:
mysql> SELECT * FROM mysql.user_info \G *************************** 1. row ************ User: superuser Full-name: Stefan Hinz Description: MySQL Documentation Team Email: stefan@mysql.com Contact-information: Phone: +49 30 123456789 Fax: +49 30 987654321 ICQ: 123456789 Icon:
Login Information
MySQL User: User name that is specified when connecting to the MySQL server. Note that this user name has nothing to do with user names you might use elsewhere on your operating system. Using special characters or spaces for a user name is not recommended.
Password: Password that is specified
when connecting to the MySQL server. Note that this
password has nothing to do with passwords you might use
elsewhere on your operating system. Again, don't use
special characters or spaces for the password. It is,
however, recommended that you use nontrivial passwords. A
very trivial password might be abcde
,
while a nontrivial password might be
lEtusMak3iThartdoGesz
. Note that it is
not mandatory to specify a password, although it is
strongly recommended that you do so.
Confirm Password: Fill in the password once again to make sure you do not accidentally introduce a typo.
Additional Information
All information specified here is optional.
Full Name: As opposed to the user name in MySQL User, the full name may contain any characters, including spaces.
Description: Additional description of user.
Email: Email address of user.
Contact Information: More contact information, like postal address, or messenger information.
Icon: Rather than using the default
icon, you may select a different icon by pressing the
button.
The icon you use must be exactly 48 by 48 pixels in size,
and its file format must be PNG
(portable network graphics).
This tab is available only if the Show Global Privileges checkbox in the Administrator section of the Options dialog has been checked.
Under Assigned Privileges, you find the
global privileges assigned to the selected user. For more
information about those privileges, see
Privileges Provided by MySQL. Global
means that those privileges apply to the MySQL server in general
(like the Shutdown
privilege), or to all
databases on the server (like the SELECT
privilege).
Privileges not assigned to the user are listed under Available Privileges. You can remove privileges by selecting them in the left box, and then clicking the button. Removed privileges will appear in the Available Privileges box. You can assign privileges by selecting them in the Available Privileges box, and then clicking the button.
This tab allows you to grant privileges on a schema level. In MySQL, a schema is the same as a database. To grant privileges for a specific database, click on the database name in the left box. (You cannot select more than one database at a time.)
You may use wild cards to grant privileges on a number of schemata
simultaneously. Use “%
” to replace
multiple characters and “_
” to
replace a single character. To use this feature, right click any
database name in the schemata list, choose the option from the pop-up menu and enter the
pattern you wish to match. Entering m%
for
example, will add the entry, m%
to the schemata
list. Highlight this entry and any privileges granted will be
granted for all schemata starting with the letter
“m
”.
This pop-up menu option is only available under Windows.
If wild card characters appear in schemata names, you may escape
them using the “\
” character.
Privileges not assigned to the user are listed under Available Privileges. You can remove privileges by selecting them in the left box, and then clicking the button. Removed privileges will appear in the Available Privileges box. You can assign privileges by selecting them in the Available Privileges box, and then clicking the button.
The Schema Privileges tab only displays
privileges that have been granted for a specific schema. No schema
privileges will show in the Assigned
Privileges column for users with global privileges. For
example, the Assigned Privileges column will
be blank for a root
user with global privileges
on all schemata. Likewise, the Assigned
Privileges column will be blank for a user with a
global SELECT
privilege, unless
SELECT
has also specifically been granted for
the currently selected schema.
This tab is available only if the Show Schema Object Privileges checkbox in the Administrator section of the Options dialog has been checked. It allows you to grant privileges on a table level or on a column level.
To access the tables, double click on the schema (database) name containing that table. You will not see and cannot grant privileges on a schema level in this tab; if you want to do that, use the Global Privileges tab instead. Also, you cannot select more than one table at a time.
If you want to grant privileges on a column level, double click on the table name; this will give you access to its columns. You cannot select more than one column at a time.
Privileges not assigned to the user are listed under Available Privileges. You can remove privileges by selecting them in the left box, and then clicking the button. Removed privileges will appear in the Available Privileges box. You can assign privileges by selecting them in the Available Privileges box, and then clicking the button.
This tab allows you to limit a user's resources. You may set limits for the following items:
max_questions – The number of questions allowed per hour
max_update – The number of updates allowed per hour
max_connections – The number of connections allowed per hour
max_user_connections – The number of simultaneous connections allowed
This option is only available under Windows
The default value for each of these settings is
0
, indicating no restrictions whatsoever.