Chapter 4. Security and User Statements and Functions

Part 2: SQL Statements and Functions

This part of the book is a complete reference for the version of the
SQL language used by MySQL. It divides the SQL statements and functions by
the basic functions (scheme design, data manipulation, replication, etc.).
Examples use the mysql command-line client, but they are equally
valid when issued from the programming APIs discussed in Part 4.

Some of the chapters in this part start with a list of statements
grouped by type, as a quick reference. The statements are then listed in
alphabetical order. For the more complex statements, to simplify their
presentation, I've broken the syntax into several sections according to the different
uses of the statement.

Here are some general elements of MySQL's SQL syntax:

• SQL statements may span multiple lines, but they must end with
  either a semicolon or
  \G, unless another character is specified with
  DELIMITER.

• When values are enclosed in parentheses, multiple values can
  usually be specified, separated
  by commas.

• Strings and dates must be specified within single or double
  quotes, unless a date is given as a numeric and is part of a date
  calculation.

• Elements of a statement's syntax are case-insensitive. However,
  on Unix-type systems, database and table names, as well as filenames,
  are case-sensitive.

The MySQL statements, clauses, and functions explained in Chapters
Chapter 4 through
Chapter 14 are
grouped in each chapter, first by statements and clauses, then by
functions. They are listed alphabetically within each group. Each
statement is given with its syntax and an explanation. Optional clauses
and flags are shown in square brackets. Particular components, such as a
database or table name, are shown in italics. The vertical bar is used to
separate alternative choices and is not part of the statement
syntax.

Some statements have alternative syntax structures. These
alternatives are usually shown in complete form. The curly braces indicate
that one of the choices is required. Examples show how a statement and the
various clauses may be used for almost all statements.

To save space, some of the examples are shown without their results.
Occasionally, when the results are shown, the typical ASCII table format
is not shown because the statement is executed with a
\G ending instead of the usual semicolon. In order to
focus on the particulars of the statements and clauses, the statements are
fairly straightforward and do not make much use of the many built-in
functions available with MySQL. Explanations of any functions used,
though, can be found in other chapters.

Chapter 4. Security
and User Statements and
Functions

User access and privileges can be global (i.e., apply to all
databases on the server), or they can be database-specific, table-specific, or
column-specific. In version 5 of MySQL, users can also be limited to
particular functions and procedures.

In addition to security-related SQL statements, users can be limited
in their use of MySQL resources in order to prevent the monopolization of
resources and the indirect denial of service to other users. Thus, you can
limit the number of connections or the maximum resources per hour for a
user.

The primary information regarding user access and privileges is
stored in a set of regular MyISAM tables, known as the grant tables, that reside in the
mysql database on the server. The tables are:

user

Global privileges

db

Database-specific privileges

tables_priv

Table-specific privileges

columns_priv

Column-specific privileges

Several other tables provide fine-tuning for user access and security.
Execute SHOW TABLES FROM mysql; to get a list on your
server. You can manipulate the data in these tables directly with standard
SQL statements, such as INSERT,
UPDATE, and DELETE, followed by the
FLUSH PRIVILEGES statement to update the server's cache.
However, it's recommended that you use specialized SQL statements to manage
users and assign access rights:

CREATE USER

To create new users

GRANT

To create a user account, assigning privileges for a new user
account, or assigning privileges
to an existing user

REVOKE

To remove privileges

RENAME USER

To change a user's name

SET PASSWORD

To change a password

DROP USER

To delete a user's account

All of these statements are described in this chapter. This chapter
also lists and explains MySQL functions related to user maintenance and
several related to database and
network security.

4.1. Statements and Functions

The following is a list of security and user statements that are covered in this chapter:

[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].

The following related functions are covered in this chapter as well. They are
explained in detail after the SQL
statements:

[click here], [click here], [click here], [click here], [click here], [click here],[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].