High Performance MySQL [Electronic resources] نسخه متنی

10.2 The Grant Tables

MySQL's
grant tables are the heart of its
security system. The information in these tables determines the
privileges of every user and host that connects to MySQL. By
correctly manipulating the records, you can give users exactly the
permissions they need (and no more). Incorrectly manipulating them
can open up your server to the possibility of abuse and damage.

Let's take a brief look at the five grant tables
before really digging in. We've included them here
in the order that MySQL consults them. You'll see
why that becomes important in a minute.

user

The user table contains the global privileges and
encrypted passwords. It is responsible for determining which hosts
and users may connect to the server.

host

The host table assigns privileges on a per-host
basis, regardless of the user. When deciding to accept or reject a
connection, MySQL consults the user table as noted
earlier. Though we list it as a grant table, the
host is never modified through use of the
GRANT or REVOKE commands. You
can add and remove entries manually, however.

The db table sets database-level privileges.

tables_priv

The
tables_priv table controls table-specific
privileges.

columns_priv

Records in the
columns_priv table specify a user's
privileges for a single column of a single table in a particular
database.

10.2.1 Privilege Checks

For each query issued, MySQL checks to
make sure the user has the required privileges to perform the query.
In doing so, it consults each of the tables in a specific order.
Privileges set in one table may be overridden by a table checked
later.

In other words, the privilege system works through inheritance.
Privileges granted in the user table are passed
down through all the other checks. If there are no matching records
in any of the other tables, the original privileges set forth in the
user table apply.

MySQL uses different criteria when checking each grant table. Records
in the host table, for example, are matched based
on the host from which the user has connected and the name of the
database that the query will read from or write to. Records in the
db table, on the other hand, match based on the
host, database, and username. Table 10-2 summarizes
the fields used for matching records in each of the grant tables.

Table 10-2. Fields used for matching grant table records
Table	Password	User	Host	Db	Table	Column
user
host
db
tables_priv
columns_priv

Let's look at the schema for each table as well as
the privileges each affects.

10.2.2 The user Table

MySQL's
user table contains authentication information
about users as well as their global privileges. It contains fields
for the username, hostname, and password. The remainder of the fields
represent each of the privileges, which are all off by default. As
you'll see, many of the other tables also contain
the Host and User fields as
well as a subset of the privilege fields that are present in the
user table, but only the user
table contains passwords. In a way, it is the
/etc/passwd of MySQL.

Even if a user has no global privileges at all, there must be a
record in the user table for her, if she is to
issue a command successfully. See the Section 10.3.1, later in this chapter, for
an example.

In the meantime, let's have a look at the fields in
the user table:

mysql> DESCRIBE user;
+-----------------------+-------------------------+------+-----+---------+-------+
| Field                 | Type                    | Null | Key | Default | Extra |
+-----------------------+-------------------------+------+-----+---------+-------+
| Host                  | varchar(60)             |      | PRI |         |       |
| User                  | varchar(16)             |      | PRI |         |       |
| Password              | varchar(45)             |      |     |         |       |
| Select_priv           | enum('N','Y')           |      |     | N       |       |
| Insert_priv           | enum('N','Y')           |      |     | N       |       |
| Update_priv           | enum('N','Y')           |      |     | N       |       |
| Delete_priv           | enum('N','Y')           |      |     | N       |       |
| Create_priv           | enum('N','Y')           |      |     | N       |       |
| Drop_priv             | enum('N','Y')           |      |     | N       |       |
| Reload_priv           | enum('N','Y')           |      |     | N       |       |
| Shutdown_priv         | enum('N','Y')           |      |     | N       |       |
| Process_priv          | enum('N','Y')           |      |     | N       |       |
| File_priv             | enum('N','Y')           |      |     | N       |       |
| Grant_priv            | enum('N','Y')           |      |     | N       |       |
| References_priv       | enum('N','Y')           |      |     | N       |       |
| Index_priv            | enum('N','Y')           |      |     | N       |       |
| Alter_priv            | enum('N','Y')           |      |     | N       |       |
| Show_db_priv          | enum('N','Y')           |      |     | N       |       |
| Super_priv            | enum('N','Y')           |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')           |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')           |      |     | N       |       |
| Execute_priv          | enum('N','Y')           |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')           |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')           |      |     | N       |       |
| ssl_type                  | enum(,'ANY','X509','SPECIFIED')    |         |       |
| ssl_cipher            | blob                    |      |     |         |       |
| x509_issuer           | blob                    |      |     |         |       |
| x509_subject          | blob                    |      |     |         |       |
| max_questions         | int(11) unsigned        |      |     | 0       |       |
| max_updates           | int(11) unsigned        |      |     | 0       |       |
| max_connections       | int(11) unsigned        |      |     | 0       |       |
+-----------------------+-------------------------+------+-----+---------+-------+

