Master MySQL User Permissions: Creation, Grants, and Best Practices

Creating MySQL Users

Use the CREATE USER 'username'@'host' IDENTIFIED BY 'password'; statement, where username is the new account name, host restricts login locations (e.g., localhost or % for any host), and password may be empty to allow password‑less login.

Permission Assignment Principles

Grant only the minimum privileges required (e.g., SELECT instead of UPDATE if only reading is needed).

Restrict the allowed host when creating a user, preferably to a specific IP or internal range.

Remove default users without passwords after installation.

Use strong, complex passwords for every account.

Periodically clean up unused accounts and revoke unnecessary privileges.

MySQL Privilege Overview

MySQL privileges can be set at the global, database, table, column, and routine levels. The following images illustrate the privilege matrix:

Granting Privileges

Example: create a super‑user that can log in locally and grant privileges to others.

GRANT ALL PRIVILEGES ON *.* TO fog@'localhost' IDENTIFIED BY "test123" WITH GRANT OPTION;

Key parts of the syntax: ALL PRIVILEGES – grants every privilege; you can replace with specific ones such as SELECT, UPDATE, etc. ON *.* – targets all databases and tables ( * before the dot is the database name, the second * is the table name). TO fog@'localhost' – assigns the privileges to user fog connecting from localhost. Use % as a wildcard for any host. WITH GRANT OPTION – allows the user to grant the same privileges to other accounts.

Flushing Privileges

After modifying the privilege tables directly, run: FLUSH PRIVILEGES; This forces MySQL to reload the grant tables.

Viewing and Revoking Privileges

Show all privileges for the current user: SHOW GRANTS; Show privileges for a specific user: SHOW GRANTS FOR 'fog'@'%'; Revoke a privilege, e.g., delete:

REVOKE DELETE ON *.* FROM 'fog'@'localhost';

Removing and Renaming Users

Delete a user: DROP USER 'fog'@'localhost'; Rename a user:

RENAME USER 'fog'@'%' TO 'jim'@'%';

Changing Passwords

Using SET PASSWORD:

Using mysqladmin:

Updating the user table directly:

SQL Development Recommendations

Naming Conventions

Use lowercase letters and underscores for database, table, and column names.

Limit names to 64 characters (preferably under 32).

Avoid MySQL reserved words; if unavoidable, quote with backticks.

Table Design

Prefer the InnoDB storage engine (default since MySQL 5.5) for transactions, row‑level locking, and better crash recovery.

Store non‑negative numbers with UNSIGNED types to double the range.

Store IPv4 addresses as INT UNSIGNED and convert with INET_ATON / INET_NTOA.

Replace ENUM with TINYINT to avoid costly DDL changes.

Use VARBINARY for case‑sensitive strings or binary data.

Remember that INT(4) only affects display width, not storage size.

Prefer TIMESTAMP over DATETIME for automatic update capabilities and smaller storage (4 bytes vs 8 bytes).

Separate hot and cold data into different tables to improve cache efficiency.

Never store plaintext passwords; store salted, hashed values.

Always define a primary key, preferably an UNSIGNED AUTO_INCREMENT column.

Use UTF‑8 (or UTF8MB4 for emoji) as the default character set.

Adopt appropriate sharding and database‑splitting strategies for scalability.

Index Guidelines

Avoid redundant or duplicate indexes; they increase I/O and maintenance overhead.

Do not index low‑cardinality columns (e.g., gender).

Use covering indexes to satisfy queries from the index alone, reducing table lookups.

SQL Query Optimizations

Replace OR with IN (limit list size to < 1000 items).

Prefer UNION ALL over UNION when duplicate elimination is unnecessary.

Avoid ORDER BY RAND(); instead, select random primary‑key values.

Implement efficient pagination techniques.

Select only required columns; avoid SELECT *.

Do not use nondeterministic functions ( NOW(), RAND(), etc.) in statements that need to be replicated.

Batch operations using INSERT ... ON DUPLICATE KEY UPDATE, REPLACE INTO, INSERT IGNORE, or multi‑row INSERT.

Break complex statements into smaller queries to reduce transaction size.

Combine multiple ALTER operations on the same table into a single statement.

Minimize use of stored procedures, triggers, views, and user‑defined functions to avoid performance and maintenance issues.

Additional Operational Tips

Maintain a dedicated application account with limited SUPER privileges.

When submitting schema change requests, include all related SQL statements for DBA review.

Avoid embedding business logic in the database; keep it in the application layer to preserve scalability.