How to add permissions to users in mysql

Mysql method to add permissions to users: First create a user, the code is [create user username identified by 'password']; then assign permissions to the user, the code is [grant permissions on database.data table to ' user'@'hostname'].

Mysql method to add permissions to users:

1. Create a new user under Mysql

Syntax:

create user 用户名 identified by '密码';

Example

create user xiaogang identified by '123456';

Newly created users do not have any permissions by default.

2. How to assign permissions to users

Syntax:

grant 权限 on 数据库.数据表 to '用户' @ '主机名';

Example: Assign all permissions to xiaogang

grant all on *.* to 'xiaogang'@'%';

This At that time, xiaogang will have all the permissions

3. How to control user permissions more accurately?

1. Grant permission on database.data table to 'user' @ 'host name';

Example: Let xiaogang have the permission to query the tmp database tmp1 table;

grant select on temp.temp1 to 'xiaogang'@'%'; //这个时候 xiaogang 就具有查询temp小的temp1的权限了。

For example:

mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;

Assign user joe from 10.163.225.87 the permission to perform select, insert, update, delete, create, drop and other operations on the employee table of database vtdc, and set a password to 123.

mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;

Assign user joe from 10.163.225.87 the permissions to perform all operations on all tables in the database vtdc, and set the password to 123.

mysql>grant all privileges on *.* to joe@10.163.225.87 identified by ‘123′;

Assign user joe from 10.163.225.87 the permission to perform all operations on all tables in all databases, and set the password (www.111cn.net) to 123.

mysql>grant all privileges on *.* to joe@localhost identified by ‘123′;

Assign the local user joe the authority to perform all operations on all tables in all databases, and set the password to 123.

4. How to revoke permissions, generally only root users have this permission

Syntax:

1.revoke permissions on database.data table from 'user'@'hostname';

Example: Take back all permissions of xiaogang

revoke all on *.* from 'xiaogang' @'%';

Okay, let me summarize the steps and a very specific process

Step 1: Start and stop the mysql service

net stop mysql
net start mysql

Step 2: Log in to mysql directly

The syntax is as follows: mysql -u username -p user password

Type the command mysql -uroot -p, press Enter to prompt you to enter your password, enter 123456, and then press Enter to enter mysql. The prompt of mysql is:

mysql>

Note that if you are connecting to another machine, you need to add a parameter -h machine IP

Step 3: Add a new user

Format: grant permissions on the database. * to username@login host identified by "password"

For example, add a user user1 with the password password1, so that he can log in on this machine and query, insert, modify, and delete all databases. permissions. First connect to mysql as the root user, and then type the following command:

grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";

If you want the user to be able to log in to mysql on any machine, change localhost to "%".

If you don’t want user1 to have a password, you can type another command to remove the password.

grant select,insert,update,delete on mydb.* to user1@localhost identified by "";

Step 4: Operate the database

Log in to mysql, and then run the following commands at the mysql prompt. Each command ends with a semicolon

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of How to add permissions to users in mysql. For more information, please follow other related articles on the PHP Chinese website!