Password Security

Just
because MySQL passwords aren't stored in plain text,
you shouldn't be lax about password selection.
Anyone with the ability connect to your MySQL server can run a
brute-force attack against your server in an attempt to discover
passwords. A password such as fred or
database is worthless; either can be easily
guessed by automated software. It is best to invent a password that
isn't a real word.

Because choosing strong passwords is an important part of giving
users access to MySQL, here are a few guidelines for selecting and
keeping good passwords:

Have a minimum length

The longer a password is, the more difficult it will be to guess.

Require special characters

A password that includes nonalphanumeric characters such as
!@#$%^&* is more difficult to guess than one
composed of numbers and letters only. Substitute the at sign
(@) for the letter a. Add
punctuation. Be creative.

Change passwords

Once a password is set, many people have a tendency never to change
it. Often, a password may be assigned to an account that
doesn't even correspond to a real person. It might
belong to an application such as a web server, or middleware
application. Because of this, MySQL has no built-in password aging
mechanism, so you'll need to put a note on your
calendar or somehow automate the process of aging passwords.

It's important to note, though, that MySQL
doesn't provide any way for an administrator to
enforce good password standards. You can't link
MySQL against libcrack and demand that passwords
meet that criteria, no matter how cool that idea may be. Luckily,
most users can't change their own MySQL passwords,
so you don't have to worry about them switching to a
weak password at a later date, and as long as you (as the
administrator) choose a strong password for them, they should be all
right.

When a user first connects to MySQL, it checks the
user table to decide if the user is allowed to
connect and is who she says she is (the password check). But how
exactly does MySQL make those decisions?

Matching a username is a simple test of equality. If the username
exists in the table, it's a match. The same is true
of the password. Because all MySQL passwords are hashed using the
built-in PASSWORD( ) function, expect MySQL to do
something like this:

SELECT *
FROM user
WHERE User = 'username'
AND Password = PASSWORD('password')

However, this query could return multiple records. The
user table's primary key is
composed of the fields User and
Host, not just User, which
means a single user can have multiple entries in the
tableespecially if she is allowed to connect from several
specifically named hosts. MySQL must check all those records to see
which one matches.

Things get more interesting when you realize that the
Host field may contain any of the standard SQL
wildcard characters: _ (matches a single
character) and % (matches any number of
characters). What does MySQL do if the user jane
attempts to connect from the host
jane.example.com, and the user table contains
records for jane@jane.example.com as well as
jane@%.example.com?

10.2.2.1 Host matching

The
first rule you need to know about MySQL's privilege
system is this: the most specific match always wins. MySQL will
always prefer an exact match over one that uses a wildcard of any
sort.

MySQL accomplishes this by internally sorting the records in the
user table based on the Host
and User fieldsin that order. Hostnames and
IP addresses without wildcards come before those that contain them.

Given a list of host entries such as this:

localhost

jane.example.com

%.example.com

192.168.1.50

joe.example.com

192.168.2.0/255.255.255.0

