MySQL database user and Rights Management records

First, the MySQL user's basic description:

1.1 Basic structure of the user
Users of MySQL: User name @ host

Username: within 16 characters
Host: Can be host name, IP address, network address, etc.
Host Name: Www.111cn.net,localhost

ip:192.168.0.1

Network address: 172.16.0.0/255.255.0.0

The host also supports wildcard characters:% and _

172.16.%.%

%.111cn.net
Note: For a user with a host name, MySQL tries to resolve the hostname, which may cause the connection to be slow, and if the resolved IP address is not the same as the address of the connection point, an unreachable condition may occur. Therefore, to expedite the connection and avoid resolution problems, you can add the following line to the my.cnf file to accelerate the connection:

--skip-name-resolve
The MySQL user's password has password () function management within MySQL.

1.2 Authorization form:
MySQL users are only used for authentication, and the user has the right to have the appropriate authorization mechanism implementation. First MySQL user authorized, mainly for the following Liu sheet:

User:contains user accounts, global privileges, and other non-privilege columns.
User: Account number, global permissions

Db:contains database-level privileges.
DB: library-level permissions

Host:obsolete.
Host: Obsolete

Tables_priv:contains table-level privileges.
Tables_priv: Table-level permissions

Columns_priv:contains column-level privileges.
Columns_priv: Column-level permissions

Procs_priv:contains stored procedure and function privileges.
Procs_priv: Stored procedures and stored function-related permissions

Proxies_priv:contains proxy-user privileges.
Proxies_priv: Delegate User rights
After the MySQL database service is started, these six tables will be loaded directly into memory, and all future authentication will be obtained directly from the six tables in memory instead of reading the disk.

1.3 Description of each authorization form:
The User table scope column determines whether incoming connections are allowed or denied. For allowed connections, the permissions granted by the user table indicate the global (Superuser) rights of the users. These permissions apply to the all database on the server.
The DB table scope column determines which database the user can access from which host. The permission column determines which operation is allowed. The permissions granted to the database level apply to the database and its tables.
Tables_priv and Columns_priv tables are similar to DB tables, but more refined: they are applied at the table and column level rather than at the database level. Granting table-level permissions applies to tables and all of its columns. Permissions granted at the column level apply only to private columns.
The Procs_priv table applies to saved programs. Grant Program-level permissions apply only to a single program.
Administrative permissions (such as reload or shutdown, and so on) are specified only in the user table. This is because administrative operations are operations of the server itself and are not specific databases, so there is no reason to list such permissions in other authorization tables. In fact, you only need to query the user table to determine whether you are performing a management operation.

The file permission is also specified in the user table only. It is not an administrative privilege, but the ability to read or write files on the server host has nothing to do with the database you are accessing.

When the MYSQLD server is started, read the contents of the authorization table into memory. You can make it re-read the table by flush the privileges statement or by executing the mysqladmin flush-privileges or mysqladmin command.

Second, the permissions provided by MySQL
Account authority information is stored in the user, DB, host, Tables_priv, Columns_priv, and Procs_priv tables of the MySQL database. At the time of MySQL startup, the server reads the contents of these database tables into memory.

The name of the permission involved in the GRANT and REVOKE statements is displayed in the following table, as well as the table column name and the context per permission for each permission in the authorization table.

Permissions
Column
Context

CREATE
Create_priv
Database, table, or index

DROP
Drop_priv
Database or table

GRANT OPTION
Grant_priv
A database, table, or saved program

REFERENCES
References_priv
Database or table

Alter
Alter_priv
Table

DELETE
Delete_priv
Table

INDEX
Index_priv
Table

INSERT
Insert_priv
Table

SELECT
Select_priv
Table

UPDATE
Update_priv
Table

CREATE VIEW
Create_view_priv
View

Show VIEW
Show_view_priv
View

ALTER Routine
Alter_routine_priv
Saved programs

CREATE Routine
Create_routine_priv
Saved programs

EXECUTE
Execute_priv
Saved programs

FILE
File_priv
File access on the server host

CREATE Temporary TABLES
Create_tmp_table_priv
Server Management

LOCK TABLES
Lock_tables_priv
Server Management

CREATE USER
Create_user_priv
Server Management

PROCESS
Process_priv
Server Management

RELOAD
Reload_priv
Server Management

REPLICATION CLIENT
Repl_client_priv
Server Management

REPLICATION SLAVE
Repl_slave_priv
Server Management

Show DATABASES
Show_db_priv
Server Management

SHUTDOWN
Shutdown_priv
Server Management

SUPER
Super_priv
Server Management

Third, when the permission changes take effect
When Mysqld is started, the contents of all authorization tables are read into memory and take effect from this time.

When the server notices that the authorization table has been changed, existing client connections have the following effects:

Table and column permissions take effect the next time the client requests it.
Database permission changes take effect on the next use db_name command.
Changes to global permissions and password changes take effect the next time the client connects.
If the authorization table is modified with GRANT, REVOKE, or set password, the server notices and immediately loads the authorization table into memory.

If you manually modify the authorization form (using INSERT, UPDATE, or delete, and so on), you should perform mysqladmin flush-privileges or mysqladmin reload tell the server to load the authorization form, or your changes will not take effect. Unless you reboot the server.

If you change the authorization form directly but forget to overload, restart the server and your changes will take effect. This may make you wonder why your changes have not changed!

