mysql dba系统学习（14）mysql用户管理之一、二

用户管理

Mysql的用户管理包括两个部分，用户名和机器名，只要其中的一个不同，那么就是不同的用户。

一，mysql的权限类型

管理权限

数据库的访问权限

二，一些特殊权限

ALl和All PRIVILEGE表示的是全部权限，但是不能将权限授予其他的用户

USAGE 表示的是没有权限，不能访问数据库的任何数据，但是可以连接到数据库。还有就是可以查看系统变量。show variables 和show  status

三，权限级别

四，关于授权的表

 mysql用户管理

一，创建和删除用户

mysql> select current_user();  查询当前的登录用户

+----------------+

| current_user() |

+----------------+

| root@localhost |

+----------------+

 创建用户的时候没有分配任何权限，%表示的是任何机器，但是不包括localhost和127.0.0.1

mysql> create user 'chenzhongyang'@'%' identified by '123456';

mysql> create user 'chen'@'127.0.0.1' identified by '123456';

删除用户

mysql> drop user 'chenzhongyang'@'%';

Query OK, 0 rows affected (0.04 sec)

[root@test4 /]# mysql -uchen -p123456 -h127.0.0.1

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.1.70-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select  user();

+----------------+

| user()         |

+----------------+

| chen@localhost |

+----------------+

1 row in set (0.01 sec)

刚刚创建的用户的权限是usage

mysql> show  grants;

+-------------------------------------------------------------------------------------------------------------+

| Grants for chen@127.0.0.1                                                                                   |

+-------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'chen'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

+-------------------------------------------------------------------------------------------------------------+

1 row in set (0.02 sec)

修改用户名，这个时候密码和权限没有变化

mysql> rename  user 'chen'@'127.0.0.1'  to 'chenzhongyang'@'127.0.0.1';

二，匿名用户

创建匿名用户之后，那么任何机器的任何用户都可以登录到mysql，所以这样很危险

mysql> insert into user(host,user,password) values('%','','');

Query OK, 1 row affected, 3 warnings (0.17 sec)

mysql> flush privileges;   刷新权限将重新加载user表的内容

Query OK, 0 rows affected (0.03 sec)

三，授权用户

  例子如下

mysql> grant select on  mysql.user to 'chenzhongyang'@'127.0.0.1'  identified by '123456';

Query OK, 0 rows affected (0.32 sec)

[root@test4 /]# mysql -uchenzhongyang  -p123456 -h127.0.0.1

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.1.70-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

mysql> select host from mysql.user where user='chenzhongyang';

+-----------+

| host      |

+-----------+

| 127.0.0.1 |

+-----------+

1 row in set (0.04 sec)

授权的范围

回收权限

资源限制

from: http://wolfword.blog.51cto.com/4892126/1289384

http://wolfword.blog.51cto.com/4892126/1289453