Shulou(Shulou.com)06/01 Report--

Mysql permission system is very important, but at the same time, it is ignored by many developers or managers. The allocation of rights will not only lead to irreparable tragic consequences. I used to work in a company that pays no attention to database permissions, and all developers have the highest permissions on the system. Think about it, if one of them deletes the database one day, do you know who did it? Therefore, we must pay attention to it.

It is generally recommended that the highest permissions are given to only one person, who acts as a manager, and then assigns the corresponding permissions to other developers. Local libraries are better during the development phase, and you should be careful when giving permissions to online libraries.

The principle of authority authentication

The authority authentication of MySQL is authenticated through two aspects. First of all, the user's ip, user name and password will be verified, and the user who has passed the verification can connect to the Mysql. When connected, when the user takes any action, Mysql will verify the permission he has, and only with this permission will the operation requested by the user be performed. Otherwise, it will not be executed.

Mysql permission classification

The permissions of MySQL are roughly divided into three categories:

Operations on data, such as additions, deletions, modifications and queries.

Structure operations, such as creating libraries, modifying table structures, and so on.

Administrative permissions, such as creating users, assigning permissions, etc.

Mysql permission assignment principle

Give minimum permissions, such as permissions that the user currently only needs to see and only need to look at one table, then do not assign read permissions to all tables. Limit permissions to only one table, and don't be afraid to give read permission to all tables.

Be sure to restrict ip and set strong passwords when creating users.

Regularly clean up unwanted users and reclaim unwanted permissions.

Account management

Create an account

The syntax for creating a user in an mysql document is as follows:

CREATE USER [IF NOT EXISTS] user [auth_option] [, user [auth_option]]... [REQUIRE {NONE | tls_option [[AND] tls_option]...}] [WITH resource_option [resource_option]...] [password_option | lock_option]...

There are a lot of parameters, don't worry, take your time and take your time through the example. First create an account with the least number of options.

# create a user who can log in locally without a password: mysql > CREATE USER 'u1password login localhostworthy user query OK, 0 rows affected# create a user that requires password authorization, but do not restrict ipmysql > CREATE USER 'U2login authorization%' identified by '321232 password # Note: the password must use quotation marks, single quotation marks or double quotation marks, but an error will occur if you do not add it. # if you don't want to use a clear text password, you can use passwordmysql > select password ('111111') +-- + | password ('111111') | +-- + | * FD571203974BA9AFE270FE62151AE967ECA5E0AA | +- -- + 1 row in setmysql > CREATE USER'u3 destroy FD571203974BA9AFE270FE62151AE967ECA5E0AA' 192.168.1% 'IDENTIFIED BY PASSWORD' * FD571203974BA9AFE270FE62151AE967ECA5E0AA' Query OK, 0 rows affected

View a list of users

The system user list is the user table that is stored in the mysql library.

Mysql > SELECT user,host,account_locked FROM mysql.user

+-+

| | user | host | account_locked | |

+-+

| | root | localhost | N |

| | mysql.session | localhost | Y | |

| | mysql.sys | localhost | Y | |

| | U1 | localhost | N |

| | U2 |% | N |

| | U2 | localhost | N |

| | U3 | 192.168.1% | N |

+-+

7 rows in set

Delete user

The syntax for deleting a user is as follows:

DROP USER user name @ ip

Now let's delete u2percent%'.

Mysql > drop user u2 employees% query OK, 0 rows affected

In this way, U2 users are deleted.

Modify user account

The syntax is as follows:

Rename user old@'oldip' to new@'newip'

The examples are as follows:

Mysql > RENAME USER u1@localhost to user1@'127.0.0.1';Query OK, 0 rows affected

Authorization

After learning how to create and manage accounts, let's take a look at how to authorize users and how to reclaim unwanted permissions.

User authorization

The syntax for authorizing a user is as follows:

GRANT permission ON database name * table name TO user name @ ip

The examples are as follows:

Mysql > GRANT SELECT ON *. * TO 'u1roomroomlocalhost`; Query OK, 0 rows affected (0.00 sec)-- Global level authorization mysql > GRANT ALL ON test.* TO 'u2roomroomlocalhostauthorization; Query OK, 0 rows affected (0.00 sec)-- database level authorization mysql > GRANT ALL ON test.student TO 'u3roomroomlocalhost` WITH GRANT OPTION;-table level authorization

View the user's permissions

After authorizing the user, let's see if the user has been granted these permissions.

Reclaim user rights

When it is found that more permissions are given, those permissions should be reclaimed in a timely manner. The syntax for reclaiming permissions is very similar to the syntax for authorization.

REVOKE permissions ON database * table FROM user name @ ip address

The above are the details of MySQL permissions and security management, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.