MySQL Account Names and Hostnames

Together, the MySQL account name and hostname (the name of the computer that’s authorized to connect to the database) identify a unique account. Two accounts with the same name but different hostnames can exist and can have different passwords and privileges. However, you can’t have two accounts with the same name and the same hostname.

The MySQL server accepts connections from a MySQL account only when that account is connecting from hostname. When you build the GRANT or REVOKE statement, you identify the MySQL account by using both the account name and the hostname in the following format: accountname@hostname (for instance, root@localhost).

The MySQL account name is completely unrelated in any way to the Unix, Linux, or Windows username (also sometimes called the login name). If you’re using an administrative MySQL account named root, that account is not related to the Unix or Linux root login name. Changing the MySQL account name doesn’t affect the Unix, Linux, or Windows login name — and vice versa.

MySQL account names and hostnames have the following characteristics:

  • An account name can be up to 16 characters long. You can use special characters in account names, such as a space or a hyphen (-). However, you can’t use wildcards in the account name.

  • An account name can be blank. If an account exists in MySQL with a blank account name, any account name is valid for that account. A user can use any account name to connect to your database if the user is connecting from a hostname that’s allowed to connect to the blank account name and uses the correct password (if a password is required).

    You can use an account with a blank name to allow anonymous users to connect to your database.

  • The hostname can be a name or an IP address. For example, the hostname can be a name, such as thor.mycompany.com, or an IP (Internet protocol) address, such as 192.163.2.33. The machine on which the MySQL server is installed is localhost.

  • The hostname can contain wildcards. You can use a percent sign (%) as a wildcard; % matches any hostname. If you add an account for george@%, someone who uses the account named george can connect to the MySQL server from any computer.

  • The hostname can be blank. Leaving the hostname blank is the same as using % for the hostname.

You can create an account with both a blank account name and a blank hostname (or a percent sign — % — for the hostname). Such an account would allow anyone to connect to the MySQL server by using any account name from any computer. But you probably don’t want such an account. This kind of an account is sometimes installed when MySQL is installed, but it’s given no privileges, so it can’t do anything.

When MySQL is installed, it automatically installs an account with all privileges: root@localhost. Depending on your operating system, this account might be installed without a password. Anyone who’s logged in to the computer on which MySQL is installed can access MySQL and do anything to it by using the account named root.

(Of course, root is a well-known account name, so this account isn’t secure. If you’re the MySQL administrator, add a password to this account immediately.)

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com