MySQL DCL (GRANT, REVOKE) and MySQL user password related

DCL: Data Control Language
GRANT
REVOKE

User: Virtual user.
Password: Use the password () function

The user's account is only available for authentication and requires additional authorization to use the feature.

User account structure:
[Email protected]

USERNAME:
Generally no more than 16 characters

HOST:
HOSTNAME
Ip
Ip/netmask
Fqdn
DOMAIN
% any character of any length
_ Any single character

The default MySQL startup will be reversible hostname, so enable MySQL server can use--skip-name-resolv Disable resolution, can increase user connection speed.


Authorization form:
MySQL reads the following tables into memory at startup and generates authorization tables based on these tables:
User
User accounts, global permissions, other non-privileged fields
Db
Permission definitions at the database level
Host
Deprecated, consolidated into user table
Tables_priv
Table-level permission definitions
Colums_priv
Column (field) segment-level permissions
Procs_priv
Permissions related to stored procedures and stored functions
Proxies_priv
Delegate user rights

To view the contents of the corresponding authorization table:
Mysql>use MySQL;
Mysql>select * FROM DB \g

Permission level:
Global
Library
Table
Column
stored procedures and stored functions

Permissions applied to common statements:
CREATE database table Index
DROP database table View
GRANT OPTION database table stored procedures and functions
LOCK TABLES Database
REFERENCES Database Tables
EVENT Database

INSERT table field
SELECT table field
UPDATE table field

DELETE table
ALTER table
INDEX table

CREATE Temporary TABLES table
TRIGGER table

CREATE tablespace Server Management
CREATE USER Server Management
PROCESS Server Management
Proxy Server Management
RELOAD Server Management
REPLICATION CLIENT Server Management
Replaction SLAVE Server Management
SHOW DATABASES Server Management
SHUTDONW Server Management
SUPER Server Management
All [privileges] Server Management
USAGE Server Management

Files on the server where file accesses the service

ALTER ROUTINE stored procedures and functions
CREATE ROUTINE stored procedures and functions
EXECUTE stored procedures and functions

CREATE View View
SHOW View

User account and Password:
To create a user:
CREATE USER ' USERNAME ' @ ' HOST ' [identified by ' PASSWORD ']
Typically, users are created with only usage permissions and simple query permissions.

View Users:
Mysql>use MySQL;
Mysql> SELECT User,host,password from User;

To delete a user:
DROP USER ' USERNAME ' @ ' HOST '

To set a password for a user:
1. In MySQL interactive interactive mode:
mysql> SET PASSWORD for ' USERNAME ' @ ' HOST ' =password (' PASSWORD ');

2. Use mysqladmin:
#mysqladmin-uusername-hhost-p password ' password '

3. Modify the database directly:
mysql> UPDATE user SET Password=password (' Password ') WHERE user= ' USERNAME ' and host= ' HOST ';
Mysql>flush privileges;

To rename a user:
RENAME USER Olduser to NewUser


User rights:
Specify user rights:
Automatically created and authorized if the user does not exist.
GRANT Pri1,pri2,... On [object_type] priv_level to ' usernmae ' @ ' HOST ' [identified by ' PASSWORD '] [REQUIRE] [with with_option];
Pri:
All Privileges Permissions

Object_type: Object Type
TABLE
function functions
PROCEDURE stored procedures or stored functions

Priv_level:
* All libraries
* * All Tables
Dbname.* All tables for a library
DBNAME. Tbname a table for a library
Tbname Specific tables
Dbname,routinname a stored procedure or stored function for a database

REQUIRE: Related properties that must be met by the user when connecting
None None
Ssl_option SSL-related
SSL-based SSL
X509 a certificate based on the X509 format.

With_option: Resource usage limit, num is 0 means no qualification.
Grant_option allows the user to authorize access to other users
Max_queries_per_hour num The maximum number of queries per hour is num
Max_updates_per_hour num is allowed to use num times update per hour
Max_connections_per_hour num initiates a num connection request per hour
Max_user_connections num number of connections per hour for an account

To cancel a user's permissions:
REVOKE Prt1,pri2,... On [object_type] priv_level from ' USERNAME ' @ ' HOST ';

To view User rights:
SHOW GRANTS for ' USERNAME ' @ ' HOST ';

Refresh Authorization:
FLUSH privileges;

Instance:
1. Create the user test and set the password to TESTPWD:
Mysql>create USER ' Test ' @ '% ' identified by ' testpwd ';
Or
Mysql>grant ...
Or
Mysql>insert into Mysql.user;

Mysql>flush privileges;


Note: If the message creation user cannot log in, delete the anonymous user. Normally it should be done after initialization.

Mysql>use MySQL;
Mysql>delete from user WHERE user= ';
Mysql>flush privileges;


2. Use the grant command to create the account test and reset the password to TESTPWD:
Mysql>crant CREATE on test.* to ' test ' @ ' percent ' identified by ' testpwd ';


3. Grant the test user the Create permission and view:
Mysql>grant CREATE on test.* to ' test ' @ '% ';
Mysql>flush privileges;
Mysql>show GRANTS for ' test ' @ '% ';
Mysql>\q

Note: After setting permissions and refreshing, re-login will take effect.


4. Authorization for field permissions:
Mysql>use Test
Mysql>grant UPDATE ("age" on TestDB to ' test ' @ '% ');
Mysql>update testdb SET age=40 WHERE id=1;


Applications for 5.SUPER permissions:
Mysql>grant SUPER on * * to ' test ' @ '% ';

Super permissions are special, you can perform shutdown, modify global variables, and more.

6. Cancel the SELECT permission for test in the TestDB database:
Mysql>revoke SELECT on testdb.* from ' Test ' @ '% '

7. Database Administrator Password Recovery:
Find ideas:
Turn off service--Modify Service Script add startup option--Start service--Change password with update command--shut down service--Modify script--Restart service

--skip-grant-tables Skip Authorization Form
--skip-networkding disables the network and prevents other users from logging on through the network.


#systemctl Stop Mysqld
#vim/etc/init.d/mysqld
...
Case ' $mode ' in
' Start ')
$bindri/mysqld_safe--skip-grant-tables--skip-networking ...
...
#systemctl Start mysqld
#mysql
Mysql>use MySQL;
Mysql>selet User,host,password from User;
Mysql>update user SET Password=password (' 123456 ') WHERE user= ' root ';
Mysql>\q
#systemctl Stop Mysqld
#vim/etc/init.d/mysqld
...
Case ' $mode ' in
' Start ')
$bindri/mysqld_safe ...
...
#systemctl start mysqld;
#mysql-uroot-p123456
Mysql>

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1680995

MySQL DCL (GRANT, REVOKE) and MySQL user password related