One of MySQL's strengths is its advanced user privilege system. In this chapter, we'll discuss user account creation, the different privileges available, and how these privileges are represented within MySQL tables. We'll cover the following:
• Creating user accounts with GRANT and REVOKE
• Privilege levels
• Understanding the privilege tables
User privileges are given with the GRANT statement and are taken away with the REVOKE statement. These are standard SQL statements that you can execute like any other statement in this book. All MySQL user information and user privileges are eventually stored in a MySQL database, just like your own applications.
To run the statements in this section, you will yourself need to have a certain level of privilege. If you installed MySQL, you will have access to the root account and, therefore, will have the appropriate level of privilege. If you are using MySQL on a machine controlled by somebody else (such as at work or at an ISP), you may not have the appropriate privilege level to run these queries. If you do not, you will receive an error message along these lines:
ERROR 1045: Access denied for user: '[email protected]' (Using password: YES)
We'll begin by looking at the GRANT statement. This statement is used to create user accounts and give users access to databases, tables, and functions. Let's look at a simple example first, as follows:
grant usage
on *
to luke@localhost identified by 'password';
This statement creates an account for the user called luke when he is trying to log in from localhost. It sets up a password for him (the highly secure password password—obviously, you should use something better!). The word usage indicates the privilege we are giving to luke. It means that he can log in, but not do anything else. The ON clause is used to specify what things we are granting privileges on. Because we are only granting the ability to log in in this case, the ON clause is not really relevant here.
The general form of the GRANT statement from the MySQL manual is
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
The GRANT clause lists the privileges we are granting. We will look at what these are in the next section. Some privileges are global (that is, they apply across all databases), and some apply only to certain items (databases, tables, or columns).
The ON clause specifies the items we are granting privileges on. This can be a named table or a named database with all its tables (dbname.*). We can also specify *.*, which means all databases and all tables. If we specify *, the privileges are granted on the currently selected database. If no database is selected, the privileges are granted as if we had specified *.* in this clause.
The TO clause is used to specify the user we are granting privileges to. If this user already has an account, the new privileges will be added to it. If not, an account will be created for the user. We can specify more than one user in this clause. We can also specify the hosts they can log in from, for example, fred@localhost. If you are having trouble logging in as a newly created user, try adding the hostname you are logging in from to the GRANT statement. The MySQL username need not be the same as a user's operating system username. Usernames can be up to 16 characters long.
The IDENTIFIED BY clause sets the password for a new user or resets it for an existing user.
Users can change their passwords by typing
set password = password('newpassword'),
You can change a user's password by typing, for example,
set password for fred@localhost = password('newpassword'),
You need to have access to the database called mysql to do this.
The clause WITH GRANT OPTION is a special privilege that allows the user to grant privileges. If you found that you could not grant any privileges to users, this is the privilege you yourself were missing. Also, you may not grant a privilege to other users that you do not have yourself.
The WITH clause can also be used to limit the number of queries, updates, or connections that a user can make in an hour. The default value for these is zero, meaning no limitation.
The REQUIRE clause allows you specify that a user must connect using a secure connection. To use this, you will need to configure MySQL appropriately. We will discuss this further in Chapter 15, “Securing Your MySQL Installation.”
The privileges we can grant using the GRANT statement can be divided into two basic categories: user-level privileges and administrator-level privileges.
The user-level privileges are shown in Table 11.1.
Table 11.1. User-Level Privileges
|
Privilege |
Meaning |
|---|---|
|
|
User can create tables. |
|
|
User can create temporary tables. |
|
|
User can delete rows. |
|
|
User can execute procedures. |
|
|
User can create indexes. |
|
|
User can insert rows. |
|
|
User can lock tables. |
|
|
User can select rows. |
|
|
User can execute a |
|
|
User can update rows. |
|
|
User can log in, but cannot do anything else. |
Privileges that should be granted only to administrators are shown in Table 11.2. Some of these can be granted to users with caution at your discretion, but they should not be granted to users by default.
Table 11.2. Administrator-Level Privileges
|
Privilege |
Meaning |
|---|---|
|
|
User has all the privileges except |
|
|
User can alter tables. You may give this to some power users, but proceed with caution because it may be used to change the privilege tables. |
|
|
User can drop tables. You may give this to trusted users. |
|
|
User can load data from a file. Again, you may give this to trusted users. Beware of users trying to load arbitrary files, such as /etc/passwd or similar files! |
|
|
User can show full process list—that is, see all the processes that MySQL is executing. |
|
|
User can use the |
|
|
User can check where the masters and slaves are. |
|
|
Special privilege designed for the special replication user on the slave. See Chapter 16 for more details. |
|
|
User can run |
|
|
User can connect even if MySQL has its maximum number of connections and can execute the commands |
|
|
User can pass on any privileges he has. |
There is one other privilege called REFERENCES. This is reserved for future use, and although you can grant it, at present, it doesn't do anything.
Four sets of privileges are granted with the GRANT statement:
• Global privileges apply across all databases. These are specified with *.* in a GRANT statement. For example:
grant all on *.* to fred;
• Database privileges apply to one particular database. These are granted with database.* in a GRANT statement:
grant all on employee.* to fred;
• Table privileges apply to a single table. These are granted by naming a specific table in the GRANT statement:
grant select on department to fred;
• Column privileges apply to a single column. These are specified in the GRANT clause of the GRANT statement. For example:
grant select (employeeID) on employee to fred;
When trying to work out whether a user has a privilege to do a particular task, MySQL will look at the combination of that user's global privileges plus his database privileges plus his table privileges plus his column privileges ORed together.
The REVOKE statement is the opposite of GRANT. It is used to take privileges away from a user. For example:
revoke all on employee.* from fred;
The general form of the revoke statement is as follows:
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
As you can see, it has basically the same clauses as the GRANT statement and can be used to revoke the corresponding privileges.
The underlying data that is changed by the GRANT and REVOKE statements is stored in the database called mysql. Rather than using GRANT and REVOKE, you can modify the tables in this database directly if you know what you are doing. You can also read the data in them to help you resolve any privilege problems that may crop up.
If you modify these tables directly, you will need to execute the statement
flush privileges;
before the changes will take effect.
There are six tables in the mysql database:
• user
• db
• host
• tables_priv
• columns_priv
• func
Only the first five have to do with user privileges. (The func table stores user-defined function information that is beyond the scope of this book.)
The first three tables—user, db, and host—are used to determine whether you are allowed to connect to the database. All five of the privilege tables are used to determine whether you have the right to execute any given command.
The user table contains information about a user's global privilege set.
The user table contains the following columns:
• Scope columns These are used to determine when a row is relevant. These are the scope columns:
Host: Where the user is connecting from
User: The username
Password: The user's password, as encoded by the PASSWORD() function
• Privilege columns Each one corresponds to one of the global privileges. They can have the value Y (if the user has the global privilege) or N (if the user does not). These are the privilege columns:
Select_priv
Insert_priv
Update_priv
Delete_priv
Index_priv
Alter_priv
Create_priv
Drop_priv
Grant_priv
References_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Show_db_priv
Super_priv
Create_tmp_table_priv
Lock_tables_priv
Execute_priv
Repl_slave_priv
Repl_client_priv
• Secure connection columns These represent the information from the REQUIRE clause of the grant statement. These are the secure connection columns:
ssl_type
ssl_cypher
x509_issuer
x509_subject
• Resource limitation columns These represent any limitations on user resource use you may have specified at the end of a GRANT statement. These are the resource limitation columns:
max_questions
max_updates
max_connections
The database table stores a user's privileges for particular databases. It contains the following columns:
• Scope columns MySQL uses these to determine when a row of privileges is relevant. If you have different rules for different hosts, leave the host field blank and then create a corresponding set of rows in the host table to give more information. These are the scope columns:
Host
Db
User
Privilege columns These specify whether the combination of Host, Db, and User have each of the listed privileges. Again these columns can contain the values Y or N. These are the privilege columns:
Select_priv
Insert_priv
Update_priv
Delete_priv
Index_priv
Alter_priv
Create_priv
Drop_priv
Grant_priv
Create_tmp_table_priv
Lock_tables_priv
MySQL consults the host table when it finds a blank host entry in the db table. You will not get this effect from a GRANT statement, but you can set it up manually. This table contains the following columns:
• Scope columns MySQL uses these to determine when a row of privileges is relevant. Each row here gives information for a single database accessed from a single host. These are the scope columns:
Host
Db
• Privilege columns These specify whether the combination of Host and Db have each of the listed privileges. Again, these columns can contain the values Y or N. These are the privilege columns:
Select_priv
Insert_priv
Update_priv
Delete_priv
Index_priv
Alter_priv
Create_priv
Drop_priv
Grant_priv
Create_tmp_table_priv
Lock_tables_priv
The tables_priv table expresses user privileges that relate to individual tables. It contains the following columns:
• Scope columns These work as they do in the previous three tables. Here we also have the Table_name privilege, which lists the specific table that a grant applies to. These are the scope columns:
Host
Db
User
Table_name
• Grant columns These store information about who granted this privilege and when. These are the grant columns:
Grantor
Timestamp
• The Table_priv column This is a set that determines what privileges the Host/Db/User has on the table listed in Table_name. It can contain the following values: Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, and Alter.
• The Column_priv column This is a set that tells us what privileges the user has over all the columns in this table. It can contain the following values: Select, Insert, Update, and References. If a privilege is missing here, MySQL can look in the columns_priv table for more detailed information about what is and what is not allowed with this table's columns.
The columns_priv table expresses user privileges relating to individual columns. It contains the following columns:
• Scope columns These determine when a row in this table is relevant. These are the scope columns:
Host
Db
User
Table_name
Column_name
• The Column_priv column This is a set that determines which privileges have been granted to the combination outlined by the scope column. It can contain the following values: Select, Insert, Update, and References.
• The Timestamp column This column tells us when this privilege was granted.
• The GRANT statement is used to grant privileges to a user or to create a user account. It has the following format:
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
• The REVOKE statement is used to take privileges away from a user. It has the following format:
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
• Individual privileges can be granted to users.
• These are the user privileges:
CREATE
CREATE TEMPORARY TABLES
DELETE
EXECUTE
INDEX
INSERT
LOCK TABLES
SELECT
SHOW DATABASES
UPDATE
USAGE
• These are the administrator privileges:
ALL
ALTER
DROP
FILE
PROCESS
RELOAD
REPLICATION CLIENT
REPLICATION SLAVE
SHUTDOWN
SUPER
WITH GRANT OPTION
• MySQL's account and privilege information is stored in the database called mysql.
• There are five privilege tables.
• The user table stores usernames, passwords, and global privilege information.
• The db table stores information about privileges for specific databases.
• The host table stores information about which databases can be accessed from which hosts.
• The tables_priv table stores information about table-level privileges.
• The columns_priv table stores information about column-level privileges.
In the next chapter, “Configuring MySQL,” we will discuss MySQL's plethora of configuration options.