Mysql数据库用户及用户权限管理，Navicat设置用户权限

---

---

相关内容原文地址： CSDN：dayi_123：[mysql数据库用户及用户权限管理](https://blog.csdn.net/dayi_123/article/details/82989264)

---

---

一、Mysql数据库的权限

1.1 mysql数据库用户权限级别

1. 全局性管理权限：作用于整个mysql实例级别
2. 数据库级别：作用于指定的某个数据库上或者所有数据库上
3. 数据库对象级别权限：作用于指定的数据库对象上（表或者视图）

1.2 mysql数据库用户权限

权限名称	权限说明
All/All Privileges	全局或者全数据库对象级别的所有权限
Alter	修改表结构的权限，但必须要求有create和insert权限配合
Alter routine	修改或者删除存储过程、函数的权限
Create	创建新的数据库和表的权限
Create routine	允许创建存储过程、函数的权限
Create tablespace	允许创建、修改、删除表空间和日志组的权限
Create temporary tables	创建临时表权限
Create user	创建、修改、删除、重命名user
Create view	创建视图
Delete	删除行数据
drop	删除数据库、表、视图的权限，包括truncate table命令
Event	查询，创建，修改，删除MySQL事件
Execute	执行存储过程和函数
File	在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select ,into outfile,load file()函数等
Grant option	授权或者收回给其他用户你给予的权限
Index	创建和删除索引
Insert	在表里插入数据
Lock	对拥有select权限的表进行锁定，以防止其他链接对此表的读或写
Process	允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令
Reference	允许创建外键
Reload	执行flush命令，指明重新加载权限表到系统内存中
Replication client	执行show master status,show slave status,show binary logs命令
Replication slave	允许slave主机通过此用户连接master以便建立主从复制关系
Select	从表中查看数据
Show databases	通过执行show databases命令查看所有的数据库名
Show view	通过执行show create view命令查看视图创建的语句
Shutdown	关闭数据库实例，执行语句包括mysqladmin shutdown
Super	允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to创建复制关系命令，以及create/alter/drop server等命令
Trigger	允许创建，删除，执行，显示触发器的权限
Update	修改表中数据的权限
Usage	创建一个用户之后的默认权限，本身代表无权限

1.3 存放用户权限表的说明

Mysql数据库用户权限存储在mysql库的user, db, tables_priv, columns_priv, procs_priv这几个系统表中， MySQL实例启动后就加载到内存中。

存放权限的表	表的作用
user	存放用户账户信息以及全局级别（所有数据库）权限，（存放了那些用户可以访问那些数据库的权限）
db	数据库级别权限，决定了来自哪些主机的哪些用户可以访问此数据库
tables_priv	存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的那张表
columns_priv	存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的字段
procs_priv	存放存储过程和函数级别的权限

Mysql数据库权限管理中，字段user,password,authencation_string,db,table_name大小写敏感，而字段host,column_name,routine_name对大小写不敏感。

二、用户权限管理

2.1 创建用户及授权

Mysql用户创建可以通过create user/grant命令创建，也可以通过insert语句直接操作MySQL系统权限表来创建。

创建及给用户授权命令

创建用户：

CREATE USER username@'host' IDENTIFIED BY 'password'

用户授权：

GRANT permission ON database.tables TO username@’host’

创建用户并授权：

GRANT permission ON database.tables TO username@'hostt'  WITH GRANT OPTION;

MYSQL创建用户及给用户授权的用户名主机部分由两部分组成，分别为用户名和登录主机名，格式为’username’@’hostname’;hostname字段可以使用主机名或者ip地址，同时hostname字段允许使用”%”和”_”两种字符进行匹配。

# 创建用户dayi123并授予本地登录所有库所有权限
mysql> create user 'dayi123'@'localhost' identified by 'dayi123';
mysql> grant all privileges on *.* to 'dayi123'@'localhost' with grant option;
# 创建用户用户dayi通过192.168.0.0段ip地址登录的增删该查跟新库web的权限
mysql> grant select,insert,update,delete,create,drop on web.* to 'dayi'@'192.168.%.%' identified by 'dayi123';
# 给root用户授权可以通过192.168.0.0段地址登录
mysql> grant all on *.* to root@'192.168.%.%' identified by 'dayi123';

2.2 用户权限查看

查看已经授权给用户权限信息命令：show grants for ‘username’@’host’

# 查看授权给'dayi'@'192.168.%.%'的权限信息
mysql> show grants for 'dayi'@'192.168.%.%';
+---------------------------------------+
| Grants for dayi@192.168.%.%              |                                         
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'dayi'@'192.168.%.%'   |                                         
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `web`.* TO 'dayi'@'192.168.%.%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)

查看创建用户的语句：show create user ‘username’@’host’

# 查看给用户’dayi’没有授权的信息
mysql> show create user dayi@'192.168.%.%';
+------------------------------------------+
| CREATE USER for dayi@192.168.%.%            |                                                                                                                       
+--------------------------------------------+
| CREATE USER 'dayi'@'192.168.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*8FD40AC91E6D5D89D1060096FDEF8A4ECAA05B5C' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+-----------------------------------------------+
1 row in set (0.00 sec)

2.3 收回用户权限

收回用户权限是可以通过语句revoke完成，命令格式为：

Revoke permission on ‘database’,’tables’ from ‘username’@’host’

# 收回用户权限是可以通过语句revoke完成，命令格式为：
Revoke permission on ‘database’,’tables’ from ‘username’@’host’

2.4 设置用户密码

Mysql数据库一般在创建用户时就给用户设置了密码，如果在用户创建完成后想修改密码，可通过相应的命令来完成。

修改其他用户密码（当前用户要有权限）,一般在root用户下操作

登录mysql后的修改命令：

Alter user ‘username’@’host’ identified by ‘newpassword’
Set password for ‘username’@’host’=password(‘newpassword’)
Grant user on permission to ‘user’@’host’ identified by ‘newpassword’

在系统命令行下修改：

mysqladmin –u uername –h host password “newpassword”

# 修改dayi123的密码为dayi1234
mysql> alter user 'dayi123'@'localhost' identified by 'dayi1234';
Query OK, 0 rows affected (0.35 sec)
# 修改dayi远程登录密码为dayi1234
mysql> set password for 'dayi'@'192.168.%.%'=password('dayi1234');
Query OK, 0 rows affected, 1 warning (0.00 sec)

修改当前用户密码

alter user user() identified by ‘newpassword’
set password=password(‘newpassword’)

# 修改当前登录的root的密码为dayi1234
mysql> alter user user() identified by 'dayi1234';
Query OK, 0 rows affected (0.00 sec)

2.5 删除用户

删除命令：drop user ‘username’@’host’

# 删除用户dayi远程登录
mysql> drop user 'dayi'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)
# 查看删除后的用户
mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| root          | 192.168.%.% |
| dayi          | localhost   |
| dayi123       | localhost   |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+

