Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

15.8 Managing Users and Privileges

MySQL
has complex, flexible
account and database access management. It supports multiple accounts
(known as users), and each has an optional
password and a set of privileges that define what the user can do.
For example, you can allow a database administrator to startup,
shutdown, and manage MySQL. You can allow an application
administrator to create, drop, and alter tables and databases. In a
web database application, you might limit a user to only altering
data in tables, or give them read-only access. You can also control
which databases, tables, and attributes a user can access, and from
where they can access the server.

This section explains how user and privilege management is supported
in MySQL, and recommends how to manage it for a web database
application.

15.8.1 Creating Users and Privileges

When you installed MySQL by following
our instructions in Appendix A through
Appendix C, you set up two users (the root user and
a web database application user) and created passwords for each. The
root user has more privileges than should be used with an
application: it can create other users and privileges, view and
manipulate all databases, and control and manage MySQL. We recommend
that you use the additional user you created for your application and
that you create an additional user for each application that you
build. We also recommend you keep it to one simple user per
application: extra users or complex privileges slow down MySQL since
there's more information to check before an
operation can proceed.

Suppose you want to create a new user,
lucy, who has control over
the application database and can access MySQL
from the machine that hosts the MySQL server. You can create this
user by logging in as the root user and typing the following
statement into the command interpreter:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON application.* TO
lucy@127.0.0.1 IDENTIFIED BY 'password';

This statement grants the same privileges as the statement you
executed in Appendix A through Appendix C to create a winestore
database user.

The new user can then run the command interpreter from the Unix shell
with the command:

% /usr/local/mysql/bin/mysql -ulucy -ppassword

Or, for Microsoft Windows from the Run dialog in the Start menu, type:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe" -ulucy -ppassword

The user in our online winestore application in Chapter 16 through Chapter 20 has these
privileges.

15.8.1.1 Privileges and scope

Table 15-6

shows the privileges you can grant to a
user; we've omitted a few privileges that involve
advanced applications and future MySQL features.
We've granted the basic privileges you need to work
with a database to the user lucy in the previous
section. To that list, depending on your requirements, you might add
CREATE, CREATE TEMPORARY
TABLES, DROP, INDEX,
and FILE.

Unless you want to give the user administrator-style privileges,
there's no need to use PROCESS,
RELOAD, SHOW DATABASES,
SHUTDOWN, SUPER, or
GRANT OPTION. Be careful with GRANT
OPTION
: it allows a user to pass on their
privileges to another user, and users can get together to grant each
other privileges (perhaps leading to a security hole). Also, we
don't recommend using
ALL
: for better security, think about
what privileges are needed and explicitly list them.

ALL

ALTER

CREATE

CREATE TEMPORARY TABLES

DELETE

DROP

FILE

INDEX

INSERT

LOCK TABLES

PROCESS

RELOAD

SELECT

SHOW DATABASES

SHUTDOWN

SUPER

UPDATE

USAGE

GRANT OPTION

In our examples so far, we've granted privileges to
application.*. This means that the privileges
apply to the application database, and all
tables within the database. For a database, you can grant
ALTER, CREATE,
CREATE TEMPORARY TABLES,
DELETE, DROP,
FILE, INDEX,
INSERT, LOCK TABLES,
FLUSH, SELECT, and
UPDATE. Some privileges can be applied to complete
databases, some to individual tables, and some even to attributes
(columns) within tables.

If you want to grant a global privilege for all databases and tables,
use *.*. For example, to allow
lucy to SELECT from all
databases and tables:

GRANT SELECT ON *.* TO lucy@127.0.0.1 IDENTIFIED BY 'password';

For the privileges PROCESS,
RELOAD, SHOW DATABASES,
SHUTDOWN, and SUPER, it only
makes sense to grant these to *.* since they
aren't specific to a database.

If you want to grant the INSERT privilege for only
the customer table in the
winestore database, you can do that with:

GRANT INSERT ON winestore.customer TO lucy@127.0.0.1 
IDENTIFIED BY 'password';

For tables, you can grant the SELECT,
INSERT, UPDATE,
DELETE, CREATE,
DROP, GRANT
OPTION, INDEX, and
ALTER privileges.

