Identifying what accounts currently exist

To see what accounts currently exist for your database, you need an account that has the necessary permissions. Try to execute the following query on a database named mysql:

SELECT * FROM user

You should get a list of all the accounts. However, if you’re accessing MySQL through your company or a Web hosting company, you probably don’t have the necessary permissions. In that case, you might get an error message like this:

No Database Selected

This message means that your account is not allowed to select the mysql database. Or you might get an error message saying that you don’t have SELECT permission. Even though this message is annoying, it’s a sign that the company has good security measures in place. However, it also means that you can’t see what privileges your account has. You must ask your MySQL administrator or try to figure it out yourself by trying queries and seeing whether you’re allowed to execute them.

The MySQL security database

When MySQL is installed, it automatically creates a database called mysql. All the information used to protect your data is stored in this database, including account names, hostnames, passwords, and permissions.
Permissions are stored in columns. The format of each column name is permission_priv. For instance, the column containing ALTER permissions is named alter_ priv. The value in each permission column is Y or N, meaning yes or no. So, for instance, in the user table (described in the following list), there would be a row for an account and a column for alter_priv. If the account field for alter_priv contains Y, the account can be used to execute an ALTER query. If alter_ priv contains N, the account doesn’t have permission to execute an ALTER query. The mysql database contains the following tables that store permissions:

• user table: This table stores permissions that apply to all the databases and tables. It contains a row for each valid account that includes the column’s user name, hostname, and password. The MySQL server will reject a connection for an account that does not exist in this table.
• db table: This table stores permissions that apply to a particular database. It contains a row for the database, which gives permissions to an account name and a hostname. The account must exist in the user table for the permissions to be granted. Permissions that are given in the user table overrule permissions in this table. For instance, if the user table has a row for the account designer that gives INSERT privileges, designer can insert into all the databases. If a row in the db table shows N for INSERT for the designer account in the PetCatalog database, the user table overrules it, and designer can insert in the PetCatalog database.
• host table: This table controls access to a database depending on the host. The host table works with the db table. If a row in the db table has an empty field for the host, MySQL checks the host table to see whether the db has a row there. In this way, you can allow access to a db from some hosts but not from others. For instance, suppose you have two databases: db1 and db2. The db1 database has sensitive information, so you want only certain people to see it. The db2 database has information that you want everyone to see. If you have a row in the db table for db1 with a blank host field, you can have two rows for db1 in the host table. One row can give all permissions to users connecting from a specific host, whereas another row can deny privileges to users connecting from any other host.
• tables_priv table: This table stores permissions that apply to specific tables.
• columns_priv table: This table stores permissions that apply to specific columns.

You can see and change the tables in mysql directly if you’re using an account that has the necessary permissions. You can use SQL queries such as SELECT, INSERT, and UPDATE. If you’re accessing MySQL through your employer, a client, or a Web hosting company, it is unlikely that you will be given an account that has the necessary permissions.

Setting Up MySQL Accounts

An account is identified by the account name and the name of the computer allowed to access MySQL using this account. When you create a new account, you specify it as accountname@hostname. You can specify a password when you create an account or you can add a password later. You can set up permissions when you create an account or add permissions later. All the account information is stored in a database named mysql that is automatically created when MySQL is installed. To add a new account or change any account information, you must use an account that has the proper permissions on the mysql database.
You need at least one account to access the MySQL server. When MySQL is installed, it automatically sets up some accounts, including an account called root that has all permissions. If you have MySQL access through a company Web site or a Web hosting company, the MySQL administrator for the company should give you the account; the account is probably not named root, and it might or might not have all permissions.
In the rest of this section, I describe how to add and delete accounts and change passwords and permissions for accounts. If you have an account that you received from your company IT department or from a Web hosting company, you might receive an error when you try to send any or some of the GRANT or REVOKE queries described. If your account is restricted from performing any of the necessary queries, you need to request an account with more permissions or ask the MySQL administrator to add a new account or make the changes you need.

Taking a look at account permissions