2.6 用户的锁定与解锁

在创建用户时或用户创建后可以将用户锁定，用户被锁定后，此用户无法完成登录，锁定与解锁的命令如下：

在创建用户时锁定用户：

     Create user ‘username’@’host’ identified by ‘password’ account lock;

用户创建受锁定用户L

     Alter user ‘username’@’host’ account lock

解锁用户：alter user ‘username’@’host’ account unlock;

# 锁定用户dayi123
mysql> alter user 'dayi123'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)
# 用户被锁定后等录时报如下错：
~]# mysql -udayi123 -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'dayi123'@'localhost'. Account is locked.
# 解锁用户dayi123
mysql> alter user 'dayi123'@'localhost' account unlock;
Query OK, 0 rows affected (0.00 sec)

2.7 设置密码过期策略

Mysql数据库用户同系统用户一样，可以设置密码过期策略，密码的过期天数可以在配置文件里面设置，也可以通过命令设置，通过命令为每个用户设置密码的过期天数则会覆盖系统配置文件中的设置。

1. 配置文件中设置
   选项：default_password_lifetime=”过期天数” ：如果设置为0，则密码永不过期。
2. 通过命令设置

alter user ‘username’@’localhost’ password expire interval number day; 设置过期天数
 alter user ‘username’@’localhost’ password expire never; 设置密码不过期
alter user ‘username’@’localhost’ password expire default; 默认过期策略
alter user ‘username’@’localhost’ password expire; 手动强制密码过期

#设置用户dayi123的密码180天后过期
mysql> alter user 'dayi123'@'localhost' password expire interval 180 day;
Query OK, 0 rows affected (0.00 sec)
# 设置用户dayi的密码立即过期
mysql> alter user 'dayi'@'localhost' password expire;
Query OK, 0 rows affected (0.00 sec)
# 设置密码过期后执行命令时需要修改密码才能执行命令
]# mysql -udayi –p
……
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

2.8 mysql数据库用户资源限制

通过修改mysql数据库资源限制可以限制用户在某个时间段内连接mysql数据库的时间、执行语句的次数等。

1. 设置参数：

max_user_connections：全局选项，限制所有用户在同一时间连接mysql数据库实例的数量
MAX_QUERIES_PER_HOUR：一个用户在一个小时内可以执行查询的次数（基本包含所有语句）
MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修改数据库或表的语句）
MAX_CONNECTIONS_PER_HOUR：一个用户在一个小时内可以连接MySQL的时间
MAX_USER_CONNECTIONS：一个用户可以在同一时间连接MySQL实例的数量

2. 设置mysql数据库用户资源限制
   设置mysql数据库用户资源限制时可以在创建用户时设置，也可以在用户创建完成后通过命令设置。

# 创建用户day,并对用户day的资源进行控制
mysql> create user 'day'@'localhost' identified by 'dayi123' with max_queries_per_hour 20 max_updates_per_hour 10 max_connections_per_hour 5 max_user_connections 2;  
Query OK, 0 rows affected (0.00 sec)
# 对用户dayi进行资源控制
mysql> alter user 'dayi'@'localhost'  with max_queries_per_hour 20 max_updates_per_hour 10 max_connections_per_hour 5 max_user_connections 2;
Query OK, 0 rows affected (0.00 sec)

当需要取消某个选项的，只需将某个选项的值设置为0即可。

三、Navicat设置mysql用户数据库权限

点击上图的新建用户，可以看到相关权限的配置。