MySQL用户及权限管理

MySQL——用户及权限管理

Mysql权限一览表：

Privilege	Context	description
[ALL PRIVILEGES]	Server administration	在全局或表级别授予所有全局特权或 所有表级权限
ALTER	Tables	“Alter table”可以更改表结构，“Alter table”需要“create”和“insert”权限
ALTER ROUTINE	Stored routines	更改或删除过程和函数
CREATE	Databases, tables, or indexes	创建数据库，创建表，创建索引
CREATE ROUTINE	Stored routines	创建存储过程和函数
CREATE TABLESPACE	Server administration	创建，删除表空间和日志组
CREATE TEMPORARY TABLES	Tables	创建临时表，当一个会话创建完一个临时表。该会话具有对该表任何操作权限
CREATE USER	Server administration	创建用户，删除用户，重命名用户
CREATE VIEW	Views	创建视图
DELETE	Tables	从表中删除行
DROP	Databases, tables, or views	删除数据库，删除表，删除视图
EVENT	Databases	创建，删除，更改及展示 event scheduler
EXECUTE	Stored routines	运行存储过程和函数
FILE	File access on server host	该权限影响
GRANT OPTION	Databases, tables, or stored routines	将自己拥有的权限授给及回收其他用户的权限
INDEX	Tables	创建删除索引
INSERT	Tables or columns	对表插入行
LOCK TABLES	Databases	执行lock tables/unlock tables的权限
PROCESS	Server administration	执行SHOW PROCESSLIST和KILL命令的权限，只能指定所有数据库。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。
PROXY	Server administration	允许一个用户模拟或被当做另一个用户。
REFERENCES	Databases or tables	创建外键
RELOAD	Server administration	Flush命令需要该权限
REPLICATION CLIENT	Server administration	使用SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOGS语句
REPLICATION SLAVE	Server administration	允许用户向主库请求变化
SELECT	Tables or columns	查询表的权限
SHOW DATABASES	Server administration	查看数据库列表
SHOW VIEW	Views	查看视图列表
SHUTDOWN	Server administration	调用关闭数据库的权限
SUPER	Server administration
TRIGGER	Tables	创建删除触发器
UPDATE	Tables or columns	更新表中行
USAGE	Server administration	在全局级别用grant配合去修改用户属性，比如resource limit

mysql里的用户以USER_NAME@HOST唯一确定一个用户，所以在创建用户和赋权时需要指定host，如果省略，则默认为%（无访问地址限制）

注意：host实现同pg_hba.conf的addres功能

1、创建用户

mysql>  create user test@'localhost' identified by 'test123';
Query OK, 0 rows affected (0.00 sec)

2、删除用户

drop user test@'localhost';

3、查询用户

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

4、用户授权

语法：

GRANT priv_type  ON [object_type] priv_level TO user [WITH GRANT OPTION]

回收权限使用revoke from 关键字其他同授予权限

mysql> grant select on mysql.user to test@'localhost';
Query OK, 0 rows affected (0.00 sec)

#可以使用user.*来授权
mysql> grant select on mysql.* to test@'localhost';
Query OK, 0 rows affected (0.00 sec)

5、查看权限

查看某一个用户权限：

show grants for USER_NAME@HOST

mysql> show grants for test@'localhost';
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'             |
| GRANT SELECT ON `mysql`.* TO 'test'@'localhost'      |
| GRANT SELECT ON `mysql`.`user` TO 'test'@'localhost' |
+------------------------------------------------------+
3 rows in set (0.00 sec)

查看当前session登录用户的权限：

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

也可以从以下系统表查看：

• 查看mysql.user表 ---- 查看全局所有库的权限
• 查看mysql.db表 ---- 查看指定库的权限
• 查看mysql.table_priv表 ---- 查看指定表的权限
• 查看mysql.column_priv表 ---- 查看指定列的权限

更多用户及权限信息请参阅：官方文档

生产环境一般如下赋权：

业务用户类型	赋权语句	说明
查询用户	Grant select on dbname.* to user_a	给予查询所有表
开发用户	Grant insert,update,select,delete on dbname.* to user_b	给予增删改查权限
业务管理用户	Grant all privileges on dbname.* to user_c	给予业务库所有权限

6、用户登录

# mysql -utest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> 

7、查系统表

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)