How to define "who" can access MySQL via PHP, MySQL client, and what rights "who" has.
There is a database called "mysql" with user rights tables. Two key tables are:
- The mysql.user is a global table and privileges apply to all databases.
- The mysql.db defines what privileges a user has on each individual database.
If user 'lisa' is defined to have access to her database 'lisadb' and have limited or no access defined in mysql.user except only her password defined there, the mysql.db file will show her access rights to lisadb. Lisa will be able to write PHP to access lisadb as well as run the mysql client from console and access only lisadb.
Commands and what they do:
mysql> grant all privileges on *.* to lisa@localhost identified by 'password';
- This gives lisa global rights in mysql.user to all the databases. She will be able to do almost anything to anyone's database, include delete the database, drop tables, etc.
mysql> grant select,insert,update,delete,create,drop,alter,index on *.* to lisa@localhost identified by 'password';
- Grant specific global rights to lisa for all databases. Creates privileges visible in mysql.user
mysql> grant select,insert,update,delete,create,drop,alter,index on lisadb.* to lisa@localhost identified by 'password';
- Recommended. Grant specific and perhaps necessary privileges to lisa for her specific database. Privileges will be visible in mysql.db
mysql> show grants for lisa@localhost;
- Displays what privileges her account has.
mysql> select * from mysql.user; mysql> select * from mysql.db;
- You know what these do.
mysql> set password for lisa@localhost = password('shhsecret');
mysql> update mysql.user set password = password('shhsecret') where User = 'lisa';
mysql> flush privileges;
- Various methods of setting the authentication password for lisa. The first is recommended and provided by mysql. User's password is stored in mysql.user, there are _not_ individual passwords for each database in mysql.db, so the same password applies to all databases for user.
- The second is the manual method of setting user's password. In this example lisa's password is being set to a most secure 'shhsecret'.
- Finally, it _is_ necessary to execute the flush command for mysql to recognize the changes to lisa's account during the current session.
mysql> delete from mysql.user where User = 'lisa';
- Now we are done with lisa so we can revoke all her access and get her out of all our mysql databases.
Privilege Meaning
----------------------------------------------------------------------
ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION
ALTER Allows use of ALTER TABLE
CREATE Allows use of CREATE TABLE
CREATE TEMPORARY TABLES Allows use of CREATE TEMPORARY TABLE
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 procedures (MySQL 5.0)
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 you have the SELECT privilege
PROCESS Allows use of SHOW FULL PROCESSLIST
RELOAD Allows use of FLUSH
REPLICATION CLIENT Allows the user to ask where the slave or master servers are
REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)
SELECT Allows use of SELECT
SHOW DATABASES SHOW DATABASES shows all 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 statements, the mysqladmin
debug command; allows you to connect (once) even if max_connections is reached
UPDATE Allows use of UPDATE
USAGE Synonym for ``no privileges
GRANT OPTION Allows privileges to be granted