MySQL/MariaDB user permissions and permission management
1. Introduction to MySQL permission levels
| Managed objects | Permission description |
|---|---|
| Global | Can manage the entire MySQL |
| Library | Can manage the specified database |
| surface | Can manage the specified table of the specified database |
| Field | Can manage the specified fields of the specified table of the specified database |
The permissions are stored in the user, db, tables_priv, columns_priv, and procs_priv system tables of the MySQL library, and are loaded into the memory after the MySQL instance is started.
2. View user permissions
2.1. View all users (user name, who is authorized)
MariaDB [(none)]> SELECT user, host, authentication_string FROM mysql.user;
+----------+--------------+-------------------------------------------+
| user | host | authentication_string |
+----------+--------------+-------------------------------------------+
| root | localhost | |
| admin | localhost | |
| admin | % | *9D59E8AF06195817B4585B0045A6601BBE64259F |
| jeffrey | % | |
| jeffreys | localhost | |
| wang | % | |
| wang1 | localhost | |
| wang3 | 192.168.1.59 | |
| wang4 | localhost | |
| lyshark | localhost | |
| lyshark | % | |
+----------+--------------+-------------------------------------------+
11 rows in set (0.000 sec)2.2. View all situations of a single user
MariaDB [(none)]> SELECT * FROM mysql.user WHERE user='wang'\G
*************************** 1. row ***************************
Host: %
User: wang
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
Delete_history_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
is_role: N
default_role:
max_statement_time: 0.000000
1 row in set (0.000 sec)\G causes each field to be printed on a separate line, and also has the effect of';'.
Host: % # 授权用户，% 代表所有
User: wang # 用户名
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 # 密码，MD5加密
Select_priv：确定用户是否可以通过SELECT命令选择数据
Insert_priv：确定用户是否可以通过INSERT命令插入数据
Update_priv：确定用户是否可以通过UPDATE命令修改现有数据
Delete_priv：确定用户是否可以通过DELETE命令删除现有数据
Create_priv：确定用户是否可以创建新的数据库和表
Drop_priv：确定用户是否可以删除现有数据库和表
Reload_priv：确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表
Shutdown_priv：确定用户是否可以关闭MySQL服务器，将此权限提供给root账户之外的任何用户时，都应当非常谨慎
Process_priv：确定用户是否可以通过SHOW
File_priv：确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv：确定用户是否可以将已经授予给该用户自己的权限再授予其他用户，例如，如果用户可以插入、选择和删除foo数据库中的信息，并且授予了GRANT权限，则该用户就可以将其任何或全部权限授予系统中的任何其他用户
References_priv：目前只是某些未来功能的占位符，现在没有作用
Index_priv：确定用户是否可以创建和删除表索引
Alter_priv：确定用户是否可以重命名和修改表结构
Show_db_priv：确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库，可以考虑对所有用户禁用这个权限，除非有特别不可抗拒的原因
Super_priv：确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令
Create_tmp_table_priv：确定用户是否可以创建临时表
Lock_tables_priv：确定用户是否可以使用LOCK
Execute_priv：确定用户是否可以执行存储过程，此权限只在MySQL 5.0及更高版本中有意义
Repl_slave_priv：确定用户是否可以读取用于维护复制数据库环境的二进制日志文件，此用户位于主系统中，有利于主机和客户机之间的通信
Repl_client_priv：确定用户是否可以确定复制从服务器和主服务器的位置
Create_view_priv：确定用户是否可以创建视图，此权限只在MySQL 5.0及更高版本中有意义
Show_view_priv：确定用户是否可以查看视图或了解视图如何执行，此权限只在MySQL 5.0及更高版本中有意义 Create_routine_priv：确定用户是否可以更改或放弃存储过程和函数，此权限是在MySQL 5.0中引入的 Alter_routine_priv：确定用户是否可以修改或删除存储函数及函数，此权限是在MySQL 5.0中引入的 Create_user_priv：确定用户是否可以执行CREATE
Event_priv：确定用户能否创建、修改和删除事件，这个权限是MySQL 5.1.6新增的
Trigger_priv：确定用户能否创建和删除触发器，这个权限是MySQL 5.1.6新增的
Create_tablespace_priv: 创建表的空间3. Permission table
4. Authorization
4.1. Format:
GRANT [权限] ON [库.表] TO [用户名]@[IP] IDENTIFIED BY [密码]
# WITH GRANT OPTION; GRANT command description:
(1) ALL PRIVILEGES means all permissions, and permissions such as SELECT and UPDATE can also be used.
(2) ON used to specify which libraries and tables the authority is for.
(3) The *.* is used to specify the database name, and the back number is used to specify the table name.
(4) TO means granting authority to a user.
(5) The @ indicates the user, and the @ followed by the restricted host, which can be IP, IP segment, domain name, and %, and% indicates anywhere.
(6) IDENTIFIED BY specifies the user's login password.
(7) WITH GRANT OPTION This option means that the user can authorize the permissions he owns to others.
Note: WITH GRANT OPTION option when creating an operating user will cause the user to be unable to use the GRANT command to create a user or authorize other users.FLUSH PRIVILEGES every time you update the permissions;
Remarks:
Use GRANT to repeatedly add permissions to the user, and the permissions overlap.
If you first add a SELECT permission to the user, and then add an UPDATE permission to the user, then the user has both SELECT and UPDATE permissions.
4.2. Authorization example
1. Global authorization
Create a new user, give all permissions
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'wang5'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SELECT user, host, authentication_string FROM mysql.user;
+----------+--------------+-------------------------------------------+
| user | host | authentication_string |
+----------+--------------+-------------------------------------------+
| root | localhost | |
| admin | localhost | |
| admin | % | *9D59E8AF06195817B4585B0045A6601BBE64259F |
| jeffrey | % | |
| jeffreys | localhost | |
| wang | % | |
| wang1 | localhost | |
| wang3 | 192.168.1.59 | |
| wang4 | localhost | |
| lyshark | localhost | |
| lyshark | % | |
| wang5 | % | |
+----------+--------------+-------------------------------------------+
12 rows in set (0.000 sec)2, single database authorization
MariaDB [(none)]> GRANT ALL PRIVILEGES ON mysql.* TO 'wang6'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SELECT user, host, authentication_string FROM mysql.user;
+----------+--------------+-------------------------------------------+
| user | host | authentication_string |
+----------+--------------+-------------------------------------------+
| root | localhost | |
| admin | localhost | |
| admin | % | *9D59E8AF06195817B4585B0045A6601BBE64259F |
| jeffrey | % | |
| jeffreys | localhost | |
| wang | % | |
| wang1 | localhost | |
| wang3 | 192.168.1.59 | |
| wang4 | localhost | |
| lyshark | localhost | |
| lyshark | % | |
| wang5 | % | |
| wang6 | % | |
+----------+--------------+-------------------------------------------+
13 rows in set (0.000 sec)3. Single database and single table authorization
MariaDB [(none)]> GRANT ALL PRIVILEGES ON mysql.user TO 'wang7'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.000 sec)4. A single database, a single table authorizes certain fields to authorize
MariaDB [(none)]> GRANT SELECT(host, user) ON mysql.user TO 'wang8'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.000 sec)If you do this, the table cannot be opened, and you can only find out the fields that are open to us through query statements.
4. Withdraw permissions and delete users
4.1, withdraw permissions
REVOKE [Permission] ON [Library. Table] FROM [User Name]@[IP];
MariaDB [(none)]> REVOKE SELECT(host, user) ON mysql.user FROM 'wang8'@'%';
Query OK, 0 rows affected (0.000 sec)4.2, delete user
DROP USER [User Name]@[IP];
MariaDB [(none)]> DROP USER IF EXISTS 'wang8'@'%';
Query OK, 0 rows affected (0.000 sec)
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。