MySQL Add user, delete user and authorization

Transferred from: http://www.cnblogs.com/fly1988happy/archive/2011/12/15/2288554.html

MySQL add user, new database, user authorization, delete user, change password (note that each line followed by A; indicates that a command statement ends):

1. Create a new user

1.1 Log in to MySQL:

@>mysql-u root-p

@> Password

1.2 Create User:

mysql> INSERT INTO Mysql.user (Host,user,password) VALUES ("localhost", "Test", Password ("1234"));

This creates a user named: Test with a password of: 1234.

Note: "localhost" here means that the user can only log on locally and cannot telnet to another machine. If you want to telnet, change "localhost" to "%", which means you can log on on any computer. You can also specify that a machine can log on remotely.

1.3 Then log in:

mysql>exit;

@>mysql-u test-p

@> Enter password

Mysql> Login Successful

2. Authorizing the user

Authorization format: Grant permissions on database. * To User name @ login host identified by "password";

2.1 Log in to MySQL (rooted), log in as root here:

@>mysql-u root-p

@> Password

2.2 First create a database for the user (TestDB):

Mysql>create database TestDB;

2.3 Authorization test The user has all permissions for the TestDB database (all permissions for a database):

Mysql>grant all privileges in testdb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges;//Refresh System Permissions Table

Format: Grant permissions on database. * To User name @ login host identified by "password";

2.4 If you want to specify a partial permission to a user, you can write:

Mysql>grant select,update on testdb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges; Refresh System Permissions Table

2.5 Authorization test The user has some permissions for all databases:

Mysql>grant Select,delete,update,create,drop On * * to [e-mail protected] "%" identified by "1234";

The test user has Select,delete,update,create,drop permissions on all databases.

@ "%" indicates authorization for all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1, if set to the real local address, do not know whether it can, no authentication.) ）

Authorization to localhost: plus a grant all privileges on testdb.* to [email protected] identified by ' 1234 ';

3. Delete a user

@>mysql-u root-p

@> Password

Mysql>delete from Mysql.user Where user= ' test ' and host= ' localhost ';

Mysql>flush privileges;

Mysql>drop database TestDB; Delete a user's database

Delete account and permissions: >drop user username @ '% ';

>drop user username @ localhost;

4. Modify the specified user password

@>mysql-u root-p

@> Password

Mysql>update Mysql.user Set Password=password (' New password ') where user= "test" and host= "localhost";

Mysql>flush privileges;

5. List all databases

Mysql>show databases;

6. Switching the database

Mysql>use ' database name ';

7. List all Tables

Mysql>show tables;

8. Display the data table structure

Mysql>describe table name;

9. Deleting databases and data tables

Mysql>drop database name;

Mysql>drop table Data Sheet name;

MySQL Add user, delete user and authorization