Mysql add user and database authorization

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 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, 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-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");

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 revoke SELECT on test.user from ' pig ' @ '% '; The command also cannot 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 ' @ '% '; View.

Five. Delete a user

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

Authorization law. For example, if you want to myuser use MyPassword to connect to a MySQL server from any host.  SQL code   1. Grant all privileges on * * to ' myuser ' @ ' percent ' identified by ' MyPassword ' with      GRANT OPTION;    2.FLUSH   privileges;  If you want to allow the user to connect to the MySQL server myuser from the IP 192.168.1.6 host, and use MyPassword as the password for the  SQL code     1. GRANT all privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by      2. ' MyPassword ' with GRANT OPTION;      3. FLUSH   privileges;   GRANT all privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION; FLUSH   privileges;  If you want to allow the user to myuser from the IP-192.168.1.6 host to the MySQL server's DK database, and use MyPassword as the password for the SQL code     1. GRANT all privileges the dk.* to ' myuser ' @ ' 192.168.1.3 ' identified by      2. ' MyPassword ' with GRANT OPTION;      3. FLUSH   privileges;   GRANT all privileges the dk.* to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION; FLUSH   privileges;  

　　

Mysql add user and database authorization