How to Create, Manage, and Revoke MySQL Users and Permissions

User Creation

Three common ways to create a MySQL user are provided:

Method 1 – Direct statements

create user lxm;

set password for lxm=PASSWORD('123456');

alter user lxm password expire;

select user, host, password from mysql.user;

Method 2 – Grant creates the user automatically

grant select on test.table1 to [email protected] identified by '123456';

Method 3 – Directly modify the mysql.user table

insert into mysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values ('192.168.2.154','lxm','123456','','','');

update mysql.user set password=password('lxm') where user='lxm' and host='%';

User Deletion

Delete one or multiple users with a single statement:

drop user [email protected], [email protected], [email protected];

Note that drop user does not automatically terminate existing sessions of the removed users.

Permission Granularity

MySQL permissions are hierarchical from coarse to fine: global, database, table, column, and program (procedure/function). The scope can be described as granting a user from a specific host access to particular columns of a table in a database.

Global level – stored in mysql.user.

Database level – stored in mysql.db.

Table level – stored in mysql.tables_priv.

Column level – stored in mysql.column_priv.

Program level – stored in mysql.procs_priv.

Granting and Revoking Specific Privileges

Examples of granting column‑level privileges:

grant select (col1) on test.table1 to lxm;

grant insert (col1) on test.table1 to lxm;

Revoking privileges:

revoke select on test.table1 from [email protected];

revoke all privileges, grant option from user;

When Permission Changes Take Effect

If you use MySQL commands such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, changes are applied immediately.

When you modify the privilege tables directly (INSERT/UPDATE/DELETE), you must either restart the MySQL service or execute FLUSH PRIVILEGES to reload the tables into memory.

Effect on clients:

Table or column privilege changes become effective on the next operation.

Database‑level changes take effect after the client switches databases.

Global privileges and password changes take effect on the next client connection.

Comprehensive List of MySQL Privilege Types

create user – allows CREATE USER, DROP USER, RENAME USER, REVOKE ALL PRIVILEGES.

create – create databases or tables; can view objects it creates.

create view – create or modify views.

select – read data.

insert – insert rows.

update – modify rows.

delete – delete rows.

drop – drop tables, views, or databases.

alter – alter tables.

index – create or drop indexes.

create tablespace – manage tablespaces and log file groups.

create temporary tables – create temporary tables.

lock tables – lock tables for which the user has SELECT.

trigger – create or drop triggers.

create routine – create stored procedures and functions.

alter routine – modify or drop stored procedures and functions.

execute – execute stored programs.

replication client – connect to replication master/slave.

replication slave – read data from master as a slave.

grant option – allow the user to grant its privileges to others.

all privileges – grant all privileges except GRANT OPTION.

usage – no privileges but can log in (default).

event – use EVENT objects.

file – read/write files.

proxy – use PROXY.

Special notes: ALL PRIVILEGES and GRANT OPTION cannot be combined with other privileges in a single grant or revoke statement.

Resource‑Limiting Privileges

max_queries_per_hour – limit number of queries per hour.

max_updates_per_hour – limit number of updates per hour.

max_connections_per_hour – limit number of connections per hour.

max_user_connections – limit simultaneous connections for a user.

Additional Remarks

At the table level, you can grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER. Administrative privileges such as EXECUTION, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER are global‑only and must be granted with ON *.*.