Getting started with MySQL is simple-learning notes-Chapter 15th MySQL user management

Document directory

• 15.2.1 log on to and exit the MySQL server
• 15.2.2. New common users
• 15.2.3 Delete common users
• 15.2.4. the root user changes his/her password.
• 15.2.5. change the password of a common user as the root user.
• 15.2.6. change the password of a common user
• 15.2.7. Solution to Root User Password loss
• 15.3.1. Various MySQL Permissions
• 15.3.2. Authorization
• 15.3.3. revoke permissions

15.2. Account Management 15.2.1. log on to and exit the MySQL server

Mysql-hhostname | hostip-P port-u username-P [Password] databasename-e "SQL statement"

 

-H host name or IP address

-P port [2, 3306]

-U Username

-P-p [Password] note that there is no space between them

-E. Execute the SQL statement and enclose it with double quotation marks.

 

You can use this statement to work with scheduled tasks of the operating system to automatically process table data. For example, you can regularly Delete expired data in a table.

 

15.2.2. New common users

1. Use the create user statement to create

Create user [identified by [Password] 'Password']

[, User [identified by [Password] 'Password']...

 

2. Use the insert statement to create a common user

Insert into mysql. User (host, user, password, ssl_cipher, x509_issuer, x509_subject)

Values ('localhost', 'test2', password ('test2 '),'','','');

Flush priveleges;

 

3. Use the grant statement to create a common user

Grant priv_type on database. Table to user [identified by [Password] 'password

[, User [identified by [Password] 'Password']...

 

15.2.3 Delete common users

1. Use the drop user statement to delete normal users.

Drop user [, user]…;

 

For example, drop user 'test' @ 'localhost ';

 

2. Use the delete statement to delete a common user.

Delete from mysql. User where user = 'username' and host = 'hostname ';

Flush privileges;

 

15.2.4. the root user changes his/her password.

1. Use the mysqladmin command to modify the password of the root user

Mysqladmin-u username-P password "new_password ";

 

2. Modify the user table

Update mysql. User SET Password = PASSWORD ("new_password") where user = 'root' and host = '';

Flush privileges;

 

3. Use the set statement to modify the password of the root user.

Set Password = PASSWORD ("new_password ");

 

15.2.5. change the password of a common user as the root user.

1. Use the mysqladmin command

Not applicable. mysqladmin can only change the root user password.

 

2. Modify the user table

Update mysql. User SET Password = PASSWORD ("new_password") where user = ''andhost = '';

Flush privileges;

 

3. Use the set statement to modify the password of a common user.

Set password for 'user' @ 'localhost' = PASSWORD ("new_password ");

4. Use the grant statement to modify the password of a common user.

Grant priv_type on database. Table to user [identified by [Password] 'password

[, User [identified by [Password] 'Password']...

15.2.6. change the password of a common user

Set Password = PASSWORD ("new_password ");

 

15.2.7. Solution to Root User Password loss

1. Use the-Skip-grant-tables option to start the MySQL service.

> Mysqld-Skip-grant-tables

 

#/Etc/init. d/MySQL start-mysqld-Skip-grant-tables

 

2. log on to the root user and set a new password.

Mysql-u Root

Update mysql. User SET Password = PASSWORD ("new_password") where user = 'root' and host = 'localhost ';

 

3. Load the permission table

Fush privileges;

 

 

15.3 permission management 15.3.1 and various MySQL Permissions

Create, select, update, delete

All [privileges] indicates all permissions.

15.3.2. Authorization

Grant priv_type [(column_list)] [, priv_type [(column_list)]...

On [object_type] {tbl_name | *. * | db_name .*}

To user [identified by [Password] 'Password']

[, User [identified by [Password] 'Password']...

[Require

None |

[{SSL | X509}]

[Cipher 'cipher' [and]

[Issuer 'issuer' [and]

[Subject 'subobject']

[With with_option [with_option]...]

 

Object_type =

Table

| Function

| Procedure

 

With_option =

Grant Option

| Max_queries_per_hour count

| Max_updates_per_hour count

| Max_connections_per_hour count

| Max_user_connections count

 

Grant select, insert, update, delete on testdb. * tocommon_user @ '%'

 

Database. Table *. * all tables in all databases

User 'user' @ 'host'

15.3.3. revoke permissions

Revoke priv_type [(column_list)] [, priv_type [(column_list)]...

On [object_type] {tbl_name | *. * | db_name .*}

From user [, user]...

 

Revoke all privileges, grant option from user [, user]...