mySQL 帐户权限管理

参考网站:
URL:http://dev.mysql.com/doc/refman/5.1/en/server-administration.html

mysql client登陆的格式:

shell>mysql --user=monty --password=guessdb_name
or
shell>mysql -u monty -pguessdb_name

用root的帐户登陆mysql server:
shell>mysql --user=root mysql

登陆后, 用下面的方式建立user和对应的权限:
mysql>CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'WITH GRANT OPTION;
mysql>CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'WITH GRANT OPTION;
mysql>CREATE USER 'admin'@'localhost';
mysql>GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql>CREATE USER 'dummy'@'localhost';

其中 *.*表示所有已存在的库和表, identified by 后面是该用户密码, To 后面的是user的名字, 如果client是本地登陆到server则用localhost,
如果允许他在所有机器上远程登陆到server上, 则用'%'. 必须用引号引起.

查看该用户权限:
mysql>SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost                          |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |

通过插入user表来加user的信息:
shell>mysql --user=root mysql
mysql>INSERT INTO user
->VALUES('localhost','monty',PASSWORD('some_pass'),
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO user
->VALUES('%','monty',PASSWORD('some_pass'),
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'','','','',0,0,0,0);
mysql>INSERT INTO user SET Host='localhost',User='admin',
->Reload_priv='Y', Process_priv='Y';
mysql>INSERT INTO user (Host,User,Password)
->VALUES('localhost','dummy','');
mysql>FLUSH PRIVILEGES;

原理如上.

给user部分权限, 而不是全部权限:

shell>mysql --user=root mysql
mysql>CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON bankaccount.*
->TO 'custom'@'localhost';
mysql>CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON expenses.*
->TO 'custom'@'host47.example.com';
mysql>CREATE USER 'custom'@'server.domain' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON customer.*
->TO 'custom'@'server.domain';

同样可以用直接插入表方式:
shell>mysql --user=root mysql
mysql>INSERT INTO user (Host,User,Password)
->VALUES('localhost','custom',PASSWORD('obscure'));
mysql>INSERT INTO user (Host,User,Password)
->VALUES('host47.example.com','custom',PASSWORD('obscure'));
mysql>INSERT INTO user (Host,User,Password)
->VALUES('server.domain','custom',PASSWORD('obscure'));
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('localhost','bankaccount','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('host47.example.com','expenses','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('server.domain','customer','custom',
->'Y','Y','Y','Y','Y','Y');

设定某特定域用户才可以登陆到server方法:
mysql>CREATE USER 'myname'@'%.mydomain.com' IDENTIFIED BY 'mypass';
or
mysql>INSERT INTO user (Host,User,Password,...)
->VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql>FLUSH PRIVILEGES;

%表示所有字符串. 通配符