Chapter 13. User Administration

Table of Contents

13.1. Introduction
13.2. User Accounts
13.3. User Information
13.4. Global Privileges
13.5. Schema Privileges
13.6. Table Column Privileges
13.7. Resources

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

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

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 13.1. User Accounts

User accounts

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:

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

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

    Note

    Cloning is only available on Windows.

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

13.3. User Information

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

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

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

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 Add Schema with Wildcards 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”.

Note

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.

13.6. Table Column Privileges

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.

13.7. Resources

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

    Note

    This option is only available under Windows

The default value for each of these settings is 0, indicating no restrictions whatsoever.