MySQL Operation Specifications (Summary)

User permission management create USER command: CREATEUSER

USER permission management create user command: CREATE USER

User permission management

Create user

Command: create user 'username' @ 'host' identified by 'Password ';

Note:

Username

Host specifies the host on which the user can log on. If the local user can use localhost, if you want the user to log on from any remote host, you can use the wildcard %.

Password: the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password.

For example, create user 'pig' @ '192. 168.1.101 _ 'idendified BY '123 ';

Note: When creating a user, you can add user information to the user table to add users.

Permission allocation

MySQL permission principle:

Permission control is mainly for security reasons. Therefore, we need to follow the following principles:

1. Grant only the minimum permissions that meet the requirements to prevent unauthorized users. If the user only needs to query, only the select permission should be granted, and the update, insert, or delete permission should not be granted to the user.

2. When creating a user, restrict the user's login host to a specified IP address or Intranet IP address segment.

3. delete a user without a password when initializing the database. Some users are automatically created after the database is installed. By default, these users do not have a password.

4. Set a password that meets the password complexity for each user.

5. Clear unnecessary users on a regular basis. Revoke permissions or delete users.

MySQL permissions:

Permission

Permission level

Permission description

CREATE

Databases, tables, or Indexes

Create Database, table, or index Permissions

DROP

Database or table

Delete database or table Permissions

GRANT OPTION

Database, table, or stored program

Grant permission options

REFERENCES

Database or table

ALTER

Table

Change tables, such as adding fields and indexes.

DELETE

Table

Delete data permission

INDEX

Table

Index permission

INSERT

Table

Insert permission

SELECT

Table

Query permission

UPDATE

Table

Update Permissions

CREATE VIEW

View

Create view Permissions

SHOW VIEW

View

View view Permissions

ALTER ROUTINE

Stored Procedure

Change Stored Procedure Permissions

CREATE ROUTINE

Stored Procedure

Create Stored Procedure permission

EXECUTE

Stored Procedure

Stored Procedure execution permission

FILE

File access on the server host

File Access permission

CREATE TEMPORARY TABLES

Server Management

Create temporary table Permissions

LOCK TABLES

Server Management

Lock table Permissions

CREATE USER

Server Management

Create User Permissions

PROCESS

Server Management

View process Permissions

RELOAD

Server Management

Execute commands such as flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.

REPLICATION CLIENT

Server Management

Copy permission

REPLICATION SLAVE

Server Management

Copy permission

SHOW DATABASES

Server Management

View database Permissions

SHUTDOWN

Server Management

Disable database Permissions

SUPER

Server Management

Run the kill thread permission

The distribution of MySQL permissions refers to the permissions that can be set for tables and columns. This can be explained from a table in the official document:

Permission Distribution

Possible Permissions

Table Permissions

'Select', 'insert', 'update', 'delete', 'create', 'drop', 'Grant ', 'references', 'index', 'alter'

Column permission

'Select', 'insert', 'update', 'references'

Process permission

'Execute ', 'alter Routine', 'Grant'

MySQL permission control:

1. GRANT command instructions:

Create a superuser fog that only allows local login, and grant permissions to other users with the password test123

Mysql> grant all privileges on *. * to fog @ 'localhost' identified by "test123" with grant option;

GRANT command description:

All privileges indicates ALL permissions. You can also use select, update, and other permissions.

ON is used to specify which databases and tables the Permission applies.

*. * Indicates the database name, and * indicates the table name.

TO: grant permissions TO a user.

Fog @ 'localhost' indicates the fog user. @ is followed by a restricted host, which can be an IP address, IP address segment, domain name, and %. % indicates any location. Note: Here % Some versions do not include the local version. I have previously set % to allow a user to log on anywhere, but cannot log on locally. This is related to the version, when this problem occurs, add a localhost user.

Identified by specifies the user's logon password.

With grant option indicates that the user can GRANT permissions to others.

Note: Some users often do not specify the with grant option when creating operation users. As a result, this user cannot use the GRANT command to create users or GRANT permissions to other users.

2. Refresh Permissions

Use this command to make the permission take effect, especially when you update or delete the permission table user, db, host, and so on. In the past, the permission has not been updated after grant is used. If the permission is changed, use the flush privileges command to refresh the permission.

Mysql> flush privileges;

3. View Permissions

View the permissions of the current user:

Mysql> show grants;

+ --------------------------------------------------------------------- +

| Grants for root @ localhost |

+ --------------------------------------------------------------------- +

| Grant all privileges on *. * TO 'root' @ 'localhost' with grant option |

| Grant proxy on ''@'' TO 'root' @ 'localhost' with grant option |

+ --------------------------------------------------------------------- +

View the permissions of a user:

Mysql> show grants for 'fog' @ '% ';

4. revoke permissions

Mysql> revoke delete on *. * from 'fog' @ 'localhost ';

5. delete a user

Mysql> drop user 'fog' @ 'localhost ';

Query OK, 0 rows affected (0.01 sec)

6. Rename an account

Mysql> rename user 'fog' @ '%' to 'jim' @ '% ';

7. Change the password

1. Use the set password command

Mysql> set password for 'root' @ 'localhost' = PASSWORD ('123 ');

2. Use mysqladmin

[Root @ rhel5 ~] # Mysqladmin-uroot-p123456 password 1234 abcd

Format: mysqladmin-u username-p old password New password

3. Use update to directly edit the user table

Mysql> use mysql

Mysql> update user set PASSWORD = PASSWORD ('1234abcd') where user = 'root ';

Mysql> flush privileges;

For more details, please continue to read the highlights on the next page: