Understanding MySQL Authentication


MySQL has a sophisticated multiuser access-control system that can allow or prevent each user from having access to a particular database or individual tables. You can even restrict the types of operations the user is able to perform on a table.

Web Access

When MySQL is used as the back end to a website, you will usually have only a single user account through which all the connections from the web scripts are performed. Your application will handle any additional user authentication that is required in the web browser.


Authenticating with MySQL

When you connect to a MySQL server, you provide a username and password to gain access. MySQL authenticates you using not only these login details, but also the hostname or IP address you are connecting from.

localhost

Often you communicate with MySQL only from localhostthat is, when you run the mysql client on the same machine as the MySQL server, or you access a database from an application or web script that is running on the same machine.


Not only do you need to supply a valid username and password for the database you want to gain access to, but you also need to satisfy MySQL that you are connecting from an allowed host for that user. If you try to connect with a username and password that are not valid for the host you are connecting from, access will be denied, even if the login information works from another host.

It is also possible for two users who have the same username but are connecting from two different hosts to use separate passwords and have separate privileges. Therefore, you should try to think of a MySQL user as a username plus a host when dealing with user permissions, rather than just the username. In other words, consider the access requirements for chris@localhost or chris@192.168.1.100, not for user chris.

MySQL Privileges

MySQL includes a number of individual privileges that determine the type of actions that a user can perform. For example, a user with the SELECT privilege may query a table, but the same user also requires the INSERT, DELETE, and UPDATE privileges to perform data manipulation on the same table.

A privilege can be granted to a userremember, this means a user plus a hostat the table level. Therefore, two users with access to the same database can be given permission to use only the tables they require access to.

Table 18.1 lists the privileges that can be assigned. User permissions are set using the GRANT and REVOKE commands, which are discussed later in this chapter.

Table 18.1. Privileges in MySQL

ALTER

Allows use of ALTER TABLE.

ALTER ROUTINE

Alters or drops stored routines.

CREATE

Allows use of CREATE TABLE.

CREATE ROUTINE

Creates stored routines.

CREATE TEMPORARY TABLE

Allows use of CREATE TEMPORARY TABLE.

CREATE USER

Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.

CREATE VIEW

Allows use of CREATE VIEW.

DELETE

Allows use of DELETE.

DROP

Allows use of DROP TABLE.

EXECUTE

Allows the user to run stored routines.

FILE

Allows use of SELECT... INTO OUTFILE and LOAD DATA INFILE.

INDEX

Allows use of CREATE INDEX and DROP INDEX.

INSERT

Allows use of INSERT.

LOCK TABLES

Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.

PROCESS

Allows use of SHOW FULL PROCESSLIST.

RELOAD

Allows use of FLUSH.

REPLICATION

Allows the user to ask where slave or master

CLIENT

servers are.

REPLICATION SLAVE

Needed for replication slaves.

SELECT

Allows use of SELECT.

SHOW DATABASES

Allows use of SHOW DATABASES.

SHOW VIEW

Allows use of SHOW CREATE VIEW.

SHUTDOWN

Allows use of mysqladmin shutdown.

SUPER

Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.

UPDATE

Allows use of UPDATE.

USAGE

Allows connection without any specific privileges.


The MySQL Privilege Tables

Whether access is granted to MySQL is determined by data in the mysql database, which contains a number of tables that control user privileges.

Superuser

Access to the mysql database is usually available only to the MySQL root user. Only a MySQL superuser user should have access to the privilege tables.


Data from the user table is used for authentication. The Host, User, and Password columns contain the values used to validate a connection attempt. The Password column is encrypted, so you cannot view a user's actual password by querying this table.

The db database contains the default permissions that a user has for each database. The Db, User, and Host columns are followed by a series of privilege columns, such as Select_priv and Alter_priv. Those columns contain either a Y or an N value to indicate whether that privilege has been granted.

Individual table privileges are stored in the tables_priv table, using the Db, User, Host, and Table_name columns to identify the database table and user. The Table_priv column contains the name of the privilege granted. Whereas db contains one row per user and database, there is one row in tables_priv for each table-level privilege assigned.

Changing Privileges

Although it is possible to query and update the privilege tables directly, it is easier to use the commands described in the "User Management" section of this lesson to do so.

Furthermore, changes made to the privilege tables do not take effect until you issue the FLUSH PRIVILEGES command.





Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net