User account is actually username@hostname pair. Enables different privileges for different connections: joe@localhost #on the server joe@%.ourcompany.com #from our domain joe@192.168.% #from internal network joe@% #from anywhere (else) Each of these accounts could have different sets of privileges. 1. are you even allowed to logon (need record in mysql.user table) 2. once connected, is each statement allowable (do you have the necessary privilege). Privileges in the grant tables (i.e. the 'user', 'db', 'tables_priv', and 'columns_priv' tables) in 'mysql' database. Global privileges in 'user' table, per-database privileges in 'db' table, per-table privileges in 'tables_priv' table, per-column privileges in 'column_priv' table. The finer-grained privilege tables can grant privilege not given in coarser-grained table. 'user' table has accounts and passwords. Create login accounts: #typical user: can login but can't access data: all privileges are N: #can do show status etc. Usage==no priviliges GRANT USAGE ON *.* TO 'joe'@'%' IDENTIFIED BY 'password'; #admin account: all privileges to all databases and all admin privileges: GRANT ALL PRIVILEGES ON *.* TO 'joe'@'localhost' IDENTIFIED BY 'password'; #show all privileges for a user: SHOW GRANTS FOR 'joe'@'%'; #delete a user: DELETE FROM mysql.User WHERE User='joe' AND Host='%'; FLUSH PRIVILEGES; To see the actual records in mysql database: [mysql]> SELECT user,host,password FROM user; +-------+------------------+-------------------------------------------+ | user | host | password | +-------+------------------+-------------------------------------------+ | root | localhost | *ECDEFFA988F855DBDA7BE1C5E6DBC449B02D9485 | | root | fostix | *ECDEFFA988F855DBDA7BE1C5E6DBC449B02D9485 | | wills | localhost | | | wills | % | | | joe | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | joe | % | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | +-------+------------------+-------------------------------------------+ encrypted password. % is wildcard, here meaning any host More-specific host is looked at first. 'user' table also has 28 global privileges applicable to all databases unless overridden by other grant tables. enum('N','Y') values. Database access privileges: Select Insert Update Delete Create tables & databases Drop tables & databases Index create & drop indexes Alter use alter table Administrative privileges: Reload use FLUSH and RESET Shutdown shutdown server Process view threads File access files on server Grant Option* create accounts and give them my privileges References* reserved. Show_db see all dbs with SHOW DATABASES Super misc admin ops Create_tmp_table* create temporary table Lock_tables* use LOCK TABLES Execute reserved. run stored procedures Repl_slave act as replication slave Repl_client The 'db' table can have privileges on particular databases that override the global privilege in the 'user' table. It has the same 8 database-access privileges and the administrative privileges marked with an *. #give joe@% some Select privilege (i.e. read-only) on world database: GRANT SELECT ON world.* TO 'joe'@'%'; SELECT * FROM db WHERE user='joe'\G *************************** 1. row *************************** Host: % Db: world User: joe Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N 'tables_priv' table has per-table privileges. #give joe@% some modify privileges on world.City table: GRANT Insert,Delete,Update ON world.City TO 'joe'@'%'; SELECT user,host,db,table_name,table_priv FROM tables_priv WHERE user='joe' AND host='%'; +------+------+-------+------------+-----------------------------+ | user | host | db | table_name | table_priv | +------+------+-------+------------+-----------------------------+ | joe | % | world | City | Select,Insert,Delete,Update | +------+------+-------+------------+-----------------------------+ table_priv column is a set of the 8 database-access privileges and Grant and References #remove joe@% Delete privilege from the City table of world database: REVOKE Delete ON world.City FROM 'joe'@'%'; 'columns_priv' table has per-column privileges: select, update, insert.