MySQL Create user and authorization

MySQL database user creation, authorization management

Create a User:

Command:

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

Description

Username-the user name you will create

Host-Specifies the host on which the user can log on, if localhost is available to the local user, if you want the user to be able to log on from any remote host, you can use the wildcard%

Password-The user's login password, the password can be empty, if it is empty, the user can not require a password to log on to the 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-the 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-' 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;flush privileges;//Refresh System Permissions Table

Three. setting and changing user passwords

Command:

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

If the user is currently logged in

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.

Specific information can be used in order

SHOW GRANTS for ' pig ' @ '% '

; View.

Five. Delete a user

Command:

Drop USER ' username ' @ ' host '; instance drop ' pig ' @ '% ';d rop ' pig ' @ ' localhost ';

This article is from the "happy Programming _ Endless" blog, please be sure to keep this source http://happyliu.blog.51cto.com/501986/1744884

MySQL Create user and authorization