限制用户的操作权限并不是怕有人恶意搞破坏，而是为了减少失误操作的可能性。
详细文档：https://dev.mysql.com/doc/refman/8.0/en/user-account-management.html
关于用户的信息都存储在 mysql 数据库下的 user 表中，查看所有用户名：
mysql> USE mysql; mysql> SELECT user FROM user;
+------------------+
| user |
+------------------+
| root |
| root |
| root |
| debian-sys-maint |
| root |
+------------------+
5 rows in set (0.04 sec)
创建新用户：
CREATE USER 'new_guy'@'localhost' IDENTIFIED BY '';
-- 创建一个叫 new_guy 的账号，只允许在 localhost 登陆，密码为 123456
明文密码会被加密为 HASH 码存储到 user 表中：
mysql> SELECT host, user, password FROM user WHERE user='new_guy'; +-----------+---------+-------------------------------------------+
| host | user | password |
+-----------+---------+-------------------------------------------+
| localhost | new_guy | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
1 row in set (0.00 sec)
可以通过下面的语句查看用户的详细信息：
SELECT * FROM user WHERE user='new_guy'\G
没有被赋予任何权限的新用户几乎是什么都做不了：
-- 授权
GRANT SELECT ON mysqlCrash.* TO 'new_guy'@'localhost';
-- 剥夺权限
REVOKE SELECT ON mysqlCrash.* FROM 'new_guy'@'localhost';
-- 查看用户权限
SHOW GRANTS FOR 'new_guy'@'localhost';
GRANT and REVOKE can be used to control access at several levels:
Entire server, using GRANT ALL and REVOKE ALL
Entire database, using ON database.*
Specific tables, using ON database.table
Specific columns
Specific stored procedures
Table 28.1 lists each of the rights and privileges that may be granted or revoked.
Table 28.1. Rights and Privileges
|
Privilege |
Description |
|---|---|
|
ALL |
All privileges except GRANT OPTION |
|
ALTER |
Use of ALTER TABLE |
|
ALTER ROUTINE |
Use of ALTER PROCEDURE and DROP PROCEDURE |
|
CREATE |
Use of CREATE TABLE |
|
CREATE ROUTINE |
Use of CREATE PROCEDURE |
|
CREATE TEMPORARY TABLES |
Use of CREATE TEMPORARY TABLE |
|
CREATE USER |
Use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES |
|
CREATE VIEW |
Use of CREATE VIEW |
|
DELETE |
Use of DELETE |
|
DROP |
Use of DROP TABLE |
|
EXECUTE |
Use of CALL and stored procedures |
|
FILE |
Use of SELECT INTO OUTFILE and LOAD DATA INFILE |
|
GRANT OPTION |
Use of GRANT and REVOKE |
|
INDEX |
Use of CREATE INDEX and DROP INDEX |
|
INSERT |
Use of INSERT |
|
LOCK TABLES |
Use of LOCK TABLES |
|
PROCESS |
Use of SHOW FULL PROCESSLIST |
|
RELOAD |
Use of FLUSH |
|
REPLICATION CLIENT |
Access to location of servers |
|
REPLICATION SLAVE |
Used by replication slaves |
|
SELECT |
Use of SELECT |
|
SHOW DATABASES |
Use of SHOW DATABASES |
|
SHOW VIEW |
Use of SHOW CREATE VIEW |
|
SHUTDOWN |
Use of mysqladmin shutdown (used to shut down MySQL) |
|
SUPER |
Use of CHANGE MASTER, KILL, LOGS, PURGE MASTER, and SET GLOBAL. Also allows mysqladmin debug login. |
|
UPDATE |
Use of UPDATE |
|
USAGE |
No access |
删除用户用 DROP USER xxx 就可以了。
更多杂七杂八的操作，请参考 MySQL 8.0 Reference Manual / Security / MySQL User Account Management / Using Roles