Create a new mysql user and give permission management

Due to business needs, users need to be created and authorized for different databases

In slightly larger companies, there is obvious authority management. Especially for important things like operating a database, if you accidentally delete the database, it will be troublesome. Therefore, in a company, a project will generally create a special user to specify its authority to only control a certain database for operation.

In this way, the project can be carried out reasonably.

To create a new mysql user, you need to operate inside the root user.

Windows creates input commands through cmd. If you are prompted that mysql cannot be recognized, you need to configure the path. How to configure Baidu by yourself

First log in to mysql:

mysql -u account name -p password

After successful login

View all databases after logging in

show databases;

Create a new test database first

create database record character set utf8;

1. View users

There is no direct SQL statement to view the user, but into the user table of the mysql database (this mysql library and user table are both available at the beginning), and directly use select * from user; to see what users are there

use database name; switch database

select database(); View the current database

use mysql;
select * from user;

Two, create a user

Use the command:

create user'user_name'@'host' identified by'password';
  • user_name : The name of the user to be created.
  • host : Indicates which computer the newly created user is allowed to log in from. If only allowed to log in from the local computer, fill in'localhost', if remote login is allowed, fill in'%'
  • password : The login database password of the newly created user. If there is no password, leave it alone.

Create accounts with different restrictions

-- 创建账户,"%"是无登录限制的,“123”是密码create user 'liwker'@'%' identified by '123'; -- "localhost"是限制为本地登录create user 'liwker'@'localhost' identified by '123'; -- 这个是限制 ip 为 的主机访问create user 'liwker'@'' identified by '123';

Second, assign permissions

grant privileges on databasename.tablename to'username'@'host';

privileges: Indicates what rights are to be granted, such as select, insert, delete, update, etc. If you want to grant all rights, fill in ALL
    databasename.tablename: indicate that the user's privileges can be used in which table in which library, if you want The user's authority affects all tables in all databases, so fill in "*.*", * is a wildcard, which means all.
    'username'@'host': Indicates which user is authorized. The quotation marks of username can be omitted, but host must be added.

-- Liwker库的student表的 只读权限 分配给 liwker 账户grant select on Liwker.student to [email protected]'%'; -- Liwker库(所有表)的 多个权限 分配给 liwker 账户grant select,insert,delete,update on Liwker.* to [email protected]'%'; -- Liwker库的 所有权限 分配给 liwker 账户grant all on Liwker.* to [email protected]'%'; -- 所有库的 所有权限 分配给 liwker 账户grant all on *.* to [email protected]'%'; -- 刷新权限flush privileges;

Permission list:

ALTER: Modify tables and indexes.
    CREATE: Create databases and tables.
    DELETE: Delete existing records in the table.
    DROP: Abandon (delete) databases and tables.
    INDEX: Create or discard an index.
    INSERT: Insert a new row into the table.
    REFERENCE: Not used.
    SELECT: Retrieve records in the table.
    UPDATE: Modify existing table records.
    FILE: Read or write files on the server.
    PROCESS: View the thread information executed in the server or kill the thread.
    RELOAD: Reload the authorization table or clear the log, host cache or table cache.
    SHUTDOWN: Shut down the server.
    ALL: All permissions, a synonym for ALL PRIVILEGES.
    USAGE: Special "no permission" permission.

Users can be assigned corresponding permissions based on the permission fields above


The user authorized by the above command cannot authorize other users. If you want this user to be able to authorize other users, you must add WITH GRANT OPTION at the end, such as:

grant all on *.* to'liwker'@'%' with grant option;

Supplement to create a database for users

create database basename DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Three, delete users

drop user'username'@'host';

4. Set and change user password

set password for'username'@'host' = password('newpassword');
- If you are setting the current user's password
set password = password('newpassword');

Five, cancel account permissions

- Reclaim permissions, the format is similar to the distribution, grant -> revoke, to -> from
revoke select on Liwker.student from [email protected]'%';

But note:

If the right is granted as follows: grant select on *.* to [email protected]'%';
then use revoke select on Liwker.student to [email protected]'%'; It is not possible to revoke the SELECT right of the user liwker to Liwker.student.

Conversely, grant select on Liwker.student to [email protected]'%'; to grant the power
revoke select on *.* to [email protected]'%'; also cannot be used to revoke the SELECT right of the user liwker to the student table of the Liwker library

Refer to