You can even go a step further and allow a user only to
INSERT into the attributes
cust_id and surname in the
customer table:

GRANT INSERT (cust_id, surname) ON winestore.customer TO lucy@127.0.0.1 
IDENTIFIED BY 'password';

For attributes, you can grant the SELECT,
INSERT, and UPDATE privileges.

Privileges take precedence by their level in the hierarchy. If the
user lucy has SELECT access to
*.*, she can access all databases and tables; it
doesn't matter whether she has
SELECT access for the
winestore database. The same applies to a
database: if you can SELECT from a database, you
can SELECT from all its tables and attributes.
Similarly, if you have table privileges, you have all attribute
privileges for that table.

15.8.1.2 Network access

There is usually no need to allow
network access for a web database application if the middle-tier
components (the web server and PHP scripting engine) are installed on
the same machine as the MySQL server. However, if you want to allow
access, you can do so in a broad or selective manner. Thus, to give
user lucy access over a network from the server
hugh.hughinvy.com, you can
replace 127.0.0.1 with the IP address of
the server or its domain name:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestore.* 
TO lucy@hugh.hughinvy.com IDENTIFIED BY 'password';

If you want to allow access from all hosts in the hughinvy.com domain, you can use the wildcard
%:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestore.* 
TO lucy@"%.hughinvy.com" IDENTIFIED BY 'password';

You can use the % wildcard anywhere in a domain or
IP address. Note that when you use a wildcard, you need to enclose
the domain string in quotes. If you want to allow access from
anywhere, grant privileges to lucy without the
@ suffix:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestore.* 
TO lucy IDENTIFIED BY 'password';

As discussed previously, we don't recommend using
wildcards. Instead, we recommend that you explicitly list the servers
so that you minimize the chance of creating a security hole.

15.8.2 Revoking Privileges

The
REVOKE
statement removes privileges. In
contrast, executing a second or subsequent GRANT
statement for a user doesn't revoke their previous
privileges. For example, if you type:

GRANT SELECT ON winestore.* TO lucy@127.0.0.1 IDENTIFIED by 'password';
GRANT INSERT ON winestore.* TO lucy@127.0.0.1;

then the user lucy can now
SELECT and INSERT into the
winestore database.

To revoke one or more privileges, use REVOKE. For
example, to remove the INSERT privilege
we've just granted, use:

REVOKE INSERT ON winestore.* FROM lucy@127.0.0.1;

The REVOKE statement has much the same syntax as
GRANT. You can revoke global, databases, table,
and attribute privileges, and you use the same method of specifying
databases and tables. For example, to remove all global privileges
from lucy, use:

REVOKE ALL ON *.* FROM lucy@127.0.0.1;

Beware: this doesn't remove the
user's privileges on all databases, tables, or
attributes! You need to explicitly remove privileges that
you've granted from each level of the hierarchy.

Here's one final example. To remove an attribute
privilege from the customer table, use:

REVOKE INSERT (cust_id) ON winestore.customer FROM lucy@127.0.0.1;

15.8.2.1 How MySQL manages privileges

The user and privilege information is
stored in the mysql database, and you can
explore and maintain that database in the same way as any other. The
user table contains the global privilege
settings. It contains one row for each user and host combination, and
each attribute value in the row is set to Y or
N, depending on whether the user has the privilege
described by the attribute name. The encrypted password of the user
is also stored in the row (it's encrypted with the
password( ) function described in
"Functions").

Similarly to the user table, the
db table in the mysql
database contains database-level settings for all databases, the
tables_priv contains table-level settings for
all tables, and the columns_priv table contains
attribute-level settings for all attributes. If a table
doesn't contain a row for a user, that user has no
privileges for that level of setting. If you revoke all privileges in
a row, the row is deleted.

Exploring these tables is an excellent way to check what settings
you've created, and to remove or change settings
quickly using UPDATE, INSERT,
or DELETE. However, if you do adjust privileges
manually, issue a FLUSH PRIVILEGES statement
afterwards so that MySQL rereads the tables and updates
itself.