MySQL Add user, delete user and authorization

3. Add Users:

(Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)

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

The first type:

Add a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";

But the increase of the user is very dangerous, you want to like someone knows test1 password, then he can be on any computer on the Internet to log on to your MySQL database and to your data can do whatever you like, the solution see the second kind of example:

The first kind: Add a user test2 password for ABC, so that he can only login on localhost, and can query, insert, modify, delete the database mydb (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the number directly from the Internet.

Mysql> Grant Select,insert,update,delete on book.* to [e-mail protected] identified by "ABC";

If you do not want to test2 have a password, you can call another command to erase the password.

Mysql> Grant Select,insert,update,delete on book.* to [e-mail protected] identified by "";

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 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 database;

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