Once you establish a connection, the
server enters Stage 2. For each request that comes in on the connection, the
server checks whether you have sufficient privileges to perform it, based on
the type of operation you wish to perform. This is where the privilege fields
in the grant tables come into play. These privileges can come from any of the
user, db, host, tables_priv, or columns_priv tables. The grant tables are
manipulated with GRANT and REVOKE commands.
The user table grants privileges that
are assigned to you on a global basis and that apply no matter what the current
database is. For example, if the user table grants you the delete privilege,
you can delete rows from any database on the server host! In other words, user
table privileges are superuser privileges. It is wise to grant privileges in
the user table only to superusers such as server or database administrators.
For other users, you should leave the privileges in the user table set to 'N'
and grant privileges on a database-specific basis only, using the db and host
tables.
The db and host tables grant
database-specific privileges. Values in the scope fields may be specified as
follows:
The wild-card characters `%' and `_'
can be used in the Host and Db fields of either table.
A '%' Host value in the db table means
``any host.'' A blank Host value in the db table means ``consult the host table
for further information.''
A '%' or blank Host value in the host
table means ``any host.''
A '%' or blank Db value in either
table means ``any database.''
A blank User value in either table
matches the anonymous user.
The db and host tables are read in and
sorted when the server starts up (at the same time that it reads the user
table). The db table is sorted on the Host, Db, and User scope fields, and the
host table is sorted on the Host and Db scope fields. As with the user table,
sorting puts the most-specific values first and least-specific values last, and
when the server looks for matching entries, it uses the first match that it
finds.
The tables_priv and columns_priv
tables grant table- and column-specific privileges. Values in the scope fields
may be specified as follows:
The wild-card characters `%' and `_'
can be used in the Host field of either table.
A '%' or blank Host value in either
table means ``any host.''
The Db, Table_name and Column_name
fields cannot contain wild cards or be blank in either table.
The tables_priv and columns_priv
tables are sorted on the Host, Db, and User fields. This is similar to db table
sorting, although the sorting is simpler because only the Host field may
contain wild cards.
The request verification process is
described below. (If you are familiar with the access-checking source code, you
will notice that the description here differs slightly from the algorithm used
in the code. The description is equivalent to what the code actually does; it
differs only to make the explanation simpler.)
For administrative requests (shutdown,
reload, etc.), the server checks only the user table entry, because that is the
only table that specifies administrative privileges. Access is granted if the
entry allows the requested operation and denied otherwise. For example, if you
want to execute mysqladmin shutdown but your user table entry doesn't grant the
shutdown privilege to you, access is denied without even checking the db or
host tables. (They contain no Shutdown_priv column, so there is no need to do
so.)
For database-related requests (insert,
update, etc.), the server first checks the user's global (superuser) privileges
by looking in the user table entry. If the entry allows the requested operation,
access is granted. If the global privileges in the user table are insufficient,
the server determines the user's database-specific privileges by checking the
db and host tables:
The server looks in the db table for a
match on the Host, Db, and User fields. The Host and User fields are matched to
the connecting user's hostname and MySQL user name. The Db field is matched to
the database the user wants to access. If there is no entry for the Host and
User, access is denied.
If there is a matching db table entry
and its Host field is not blank, that entry defines the user's
database-specific privileges.
If the matching db table entry's Host
field is blank, it signifies that the host table enumerates which hosts should
be allowed access to the database. In this case, a further lookup is done in
the host table to find a match on the Host and Db fields. If no host table
entry matches, access is denied. If there is a match, the user's
database-specific privileges are computed as the intersection (not the union!)
of the privileges in the db and host table entries, that is, the privileges
that are 'Y' in both entries. (This way you can grant general privileges in the
db table entry and then selectively restrict them on a host-by-host basis using
the host table entries.)
After determining the
database-specific privileges granted by the db and host table entries, the
server adds them to the global privileges granted by the user table. If the
result allows the requested operation, access is granted. Otherwise, the server
checks the user's table and column privileges in the tables_priv and
columns_priv tables and adds those to the user's privileges. Access is allowed
or denied based on the result.
Expressed in boolean terms, the
preceding description of how a user's privileges are calculated may be
summarized like this:
global privileges
OR (database privileges AND host
privileges)
OR table privileges
OR column privileges
It may not be apparent why, if the
global user entry privileges are initially found to be insufficient for the
requested operation, the server adds those privileges to the database-, table-,
and column-specific privileges later. The reason is that a request might
require more than one type of privilege. For example, if you execute an INSERT
... SELECT statement, you need both insert and select privileges. Your
privileges might be such that the user table entry grants one privilege and the
db table entry grants the other. In this case, you have the necessary
privileges to perform the request, but the server cannot tell that from either
table by itself; the privileges granted by the entries in both tables must be
combined.
The host table can be used to maintain
a list of secure servers.
At TcX, the host table contains a list
of all machines on the local network. These are granted all privileges.
You can also use the host table to
indicate hosts that are not secure. Suppose you have a machine
public.your.domain that is located in a public area that you do not consider
secure. You can allow access to all hosts on your network except that machine
by using host table entries like this:
+--------------------+----+-
| Host | Db | ...
+--------------------+----+- |
public.your.domain | % | ... (all privileges set to 'N')
| %.your.domain | % | ... (all privileges
set to 'Y')
+--------------------+----+-
Naturally, you should always test your
entries in the grant tables (for example, using mysqlaccess) to make sure your
access privileges are actually set up the way you think they are.
No comments:
Post a Comment