MySQL in the creation of user and authorization implementation method, for the first contact with MySQL friends can refer to the next
Note: My operating environment is Widnows XP Professional + MySQL5.0
One, create the user:
command: CREATE USER ' username ' @ ' host ' identified by ' password ';
Description: Username-the user name you will create, host-Specifies which host the user can log on to, if localhost is available to the local user, 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 , 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_ ' idendified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ';
CREATE USER ' pig ' @ '% ';
Two, 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 on Test.user to ' pig ' @ '% ';
GRANT All on * * to ' pig ' at '% ';
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"); /c0>
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 authorizing the user ' pig ' @ '% ' (or similar): Grant SELECT on Test.user to ' pig ' @ '% ', then use revoke select On *. * from ' pig ' @ '% '; command It is not possible to revoke the user's SELECT operation on the Users table in the test database. Conversely, if the authorization is using Grant SELECT on * * to ' pig ' @ '% '; then revoke SELECT on test.user from ' pig '; It is also not possible to revoke the user's SELECT permission to the Users table in the test database.
specific information can be used with the command show GRANTS for ' pig ' @ '% ';
Five. Delete a user
command: 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 create user , drop user Span style= "Line-height:25.2000007629395px;font-family:verdana;" >, , and |
| 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 create user and authorization method
