**用户权限管理主要有以下作用：**
1. 可以限制用户访问哪些库、哪些表
2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
3. 可以限制用户登录的IP或域名
4. 可以限制用户自己的权限是否可以授权给别的用户
## 一、创建用户
```sql
:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
```
说明：
**username**：你将创建的用户名
**host**：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
**password**：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
## 二、用户授权
```sql
mysql> grant all privileges on *.* to 'shuang'@'%' identified by 'shuang123456' with grant option;
```
**all privileges**：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。
**on**：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user
**to**：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”shuang”@”192.168.0.%”，表示shuang这个用户只能在192.168.0IP段登录。
**identified by**：指定用户的登录密码。
**with grant option**：表示允许用户将自己的权限授权给其它用户
可以使用GRANT给用户添加权限，权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个SELECT权限，后来又给用户添加了一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。
**总结： MySQL 赋予用户权限命令的简单格式可概括为：grant 权限 on 数据库对象 to 用户。**
*************************************************************************************************
**grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利：**
```sql
grant select on testdb.* to common_user@'%';
grant insert on testdb.* to common_user@'%';
grant update on testdb.* to common_user@'%';
grant delete on testdb.* to common_user@'%';
```
或者，用一条 MySQL 命令来替代：
```sql
grant select, insert, update, delete on testdb.* to common_user@'%';
```
## 三、刷新权限
对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。
```powershell
mysql> flush privileges;
```
## 四、查看用户权限
```powershell
mysql> grant select,create,drop,update,alter on *.* to 'yangxin'@'localhost' identified by 'yangxin0917' wif grant option;
mysql> show grants for 'yangxin'@'localhost';
```
查看当前用户（自己）权限：
```powershell
mysql> show grants;
```
## 五、回收权限
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：
```powershell
mysql> grant all on *.* to dba@localhost;
mysql> revoke all on *.* from dba@localhost;
```
例：删除yangxin这个用户的create权限，该用户将不能创建数据库和表。
```powershell
mysql> revoke create on *.* from 'yangxin@localhost';
mysql> flush privileges;
```
**MySQL grant、revoke 用户权限注意事项**
1、grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。
2、如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“
grant select on testdb.* to dba@localhost wif grant option;
这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。
*************************************************************************************************
**遇到 SELECT command denied to user '用户名'@'主机名' for table '表名' 这种错误，解决方法是需要把吧后面的表名授权，即是要你授权核心数据库也要。**
## 六、删除用户
```sql
mysql> select host,user from user;
+---------------+---------+
| host | user |
+---------------+---------+
| % | root |
| % | test3 |
| % | yx |
| 192.168.0.% | root |
| 192.168.0.% | test2 |
| 192.168.0.109 | test |
| ::1 | yangxin |
| localhost | yangxin |
+---------------+---------+
8 rows in set (0.00 sec)
mysql> drop user 'yangxin'@'localhost';
```
## 七、用户重命名
```powershell
shell> rename user 'test3'@'%' to 'test1'@'%';
```
## 八、修改密码
更新mysql.user表
**1、mysql> use mysql;**
###### mysql5.7之前
```powershell
mysql> update user set password=password('123456') where user='root';
```
###### mysql5.7之后
```powershell
mysql> update user set authentication_string=password('123456') where user='root';
或 alter user 'root'@'%' identified by "PassWord";
mysql> flush privileges;
```
**2、 用set password命令**
```powershell
语法：set password for ‘用户名’@’登录地址’=password(‘密码’)
mysql> set password for 'root'@'localhost'=password('123456');
```
**3、 mysqladmin**
```powershell
语法：mysqladmin -u用户名 -p旧的密码 password 新密码
mysql> mysqladmin -uroot -p123456 password 1234abcd
注意：mysqladmin位于mysql安装目录的bin目录下
```
## 九、忘记密码
**1、 添加登录跳过权限检查配置**
修改my.cnf，在mysqld配置节点添加skip-grant-tables配置
```powershell
[mysqld]
skip-grant-tables
```
**2、 重新启动mysql服务**
```powershell
shell> service mysqld restart
```
**3、 修改密码**
此时在终端用mysql命令登录时不需要用户密码，然后按照修改密码的第一种方式将密码修改即可。
注意：mysql库的user表，5.7以下版本密码字段为password，5.7以上版本密码字段为authentication_string
**4、 还原登录权限跳过检查配置**
将my.cnf中mysqld节点的skip-grant-tables配置删除，然后重新启动服务即可。
## 十、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限；
1、grant 创建、修改、删除 MySQL 数据表结构权限。
```powershell
mysql> grant create on testdb.* to developer@'192.168.0.%';
mysql> grant alter on testdb.* to developer@'192.168.0.%';
mysql> grant drop on testdb.* to developer@'192.168.0.%';
```
2、grant 操作 MySQL 外键权限。
```powershell
mysql> grant references on testdb.* to developer@'192.168.0.%';
```
3、grant 操作 MySQL 临时表权限。
```powershell
mysql> grant create temporary tables on testdb.* to developer@'192.168.0.%';
```
4、grant 操作 MySQL 索引权限。
```powershell
mysql> grant index on testdb.* to developer@'192.168.0.%';
```
5、grant 操作 MySQL 视图、查看视图源代码 权限。
```powershell
mysql> grant create view on testdb.* to developer@'192.168.0.%';
mysql> grant show view on testdb.* to developer@'192.168.0.%';
```
6、grant 操作 MySQL 存储过程、函数 权限。
```powershell
mysql> grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
mysql> grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
mysql> grant execute on testdb.* to developer@'192.168.0.%';
```
## 十一、grant 普通 DBA 管理某个 MySQL 数据库的权限。
```powershell
mysql> grant all privileges on testdb to dba@'localhost'
```
其中，关键字 “privileges” 可以省略。
## 十二、grant 高级 DBA 管理 MySQL 中所有数据库的权限。
```powershell
mysql> grant all on *.* to dba@'localhost'
```
## 十三、MySQL grant 权限，分别可以作用在多个层次上。
1、grant 作用在整个 MySQL 服务器上：
```powershell
mysql> grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
mysql> grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
```
2、grant 作用在单个数据库上：
```powershell
mysql> grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
```
3、grant 作用在单个数据表上：
```powershell
mysql> grant select, insert, update, delete on testdb.orders to dba@localhost;
这里在给一个用户授权多张表时，可以多次执行以上语句。例如：
mysql> grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
mysql> grant select on smp.mo_sms to mo_user@'%' identified by '123345';
```
4、 grant 作用在表中的列上：
```powershell
mysql> grant select(id, se, rank) on testdb.apache_log to dba@localhost;
```
5、 grant 作用在存储过程、函数上：
```powershell
mysql> grant execute on procedure testdb.pr_add to 'dba'@'localhost'
mysql> grant execute on function testdb.fn_add to 'dba'@'localhost'
```
## 十四、grant 高级 DBA 管理 MySQL 中所有数据库的权限。
```powershell
mysql> grant all on *.* to dba@'localhost'
```
## 十五、MySQL grant 权限，分别可以作用在多个层次上。
1、grant 作用在整个 MySQL 服务器上：
```powershell
mysql> grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
mysql> grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库。
```
2、grant 作用在单个数据库上：
```powershell
mysql> grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
```
3、grant 作用在单个数据表上：
```powershell
mysql> grant select, insert, update, delete on testdb.orders to dba@localhost;
这里在给一个用户授权多张表时，可以多次执行以上语句。例如：
mysql> grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
mysql> grant select on smp.mo_sms to mo_user@'%' identified by '123345';
```
4、 grant 作用在表中的列上：
```powershell
mysql> grant select(id, se, rank) on testdb.apache_log to dba@localhost;
```
5、 grant 作用在存储过程、函数上：
```powershell
mysql> grant execute on procedure testdb.pr_add to 'dba'@'localhost'
mysql> grant execute on function testdb.fn_add to 'dba'@'localhost'
```
*************************************************************************************************
很明显总共28个权限，下面是具体的权限介绍：
## 一．权限表
mysql授权表共有5个表：user、db、host、tables_priv和columns_priv。
授权表的内容有如下用途：
**user表**
user表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局（超级用户）权限。在user表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了DELETE权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。
**db表**
db表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。
**host表**
host表与db表结合使用在一个较好层次上控制特定主机对数据库的访问权限，这可能比单独使用db好些。这个表不受GRANT和REVOKE语句的影响，所以，你可能发觉你根本不是用它。
**tables_priv表**
tables_priv表指定表级权限，在这里指定的一个权限适用于一个表的所有列。
**columns_priv表**
columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。
权限表的存取过程是：
1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证；
2)通过权限验证，进行权限分配时，按照useràdbàtables_privàcolumns_priv的顺序进行分配。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推
## 二．MySQL各种权限（共27个）
（以下操作都是以root身份登陆进行grant授权，以p1@localhost身份登陆执行各种命令。）
**1. usage**
连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。
```sql
mysql> grant usage on *.* to ‘p1′@’localhost’ identified by ‘123′;
```
该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。
**2. select**
必须有select的权限，才可以使用select table
```sql
mysql> grant select on pyt.* to ‘p1′@’localhost’;
mysql> select * from shop;
```
**3. create**
必须有create的权限，才可以使用create table
```sql
mysql> grant create on pyt.* to ‘p1′@’localhost’;
```
**4. create routine**
必须具有create routine的权限，才可以使用{create |alter|drop} {procedure|function}
```sql
mysql> grant create routine on pyt.* to ‘p1′@’localhost’;
```
当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：
```sql
mysql> show grants for ‘p1′@’localhost’;
+—————————————————————————+
Grants for p1@localhost
+————————————————————————–+
| GRANT USAGE ON *.* TO ‘p1′@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |
| GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO ‘p1′@’localhost’|
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO ‘p1′@’localhost’ |
+————————————————————————————-+
```
**5. create temporary tables(注意这里是tables，不是table)**
必须有create temporary tables的权限，才可以使用create temporary tables.
```sql
mysql> grant create temporary tables on pyt.* to ‘p1′@’localhost’;
[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
mysql> create temporary table tt1(id int);
```
**6. create view**
必须有create view的权限，才可以使用create view
```sql
mysql> grant create view on pyt.* to ‘p1′@’localhost’;
mysql> create view v_shop as select price from shop;
```
**7. create user**
要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
```sql
mysql> grant create user on *.* to ‘p1′@’localhost’;
或：mysql> grant insert on *.* to p1@localhost;
```
**8. insert**
必须有insert的权限，才可以使用insert into ….. values….
**9. alter**
必须有alter的权限，才可以使用alter table
```sql
alter table shop modify dealer char(15);
```
**10. alter routine**
必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}
```sql
mysql>grant alter routine on pyt.* to ‘p1′@’ localhost ‘;
mysql> drop procedure pro_shop;
Query OK, 0 rows effected (0.00 sec)
mysql> revoke alter routine on pyt.* from ‘p1′@’localhost’;
[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
mysql> drop procedure pro_shop;
ERROR 1370 (42000): alter routine command denied to user ‘p1′@’localhost’ for routine ‘pyt.pro_shop’
```
**11. update**
必须有update的权限，才可以使用update table
```sql
mysql> update shop set price=3.5 where article=0001 and dealer=’A';
```
**12. delete**
必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)
**13. drop**
必须有drop的权限，才可以使用drop database db_name; drop table tab_name;
```sql
drop view vi_name; drop index in_name;
```
**14. show database**
通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。
对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：
```sql
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema|
| pyt |
| test |
+——————–+
```
**15. show view**
必须拥有show view权限，才能执行show create view。
```sql
mysql> grant show view on pyt.* to p1@localhost;
mysql> show create view v_shop;
```
**16. index**
必须拥有index权限，才能执行[create |drop] index
```sql
mysql> grant index on pyt.* to p1@localhost;
mysql> create index ix_shop on shop(article);
mysql> drop index ix_shop on shop;
```
**17. excute**
执行存在的Functions,Procedures
```sql
mysql> call pro_shop1(0001,@a)；
+———+
| article |
+———+
| 0001 |
| 0001 |
+———+
mysql> select @a;
+——+
| @a |
+——+
| 2 |
+——+
```
**18. lock tables**
必须拥有lock tables权限，才可以使用lock tables
```sql
mysql> grant lock tables on pyt.* to p1@localhost;
mysql> lock tables a1 read;
mysql> unlock tables;
```
**19. references**
有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。
**20. reload**
必须拥有reload权限，才可以执行flush [tables | logs | privileges]
```sql
mysql> grant reload on pyt.* to p1@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> grant reload on *.* to ‘p1′@’localhost’;
Query OK, 0 rows effected (0.00 sec)
mysql> flush tables;
```
**21. replication client**
拥有此权限可以查询master server、slave server状态。
```sql
mysql> show master status;
ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
mysql> grant Replication client on *.* to p1@localhost;
或：mysql> grant super on *.* to p1@localhost;
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000006 | 2111 | | |
+——————+———-+————–+——————+
mysql> show slave status;
```
**22. replication slave**
拥有此权限可以查看从服务器，从主服务器读取二进制日志。
```sql
mysql> show slave hosts;
ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
mysql> show binlog events;
ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
mysql> grant replication slave on *.* to p1@localhost;
mysql> show slave hosts;
Empty set (0.00 sec)
mysql>show binlog events;
+—————+——-+—————-+———–+————-+————–+
| Log_name | Pos | Event_type | Server_id| End_log_pos|Info |
+—————+——-+————–+———–+————-+—————+
| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use `mysql`; create table a1(me int)engine=myisam|
……………………………………
```
**23. Shutdown**
关闭MySQL：
```powershell
[mysql@mydev ~]$ mysqladmin shutdown
```
重新连接：
```powershell
[mysql@mydev ~]$ mysql
```
```powershell
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
```
```powershell
[mysql@mydev ~]$ cd /u01/mysql/bin
[mysql@mydev bin]$ ./mysqld_safe &
[mysql@mydev bin]$ mysql
```
**24. grant option**
拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）
```sql
mysql> grant Grant option on pyt.* to p1@localhost;
mysql> grant select on pyt.* to p2@localhost;
```
**25. file**
拥有file权限才可以执行 select ..into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。
```sql
mysql> grant file on *.* to p1@localhost;
mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;
```
**26. super**
这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。
```sql
mysql> grant super on *.* to p1@localhost;
mysql> purge master logs before ‘mysql-bin.000006′;
```
**27. process**
通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。
```sql
mysql> show processlist;
+—-+——+———–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+——-+——————+
| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |
+—-+——+———–+——+———+——+——-+——————+
另外，
管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟*.*
mysql> grant super on pyt.* to p1@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> grant super on *.* to p1@localhost;
Query OK, 0 rows affected (0.01 sec)
```
