Chapter 11. User Administration

Table of Contents

11.1. Introduction
11.2. User Accounts
11.3. User Information
11.4. Global Privileges
11.5. Schema Privileges
11.6. Table Column Privileges

11.1. Introduction

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.

11.2. User Accounts

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 (user@host ), but rather with their usernames only. If double clicking on a username 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.

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'.

Figure 11.1. User accounts

User accounts

For example, you might have a user called superuser. If double clicking on that username 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 username, or on one of the subcategories a user might have, provides access to the following commands:

  • Add a new User: Selecting this command adds a new user with the default name New User to the user list. You will have to fill in at least a username 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.

  • Add Host from which the User can connect: Allows you to specify a host from which the user can connect. This command is unavailable if you highlight a subcategory.

  • Remove Host from which the User can connect: 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).

  • Clone User: Makes a copy of the selected user, including all subcategories. The new user is called 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.

  • Delete User: Allows you to delete the selected user. You will be prompted to confirm that you really want to delete that user.

  • Refresh User List: 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.

11.3. User Information

The fields in this tab are unavailable unless you either click on a username in the User Accounts area of the sidebar, or create a new user in that sidebar or using the New User 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 Apply Changes button. If you don't want to save your changes, click the Discard Changes button.

Figure 11.2. User Information tab

User Information tab

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: Username that is specified when connecting to the MySQL server. Note that this username has nothing to do with usernames you might use elsewhere on your operating system. Using special characters or spaces for a username 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 non-trivial passwords. A very trivial password might be abcde, while a non-trivial password might be lEtusMak3iThartdoGesz. Note that it is not mandatory to specify a password, although it is highly recommendable to 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 username 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 Load from Disk button. The icon you use must be exactly 48 by 48 pixels in size, and its file format must be PNG (portable network graphics).

11.4. Global Privileges

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.

11.5. Schema Privileges

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.)

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.

11.6. Table Column Privileges

This tab is available only if the Show Table/Column Privileges checkbox in the Admin istrator 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.