MySQL database operations (1) Users and permissions

Enter MySQL locally via the command line:

Mysql-u User name-p password
Mysql-u User name-p Enter password

Exit MySQL:
\q
Exit
Forced exit, CTRL + Z

To create a user:
CREATE user ' username ' @ ' domain name ' identified by ' password ';
Domain name: Specify on which host the user can log on, if localhost is available to local users, if you want the user to be able to log in from any remote host, you can use a wildcard%
Password: The password can be empty, and if it is empty, the user can log on to the server without a password.
Example:
CREATE USER ' dog ' @ ' localhost ' identified by ' 123456 ';
CREATE USER ' pig ' @ ' 192.168.1.101 ' identified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ';
CREATE USER ' pig ' @ '% ';

To delete a user:
DROP user ' username ' @ ' domain name ';
Example:
DROP USER ' pig ' @ ' 192.168.1.101 ';
DROP USER ' pig ' @ '% ';

To authorize the user:
GRANT permissions on the database name. Table name to ' username ' @ ' domain name ';
Allows the user to specify the operation for the specified table under the specified database, separating each permission with a comma, and the permission table at the end
Example:
GRANT SELECT, INSERT on Test.user-' pig ' @ '% ';
GRANT all on * * to ' pig ' at '% ';
Attention:
A user authorized with the above command cannot authorize another user, and if you want the user to be authorized to do so, use the following command:
GRANT permissions on the database name. Table name to ' user name ' @ ' domain name ' with GRANT OPTION;

Revoke user rights:
REVOKE permissions on the database name. Table name from ' username ' @ ' domain name ';
Example:
REVOKE SELECT on * * from ' pig ' @ '% ';
Attention:
Revoked permissions and library tables must be consistent with authorization, and the permission will not be revoked if the revoked permissions and authorizations are inconsistent.

Show user rights:
SHOW GRANTS for ' username ' @ ' domain name ';
Example:
SHOW GRANTS for ' pig ' @ '% ';

To set and change a user password:
For the specified user SET PASSWORD for ' username ' @ ' domain name ' = PASSWORD (' new password ');
SET PASSWORD = PASSWORD ("New password") for the current user;
Example:
SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

See which databases are available:
SHOW DATABASES;

See which database is currently in:
SELECT DATABASE ();

View Current User:
SELECT USER ();

To view a list of users:
Select Host,user from Mysql.user;

Schedule: Permissions for operations in MySQL

Alter	Allows use of ALTER TABLE.
ALTER ROUTINE	Alters or drops stored routines.
CREATE	Allows use of CREATE TABLE.
CREATE ROUTINE	Creates stored routines.
CREATE Temporary TABLE	Allows use of CREATE temporary TABLE.
CREATE USER	Allows use of the CREATE user, DROP user, RENAME user, and REVOKE all privileges.
CREATE VIEW	Allows use of CREATE VIEW.
DELETE	Allows use of DELETE.
DROP	Allows use of DROP TABLE.
EXECUTE	Allows the user to run stored routines.
FILE	Allows use of SELECT... Into OUTFILE and LOAD DATA INFILE.
INDEX	Allows use of CREATE index and DROP index.
INSERT	Allows use of INSERT.
LOCK TABLES	Allows use of LOCK TABLES on TABLES for which the user also have SELECT privileges.
PROCESS	Allows use of the SHOW full processlist.
RELOAD	Allows use of FLUSH.
REPLICATION	Allows the user to ask where slave or master
CLIENT	Servers is.
REPLICATION SLAVE	Needed for replication slaves.
SELECT	Allows use of SELECT.
SHOW DATABASES	Allows use of SHOW DATABASES.
SHOW VIEW	Allows use of SHOW CREATE VIEW.
SHUTDOWN	Allows use of mysqladmin shutdown.
SUPER	Allows use of change master, KILL, PURGE master LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections is reached.
UPDATE	Allows use of UPDATE.
USAGE	Allows connection without any specific privileges.

MySQL database operations (1) Users and permissions