Mysql creates and deletes users and authorizations (for linux testing), and mysqllinux

Mysql creates and deletes users and authorizations (for linux testing), and mysqllinux

Note: My runtime environment is SUSE Linux + mysql5.6

1. Create a user:
Command: create user 'username' @ 'host' identified by 'Password ';
Description: username-the username you will create, host-specifies the host on which the user can log on. If a local user can use localhost, if you want to allow the user to log on from any remote host, you can use the wildcard %. password-the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password.
Example: create user 'zpc' @ 'localhost' identified by '123 ';
Create user 'zpc' @ '192. 168.1.101 _ 'idendified BY '123 ';
Create user 'zpc' @ '%' identified by '20140901 ';
Create user 'zpc' @ '%' identified '';
Create user 'zpc' @ '% ';
Ii. Authorization:
Command: GRANT privileges ON databasename. tablename TO 'username' @ 'host'
Pri: privileges-operation permissions of users, such as SELECT, INSERT, and UPDATE (for detailed list, see the end of this Article ). use ALL .; databasename-Database Name, tablename-table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, as shown in *. *.
Example: grant select, insert on test. user TO 'pig' @ '% ';
Grant all on *. * TO 'zpc' @ '% ';
Note: The user authorized with the preceding command cannot authorize other users. to authorize the user, run the following command:
GRANT privileges ON databasename. tablename TO 'username' @ 'host' with grant option;

View a user's permissions in a database

Show grants for 'zpc' @ '% ';

View All database users

Select distinct concat ('user: ''', User, ''' @ ''', host, '''; ') AS query FROM mysql. user;

3. Set and change user passwords
Command: set password for 'username' @ 'host' = PASSWORD ('newpassword'); if the current login user uses set password = PASSWORD ("newpassword ");
Example: set password for 'pig' @ '%' = PASSWORD ("123456 ");

If the preceding command is invalid, try the following method:

# Enter
Bash $ mysql-u root mysql
# Using a mysql client program
Mysql> UPDATE mysql. user SET password = PASSWORD ("123456") WHERE user = 'zpc ';

Mysql> flush privileges;
Mysql> QUIT

Iv. Revoke User Permissions
Command: REVOKE privilege ON databasename. tablename FROM 'username' @ 'host ';
Note: privilege, databasename, tablename-same as the authorization section.
Example: revoke select on *. * FROM 'pig' @ '% ';
Note: If you authorize the user 'pig' @ '%' like this (or similar): grant select on test. user TO 'pig' @ '%', use revoke select on *. * FROM 'pig' @ '%'; the command does not cancel the SELECT Operation on the user table in the test database. conversely, grant select on * is used for authorization *. * TO 'pig' @ '%'; then revoke select on test. user FROM 'pig' @ '%'; the command cannot revoke this user's Select permission on the user table in the test database.
FOR more information, run the show grants for 'zpc' @ '%' command.
5. delete a user
Command: drop user 'username' @ 'host ';

Appendix: Operation permissions in MySQL

ALTER	Allows useALTER TABLE.
ALTER ROUTINE	Alters or drops stored routines.
CREATE	Allows useCREATE TABLE.
CREATE ROUTINE	Creates stored routines.
CREATE TEMPORARY TABLE	Allows useCREATE TEMPORARY TABLE.
CREATE USER	Allows useCREATE USER,DROP USER,RENAME USER, AndREVOKE ALL PRIVILEGES.
CREATE VIEW	Allows useCREATE VIEW.
DELETE	Allows useDELETE.
DROP	Allows useDROP TABLE.
EXECUTE	Allows the user to run stored routines.
FILE	Allows useSELECT...INTO OUTFILEAndLOAD DATA INFILE.
INDEX	Allows useCREATE INDEXAndDROP INDEX.
INSERT	Allows useINSERT.
LOCK TABLES	Allows useLOCK TABLESOn tables for which the user also hasSELECTPrivileges.
PROCESS	Allows useSHOW FULL PROCESSLIST.
RELOAD	Allows useFLUSH.
REPLICATION	Allows the user to ask where slave or master
CLIENT	Servers are.
REPLICATION SLAVE	Needed for replication slaves.
SELECT	Allows useSELECT.
SHOW DATABASES	Allows useSHOW DATABASES.
SHOW VIEW	Allows useSHOW CREATE VIEW.
SHUTDOWN	Allows useMysqladmin shutdown.
SUPER	Allows useCHANGE MASTER,KILL,PURGE MASTER LOGS, AndSET GLOBALSQL statements. AllowsMysqladmin debugCommand. Allows one extra connection to be made if maximum connections are reached.
UPDATE	Allows useUPDATE.
USAGE	Allows connection without any specific privileges.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.