mysql通过grant来添加用户

我们知道在mysql中用户是存储在mysql.user表中的,我们可以通过如下命令来查看mysql中的用户信息

mysql> select host,user,password from mysql.user;                        
+-----------------------+------+-------------------------------------------+
| host                  | user | password                                  |
+-----------------------+------+-------------------------------------------+
| localhost             | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost.localdomain | root |                                           |
| 127.0.0.1             | root |                                           |
| localhost             |      |                                           |
| localhost.localdomain |      |                                           |
+-----------------------+------+-------------------------------------------+
5 rows in set (0.00 sec)

在上面我们可以看到我们的root用户可以在localhost上登录mysql

那么，如果我们要在mysql中添加一个用户，我们可以使用grant命令来实现

下面一个例子

mysql> grant all on *.* to user1 @192.168.1.108 identified by "123";
Query OK, 0 rows affected (0.00 sec)

这样我们就添加了一个用户，看一下mysql.user中，再次查看

mysql> select host,user,password from mysql.user;
+-----------------------+-------+-------------------------------------------+
| host                  | user  | password                                  |
+-----------------------+-------+-------------------------------------------+
| localhost             | root  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost.localdomain | root  |                                           |
| 127.0.0.1             | root  |                                           |
| localhost             |       |                                           |
| localhost.localdomain |       |                                           |
| 192.168.1.108         | user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------------------+-------+-------------------------------------------+
6 rows in set (0.00 sec)

我们发现多了一个用户user1 ，可以通过show grants for user1@192.168.1.108来查看权限 

mysql> show grants for user1@192.168.1.108;
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@192.168.1.108                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.1.108' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

这样他就可以在192.168.1.108这个ip*问mysql，

C:\Users\Administrator>mysql -h192.168.1.10 -uuser1 -p123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.60-log Source distribution

这样我们就添加一个用户

那么grant命令到底是怎么用户呢？

我们可以在mysql中使用 ? grant 

可以得到grant的帮助信息，和使用grant的一些小例子

我们只看一部分

mysql> ? grant;
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

user_specification:
    user [IDENTIFIED BY [PASSWORD] 'password']

priv_type就是分配给用户的权限，上面的all就是所有权限，至于其他的权限可以查看下手册，比如insert，update，delete等，这里不一一列举

object_type就是作用在哪儿上面 table(表，默认)，function(函数),procedure（存储）

priv_level就是指作用在哪个数据库的哪个表中，*.*表示所有的数据库的所有表

identified 则是设置登录密码，最好用引号引起来

 

取消一个用户的权限则是用revoke

revoke 和grant用法差不多，就是把to换成from

mysql> revoke all on *.* from user1@192.168.1.108;
Query OK, 0 rows affected (0.17 sec)

mysql> show grants for user1@192.168.1.108;       
+------------------------------------------------------------------------------------------------------------------+
| Grants for user1@192.168.1.108                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'192.168.1.108' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以看到用户user1的权限没有了，

到此添加用户是这样了