MySQL series 6 users and authorization

MySQL series 6 users and authorization

Tutorial Series

MySQL series: Basic concepts of MySQL relational database
MariaDB-server installation of MySQL series
MySQL Series II Multi-Instance Configuration
MySQL Series 3 Basics
MySQL Series 4 SQL Syntax
MySQL series five views, stored functions, stored procedures, triggers
MySQL Series 7 MySQL Storage Engine
MySQL Series 8 MySQL Server Variables
MySQL series 9 MySQL query cache and index
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
MySQL Series 11 Logging
MySQL Series 12 Backup and Recovery
MySQL Series 13 MySQL Replication
MySQL Series 14 MySQL High Availability Implementation
MySQL series 15 MySQL common configuration and performance stress test

1. User Management

1. User Account

The user account consists of two parts: username and HOST ('USERNAME'@'HOST')

HOST means:

  • Hostname
  • Specific IP address
  • Network segment/mask

You can use wildcards, % and _; 192.168.% means all hosts in this network segment

2. Add and delete accounts

Main: After modifying the user information in the database, you need to execute FLUSH PRIVILEGES; to refresh the authorization table to make it effective

create

  MariaDB [mysql]> CREATE USER 'user1'@'192.168.%';
  MariaDB [mysql]> CREATE USER 'user2'@'192.168.%' IDENTIFIED BY 'your_password';
  MariaDB [mysql]> SELECT user,host,password FROM user;
  +-------+-----------+-------------------------------------------+
  | user | host | password |
  +-------+-----------+-------------------------------------------+
  | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |
  | root | centos7 | |
  | root | 127.0.0.1 | |
  | root | ::1 | |
  | | localhost | |
  | | centos7 | |
  | user1 | 192.168.% | |
  | user2 | 192.168.% | *9E72259BA9214F692A85B240647C4D95B0F2E08B |
  +-------+-----------+-------------------------------------------+

delete

  MariaDB [mysql]> DROP USER user2@'192.168.%';
  MariaDB [mysql]> SELECT user,host,password FROM user;      
  +-------+-----------+-------------------------------------------+
  | user | host | password |
  +-------+-----------+-------------------------------------------+
  | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |
  | root | centos7 | |
  | root | 127.0.0.1 | |
  | root | ::1 | |
  | | localhost | |
  | | centos7 | |
  | user1 | 192.168.% | |
  +-------+-----------+-------------------------------------------+

Rename

  MariaDB [mysql]> RENAME USER user1@'192.168.%' TO testuser@'%';
  MariaDB [mysql]> SELECT user,host,password FROM mysql.user;
  +----------+----------+-------------------------------------------+
  | user | host | password |
  +----------+----------+-------------------------------------------+
  | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |
  | root | centos7 | |
  | root | 127.0.0.1 | |
  | root | ::1 | |
  | | localhost | |
  | | centos7 | |
  | testuser | % | |
  +----------+----------+-------------------------------------------+

Change Password

  MariaDB [mysql]> SET PASSWORD FOR testuser@'%' = PASSWORD('testpass');
  MariaDB [mysql]> SELECT user,host,password FROM mysql.user;          
  +----------+----------+-------------------------------------------+
  | user | host | password |
  +----------+----------+-------------------------------------------+
  | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |
  | root | centos7 | |
  | root | 127.0.0.1 | |
  | root | ::1 | |
  | | localhost | |
  | | centos7 | |
  | testuser | % | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
  +----------+----------+-------------------------------------------+

Other ways to change your password:

​ UPDATE user SET password=PASSWORD('testpass') WHERE user='testuser';


# mysqladmin -uroot -poldpass password 'newpass'

3. Crack the administrative account password

Recovering passwords from an empty database

  # systemctl stop mariadb
  # rm -rf /var/lib/mysql/* #Delete the database and run away # systemctl start mariadb

Recover password with data

1) Add the skip-grant-tables and skip-networking parameters under [mydqld] in the /etc/my.cnf configuration file

2) # systemctl restart mariadb restart service

3) Execute mysql to log in to the database

4) MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE user='root' AND host='localhost'; #Update password

5) MariaDB [(none)]> FLUSH PRIVILEGES; #Refresh the authorization table

6) Exit, modify the configuration file, delete the skip-grant-tables and skip-networking parameters, and restart the service. You can also use the following options for it when starting the mysqld process:

--skip-grant-tables

--skip-networking

2. Authorization Management

1. Authorization

​ Syntax: GRANT priv_type ON [object_type] priv_level TO user@'%' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

