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)