mysql认证指南_MySQL授权认证

MySQL权限系统介绍

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

不能明确的指定拒绝某个用户的连接

权限控制(授权与回收)的执行语句包括create user,grant,revoke

授权后的权限都会存放在MySQL的内部数据库中(数据库名叫mysql)，并在数据库启动之后把权限信息复制到内存中

MySQL用户的认证信息不光包括用户名，还要包含连接发起的主机(以下两个joe被认为不是同一个用户)

SHOW GRANTS FOR 'joe'@'office.example.com';

SHOW GRANTS FOR 'joe'@'home.example.com';

查看用户：

mysql> selectuser,host from mysql.user;+---------------+-----------+

| user | host |

+---------------+-----------+

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+---------------+-----------+

3 rows in set (0.00 sec)

创建与删除用户：

#创建用户并授权

mysql> create user admin@'10.0.0.51' identified by '123456';

Query OK,0 rows affected (0.00sec)

#删除用户

mysql> drop user admin@'10.0.0.51';

Query OK,0 rows affected (0.00 sec)

查询与插入权限：

#admin@'10.0.0.1'用户对course有查询权限

mysql> grant select on course.* to admin@'10.0.0.1';

Query OK,0 rows affected (0.00sec)

#admin@'10.0.0.2'用户对course有插入权限

mysql> grant insert on course.* to admin@'10.0.0.2';

MySQL权限级别

全局性的管理权限，作用于整个MySQL实例级别

数据库级别的权限，作用域某个指定的数据库上或者所有的数据库上

数据库对象级别的权限，作用于指定的数据库对象上(表、视图等)或者所有的数据库对象上

系统权限表

权限存储在mysql库的 user，db，tables_priv，columns_priv，and proces_priv这几个系统表中，待MySQL实例启动后就加载到内存中

user表：存放用户信息以及全局信息(所有数据库)权限，决定了来自哪里主机的哪些用户可以访问数据库实例，如果有全局权限则意味这对所有数据库都有此权限

Db表：存放数据库级别的权限，决定了来自哪里主机的哪些用户可以访问此数据库

Tables_priv表：存放表级别的权限，决定了来自哪里主机的哪些用户可以访问数据库的这个表

Columns_priv表：存放列级别的权限，决定了来自哪些数据的哪些用户可以访问数据库表的这个字段

Procs_priv表：存放存储过程和函数级别的权限

MySQL修改权限的生效

执行Grant，revoke，set，password，rename，user命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中

如果执行 insert/update/delete操作上述的系统权限表之后，则必须在执行刷新权限命令才能同步到系统内存中，刷新权限命令包括：flush privileges/ mysqladmin flush-privileges/ mysqladmin reload

如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效

如果是修改database级别的权限，则新权限在客户端执行use database命令后生效

如果是修改global级别的权限，则需要重新创建连接新权限才能生效

--skip-grant-tables可以跳过所有系统权限表才允许所有用户登录，只在特殊情况下暂停使用

全局权限授予与撤销：

#全局查询权限和插入授予10.0.0.1mysql> grant select,insert on *.* to admin@'10.0.0.51';

Query OK,0 rows affected (0.00sec)

#查看有哪些权限

mysql> show grants for admin@'10.0.0.51';+----------------------------------------------------+

| Grants for admin@10.0.0.51 |

+----------------------------------------------------+

| GRANT SELECT, INSERT ON *.* TO 'admin'@'10.0.0.51' |

+----------------------------------------------------+

1 row in set (0.00sec)

mysql> select * from mysql.user where user='admin'\G;

#撤销权限

mysql> revoke select on *.* from admin@'10.0.0.51';

Query OK,0 rows affected (0.01sec)

#现在只能插入，不能查询

mysql> show grants for admin@'10.0.0.51';+--------------------------------------------+

| Grants for admin@10.0.0.51 |

+--------------------------------------------+

| GRANT INSERT ON *.* TO 'admin'@'10.0.0.51' |

+--------------------------------------------+

1 row in set (0.00sec)

mysql> select * from mysql.user\G;

数据库级别授权与撤销：

#10.0.0.51用户对test库有更新权限

mysql> grant update on test.* to admin@'10.0.0.51';

Query OK,0 rows affected (0.00sec)

#查看

mysql> show grants for admin@'10.0.0.51';+----------------------------------------------------+

| Grants for admin@10.0.0.51 |

+----------------------------------------------------+

| GRANT SELECT, INSERT ON *.* TO 'admin'@'10.0.0.51' |

| GRANT UPDATE ON `test`.* TO 'admin'@'10.0.0.51' |

+----------------------------------------------------+

2 rows in set (0.00sec)

mysql> select * from mysql.db where user='admin'\G;

#撤销授权

mysql> revoke update on test.* from admin@'10.0.0.51';

Query OK,0 rows affected (0.00sec)

#查询权限表

mysql> select * from mysql.db where user='admin'\G;

数据库对象级别授权与撤销：

#10.0.0.51用户表级别授权

mysql> grant delete on world.city to admin@'10.0.0.51' identified by '123456';

Query OK,0 rows affected (0.00sec)

#查询权限

mysql> show grants for admin@'10.0.0.51';+-------------------------------------------------------+

| Grants for admin@10.0.0.51 |

+-------------------------------------------------------+

| GRANT SELECT, INSERT ON *.* TO 'admin'@'10.0.0.51' |

| GRANT UPDATE ON `test`.* TO 'admin'@'10.0.0.51' |

| GRANT UPDATE ON `mysql`.* TO 'admin'@'10.0.0.51' |

| GRANT DELETE ON `world`.`city` TO 'admin'@'10.0.0.51' |

4 rows in set (0.00sec)

mysql> select *from mysql.tables_priv\G;

#删除权限

mysql> delete from world.city where name='Wuhan';

Query OK,1 row affected (0.00sec)

#查询

mysql> select * from world.city where name='Wuhan';

Empty set (0.00sec)

#更新到内存

mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

#查询权限表

mysql> select * from mysql.tables_priv where user='admin'\G;

授权列层级的权限：

#授权列级name和id的查询权限

mysql> grant select (name,id) on world.city to admin@'10.0.0.51';

Query OK,0 rows affected (0.00sec)

#查看用户权限

mysql> show grants for admin@'10.0.0.51';+--------------------------------------------------------------------------+

| Grants for admin@10.0.0.51 |

+--------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'admin'@'10.0.0.51' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.* TO 'admin'@'10.0.0.51' |

| GRANT SELECT (name, id) ON `world`.`city` TO 'admin'@'10.0.0.51' |

3 rows in set (0.00sec)

#添加到内存

mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

#查询表

mysql> select *from mysql.columns_priv;+-----------+-------+-------+------------+-------------+---------------------+-------------+

| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |

+-----------+-------+-------+------------+-------------+---------------------+-------------+

| 10.0.0.51 | world | admin | city | id | 0000-00-00 00:00:00 | Select |

| 10.0.0.51 | world | admin | city | name | 0000-00-00 00:00:00 | Select |

+-----------+-------+-------+------------+-------------+---------------------+-------------+

2 rows in set (0.00 sec)