If the user does not exist during authorization, it will be created. Therefore, we usually do not create a user separately, but complete the authorization creation at the same time.

priv_type authorization type

-SELECT

- ​INSERT

- ​UPDATE

- ​DELETE

- ​CREATE

- DROP

-INDEX

-ALTER

- ​SHOW DATABASES

- ​CREATE TEMPORARY TABLES

- LOCK TABLES

- ​CREATE VIEW

- SHOW VIEW

- CREATE USER

- ALL PRIVILEGES or ALL

object_type Authorization object

-TABLE

- FUNCTION

- PROCEDURE

priv_level authorization level

- * or *.* means all libraries

- db_name.* means all tables in the specified database

- db_name.tbl_name The specified table in the specified library

- tbl_name indicates the table of the current library

- db_name.routine_name indicates the function, stored procedure, or trigger of the specified library

WITH GRANT OPTION

- MAX_QUERIES_PER_HOUR count

- MAX_UPDATES_PER_HOUR count

-MAX_CONNECTIONS_PER_HOUR count

-MAX_USER_CONNECTIONS count

MariaDB [school]> GRANT SELECT(stuid,name) ON TABLE school.students TO admin@'%' IDENTIFIED BY 'admin'; #Authorize the query permission of the stuid and name fields of the students table to the admin@'%' userMariaDB [school]> FLUSH PRIVILEGES; #Refresh the authorization table

2. Query authorization

MariaDB [school]> SHOW GRANTS FOR admin@'%'\G #View the permissions of the specified user**************************** 1. row ***************************
Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
*************************** 2. row ***************************
Grants for admin@%: GRANT SELECT (stuid, name) ON `school`.`students` TO 'admin'@'%'

[root@working ~]# mysql -uadmin -padmin -h192.168.0.7
MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER()\G #Query your own permissions**************************** 1. row ***************************
Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
*************************** 2. row ***************************
Grants for admin@%: GRANT SELECT (stuid, name) ON `school`.`students` TO 'admin'@'%'

3. Revocation of authorization

MariaDB [school]> REVOKE SELECT(stuid) ON school.students FROM admin@'%'; #Revoke the query permission of admin@'%' user on the stuid field

Summarize

This is the end of this article about MySQL users and authorization. For more relevant MySQL users and authorization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL user creation and authorization method
  • MySql add user, authorization, change password and other statements
  • How to create, authorize, and revoke MySQL users
  • Sharing of methods for creating new users and authorization in MySQL
  • User authorization and authorization deletion methods in MySQL
  • mysql create database, add users, user authorization practical method
  • mysql5.7 create user authorization delete user revoke authorization
  • MySql installation and configuration method (MySQL add users, delete users and authorization)
  • Create, authorize, delete, and modify passwords of mysql users in the WIN command prompt
  • MySQL creates users, authorizes users, revokes user permissions, changes user passwords, and deletes users (practical tips)

<<:  Detailed explanation of setting Context Path in Web application

>>:  Specific use of CSS front-end page rendering optimization attribute will-change

Recommend

Detailed tutorial on deploying Django project under CentOS

Basic Environment Pagoda installation service [Py...

W3C Tutorial (3): W3C HTML Activities

HTML is a hybrid language used for publishing on ...

CSS3 uses transform deformation combined with events to complete fan-shaped navigation

Because I wrote the word transition incorrectly i...

nginx solves the problem of slow image display and incomplete download

Written in front Recently, a reader told me that ...

Explore VMware ESXI CLI common commands

Table of contents 【Common commands】 [Summary of c...

vue-router hook function implements routing guard

Table of contents Overview Global hook function R...

This article will help you get started and understand the basic operations of Jquery

Table of contents 1. Steps to use Jquery: (1) Imp...

Use pure JS to achieve the secondary menu effect

This article example shares the specific code of ...

Summary of coalesce() usage tips in MySQL

Preface Recently, I accidentally discovered MySQL...

Problems and solutions encountered when connecting node to mysql database

I installed a new version of MySQL (8.0.21) today...

Summary of commonly used tags in HTML (must read)

Content Detail Tags: <h1>~<h6>Title T...

Three ways to check whether a port is open in a remote Linux system

This is a very important topic, not only for Linu...

Layui table selects the radio button of the specified row and scrolls to the implementation code of the row

The layui table has multiple rows of data. Throug...

Analysis of the difference between HTML relative path and absolute path

HTML beginners often encounter the problem of how ...

How to use Docker-compose to build an ELK cluster

All the orchestration files and configuration fil...