MySQL User management under CentOS

1. Create a user

CREATE USER ' username '@'host'by'password'

2. User authorization

GRANT Privileges  on  to ' username '@'host';

Description: Privileges-user's operation permissions, such as SELECT, INSERT, UPDATE, etc. (see the last side of the article for a detailed list). Use all if you want to grant the permission. DatabaseName-database name, tablename-table name, if you want to grant the user the appropriate operation permissions on all databases and tables, the * representation, such as *. *.

Example:

 grant  select , insert  on  test. user  to   " pig   " @"  %   "  grant  all  on  * . *  to   " pig   " @"  %   "; 

Note: 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 Privileges  on  to ' username '@'host'withGRANTOPTION;

3. Cancellation of Authorization

REVOKE  on  from ' username '@'host'

Description: Privilege, DatabaseName, TableName-With the authorization section.

Example:

REVOKE SELECT  on *. *  from ' Pig '@'%'

Note: If you are giving the user ' pig ' @ '% ' authorization (or similar): Grant SELECT on Test.user to ' pig ' @ '% ', then use revoke select On * * from ' pig ' @ '% '; A select operation for the user table in the test database. Conversely, if the authorization is to use the grant SELECT on * * to ' pig ' @ '% ', then the revoke select on Test.user ' pig ' @ '% '; Revoke the user's SELECT permission to the Users table in the test database.

Specific information can be used with the command show GRANTSfor 'pig'@ '%';

4. Set or change the password

SET  for ' username '@'host'= PASSWORD ('newpassword');

If it is the current login user with SET PASSWORD = PASSWORD ("NewPassword");

Example:

SET  for ' Pig '@'%'= PASSWORD ("123456");

If you forget the root password, the operation is as follows:

1). Linux Switch to root user

2). Modify the MySQL login settings, edit the my.cnf file

[email protected]/]# VI /etc/my.cnf

Add a sentence under the [mysqld] field: Skip-Grant-tables

Save exit.

3). Restart Mysqld

[email protected]/]# service mysqld Restart

4). Then password-free access to MySQL and use MySQL

[[email protected]/]# Mysqlwelcome toThe MySQL Monitor. CommandsEnd  with;or\g.your MySQL Connection ID is 3Server Version:5.7. -MySQL Community Server (GPL) Copyright (c) -, ., Oracleand/orits affiliates. Allrights reserved. Oracle isA registered trademark ofOracle Corporationand/oritsaffiliates. Names may trademarks oftheir respectiveowners. Type'Help ;' or '\h'  forHelp. Type'\c'  toClear the Currentinput Statement.mysql>  UseMySQL; ReadingTableInformation forCompletion of Table  and columnnamesyou can turnifThis feature toGet a quicker startup with -ADatabaseChanged

5). Modify the root user password

Mysql> UpdateMysql.User SetAuthentication_string=Password'Root')where User='Root'  andHost='localhost'; Query OK,1Row affected,1Warning (0.00sec) Rows matched:1Changed:1Warnings:1

6). Exit MySQL, edit my.cnf, delete skip-grant-tables line

7). Log in to MySQL with the new password of the root user

5. Delete a user

DROP USER ' username '@'host'

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 User management under CentOS