Shulou(Shulou.com)05/31 Report--

This article mainly explains "introduction to MySQL rights management". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "introduction to MySQL Rights Management".

I. brief introduction of MySQL permissions

The simple understanding of the permissions of mysql is that mysql allows you to do what you can, not to cross the line. For example, if you are only allowed to perform select operations, then you cannot perform update operations. If you are only allowed to connect to mysql from one machine, then you cannot connect to mysql from any machine other than that machine.

So how is the permission of Mysql implemented? This is about the two-phase verification of mysql, which is described in detail below: the first phase: the server will first check to see if you allow the connection. Because you add host restrictions when you create a user, you can limit the cost, a certain IP, a certain IP segment, and anywhere, and only allow you to log in from the specified place of the configuration. Phase 2: if you can connect, Mysql will check every request you make to see if you have sufficient permissions to implement it. For example, if you want to update a table or query a table, Mysql will check to see if you have permissions on which table or column. For example, if you want to run a stored procedure, Mysql will check whether you have permission to execute the stored procedure and so on.

What permissions does MYSQL have? Copy a table from the official website to see:

Authority

Permission level

Permission description

CREATE

Database, table, or index

Create database, table, or index permissions

DROP

Database or table

Delete database or table permissions

GRANT OPTION

A database, table, or saved program

Give permission option

REFERENCES

Database or table

ALTER

Table

Change tables, such as adding fields, indexes, etc.

DELETE

Table

Delete data permissions

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 permissions

EXECUTE

Stored procedure

Execute stored procedure permissions

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 permission

CREATE USER

Server management

Create user permissions

PROCESS

Server management

View process permissions

RELOAD

Server management

Permission to execute flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload, etc.

REPLICATION CLIENT

Server management

Copy permission

REPLICATION SLAVE

Server management

Copy permission

SHOW DATABASES

Server management

View database permissions

SHUTDOWN

Server management

Turn off database permissions

SUPER

Server management

Execute kill thread permissions

How the permissions of MYSQL are distributed, that is, what permissions can be set for tables and columns, and so on, can be explained from a table in the official document:

Permission distribution

Permissions for possible settings

Table permissions

'Select', 'Insert',' Update', 'Delete',' Create', 'Drop',' Grant', 'References',' Index', 'Alter'

Column permissions

'Select', 'Insert',' Update', 'References'

Process permission

'Execute', 'Alter Routine',' Grant'

Second, the experience principle of MySQL authority:

Permission control is mainly for security reasons, so you need to follow the following empirical principles:

1. Grant only the minimum permissions that can meet the needs to prevent users from doing bad things. For example, users only need to query, then only give select permission, do not give users update, insert or delete permissions.

2. Limit the login host of a user when creating a user, which is generally limited to a specified IP or private network IP segment.

3. Delete users without passwords when initializing the database. When the database is installed, some users are automatically created who do not have a password by default.

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

5. Clean up unwanted users regularly. Reclaim permissions or delete users.

3. Actual combat of MySQL authority:

1. Instructions for using the GRANT command:

Let's take a look at an example, create a superuser jack that allows only local login, and allow permissions to be granted to other users with a password of: jack.

Mysql > grant all privileges on *. * to jack@'localhost' identified by "jack" with grant option;Query OK, 0 rows affected (0.01 sec)

The GRANT command describes:

ALL PRIVILEGES means all permissions, and you can also use select, update and other permissions.

ON is used to specify which libraries and tables the permissions are for.

The preceding * sign in *. * is used to specify the database name, and the subsequent * sign is used to specify the table name.

TO means to grant permissions to a user.

Jack@'localhost' stands for jack user, @ followed by restricted host, can be IP, IP segment, domain name and%,% means anywhere. Note: some versions here do not include local. You have previously set% for a user to log in anywhere, but you can't log in locally. This has something to do with the version. If you encounter this problem, just add a localhost user.

IDENTIFIED BY specifies the user's login password.

The WITH GRANT OPTION option means that the user can delegate the permissions he or she has to others. Note: there are often people who create an operating user without specifying the WITH GRANT OPTION option so that the user cannot later use the GRANT command to create a user or authorize another user.

Note: you can add permissions to the user repeatedly using GRANT, and the permissions can be superimposed. For example, if you first add a select permission to the user, and then add an insert permission to the user, then the user will have both select and insert permissions.

2. Refresh permissions

Use this command to make permissions effective, especially if you make update or delete updates to those permission tables such as user, db, host, etc. In the past, when the permissions are not updated after using grant, use the FLUSH PRIVILEGES command to refresh the permissions as long as you make changes to the permissions.

Mysql > flush privileges;Query OK, 0 rows affected (0.01 sec)

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 | +-- -+ 2 rows in set (0.00 sec) to view a user's permissions: mysql > show grants for 'jack'@'%' +-+ | Grants for jack@% | +- -+ | GRANT USAGE ON *. * TO 'jack'@'%' IDENTIFIED BY PASSWORD' * 9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0' | +-- -+ 1 row in set (0.00 sec)

4. Reclaim permissions

Mysql > revoke delete on *. * from 'jack'@'localhost';Query OK, 0 rows affected (0.01sec)

5. Delete a user

Mysql > select host,user,password from user +-- + | host | user | password | +- -+ | localhost | root | rhel5.4 | root | 127.0.0.1 | root |:: 1 | root | localhost | localhost | jack | * 9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0 | +- -+ 7 rows in set (0.00 sec) mysql > drop user 'jack'@'localhost' Query OK, 0 rows affected (0.01 sec)

6. Rename the account

Mysql > rename user 'jack'@'%' to' jim'@'%';Query OK, 0 rows affected (0.00 sec)

7. Change the password

1. Use the set password command mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' 123456') Query OK, 0 rows affected (0.00 sec) 2, with mysqladmin [root@rhel5 ~] # mysqladmin-uroot-p123456 password 1234abcd remarks: format: mysqladmin-u username-p old password password new password 3, edit user table mysql > use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-A Database changedmysql > update user set PASSWORD = PASSWORD ('1234abcd') where user =' root' 'directly with update Query OK, 1 row affected (0.01sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) 4, when losing root password: [root@rhel5 ~] # mysqld_safe-- skip-grant-tables & [1] 15953 [root@rhel5 ~] # 130911 09:35:33 mysqld_safe Logging to'/ mysql/mysql5.5/data/rhel5.4.err'. 130911 09:35:33 mysqld_safe Starting mysqld daemon with databases from / mysql/mysql5.5/data [root@rhel5] # mysql-u rootWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2 Server version: 5.5.22 Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >\ s-mysql Ver 14.14 Distrib 5.5.22, for Linux (i686) using EditLine wrapperConnection id: 2 Current database: Current user: root@SSL: Not in use Current pager: stdoutUsing outfile:''Using delimiter:; Server version: 5.5.22 Source distributionProtocol version: 10 Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. Characterset: utf8UNIX socket: / tmp/mysql.sockUptime: 36 secThreads: 1 Questions: 5 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 18 Queries per second avg: 0.138-mysql > use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-A Database changedmysql > update user set password = PASSWORD ('123456') where user = 'root';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > flush privileges Query OK, 0 rows affected (0 sec) so far, I believe you have a deeper understanding of "introduction to MySQL Rights Management". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.