MySQL permissions and security management

Mysql permission system is very important, but at the same time it is something that many developers or managers ignore. Not only the distribution of authority will lead to irreparable tragic consequences. The company I worked for before didn't pay much attention to database permissions at all. All developers had the highest permissions for the online system. Think about it, if one of them deletes the database one day, do you know who did it among so many people? Therefore, everyone must pay attention to it.

It is generally recommended that the highest authority is given to only one person, and this person serves as the manager and then allocates corresponding permissions to other developers. Local libraries are better during the development stage. For online libraries, be careful when granting permissions.

The principle of authority authentication

MySQL's authority authentication is authenticated through two aspects. First, the user's IP, user name and password will be verified. Only users who pass the verification can connect to Mysql. When connected, when the user performs any operation, Mysql will verify the permissions it has. Only with the permissions, the operation requested by the user will be performed. Otherwise, it is not executed.

Mysql permission classification

MySQL permissions are roughly divided into three categories:

• Data operations, such as adding, deleting, modifying, and checking.

• Structural operations, such as creating libraries, modifying table structures, etc.

• Management permissions, such as creating users, assigning permissions, etc.

Mysql permission allocation principle

• Give the minimum permissions, for example, the user currently only needs If you have read permissions and only need to see one table, then don't assign read permissions to all tables. Limit permissions to only one table. Don't be afraid of the trouble of giving read permissions to all tables.

• When creating a user, be sure to restrict IP addresses and set a password with sufficient strength.

• Regularly clean up unnecessary users and reclaim unnecessary permissions.

Account management

Create account

The syntax for creating a user in the mysql document is as follows:

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

There are a lot of parameters, don’t worry, take your time and look at the examples. Start by creating an account with minimal options.

# 创建一个无需密码即可本地登录的用户
mysql> CREATE USER 'u1'@'localhost';
Query OK, 0 rows affected

# 创建一个需要密码授权的用户，但不限制ip
mysql> CREATE USER 'u2'@'%' identified by '321232';
# 注意，密码必须使用引号，单引号或双引号都行，但不加就出错。

# 如果不想使用明文的密码，可以使用password
mysql> select password('111111');
+-------------------------------------------+
| password('111111')                        |
+-------------------------------------------+
| *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+-------------------------------------------+
1 row in set
mysql> CREATE USER 'u3'@'192.168.1.%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA';
Query OK, 0 rows affected

View user list

The system user list is stored in the user table in the mysql library.

mysql> SELECT user,host,account_locked FROM mysql.user;
--------------- ------------- ----------------
| user | host | account_locked |
--------------- ------ ------- -------------
| root | localhost | N |
| mysql.session | localhost | Y |
| mysql .sys | localhost | Y |
| u1 | localhost | N |
| u2 | % | N | localhost | N |
| u3 | 192.168.1.% | N |
------------------ ---------------- ----------------
7 rows in set

Delete user

DROP USER 用户名@ip;

mysql> drop user u2@'%';
Query OK, 0 rows affected

Modify user account

rename user old@'oldip' to new@'newip';

mysql> RENAME USER u1@localhost to user1@'127.0.0.1';
Query OK, 0 rows affected

Authorization

学完了如何创建账号及管理账号后，我们来看看如何给用户授权以及如何回收不需要的权限。

用户授权

给用户授权语法如下：

GRANT 权限 ON 数据库名*表名 TO 用户名@ip;

案例如下：

mysql> GRANT SELECT ON *.* TO 'u1'@'localhost' ; 
Query OK, 0 rows affected (0.00 sec) 
-- 全局级别授权   
mysql> GRANT ALL ON test.* TO 'u2'@'localhost'; 
Query OK, 0 rows affected (0.00 sec) 
-- 数据库级别授权   
mysql> GRANT ALL ON test.student TO 'u3'@'localhost' WITH GRANT OPTION; 
-- 表级别授权

查看用户的权限

给用户授权后，我们来查看用户是否已经获得到了这些权限。

回收用户权限

当发现给与的权限多了，那么就应该及时回收这些权限。回收权限的语法和授权的语法非常像。

REVOKE 权限 ON 数据库*表 FROM 用户名@ip地址

The above is the detailed content of MySQL permissions and security management. For more information, please follow other related articles on the PHP Chinese website!