mysql授权管理

1 简介

权限系统的作用是授予来自某个主机的某个用户可以查询、更新、删除等数据库操作的权限。

通过create user、grant、revoke语句授权

权限信息保存在名叫mysql的数据库中，并在数据库启动后加载到内存。

认证信息包括用户名@主机名

2 权限的分级

全局：作用于整个mysql实例

数据库级：作用于特定数据库

数据库对象级：作用于表、视图

3 查看默认root权限

mysql> show grants for root@localhost\G;

4 系统权限表

user：存放用户账户信息，以及全局级别权限

db：存放数据库级别权限

tables_priv：表级别权限

columns_priv：列级别权限

procs_priv：存储过程和函数级别权限

5 修改权限后生效时间

执行grant,revoke,setpassword,renameuser命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中。

如果直接修改上面的权限表，生效时间各不相同，这里不详述。

6 创建用户并授权

CREATE USER/GRANT命令

例子：创建szj@*用户

 

授权

mysql> CREATE USER 'szj'@'localhost' IDENTIFIED BY 'xxx';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON szj_db.* TO `szj`@`localhost` WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql>

7 回收权限

mysql> REVOKE ALL PRIVILEGES ON szj_db.* FROM 'szj'@'*';

8 删除用户

9 设置用户访问限制

max_user_connections

• 通过设置全局变量max_user_connections可以限制所有用户在同一时间连接MySQL实例的数量，但此参数无法对每个用户区别对待，所以MySQL提供了对每个用户的资源限制管理

MAX_QUERIES_PER_HOUR

• MAX_QUERIES_PER_HOUR:一个用户在一个小时内可以执行查询的次数(基本包含所有语句)

MAX_UPDATES_PER_HOUR

• MAX_UPDATES_PER_HOUR:一个用户在一个小时内可以执行修改的次数(仅包含修改数据库或表的语句)

MAX_CONNECTIONS_PER_HOUR

• MAX_CONNECTIONS_PER_HOUR:一个用户在一个小时内可以连接MySQL的时间

MAX_USER_CONNECTIONS

    • MAX_USER_CONNECTIONS:一个用户可以在同一时间连接MySQL实例的数量，注意，当针对某个用户当MAX_USER_CONNECTIONS非0时，则忽略全局系统参数MAX_USER_CONNECTIONS,反之则全局系统参数生效！

    • 从5.0.3版本开始，对用户‘user’@‘%.example.com’的资源限制是指所有 通过example.com域名主机连接user用户的连接，而不是分别指从 host1.example.com和host2.example.com主机过来的连接

10 已存在用户作访问限制

mysql> ALTER USER szj@localhost WITH MAX_USER_CONNECTIONS 5;

11 已存在用户取消访问限制

取消某项资源限制即是把原先的值修改为0

mysql> ALTER USER szj@localhost WITH MAX_USER_CONNECTIONS 0;

12 用户上锁and解锁

 mysql> alter user 'szj'@'localhost' account lock; 

 mysql> alter user 'szj'@'localhost' account unlock; 

13 附录

显示哪些线程正在运行

mysql> show processlist;

mysql> show engines;

 

由此可以看出，在诸多的存储引擎中，只有InnoDB支持事务

查看字段信息

 mysql> desc user;