MySQL uses account permissions to specify who can do what. Anyone using a valid account can connect to the MySQL server, but he or she can only do those things that are allowed by the permissions for the account. For example, an account might be set up so that users can select data but cannot insert or update data.
Permissions can be granted for particular databases, tables, or columns. For instance, an account can be set up that allows the user to select data from all the tables in the database but insert data into only one table and update only a single column in a specific table.
Permissions are added by using the GRANT query and removed by using the REVOKE query. The GRANT or REVOKE query must be sent using an account that has permission to execute GRANT or REVOKE statements in the database. If you attempt to send a GRANT query or a REVOKE query using an account without grant permission, you get an error message. For instance, if you try to grant permission to use a select query, and you send the query using an account that does not have permission to grant permissions, you might see the following error message: grant command denied Permissions can be granted or removed individually or all at once.
Permission &Description
ALL All permissions
ALTER Can alter the structure of tables
CREATE Can create new databases or tables
DELETE Can delete rows in tables
DROP Can drop databases or tables
FILE Can read and write files on the server
GRANT Can change the permissions on a MySQL account
INSERT Can insert new rows into tables
SELECT Can read data from tables
SHUTDOWN Can shut down the MySQL server
UPDATE Can change data in a table
USAGE No permissions
Granting ALL is not a good idea because it includes permissions for administrative operations, such as shutting down the MySQL server. You are unlikely to want anyone other than yourself to have such sweeping privileges.

Finding out about passwords

A password is set up for every account. If no password is provided for the account, the password is blank, which means that no password is required. MySQL doesn’t have any limit for the length of a password, but sometimes other software on your system limits the length to eight characters. If so, any characters after eight are dropped.
For extra security, MySQL encrypts passwords before it stores them. That means passwords are not stored in the recognizable characters that you entered. This security measure ensures that no one can look at the stored passwords and see what they are.
Unfortunately, some bad people out there might try to access your data by guessing your password. They use software that tries to connect rapidly in succession using different passwords — a practice called cracking. The following are some recommendations for choosing a password that is as difficult to crack as possible:

• Use six to eight characters.
• Include one or more of each of the following — uppercase letter, lowercase letter, number, and punctuation mark.
• Do not use your account name or any variation of your account name.
• Do not include any word in a dictionary, including foreign language dictionaries.
• Do not include a name.
• Do not use a phone number or a date.

A good password is hard to guess and easy to remember. If it’s too hard to remember, you might need to write it down, which defeats the purpose of having a password. One way to create a good password is to use the first characters of a favorite phrase. For instance, you could use the phrase “All for one! One for all!” to make this password:
Afo!Ofa!
This password doesn’t include any numbers, but you can fix that by using the
numeral 4 instead of the letter f. Then your password is
A4o!O4a!
Or you could use the number 1 instead of the letter o to represent one.
Then the password is
A41!14a!
This password is definitely hard to guess. Other ways to incorporate numbers into your passwords include substituting 1 (one) for the letter l or substituting 0 (zero) for the letter o.

Sun Asserts MySQL Will Remain Open Source

By Brian Prince

Marten Mickos, head of Sun Microsystems' database group, says MySQL will remain an open-source product.

After several days of blog posts and headlines, the dust that kicked up over Sun Microsystems' commitment to keeping MySQL open source seems to have settled.

At the center of the controversy were reports of plans to close-source backup features in Version 6.0 of the MySQL database, making them available only to subscription-paying users of its enterprise version. In an effort to clear the air, Marten Mickos, senior vice president of Sun's Database Group, responded to blog posts on Slashdot and elsewhere.

In an interview with eWEEK, he stated emphatically that Sun will not withhold or close-source any features that would make the MySQL community server less functional for users.

The core backup functionality and a backup API in Version 6.0 will be available to users of both the community and enterprise versions of the database, he said. However, the company plans to develop high-end add-ons such as encryption and compression solely for its MySQL Enterprise subscription customers, Mickos said.

Mickos calls MySQL the Ferrari of databases. Read why here.

“It is not a question of close-sourcing any existing code, nor anything in the core server,” Mickos said. “Everything MySQL has released under the GPL license continues to be under GPL, and the core MySQL server will always be under GPL, or some other free and open-source software [FOSS] license.”

Mickos said it has not been decided yet what license the add-ons will be released under. Sun, he added, was not part of the initial plans regarding the backup functionality—those discussions were taking place well before the acquisition by Sun earlier this year.

“At all times, because the main backup functionality goes into the core server under GPL, anyone can use the API and build their own add-ons or other modifications,” he said. “We are also offering them a convenient, low-cost option through our add-ons, but not forcing them or restricting them from building their own.”

