MySQL create user and authorization method instance explaining

create a user and authorization implementation method in MySQL.

Operating Environment: Widnows XP Professional + MySQL5.0

One, create the user:

Command: CREATE USER ' username ' @ ' host ' identified by ' password ';

Description: Username-the user name that you will create, host-Specifies which host the user can log on to, if localhost is available to local users, you can use the wildcard% if you want the user to be able to log on from any remote host. Password-The user's login password, password can be empty, if it is empty, the user can not need password to log on to the MySQL server.

Example:

CREATE USER 'Dog'@'localhost'Identified by '123456'; CREATE USER 'Pig'@'192.168.1.101_'Idendified by '123456'; CREATE USER 'Pig'@'%'Identified by '123456'; CREATE USER 'Pig'@'%'Identified by "'; CREATE USER 'Pig'@'%';

Second, Authorization:

Command: GRANT privileges on databasename.tablename 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  onTest.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 Databasename.tablename to ' username ' @ ' host ' with GRANT OPTION;

Three. setting and changing user passwords

Command: Set PASSWORD for ' username ' @ ' host ' = PASSWORD (' NewPassword '), if current user is logged in with set PASSWORD = PASSWORD ("NewPassword");

Example: SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

Four. Revoke user rights

Command: REVOKE privilege on databasename.tablename 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. (www.jbxue.com Script Academy)

Specific information can be used with the command show GRANTS for ' pig ' @ '% '; View.

Five. Delete a user

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

Schedule: Permissions for operations in MySQL

ALTERAllows Use  of ALTER TABLE.ALTERROUTINE altersordrops stored routines.CREATEAllows Use  of CREATE TABLE.CREATEROUTINE creates stored routines.CREATE Temporary TABLEAllows Use  of CREATE Temporary TABLE.CREATE USERAllows Use  of CREATE USER,DROP USER, RENAMEUSER, and REVOKE  All Privileges.CREATE VIEWAllows Use  of CREATE VIEW.DELETEAllows Use  of DELETE.DROPAllows Use  of DROP TABLE.EXECUTEAllows theUser  torun stored routines.FILEAllows Use  of SELECT... intoOUTFILE and LOADDATA INFILE.INDEXAllows Use  of CREATE INDEX  and DROP INDEX.INSERTAllows Use  of INSERT. LOCK TABLES allows Use  ofLOCK TABLES onTables forwhich theUseralso hasSELECT Privileges. PROCESS allows Use  ofSHOW Fullprocesslist. RELOAD allows Use  ofFLUSH.REPLICATIONAllows theUser  toAskwhereSlaveormasterclient servers is.REPLICATIONSLAVE Needed for Replicationslaves.SELECTAllows Use  of SELECT. SHOW DATABASES allows Use  ofSHOW DATABASES. SHOWVIEWAllows Use  ofSHOWCREATE VIEW.SHUTDOWNAllows Use  ofMysqladminshutdown. SUPER allows Use  ofChange MASTER,KILL, PURGE MASTER LOGS, and SETGLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection tobe madeifMaximum Connections is reached.UPDATEAllows Use  of UPDATE. USAGE allows connection without anySpecificPrivileges.