How to add users and set permissions in Mysql

The full English name of DCL is Data Control Language (Data Control Language), which is used to manage database users and control database access permissions.

1. Manage users

User related commands:

Query users:select * from mysql.user;

The table contains encrypted passwords. If you forget your password, you can configure password-free login and change the password.

Host represents the host that the current user accesses. If it is localhost, it only means that it can only be accessed on the current local machine and cannot be accessed remotely. User represents the username to access the database. In MySQL, a user needs to be uniquely identified through Host and User. You can use % in the host name, which means that any host can access it.

Often mysql is installed in Linux, and then the client of other hosts cannot be connected to mysql. This is because the Host is configured with localhost!

• Create user: CREATE USER 'Username'@'Hostname' IDENTIFIED BY 'Password';

• Modify user password: ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'new password' ;

• Delete user: DROP USER 'Username'@'Hostname' ;

Note:

• In In MySQL, a user needs to be uniquely identified by username@hostname.

• The host name can be wildcarded with %.

Command usage example:

#创建用户aaa, 只能够在当前主机localhost访问, 密码123456;
create user 'aaa'@'localhost' identified by '123456';

#创建用户bbb, 可以在任意主机访问该数据库, 密码123456;
create user 'bbb'@'%' identified by '123456';

#修改用户bbb的访问密码为1234;
alter user 'bbb'@'%' identified with mysql_native_password by '1234';

#删除 aaa@localhost 用户
drop user 'aaa'@'localhost';

2. Permission control

There are many kinds of permissions defined in MySQL, but the commonly used Just the following:

Command:

• ##Query permissions:

  SHOW GRANTS FOR ' Username'@'hostname';

• Grant permissions:

  GRANT permission list ON database name.Table nameTO 'username'@'hostname';

• Revoke permissions:

  REVOKE permission list ON database name.table name FROM 'username'@'hostname';

Note:

• Use commas to separate multiple permissions

• When authorizing ,

  Database names and table names can be wildcarded with *, which represents all .

Command usage example:

#查询 'bbb'@'%' 用户的权限
show grants for 'bbb'@'%';

#授予 'bbb'@'%' 用户itcast数据库所有表的所有操作权限
grant all on itcast.* to 'bbb'@'%';

#撤销 'bbb'@'%' 用户的itcast数据库的所有权限
revoke all on itcast.* from 'bbb'@'%';

After MySQL user data and permissions are modified, I hope to directly modify the MySQL service without restarting it. To take effect, you need to execute this command. Usually after modifying the settings of the ROOT account, you are afraid that you will not be able to log in again after restarting, so you can directly flush to see whether the permission settings take effect. Without taking too much risk.

flush privileges;

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