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

1 Overview

MySQL users and rights management follow the rule of minimum authority authorization to ensure the security of the system.

This article mainly explains the related concepts and operations of user MySQL user and rights management.

3 MySQL permission category

Library level: have corresponding permissions for some libraries

Table level: have relevant permissions on some tables

Field level:

Management classes: such as super

Program class: such as calling a function or executing a function

Management class:

CREATE USER: create a user account

RELOAD: reloading

LOCK TABLES: locking tabl

REPLICATION CLIENT, REPLICATION SLAVE: replication function

SHUTDOWN: shut down the database server

FILE: loading content from a file

SHOW DATABASES: viewing database

PROCESS: related to process

SUPER: other permissions that are inconvenient to categorize, second only to users with other administrative functions of root

Program classes: combined into 12 permissions (3x4)

FUNCTION: function

PROCEDURE: stored procedure

TRIGGER: trigger

Operation: have four operation CREATE,ALTER,DROP,EXECUTE for all the above three program classes

Library and table levels:

CREATE,ALTER,DROP: create, modify, and delete libraries and tables

INDEX: index

CREATE VIEW: permissions for statements that create views

SHOW VIEW: permissions to view views

GRANT: you can generate a copy of your permission and transfer it to other users. The transferred permission cannot be recovered, and it is generally not recommended to grant this permission.

OPTION: options related to other permissions

Data manipulation:

Table:

INSERT/DELETE/UPDATE/SELECT

Field:

SELECT (col1,col2,...)

UPDATE (col1,col2,...)

INSERT (col1,col2,...)

Note that delete is a full row deletion, so it cannot be used to delete fields

All permissions: ALL, ALL PRIVILEGES

Metadata database (data dictionary): mysql library that holds relevant data about the current system, such as the definition of objects on the current database.

MySQL user Management

User account composition: user@host

User: account name

Host: which client hosts can request to create connection threads for this account

%: any character of any length

_: any single character

By default, mysql resolves the logged-in ip to the hostname. For example, if the host ip is 192.168.1.71, the hostname is CentOS7A.sunny.com, then the test@192.168.1.% account access is authorized on the mysql server, but CentOS7A.sunny.com is not authorized. When the mysql server does not turn off name resolution, 192.168.1.71 will remotely connect to the mysql server and will be recognized as the account test@CentOS7A.sunny.com. Cannot log in to the mysql server because test@CentOS7A.sunny.com is not authorized to log in to the mysql server

Authorized hostname is not the same as ip

Skip_name_resolve=ON # turns off name resolution

Create a user:

CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD]' password'] [, 'user'@'host' [IDENTIFIED BY [PASSWORD]' password'].]

Rename: RENAME USER

RENAME USER old_user TO new_user [, old_user TO new_user]...

Delete users: there is no Recycle Bin, unless there is a backup, otherwise delete can not be restored

DROP USER 'user'@'host' [,' user'@'host']...

Let MySQL reload the authorization table: change the data with update to manually execute flush

FLUSH PRIVILEGES

Authorization

Authorization at the three levels of db, host and user

Permission-related tables in mysql library: tables_priv, column_priv, procs_priv, proxies_priv

The syntax is as follows

GRANT priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_level TO user_specification [, user_specification]... [REQUIRE {NONE | ssl_option [[AND] ssl_option]...}] [WITH with_option...]

The relevant explanations are as follows

[REQUIRE {NONE | ssl_option [[AND] ssl_option]...}]: connect based on ssl

Object_type:

TABLE: tabl

| | FUNCTION: function |

| | PROCEDURE: process |

Priv_level: permission level

* represents all libraries and all tables

| | *. * indicates all tables of the owner |

| | db_name.* indicates all tables of the specified library |

| | db_name.tbl_name indicates the specified table of the specified library |

| | tbl_name indicates the specific table of all libraries |

| | db_name.routine_name: one of the storage processes |

Ssl_option:

SSL

| | Certificate in X509 format |

| | CIPHER 'cipher': specifies the encryption algorithm |

| | ISSUER 'issuer': requires the certificate issuer to be the specified issuer |

| | other information in SUBJECT 'subject': certificate |

With_option: a value of 0 below indicates no limit

GRANT OPTION: indicates that the permission obtained can be transferred

| | MAX_QUERIES_PER_HOUR count: the maximum number of operations initiated by an account per hour |

| | MAX_UPDATES_PER_HOUR count: the number of updates initiated within an hour |

| | MAX_CONNECTIONS_PER_HOUR count: how many short connection requests are initiated per hour |

| MAX_USER_CONNECTIONS count: how many connections an account can initiate at the same time.

View Authorization: SHOW GRANTS; views its own permissions

SHOW GRANTS [FOR 'user'@'host']

Deregistration: REVOKE

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

ON [object_type] priv_level

FROM 'user'@'host' [,' user'@'host']...

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM user [, user]...

Examples

Authorized account 'test'@'192.168.1.%'

MariaDB [sunny] > grant select on sunny.students to 'test'@'192.168.1.%' identified by "Pass1234"

Additional authorization is additional, and direct authorization is fine. If delete permission is added,

MariaDB [sunny] > grant delete on sunny.students to 'test'@'192.168.1.%' identified by "Pass1234"

The corresponding fields of authorization have relevant permissions

If the test account is authorized to have update permission for the field major of table sunny.students, then there is no update permission for other fields.

MariaDB [sunny] > grant update (major) on sunny.students to 'test'@'192.168.1.%' identified by "Pass1234"

Reclaim permission

MariaDB [(none)] > revoke update (major) on sunny.students from 'test'@'192.168.1.%'

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.