Several MySQL users objected on blogs last week to talk of making key functionality available only to paying customers. It wasn’t the first time open-source advocates took MySQL to task for such moves; there was a similar, albeit more subdued, reaction when MySQL AB announced it was removing Enterprise source tarballs from ftp.mysql.com last August, analyst Matt Aslett noted.

“MySQL has been quick to respond to the criticism this time, and has been very open in explaining its strategy on user blogs, and the company does appear to have been able to stop the resentment from growing,” said Aslett, who is with The 451 Group. “It would be prudent for the company to prioritize deciding on which licenses it will use for the new functionality so that it can explain its strategy properly before doubt continues to spread.”

Mickos admitted the initial information was released awkwardly by the company and said he understood why some members of the open-source community were upset at first. This is why the company sought to aggressively address some of their concerns, he said.

“Open-source software is really just now reaching the radar of mainstream IT,” Mickos said. “There is still work to be done in formulating business models around open source, and so some degree of experimentation is essential. Keep in mind, open source itself began as an experiment in the software industry. So all vendors should not be afraid to continue to experiment both in software and in business models.”

Understanding account names and hostnames

Together, the account name and hostname (the name of the computer that is 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 permissions. However, you cannot have two accounts with the same name and the same hostname.
The MySQL server will accept connections from a MySQL account only when it is connecting from hostname. When you build the GRANT or REVOKE query (which I describe later in this chapter), 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 user name (also sometimes called the login name). If you’re using an administrative MySQL account named root, it is not related to the Unix or Linux root login name. Changing the MySQL login name does not affect the Unix, Linux, or Windows login name — and vice versa.
MySQL account names and hostnames are defined as follows:

• 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 cannot 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 will be valid for that account. A user could use any account name to connect to your database, given that the user is connecting from a hostname that is allowed to connect to the blank account name and uses the correct password, if 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, it 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 using the account named george can connect to the MySQL server from any computer.
• The hostname can be blank. A blank hostname is the same as using % for the hostname.

An account with a blank account name and a blank hostname is possible. Such an account would allow anyone to connect to the MySQL server by using any account name from any computer. An account with a blank name and a percent sign (%) for the hostname is the same thing. It is unlikely that you would want such an account. Such 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 may be installed without a password. Anyone who is 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 is not secure. If you’re the MySQL administrator, you should add a password to this account immediately.)
On some operating systems, additional accounts besides root@localhost are automatically installed. For instance, on Windows, an account called root@% might be installed with no password protection. This root account with all privileges can be used by anyone from any machine. You should remove this account immediately or, at the very least, give it a password.

How to Control Access to Your MySQL Data?

You need to control access to the information in your database. You need to decide who can see the data and who can change it. Imagine what would happen if your competitors could change the information in your online product catalog or copy your list of customers — you’d be out of business in no time flat. Clearly, you need to guard your data. MySQL provides a security system for protecting your data. No one can access the data in your database without an account. Each MySQL account has the following attributes:

• A name
• A hostname — the machine from which the account can access the MySQL server
• A password
• A set of permissions

To access your data, someone must use a valid account name and know the password associated with that account. In addition, that person must be connecting from a computer that is permitted to connect to your database via that specific account.
After the user is granted access to the database, what he or she can do to the data depends on what permissions have been set for the account. Each account is either allowed or not allowed to perform an operation in your database, such as SELECT, DELETE, INSERT, CREATE, or DROP. The settings that specify what an account can do are privileges, or permissions. You can set up an account with all permissions, no permissions, or anything in between. For instance, for an online product catalog, you want the customer to be able to see the information in the catalog but not be able to change it. When a user attempts to connect to MySQL and execute a query, MySQL controls access to the data in two stages:

1. Connection verification: MySQL checks the validity of the account name and password and checks whether the connection is coming from a host that is allowed to connect to the MySQL server by using the specified account. If everything checks out, MySQL accepts the connection.
2. Request verification: After MySQL accepts the connection, it checks whether the account has the necessary permissions to execute the specified query. If it does, MySQL executes the query. Any query that you send to MySQL can fail either because the connection is rejected in the first step or because the query is not permitted in the second step. An error message is returned to help you identify the source of the problem.