MySQL的用户和权限管理

1、创建与删除用户：
创建用户语法：CREATE USER user_name [IDENTIFIED BY ‘user_password’];

MariaDB [(none)]> create user  user1  identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select Host,User,Password  from mysql.user where User='user1';
+------+-------+-------------------------------------------+
| Host | User  | Password                                  |
+------+-------+-------------------------------------------+
| %    | user1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

用户重命名：
语法：RENAME USER old_username TO new_username;

MariaDB [(none)]> rename user user1 to aaa;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select Host,User,Password  from mysql.user where User='aaa';
+------+------+-------------------------------------------+
| Host | User | Password                                  |
+------+------+-------------------------------------------+
| %    | aaa  | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)

删除用户语法：DROP USER user_name;

MariaDB [(none)]> drop user user1;
Query OK, 0 rows affected (0.00 sec)

2、GRANT和REVOKE
GRANT和REVOKE命令用来管理访问权限，也可以用来创建和删除用户。GRANT和REROKE命令对于谁可以操作服务器及其内容的各个方面提供了多程度控制，从谁可以关闭服务器，到谁可以修改特定表字段的信息都可以控制。



具体使用：

MariaDB [(none)]> grant select,insert  on  aaa.*  to 'user2'@'192.168.126.%' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)      //授权user2用户select和insert权限，并指定可登录的网段；

MariaDB [(none)]> flush privileges;        //刷新授权表；
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for  'user2'@'192.168.126.%';       //查看user2用户的权限信息；
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.126.%                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.126.%' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT ON `aaa`.* TO 'user2'@'192.168.126.%'                                                       |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> revoke  insert on aaa.* from 'user2'@'192.168.126.%';       //收回user2用户的insert权限；
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show  grants  for 'user2'@'192.168.126.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.126.%                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.126.%' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT ON `aaa`.* TO 'user2'@'192.168.126.%'                                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

注意：当用户使用GRANT和REVOKE命令更改用户权限后，退出MySQL系统，用户使用新账户名登录MySQL的时候，可能会因为没有刷新授权表而导致登录错误，这是因为在用户设置账号完毕后，只有重新加载授权表才能使之前设置的授权表生效。使用FLUSH PRIVILEGES命令可以重载授权表。另外需要注意的是，只有root这样拥有全部权限的用户才可以执行此命令。当用户重载授权表后，退出MySQL后，使用新创建的用户名即可正常登录MySQL。

3、授予权限的层级
1）全局层级：
全局权限使用于一个给定服务器中的所有数据库，存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销数据库权限。
2）数据库层级
数据库权限适用于一个给定数据库中的所有目标，存储在mysql.db和mysql.host表中。GRANT ALL ON dn_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
3）表层级
表权限适用于一个给定表中的所有列，存储在mysql.tables_priv表中。GRANT ALL ON db_name.tb1_name和REVOKE ALL ON db_name.tb1_name只授予和撤销表权限。
4）列层级
列权限适用于一个给定表中的单一列，这些权限存储在mysql.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。
5）子程序层级
CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

4、账户密码管理
1）可以用mysqladmin命令更改密码

[root@www ~]# mysqladmin -uroot password "redhat"
[root@www ~]# mysql -uroot -predhat -e "select Host,User,Password from mysql.user where User='root';"
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| www       | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
+-----------+------+-------------------------------------------+

2）通过set password命令设置用户密码

MariaDB [(none)]> set password=password('123456');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select Host,User,Password from mysql.user where User='root';
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| www       | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.01 sec)

3）更改已有账户密码，应用UPDATE语句来设置password列值

MariaDB [(none)]> update mysql.user set Password=password('redhat') where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [(none)]> select Host,User,Password from mysql.user where User='root';
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| www       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)