MySQL user and Rights management of the database

1 Overview

MySQL user and Rights management obey the principle of least privilege authorization to ensure the security of the system

This article mainly explains the relevant concepts and actions about user MySQL user and Rights management.

3 MySQL Permissions category

Library level: Corresponding permissions for some libraries

Table level: Have permissions on some tables

Field Level:

Management classes: such as Super

Program class: Call a function, or execute a function

Management classes:

Create User: Creating an Account

RELOAD: Reload

Lock TABLES: Lock table

REPLICATION CLIENT, REPLICATION SLAVE: copy function

SHUTDOWN: Shutting down the database server

File: Loading content loaded from a file

Show DATABASES: View Database

Process: associated with processes

SUPER: Other permissions that are inconvenient to categorize, second only to root for users with other management functions

Program class: Combination of 12 permissions (3*4)

Function: Functions

PROCEDURE: Stored Procedures

TRIGGER: Trigger

Operation: There are four operations for the above three program classes Create,alter,drop,execute

Library and table levels:

Create,alter,drop: Creating, modifying, and deleting libraries and tables

Index: Indexed

Create VIEW: Permissions for the statement that created the view

Show View: View permissions

Grant: the ability to generate a copy of the permissions you have acquired to other users; the privilege of transferring is not recoverable, it is generally not recommended to grant this permission

Option: Additional permissions-related options

Data manipulation:

Table:

Insert/delete/update/select

Field:

SELECT (Col1,col2,...)

UPDATE (Col1,col2,...)

INSERT (Col1,col2,...)

Note that delete is an entire row and therefore cannot be used to delete a field

All permissions: All, all privileges

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

MySQL User management

User account composition: [Email protected]

User: Account name;

Host: Which client hosts the account can request to create connection threads;

%: Any character of any length;

_: any single character;

MySQL By default will log on to the IP address to the host name, such as the host IP is 192.168.1.71 host name is CentOS7A.sunny.com, then the MySQL server is authorized to [email protected]% account Access, No authorization CentOS7A.sunny.com, when the MySQL server does not turn off name resolution, 192.168.1.71 to remotely connect to the MySQL server, will be recognized as an account [email protected], because there is no authorization [email Protected] Login to MySQL server, so you cannot log on to MySQL server

The authorization hostname and IP are not equivalent,

Skip_name_resolve=on #关闭名称解析功能

To 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: no Recycle Bin, unless there is a backup, otherwise deleted can not be restored

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

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

FLUSH Privileges

Authorized

DB, host, user three-level authorization

Tables related to permissions 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] ...}] : SSL-based connection

Object_type:

Table: Tables

| Function: Functions

| PROCEDURE: Process

Priv_level: Permission Level

* denotes all tables for all libraries

| *. * represents all tables of the owner

| Db_name.* represents all tables for the specified library

| Db_name.tbl_name represents the specified table for the specified library

| Tbl_name represents a specific table for all libraries

| Db_name.routine_name: One of the storage processes

Ssl_option:

Ssl

| Certificates in X509 format

| CIPHER ' CIPHER ': Specifying cryptographic algorithms

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

| SUBJECT ' SUBJECT ': Additional information in the certificate

With_option: The following value is 0 for unlimited

GRANT OPTION: Indicates that the obtained permission can be donated

| Max_queries_per_hour count: The maximum number of operations an account initiates per hour

| Max_updates_per_hour count: Number of updates initiated within one hour

| Max_connections_per_hour Count: How many short connection requests are initiated in an hour

| Max_user_connections Count: The number of connections that an account can initiate at the same time.

View authorizations: show GRANTS; View your permissions

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

Cancel Authorization: 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] ...

Example

Authorized account ' Test ' @ ' 192.168.1.% '

MariaDB [sunny]> Grant Select on sunny.students to ' test ' @ ' 192.168.1.% ' identified by ' Pass1234 ';

Additional authorization is append, direct authorization can, if add delete permission again

MariaDB [sunny]> Grant Delete on sunny.students to ' test ' @ ' 192.168.1.% ' identified by ' Pass1234 ';

Authorization corresponding field has relevant permissions

If the grant test account has update permission on the field major of the table sunny.students, there is no update permission for the other fields

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

Reclaim Permissions

MariaDB [(None)]> revoke update (major) on sunny.students from ' Test ' @ ' 192.168.1.% ';

MySQL user and Rights management of the database