MySQL sorts them in this order:

localhost

192.168.1.50

jane.example.com

joe.example.com

192.168.2.0/255.255.255.0

%.example.com

To clarify what "most specific"
means to MySQL, let's consider how MySQL will match
several username and hostname combinations. Assuming that the user
jane and the "any
user" (represented here as the absence of a
username) can connect from some of the various hosts listed earlier,
MySQL sorts the entries like this:

jane@jane.example.com

jane@joe.example.com

@localhost

@192.168.1.50

@jane.example.com

@joe.example.com

@%.example.com

jane@%.example.com

jane@%

When jane connects from
jane.example.com, she may have a different set
of privileges from when she connects from
joe.example.com. Other users connecting from
web.example.com will match the
%@%.example.com record and receive whatever
privileges have been granted in that row. When
jane connects from
web.example.com, she'll receive
the privileges granted to
jane@%.example.com.

10.2.3 The host Table

The
host table assigns database-level privileges
for users connecting from specific hosts (or groups of hosts).
Let's look at the table:

mysql> DESCRIBE host;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      |      | PRI |         |       |
| Db                    | char(64)      |      | PRI |         |       |
| Select_priv           | enum('N','Y') |      |     | N       |       |
| Insert_priv           | enum('N','Y') |      |     | N       |       |
| Update_priv           | enum('N','Y') |      |     | N       |       |
| Delete_priv           | enum('N','Y') |      |     | N       |       |
| Create_priv           | enum('N','Y') |      |     | N       |       |
| Drop_priv             | enum('N','Y') |      |     | N       |       |
| Grant_priv            | enum('N','Y') |      |     | N       |       |
| References_priv       | enum('N','Y') |      |     | N       |       |
| Index_priv            | enum('N','Y') |      |     | N       |       |
| Alter_priv            | enum('N','Y') |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y') |      |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+

With the exception of the Db field, this table is
a subset of the user table. It is missing all the
global privileges (such as the shutdown privilege), but all the
privileges that can be applied to a database objects are there. As
expected, they all default to No.

Records might appear in this table to enforce a rule that all
connections from hosts in the public.example.com
domain are forbidden from changing any data. You can also allow
anyone connecting from secure.example.com to
have full privileges on tables in the security
database.

10.2.4 The db Table

The
db table specifies database-level privileges
for a particular user and database:

mysql> DESCRIBE db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      |      | PRI |         |       |
| Db                    | char(64)      |      | PRI |         |       |
| User                  | char(16)      |      | PRI |         |       |
| Select_priv           | enum('N','Y') |      |     | N       |       |
| Insert_priv           | enum('N','Y') |      |     | N       |       |
| Update_priv           | enum('N','Y') |      |     | N       |       |
| Delete_priv           | enum('N','Y') |      |     | N       |       |
| Create_priv           | enum('N','Y') |      |     | N       |       |
| Drop_priv             | enum('N','Y') |      |     | N       |       |
| Grant_priv            | enum('N','Y') |      |     | N       |       |
| References_priv       | enum('N','Y') |      |     | N       |       |
| Index_priv            | enum('N','Y') |      |     | N       |       |
| Alter_priv            | enum('N','Y') |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y') |      |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+

This table is virtually identical to the host
table. The only difference is the addition of the
User field, which is needed in order to create
per-user privileges.

By making the appropriate entries in this table, you could ensure
that joe has full privileges on the
sales database when connecting from either
accounting.example.com or
cfo.example.com.

10.2.5 The tables_priv Table

Going a level deeper, the
tables_priv table controls table-level privileges
(those applied to all columns in a table) for a particular user:

