MYSQL 5.X Create user and authorize methods

To add a user account to the MySQL users table:

Instead of adding a user record directly using the INSERT statement, you can use Insert to appear:

Error 1364 (HY000): Field ' ssl_cipher ' doesn ' t have a default value. However, the earlier MySQL version of the author did not appear this error, because the sky edge has always been directly modify the user table or directly using the INSERT statement to complete, and then upgrade MySQL to 5.1 when the discovery may occur this error.

Specific methods

1. Create a new user.

The code is as follows	Copy Code
Login to MySQL @>mysql-u root-p @> Password Create a user mysql> INSERT INTO Mysql.user (Host,user,password) VALUES ("localhost", "Phplamp", Password ("1234")); Refresh System Permission Table Mysql>flush privileges; This creates a user named: phplamp Password: 1234. Then log in. mysql>exit; @>mysql-u phplamp-p @> Enter password Mysql> Login Successful

It is recommended that you use the GRANT statement to authorize the following statement:

The code is as follows	Copy Code
Grant USAGE on *.* to ' username ' @ ' localhost ' identified by ' password ' with GRANT OPTION;

Last sentence:

Replace "username" with the user name to be authorized, such as CLIENTUSR;

Replace "password" with the password set by CLIENTUSR;

4, Access to data table authorization

After the account is created, the common user is given the data table authorization, the additional connection user default permissions in step 3 are "N", the account must be authorized in the DB table to access the private database, of course, the superuser will not say.

Use the following statement:

The code is as follows	Copy Code
GRANT Select,insert,update,delete,create,drop on tablename.* to ' username ' @ ' localhost ' identified by ' password ';

The permissions in this statement are determined according to the actual needs:

Replace "tablename" with the table name of the datasheet that is authorized to access

"Username" is a step 2 authorized user name

"Password" is the set password for step 2 authorized user

This gives the user a select, INSERT, UPDATE, DELETE, careate, drop permission on a datasheet.

5, the effective authorization, the creation completes

A word can: FLUSH privileges;

3. Delete the user.

code is as follows	copy code
@>mysql -u root-p @> password mysql>delete from user WHERE user= "phplamp" and host= "localhost"; Mysql>flush Privile Ges //delete users ' databases Mysql>drop database phplampdb