MySql 091225：MySQL的用户帐户

@ http://zcatt.cublog.cn

1.    添加和删除账户

有两种方法添加账户：

1） 使用CREATE USER和GRANT等创建账户的命令

 

登录为root

shell> mysql --user=root mysql

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';

 

2)使用INSERT, UPDATE和DELETE语句操纵mysql的管理表。

 

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');

mysql> FLUSH PRIVILEGES;

 

删除用户账户使用

DROP USER user [, user] ...

 

 

2.限制账户资源

MySQL可以限制账户的4类资源

1）  每小时query数

2）  每小时update数

3）  每小时连接次数

4）  并发连接数目

 

mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'

    ->     WITH MAX_QUERIES_PER_HOUR 20

    ->          MAX_UPDATES_PER_HOUR 10

    ->          MAX_CONNECTIONS_PER_HOUR 5

    ->          MAX_USER_CONNECTIONS 2;

 

 

3.password设置

 

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'biscuit';

 

mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('biscuit');

 

注意要使用PASSWORD()

 

mysql> SET PASSWORD = PASSWORD('biscuit');

 

shell> mysqladmin -u user_name -h host_name password "newpwd"