mysql> DESCRIBE tables_priv;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| Host        | char(60) binary     |      | PRI |         |       |
| Db          | char(64) binary     |      | PRI |         |       |
| User        | char(16) binary     |      | PRI |         |       |
| Table_name  | char(60) binary     |      | PRI |         |       |
| Grantor     | char(77)            |      | MUL |         |       |
| Timestamp   | timestamp(14)       | YES  |     | NULL    |       |
| Table_priv  | set(...)            |      |     |         |       |
| Column_priv | set(...)            |      |     |         |       |
+-------------+---------------------+------+-----+---------+-------+

This table probably looks a bit odd. The creators of MySQL decided to
use a SET( ) function to represent privileges in
both the tables_priv and
columns_priv tables. In doing so, they made it
difficult for authors to present a nice clean listing of all the
grant tables in their books (we're sure that
wasn't their intent).

The ... in the Table_priv field
should actually read:

'Select','Insert','Update','Delete','Create','Drop','Grant'

and the ... in the Column_priv
field really contains:

'Select','Insert','Update','References'

Both are new fields not seen in previous tables. As their names
imply, they control table and column privileges.
There's another new field in the table:
Grantor. This 77-character field records the
identity of the user who granted these privileges. It is 77
characters in size because it is intended to hold a username (up to
16 characters), an @ symbol, and a hostname (up to
60 characters).

The Timestamp field also makes its first
appearance in this table. As you'd expect, it simply
records the time when the record was created or modified.

Using table-level privileges isn't very common in
MySQL, so don't be surprised if your server has no
records in its tables_priv table. If
you've installed the popular
phpMyAdmin utility (discussed in Appendix C), however, you might see something like
this:

mysql> SELECT * FROM tables_priv \G
*************************** 1. row ***************************
Host: localhost
Db: mysql
User: phpmyadmin
Table_name: user
Grantor: root@localhost
Timestamp: 20020308185823
Table_priv:
Column_priv: Select

This entry grants the phpmyadmin user access to
the database, with the Select privileges he needs
to obtain information from MySQL. This table doesn't
grant privileges on any particular data; that has to be done in
another table, as you'll see in the next section.

10.2.6 The columns_priv Table

The final table,
columns_priv, is similar to the
tables_priv table. It specifies individual column
privileges in a particular table:

mysql> DESCRIBE columns_priv;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| Host        | char(60) binary     |      | PRI |         |       |
| Db          | char(64) binary     |      | PRI |         |       |
| User        | char(16) binary     |      | PRI |         |       |
| Table_name  | char(64) binary     |      | PRI |         |       |
| Column_name | char(64) binary     |      | PRI |         |       |
| Timestamp   | timestamp(14)       | YES  |     | NULL    |       |
| Column_priv | set(...)            |      |     |         |       |
+-------------+---------------------+------+-----+---------+-------+

Just as in the previous table, the ... in the
Column_priv field really contains:

'Select','Insert','Update','References'

Column-level privileges also aren't very common in
MySQL. But there are cases when you're likely to
encounter them. Again, phpMyAdmin is a great
example:

mysql> SELECT * FROM columns_priv LIMIT 1 \G
*************************** 1. row ***************************
Host: localhost
Db: mysql
User: phpmyadmin
Table_name: tables_priv
Column_name: Column_priv
Timestamp: 20020308185830
Column_priv: Select

This record allows the phpmyadmin user to select
data from the Column_priv column of the
tables_priv table in the mysql
database.

Confused yet? Can't blame you. The grant tables can
be quite confusing at first. Until you spend some time working with
them, you won't really appreciate the flexibility
this design provides.

We wouldn't recommend spending that time unless
absolutely necessary. Instead, read the next section. It reviews the
GRANT and REVOKE commands and
then looks at how they interact with the grant tables so that you
don't have to. It's only worth
delving deeply into the grant tables if you find a situation that
can't be set up (or is too complex) using the
GRANT command.

High Performance MySQL [Electronic resources] نسخه متنی

فارسی

کردی

العربیه

اردو

Türkçe

Русский

English

Français

کانال فیلم من

تبیان من

فایلهای من

کتابخانه من

پنل پیامکی

وبلاگ من

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی