MySQL user and permission management

MySQL user and permission management

The main function of the MySQL permission system is to verify the user connected to a given host and grant the user the related DML and DQL permissions on the database. MySQL access control involves two phases: one is to check whether the server allows you to connect, and the other is to assume that you can connect, and the server checks every request you send. Check whether you have sufficient permissions to implement it. This document describes how to create, authorize, and revoke permissions related to the MySQL permission system.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

1. Get help on permission management

Root @ localhost [(none)]> help Account Management
For more information, type 'help <item> ', where <item> is one of the following
Topics:
You asked for help about help category: "Account Management"
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD

2. Create a mysql database user

-- Create a user's syntax
Root @ localhost [(none)]> help create user;
Name: 'create user'
Description:
Syntax:
Create user user_specification [, user_specification]...

User_specification:
User
[
| Identified with auth_plugin [AS 'auth _ string']
Identified by [PASSWORD] 'Password'
]

The create user command creates a new account and can also specify a password for it. This command adds a record to the user table.
This command only grants the usage permission. You need to use the grant command for further authorization. You can also use the grant command to directly create an account.
The following is an explanation of usage in the mysql official manual.
The USAGE privilege specifier stands for "no privileges." It is used at the global level
GRANT to modify account attributes such as resource limits or SSL characteristics without affecting
Existing account privileges.

-- Current demo Environment
Root @ localhost [(none)]> show variables like 'version ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Version | 5.5.39-log |
+ --------------- + ------------ +

-- Create a new user (no password specified)
Root @ localhost [(none)]> create user 'fred '@ 'localhost ';
Query OK, 0 rows affected (0.00 sec)

-- Specify the password to create a new user. % indicates arbitrary. That is, frank can access the database from any host.
Root @ localhost [(none)]> create user 'frank' @ '% 'identified by 'frank ';
Query OK, 0 rows affected (0.00 sec)

-- View the account you just added
Root @ localhost [(none)]> select host, user, password from mysql. user where user like 'Fr % ';
+ ----------- + ------- + --------------------------------------------- +
| Host | user | password |
+ ----------- + ------- + --------------------------------------------- +
| % | Frank | * 63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| Localhost | fred |
+ ----------- + ------- + --------------------------------------------- +

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page