MySQL series 6 users and authorization

Tutorial Series
1. User Management

1. User Account
2. Add and delete accounts
3. Crack the administrative account password

2. Authorization Management

1. Authorization
2. Query authorization
3. Revocation of authorization

Summarize

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