Common operations for mysql users

1. Create a user:
Command: CREATE USER 'username'@'host' IDENTIFIED BY 'password';
Instructions: username - the username you will create, host - Specify which host the user can log in to. If it is a local user, localhost can be used. If you want the user to log in from any remote host, you can use the wildcard character %. password - the user's login password. The password can be empty. If it is empty, Then the user can log in to the server without a password.
Example: CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'testuser'@'192.168.1.101_' IDENDIFIED BY '123456' ;
CREATE USER 'testuser'@'%' IDENTIFIED BY '123456';
CREATE USER 'testuser'@'%' IDENTIFIED BY '';
CREATE USER 'testuser'@'%';
2. Authorization:

Command: GRANT privileges ON databasename.tablename TO 'username'@'host'

Description: privileges - user's operation permissions, such as SELECT, INSERT, UPDATE, etc. (see the end of this article for a detailed list). If you want to grant all permissions, use ALL.; databasename - database name, tablename - table name, if you want to grant the user the corresponding operations on all databases and tables Permissions can be represented by *, such as *.*.

Example: GRANT SELECT, INSERT ON test.user TO 'testuser'@'%';
GRANT ALL ON *.* TO 'testuser'@ '%';

Note: Users authorized with the above command cannot authorize other users. If you want the user to be able to authorize, use the following command:
GRANT privileges ON databasename.tablename TO 'username'@ 'host' WITH GRANT OPTION;

3. Set and change user password

Command: SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword' ); If it is the currently logged in user, use SET PASSWORD = PASSWORD("newpassword");

Example: SET PASSWORD FOR 'testuser'@'%' = PASSWORD("123456");

4. Revoke user privileges

Command: REVOKE privilege ON databasename.tablename FROM 'username'@'host';

Description: privilege, databasename, tablename - same authorization Part.

Example: REVOKE SELECT ON *.* FROM 'testuser'@'%';

Note: This is what happens if you authorize user 'pig'@'%' (or similar): GRANT SELECT ON test.user TO 'testuser'@'%', then using the REVOKE SELECT ON *.* FROM 'testuser'@'%'; command cannot revoke the user's access to the test database. SELECT operation of the user table. On the contrary, if the authorization is using GRANT SELECT ON *.* TO 'testuser'@'%';, then the REVOKE SELECT ON test.user FROM 'testuser'@'%'; command cannot revoke the user. Select permission on the user table in the test database.

Detailed information can be viewed with the command SHOW GRANTS FOR 'testuser'@'%';.

5. Delete users

Command: DROP USER 'username'@'host';

The above is the detailed content of Common operations for mysql users. For more information, please follow other related articles on the PHP Chinese website!