MySQL user表,云主机与云存储,vps服务器租用和云服务器,云ecs和vps区别,新创云主机

🗣 Wd提问: MySQL user表

🤖 Ai回答:
The MySQL `user` table is a critical system table located in the `mysql` database、It stores user accounts, authentication details, and global privileges for MySQL server access、Below is a structured overview of its purpose, structure, and key considerations.

1、Purpose
Authentication: Verifies user credentials (username, password, host) when connecting to the MySQL server.
Global Privileges: Defines server-wide permissions (e.g., `CREATE`, `DROP`, `SHUTDOWN`) that apply to all databases unless overridden by more specific privilege tables (e.g., `db`, `tables_priv`).

2、Location and Access
Database: `mysql`
Table: `user`
Query Example:
sql
SELECT user, host, authentication_string FROM mysql.user;

3、Key Columns (MySQL 8.0+)
| Column | Description |

| `user` | Username (e.g., `'admin'`)、|
| `host` | Host from which the user can connect (e.g., `'localhost'`, `'%'` for any host)、|
| `authentication_string` | Hashed password (replaced `password` in MySQL 5.7+)、|
| `plugin` | Authentication plugin (e.g., `caching_sha2_password`)、|
| `account_locked` | Indicates if the account is locked (`Y`/`N`)、|
| `password_last_changed` | Timestamp of last password change (for expiration policies)、|
| `Select_priv`, `Insert_priv`, etc、| Global privileges (`Y` = granted, `N` = denied)、|
| `max_connections` | Max simultaneous connections allowed for the user、|
| `ssl_type` | SSL/TLS requirements (e.g., `ANY`, `X509`)、|

4、Critical Notes
Wildcards in `host`:
`%` = any host (e.g., `'user'@'%'`).
`_` = single character wildcard (e.g., `'user'@'192.168.1._'`).
Privilege Hierarchy:
Global privileges (in `user`) override database-specific privileges (in `db` table).
Column/table-level privileges (in `columns_priv`, `tables_priv`) override global privileges.
Password Management:
Use `ALTER USER` to change passwords, not direct updates to `authentication_string`.

5、Best Practices
Never edit directly:
Use SQL commands instead of modifying `mysql.user`:
sql
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
ALTER USER 'newuser'@'localhost' ACCOUNT LOCK;

Avoid wildcards:
Restrict `host` to specific IPs (e.g., `'192.168.1.10'`) instead of `%` for security.
Regular audits:
Check for unused accounts or excessive privileges:
sql
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';

6、Example Workflow
1、Create a user:
sql
CREATE USER 'developer'@'192.168.1.10' IDENTIFIED BY 'SecurePass123!';

2、Grant global privileges:
sql
GRANT SELECT, INSERT, UPDATE ON *.* TO 'developer'@'192.168.1.10';

3、Lock an account:
sql
ALTER USER 'developer'@'192.168.1.10' ACCOUNT LOCK;

7、Deprecations and Changes
MySQL 5.7+: `password` column → `authentication_string`.
MySQL 8.0+:
Data dictionary tables (not directly modifiable).
`account_locked` column added for account management.
Password expiration policies enforced via `password_last_changed`.

Why You Should Avoid Direct Edits
Risk of corruption: The `mysql` database is part of MySQL’s data dictionary、Manual changes may break replication or cause authentication failures.
Inconsistent state: Privilege changes require running `FLUSH PRIVILEGES` (if edited directly), which is error-prone.

Always use `CREATE USER`, `GRANT`, and `ALTER USER` commands for safe management、For advanced troubleshooting, consult the [MySQL Documentation](https://dev.mysql.com/doc/refman/8.0/en/user-account-management.html).

Ai作答

📣 商家广告：