Mysql database user and permission management records

In mysql, user permissions are a deep learning. If you improperly set database permissions, the database on the entire mysql database server may be exploited, let's take a look at the mysql user and permission management settings.

1. Basic instructions for MySQL users:

1.1 Basic user Structure
MySQL user: User Name @ host

■ User name: within 16 characters
■ HOST: Host Name, IP address, network address, etc.
Host Name: www. bKjia. c0m, localhost

IP: 192.168.0.1

Network Address: 172.16.0.0/255.255.0.0

The host also supports wildcards: % and _

172.16. %. %

%. BKjia. c0m
Note: For users with host names, MySQL will try to reverse resolve the host name, which may cause slow connection. If the IP address of the reverse resolution is different from the address of the connection point, the connection may fail. Therefore, to speed up 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 is managed by the MySQL internal password () function.

1.2 authorization table:
The MySQL user is only used for authentication, and the user has the corresponding authorization mechanism. First, MySQL user authorization is mainly performed in the following table:

User: Contains user accounts, global privileges, and other non-privilege columns.
User: user Account and global Permissions

Db: Contains database-level privileges.
Db: database-level Permissions

Host: Obsolete.
Host: abandon

Tables_priv: Contains table-level privileges.
Tables_priv: Table-level Permissions

Columns_priv: Contains column-level privileges.
Columns_priv: column-level permission

Procs_priv: Contains stored procedure and function privileges.
Procs_priv: permission related to stored procedures and stored Functions

Proxies_priv: Contains proxy-user privileges.
Proxies_priv: proxy user permission
After the MySQL database service is started, these six tables will be directly loaded into the memory, and all subsequent certifications will be obtained directly from these six tables in the memory instead of reading the disk.

1.3 descriptions of authorization tables:
■ The range column of the user table determines whether to allow or reject incoming connections. For allowed connections, the permissions granted by the user table indicate the global (superuser) permissions of the user. These permissions apply to all databases on the server.
■ The range column of the db table determines the host from which the user can access the database. The permission column determines which operation is allowed. The database-level permissions granted apply to the database and its tables.
■ Tables_priv and columns_priv tables are similar to db tables, but they are more refined: they are applied at the table and column level rather than at the database level. Grant the table-level permission to apply to the table and all its columns. The column-level permission is only applicable to dedicated columns.
■ The procs_priv table is applicable to stored programs. The permissions granted to a program only apply to a single program.
Management permissions (such as RELOAD or SHUTDOWN) are only specified in the user table. This is because administrative operations are performed by 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 to perform a management operation.

The FILE Permission is only specified in the user table. It is not administrative, but your ability to read or write files on the server host is not related to the database you are accessing.

When the mysqld server is started, the content of the authorization table is read into the memory. You can use the flush privileges statement or the mysqladmin flush-privileges or mysqladmin reload command to re-read the table.

Ii. Permissions provided by MySQL
Account permission information is stored in the user, db, host, tables_priv, columns_priv, and procs_priv tables of the mysql database. When MySQL is started, the server reads the contents of these database tables into the memory.

The names of permissions involved in the GRANT and REVOKE statements are displayed in the following table, and the table names of each permission in the authorization table are associated with the context of each permission.

Permission
Column
Context
 
CREATE
Create_priv
Databases, tables, or Indexes
 
DROP
Drop_priv
Database or table
 
GRANT OPTION
Grant_priv
Database, table, or stored 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 Program
 
CREATE ROUTINE
Create_routine_priv
Saved Program
 
EXECUTE
Execute_priv
Saved Program
 
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
 

Iii. When does the permission change take effect?
When mysqld is started, the contents of all authorization tables are read into the memory and take effect from this time.

When the server notices that the authorization table has been changed, the existing client connection has the following impact:

■ The table and column permissions take effect in the next request of the client.
■ The database permission change takes effect in the next USE db_name command.
■ Changes in global permissions and passwords take effect the next time the client connects.
If you use GRANT, REVOKE, or set password to modify the authorization table, the server will notice and immediately re-load the authorization table into the memory.

If you manually modify the authorization table (using INSERT, UPDATE, or DELETE), you should execute mysqladmin flush-privileges or mysqladmin reload to tell the server to load the authorization table again, otherwise, your changes will not take effect unless you restart the server.

If you directly change the authorization table but forget to reload it, your change will take effect after the server is restarted. This may confuse you with why your changes have not changed!

 

Iv. MySQL user account management
4.1 create a user and authorize the user:
4.1.1 create user: CREATE USER
Basic Syntax:

Create user username @ host [identified by 'Password']
Example:

Mysql> create user barlow @ '%' identified by '201312 ';
Query OK, 0 rows affected (0.34 sec)
4.2.2 create a user and authorize: GRANT
Basic Syntax:

GRANT priv_type [(column_list)] ON [object_type] priv_level TO username @ '%' [identified by [PASSWORD] 'Password'];
■ Priv_type: ALL or the permissions in the preceding 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' @ '% ';
+ Shards +
| Grants for barlow @ % |
+ Shards +
| Grant usage on *. * TO 'barlow' @ '%' identified by password' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 '|
| Grant select, INSERT, UPDATE, DELETE, create on 'testdb'. * TO 'barlow' @ '%' |
+ Shards +
2 rows in set (0.01 sec)

You can see through the instance that the user only has the explicit authorization permission.

4.3 user management
4.3.1 delete a user:
Basic Syntax:

Drop user 'username' @ 'host'
4.3.2 rename a user:
Basic Syntax:

Rename user old_name TO new_name
4.3.3 revoke the granted User Permissions
Basic Syntax:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]... ON [object_type] priv_level FROM user [, user]...
Example:

Mysql> revoke insert on testdb. * FROM barlow @ '% ';
Query OK, 0 rows affected (0.01 sec)

As you can see, the barlow @ '%' user has no INSERT permission.

4.3.4 change user password:
Method 1: SET PASSWORD
Basic Syntax:

Set password for 'user _ name' @ 'host' = PASSWORD ('new _ password ');
Example:

Mysql> set password for 'barlow' @ '%' = PASSWORD ('20140901 ');
Query OK, 0 rows affected (0.07 sec)
Note: The administrator can modify the password of any user, but normal users can only change their own passwords.

You can also use the above syntax to modify the password Syntax ):

Set password = PASSWORD ('new _ password'); Method 2: directly update the password field in the mysql. user table to change the 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 root user password:
If the root user's password is missing, retrieve it as follows:

■ Stop the mysqld service
■ When mysqld_safe is started, two parameters are passed: -- skip-grant-tables -- skip-networking
■ Start the mysqld service
■ Use the password field in the mysql. user table to directly update the root user password
Example:

Stop the service and modify the parameters passed by mysqld_safe:

[Root @ localhost ~] # Service mysqld stop

Shutting down MySQL... SUCCESS!

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

 

Log on to mysql to change the password:

[Root @ localhost ~] # Service mysqld start

Starting MySQL ...... SUCCESS!

[Root @ localhost ~] # Mysql # Note: No Logon password is required.

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) 2000,201 3, Oracle and/or its affiliates. All rights reserved.

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

Affiliates. Other names may be 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 ('000000') 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 the mysqld_safe 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 # login again, prompting you to Require a Password

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)

[Root @ localhost ~] # Mysql-u root-p # use the new password to log on normally

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) 2000,201 3, Oracle and/or its affiliates. All rights reserved.

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

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help; 'or 'H' for help. Type 'C' to clear the current input statement.

Mysql>
For more information about mysql user and permission management, see the official documentation: http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#privilege-changes