MySql 账号管理

1、创建账号，有两种方式，一种为使用GRANT，一种直接操作权限表，推荐使用第一种，操作简单而且不容易出错。在MySql命令行中查看GRANT帮助即可看到GRANT的用法：

mysql> ? GRANT
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [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

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

 

例1：创建用户zl，权限为可以在所有数据库上执行所有操作，只能从本地连接：

GRANT All PRIVILEGES ON *.* TO zl@localhost;

例2：在上面基础上增加对zl的GRANT权限

GRANT All PRIVILEGES ON *.* TO zl@localhost WITH GRANT OPTION;

例3：在上面基础上设置zl密码为123456

GRANT All PRIVILEGES ON *.* TO zl@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;

例4：创建z2用户，可以从任何IP进行连接，权限为对test1数据库里所有表进行SELECT、UPDATE、INSERT、DELETE操作，初始密码为123456，使用GRANT实现如下：

GRANT SELECT,INSERT,UPDATE,DELETE ON test1.* TO 'z2'@'%' IDENTIFIED BY '123456'

直接操作权限表实现如下：

INSERT INTO db(host,db,user,select_priv,insert_priv,update_priv,delete_priv) 
VALUES('%','test1','z2','Y','Y','Y','Y')

 

 

该用户在user表中的权限都为N，db表中的权限为Y，如下：

mysql> SELECT * FROM user WHERE user ='z2' AND HOST ='%' \G;
*************************** 1. row ***************************
                 Host: %
                 User: z2
             Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          ......

mysql> SELECT * FROM db WHERE user ='z2' AND HOST ='%' \G;             
*************************** 1. row ***************************
                 Host: %
                   Db: test1
                 User: z2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          ......

 

MySQL是通过user表的host字段来控制IP连接限制的，host可以有如下类型赋值：

    Host可以是主机名或者是IP，或"localhost"指本地主机

    可以在Host列使用通配符字符"%"或"_"

    Host值"%"匹配任何主机名，空Host等价于%，但是不包括本地服务器localhost，如果要使用localhost权限，则必须单独配置。

    如果有多个匹配，则按下面规则进行：

    服务器启动时读入user表进行排序，先以最具体的值进行排序，主机名和IP是最具体的，而%是最不具体的。

    当用户进行连接时，以排序的先后顺序进行浏览记录，服务器使用第一条与客户端匹配的记录进行连接。

 

例5：授予SUPER、PROCESS、FILE权限给z3@%，这几个都是管理权限，不能指定具体某个数据库，只能用*.*

GRANT SUPER,PROCESS,FILE ON *.* TO 'z3'@'%';

 

例6：只授予登录权限给z4@localhost，USAGE只能登录数据库，不能进行任何操作

GRANT USAGE ON *.* TO 'z4'@'localhost'

GRANT权限只能用于登录数据库，不能执行任何操作。

 

查看权限

(1)  SHOW GRANTS FOR user@host;

(2)  按照user->db->tables_priv->columns_priv的顺序查看用户权限

(3)  MySQL5以后，可以使用新增的数据库information_schema数据库进行查看：

USE information_schema;
SELECT * FROM SCHEMA_PRIVILEGES WHERE GRANTEE = "'z2'@'%'";

 

更改权限

      和账号创建一样，更改权限也有两种方法，一种是使用GRANT（新增）和REVOKE（回收）方法，另一种是直接操作数据库方法。

      GRANT权限在执行的时候，如果权限表中不存在目标账号，则创建，如果存在，则执行权限的新增操作，GRANT不会执行减的操作。

(0) 目前数据库在存在用户'z2'@'%'，执行GRANT USAGE ON *.* TO 'z2'@'localhost'; 语句会新增用户，而不是改变原先。用户充许连接的域

(1) 目前'z2'@'localhost'只有登录权限，如下：

mysql> SHOW GRANTS FOR 'z2'@'localhost';      
+----------------------------------------+
| Grants for z2@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'z2'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

 (2) 赋予'z2'@'localhost'所有数据库上的所有表的SELECT权限：

GRANT SELECT ON *.* TO 'z2'@'localhost';

mysql> SHOW GRANTS FOR 'z2'@'localhost';
+-----------------------------------------+
| Grants for z2@localhost                 |
+-----------------------------------------+
| GRANT SELECT ON *.* TO 'z2'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

 (3) 给'z2'@'localhost'所有数据库上的INSERT权限：

GRANT INSERT ON *.* TO 'z2'@'localhost';

mysql> SHOW GRANTS FOR 'z2'@'localhost';
+-------------------------------------------------+
| Grants for z2@localhost                         |
+-------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'z2'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)

 

 

(4) 使用REVOKE收回'z2'@'localhost'在所有数据库上的INSERT权限：

mysql> REVOKE INSERT ON *.* FROM  'z2'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'z2'@'localhost';
+-----------------------------------------+
| Grants for z2@localhost                 |
+-----------------------------------------+
| GRANT SELECT ON *.* TO 'z2'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

  USAGE权限不能被REVOKE收回，也就是说，REVOKE不能删除用户。

 

更改密码

(1) 使用mysqladmin命令

mysqladmin -u user_name -p user_password -h host_name password "new_password"

如果你的mysqladmin连接不上mysql server，或者你没有办法执行mysqladmin， 那么这种方法就是无效的。 而且mysqladmin无法把密码清空。

(2) 执行SET PASSWORD语句，如下面语句将'z2'@'%'的密码设置为111111：

SET PASSWORD FOR 'z2'@'%' = PASSWORD('111111');

 如果是修改自己的密码，则FOR可以省略。

(3) 使用GRANT USAGE语句来指定某个用户的密码，而不影响当前用户的权限，如下面语句将'z2'@'%'的密码设置为111111：

GRANT USAGE ON *.* TO 'z2'@'%' IDENTIFIED BY '111111';

 GRANT只能增加权限而不会收回权限，而USAGE权限只是登录权限，所以这里使用GRANT USAGE不会影响到用户原先的权限。

(4) 直接修改mysql数据库的user表，但是要使用PASSWORD()函数，上面两种mysqladmin和GRANT自动加上PASSWORD()函数。

UPDATE user SET Password=PASSWORD('123456') WHERE User='z2' AND Host='localhost';

 

删除用户账号

(1) 使用 DROP USER 语句

DROP USER 'z2'@'localhost';

(2) 删除user表记录