Databases Reference
In-Depth Information
create them with no privileges. You then explicitly grant the privileges that these users
need. Security experts prefer the “default deny” approach over the “default allow” one,
since there's a smaller chance that you'll create users or privileges that make your server
insecure. Developers tend to prefer the “default allow” approach, since you only need
to think about the few things that you don't want to happen, rather than the larger set
of things you do want to allow. We recommend that you use the “default deny” ap-
proach during production, but the “default allow” approach is acceptable if you're just
experimenting on noncritical data in a relatively secure environment. We now consider
the issues you need to consider when creating users and assigning privileges using the
“default deny” philosophy:
Clients
From what computers does the database server need to be accessed? Typically, the
answer is at least the
localhost
, where the machine's security protects accounts
that are used to access the database server; if you are authenticated to access the
localhost
, you must have access to an account on the host, and so must have passed
an important security check. What other clients need access? Be as explicit as pos-
sible, listing client machines by their IP addresses or full domain names; avoid using
the wildcard
%
where possible. Avoid listing clients that
may
want access, and limit
your choices to only those that
must have
access; you can always add clients later
when you're sure they're needed.
Users
Who needs access to the database server? The answer always includes the
root
user
who administers the server. You'll also need at least one other user who—as we
discuss in the next step—has the smallest set of privileges required to work with
your database. If possible, partition your database users into those that need more
privileges and those that need fewer. For example, can you divide the users into a
database administrator user, and then others who need fewer privileges to use the
database?
Can you separate the database users from the application users? For example, in a
web application, it's typical that the server scripts manage the users of the appli-
cation and always access MySQL as a single user themselves. If you have more than
one application, we recommend having a different user for each application.
Privileges
What needs to be done? The section “Privileges” at the beginning of this chapter
lists all of the privileges that are available. The
root
user has all of the privileges for
all databases and the server. Consider which privileges are needed for your data-
base, and what components of the database each is needed for. Are the privileges
needed for columns, tables, or for the database? Don't add any privileges you don't
need, avoid adding privileges you only think you might want, and avoid privileges
for parts of the database that you don't need to access. Try to avoid using the
asterisk
*
wildcard, and, in particular, avoid assigning global privileges with
*.*
where possible.
