Databases Reference
In-Depth Information
The password is set for user 'allmusic'.
The
mysql_setpermission
program is a Perl script; it should run on a Linux or Mac OS
X system, but for a Windows system you will need to follow the instructions of “In-
stalling Perl modules under Windows” in Chapter 2. Using the monitor approach is
more portable, since you can use it even when
mysql_setpermission
isn't installed on
a system, or where you have limited access to the server, such as on a server run by a
hosting company.
Only users who have access to the
mysql
database can use the
mysql_setpermission
command. Usually, only the MySQL
root
user has this access; if you try using the script
as a user who doesn't have access privileges for the
mysql
database, you'll get an “Access
denied” message:
$
mysql_setpermission --user=
unprivileged_username
Password for user
unprivileged_username
to connect to MySQL:
Can't make a connection to the mysql server.
The error: Access denied for user '
unprivileged_username
'@'localhost' to
database 'mysql' at /usr/bin/mysql_setpermission line 70, <STDIN> line 1.
Managing Privileges with SQL
MySQL privileges are managed in five tables in the
mysql
database. You can manage
this database yourself, using queries to manage users and privileges rather than using
the
GRANT
and
REVOKE
statements. It's useful to know how to do this, because it can save
you time and allow you to access features that aren't available through
GRANT
and
REVOKE
. This section explains how the privileges are managed and shows you how to
modify them directly.
The privileges are managed in the
mysql
database. As we've discussed previously, only
administrators should have access to this database and, therefore, you'll usually need
to log in as the
root
user to follow the steps in this section. In MySQL 5.0, the database
contains 17 tables, but only 5 are relevant to privileges:
user
,
db
,
tables_priv
,
col
umns_priv
, and
host
.
The user Table
The
user
table manages users and global privileges. Its structure is straightforward,
even though it has around 30 columns. Each row includes a
User
,
Password
, and
Host
column; these are the credentials that are used to match against connection attempts
and authenticate users. All three are optional; the
User
and
Password
values are optional
because MySQL includes support for anonymous access and because it's possible for
a user to not have a password (although this isn't recommended). We explain why the
Host
value is optional later in this section. Each row also contains a
Y
or
N
for each
possible privilege—for example,
Select_priv
and
Alter_priv
might be set to
Y
and
N
,