Four, MySQL user account management
4.1 Creating Users and authorizations:
4.1.1 Create User:
Basic syntax:

CREATE USER Username@host [identified by ' Password ']
Example:

mysql> CREATE USER barlow@ '% ' identified by ' 123456 ';
Query OK, 0 rows affected (0.34 sec)
4.2.2 Create user and authorize: Grant
Basic syntax:

GRANT priv_type[(column_list)] on [object_type] priv_level to username@ '% ' [identified by [PASSWORD] ' PASSWORD '];
Permissions in the Priv_type:all or the above permission table.
Priv_level: *| *.*| Db_name.*| db_name.tbl_name| tbl_name| Db_name.routine_name
Example:

Mysql> GRANT create,insert,select,update,delete on testdb.* to barlow@ '% ';
Query OK, 0 rows affected (0.21 sec)

Mysql> show grants for ' Barlow ' @ '% ';
+-------------------------------------------------------------------------------------------------------+
| Grants for barlow@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE on *.* to ' Barlow ' @ '% ' identified by PASSWORD ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on ' TestDB '. * to ' Barlow ' @ '% ' |
+-------------------------------------------------------------------------------------------------------+
2 rows in Set (0.01 sec)

As you can see from the illustration above, users have only explicitly authorized permissions.

4.3 User Management
4.3.1 Delete User:
Basic syntax:

DROP USER ' username ' @ ' host '
4.3.2 Rename User:
Basic syntax:

RENAME USER Old_name to New_name
4.3.3 to reclaim granted user rights
Basic syntax:

REVOKE Priv_type [(column_list)] [, Priv_type [(column_list)]] ... On [object_type] priv_level from user [, user] ...
Example:

Mysql> mysql> REVOKE INSERT on testdb.* from barlow@ '% ';
Query OK, 0 rows affected (0.01 sec)

As you can see from the image above, the barlow@ '% ' user has no insert permission.

4.3.4 Modify User password:
Method One: SET PASSWORD
Basic syntax:

SET PASSWORD for ' user_name ' @ ' host ' = PASSWORD (' New_password ');
Example:

mysql> SET PASSWORD for ' Barlow ' @ '% ' = PASSWORD (' 987654 ');
Query OK, 0 rows affected (0.07 sec)
Description: The administrator can modify any user's password, but ordinary users can only modify their own password.

The user modifies his or her password syntax (which can also be modified using the syntax above):

SET PASSWORD = PASSWORD (' New_password '); method two: PASSWORD field implementation of direct Update Mysql.user table Modify password: Basic syntax:mysql> use MySQL

mysql> UPDATE user SET Password = Password (' new_password ') WHERE user= ' user_name ' and host= ' Host ';

mysql> FLUSH privileges;
Example:

mysql> use MySQL

Database changed

mysql> UPDATE user SET Password = Password (' Redhat ') WHERE user= ' Barlow ' and host= '% ';

Query OK, 1 row affected (0.03 sec)

Rows matched:1 changed:1 warnings:0

mysql> FLUSH privileges;

Query OK, 0 rows affected (0.08 sec)
4.4 Retrieve the password of root user:
If the root user's password is forgotten, it can be retrieved using the following methods:

Stop MYSQLD Service
Pass two parameters when starting Mysqld_safe:--skip-grant-tables--skip-networking
Start the MYSQLD service
Using the direct Update Mysql.user table's password field implementation to modify the root user password
Example:

Stop service, modify Mysqld_safe pass parameters:

[root@localhost ~]# service mysqld Stop

Shutting down MySQL ... success!

[Root@localhost ~]# Vim/etc/init.d/mysqld

Login MySQL Modify password:

[root@localhost ~]# Service mysqld start

Starting MySQL ... ........... ..... success!

[root@localhost ~]# MySQL # #注意, no login password is needed here.

Welcome to the MySQL Monitor. Commands End With; or G.

Your MySQL Connection ID is 1

Server version:5.6.13 Source Distribution

Copyright (c), 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark the Oracle Corporation and/or its

Affiliates. Names may trademarks of their respective

Owners.

Type ' help, ' or ' h ' for help. Type ' C ' to clear the current input statement.

mysql> UPDATE mysql.user SET Password = Password (' 123456 ') WHERE user= ' root ';

Query OK, 0 rows Affected (0.00 sec)

Rows Matched:3 changed:0 warnings:0

mysql> FLUSH privileges;

Query OK, 0 rows affected (0.03 sec)
Restore Mysqld_safe Pass Parameters:

[root@localhost ~]# service mysqld Stop

Shutting down MySQL ... success!

[Root@localhost ~]# Vim/etc/init.d/mysqld

$bindir/mysqld_safe--datadir= "$datadir"--pid-file= "$mysqld _pid_file_path" $other _args >/dev/null 2>&1 &

[root@localhost ~]# Service mysqld start

Starting MySQL ... success!

[root@localhost ~]# MySQL # #再次登录, prompt requires password

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

[Root@localhost ~]# mysql-u root–p # #使用新密码正常登录

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or G.

Your MySQL Connection ID is 2

Server version:5.6.13 Source Distribution

Copyright (c), 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark the Oracle Corporation and/or its

Affiliates. Names may trademarks of their respective

Owners.

Type ' help, ' or ' h ' for help. Type ' C ' to clear the current input statement.

Mysql>
More MySQL user and rights management content, refer to official documentation: Http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#privilege-changes