MySQL Optimization-permission management and mysql permission management

MySQL Optimization-permission management and mysql permission management

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46763767

In the mysql database, the mysql_install_db script initialization permission table is available, and the storage permission table is:

1. user table

2. db table

3. host table

4. table_priv table

5. columns_priv table

6. proc_priv table

MySQLAccess control involves two phases:

• Phase 1: The server checks whether you are allowed to connect.
• Phase 2: If you can connect, the server checks every request you send. Check whether you have sufficient permissions to implement it. For example, if you select rows from a table in the database or discard a table from the database, the server determines that you haveSelectPermission or permission on the databaseDropPermission.

The two phases of server access control are as follows:mysqlInuser,dbAndhostTable. The fields in these authorization tables are as follows:

Table Name	user	db	host
Range Field	Host	Host	Host
	User	Db	Db
	Password	User
Permission Field	Select_priv	Select_priv	Select_priv
	Insert_priv	Insert_priv	Insert_priv
	Update_priv	Update_priv	Update_priv
	Delete_priv	Delete_priv	Delete_priv
	Index_priv	Index_priv	Index_priv
	Alter_priv	Alter_priv	Alter_priv
	Create_priv	Create_priv	Create_priv
	Drop_priv	Drop_priv	Drop_priv
	Grant_priv	Grant_priv	Grant_priv
	Reload_priv
	Shutdown_priv
	Process_priv
	File_priv

For the second stage of access control (request validation), if the request involves a table, the server can also refertables_privAndcolumns_privTable. The fields of these tables are as follows:

Table Name	tables_priv	columns_priv
Range Field	Host	Host
	Db	Db
	User	User
	Table_name	Table_name
		Column_name
Permission Field	Table_priv	Column_priv
	Column_priv
Other fields	Timestamp	Timestamp
	Grantor

Each authorization table contains the range field and permission field.

The user table consists of the user column, permission column, security column, and resource control column.

Host tables are mainly divided into user columns and permission columns.

What is missing here isMysql. user does not have a column that stores the creation time of the user.

Sometimes when troubleshooting user problems, for example, if a customer cannot connect to the database at a certain time, we can only check whether the user exists in the user table.

However, we do not know the creation time of this user. That is to say, we do not know whether the user has been created at the time specified by the customer.

Account Management

MYSQL provides many statements to manage user accounts. These statements can be used to log on to and exit the MYSQL server, create users, delete users, manage passwords, and manage permissions.

The security of the MYSQL database must be ensured through account management.

Log on to and exit MYSQL

Common mysql Command Parameters

-H: Host Name or ip address. The default value is localhost. It is recommended to specify the-h parameter.

-U: User Name

-P: password. Note: The string and-p following this parameter cannot contain spaces.

-P: port number. The default value is 3306.

Database Name: You can specify the database name at the end of the command.

-E: Execute an SQL statement. If this parameter is specified, the command or SQL statement following-e will be executed and exited after logon.

 

 

After the command is executed, the structure of the book table is returned. After the query is returned, MYSQL is automatically exited.

User

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']    [, user [IDENTIFIED BY [PASSWORD] 'password']] 

Create a common user

CREATE USER 'jeffrey'@'localhost' identified BY 'mypass';

The user name is "jeffrey", and the default host name is "%" (that is, permission is granted to all hosts)

If the specified user does not need a password for logon, You can omit the identified BY section.

If you use a plug-in to authenticate the connection, the client must provide the creden。 required for the authentication method when the server calls the plug-in with the specified name.

If the server cannot find the corresponding plug-in when creating a user or connecting to the server, an error is returned.

Identified with syntax

CREATE user 'jeffrey'@'localhost' identified with my_auth_plugin;

Identified with can only be used in MySQL 5.5.7 or later versions.

Identified with and identified by are mutually exclusive, so only one authentication method can be used for one account.

Operations of the create user statement are recorded in the server log file or operation history file.

Example ~ /. Mysql_history. This means that anyone with read permission on these files can read the plaintext password of the newly added user.

One way is to use the password keyword when creating a user.

CREATE user 'tom'@'localhost' identified BY password'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';SELECT password('mypass');SELECT * FROM `mysql`.`user` WHERE `User` ='tom';

First, find out the hash value of your password, and then enter the hash value when creating a user.

You can only view the hash value in the log.

 

Use the GRANT statement to create a new user

The grant user statement can be used to create an account. You can use this statement to add a new record to the user table.

Compared with the new USER created by the create user statement, the GRANT statement must be used to GRANT the USER permission.

You must have the GRANT permission when using the GRANT statement to create a new user.

Syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...    ON [object_type] {tbl_name | * | *.* | db_name.*}    TO user [IDENTIFIED BY [PASSWORD] 'password']        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...    [REQUIRE        NONE |        [{SSL| X509}]        [CIPHER 'cipher' [AND]]        [ISSUER 'issuer' [AND]]        [SUBJECT 'subject']]    [WITH with_option [with_option] ...]

Create a new user testUser with the password testpwd using the GRANT statement, and GRANT the user the SELECT and UPDATE permissions for all data tables.

GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv`  FROM mysql.user WHERE `User` ='testUser';

The execution result shows that the execution is successful. You can use the SELECT statement to query the permissions of the user testUser.

The query results show that the SELECT and UPDATE permission fields are Y

Note: The User and host fields in the user table are case sensitive. You must specify the correct User name or host name during query.

Directly operate the MYSQL user table

Whether it is create user or grant user, a new record is actually added to the user table During USER Creation.

Use the INSERT statement to INSERT a record to the mysql. user table to create a new user.

You must have the INSERT permission when inserting data.

INSERT INTO mysql.user(host,user,password,[privilegelist])VALUES ('host','username',password('password'),privilegevaluelist)

Create a new user using INSERT. the user name is customer1, the host name is localhost, And the password is customer1.

INSERT INTO mysql.user(host,user,password)VALUES ('localhost','customer1',password('customer1'))

Statement execution failed. view the warning information as follows:

show WARNINGS ;

Because the ssl_cipher field does not have a default value defined in the user table, an error message is prompted here.

The execution of the insert statement is affected. You can use the SELECT statement to view records in the user table.

As you can see, insertion failed.

Delete common users

Use the drop user statement to DELETE a user. You can also use DELETE to DELETE the corresponding records from the mysql. USER table.

The drop user statement is used to delete one or more MYSQL accounts. To use drop user, you must have the global

Create user or DELETE permission.

Delete the user testUser

DROP user 'testUser'@'localhost';

 

The user testUser has been deleted.

Delete a user using the delete statement

DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'

The root user changes his/her password.

You can change the root password in multiple ways.

1. Use the mysqladmin command to specify the new password on the command line.

mysqladmin -u root -p password"rootpwd"

2. Modify the user table of the mysql database

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

The password ('') function is used to encrypt the user password. After the update is executed, run the flush privileges statement to reload the user permission.

3. Use the SET statement to change the password of the root user.

The set password statement can be used to reset the logon PASSWORD of another user or the account PASSWORD used by the user.

Syntax

SET PASSWORD=PASSWORD("ROOTPWD")

The new PASSWORD must be encrypted using the PASSWORD function.

Use the root user to log on to mysql and execute the following statement:

SET password=password('123456')

Run the flush privileges statement or restart MYSQL to reload user permissions.

Change the password of a common user as the root user

1. Use the SET statement to modify the password of a common user.

SET PASSWORD FOR 'USER'@'HOST' =PASSWORD("ROOTPWD") 

2. Use the update statement to change the password of a common user.

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

After execution, use the flush privileges statement or restart MYSQL to reload user permissions.

3. Use the GRANT statement to change the password of a common user.

GRANT USAGE ON *.* TO 'someuser'@'%'  IDENTIFIED BY 'somepwd'

Use the following statement to change the password of testUser to 123456.

grant USAGE ON *testUser*TO 'localhost' identified BY '123456';

Note: If you use the GRANT statement and MYSQLADMIN to set the PASSWORD, both of them will encrypt the PASSWORD. In this case, you do not need to use the PASSWORD () function.

Change Password for common users

Use the SET statement to change your password

SET password=password('newpassword'); 

For example, to change the password of the user testUser, you need to use the user testUser to log on to mysql and then execute

SET password=password('123456');

Root User Password loss Solution

Use the -- skip-grant-tables option to start the MYSQL service.

When MYSQL is started using the -- skip-grant-tables option, the server does not load the permission judgment, and any user can access the database.

LINUX

Use mysqld_safe to start the MYSQL service, or use the/etc/init. d/mysql command to start mysql

mysqld_safe --skip-grant-tables user=mysql

Or

/etc/init.d/mysql start-mysqld --skip-grant-tables

After starting the MYSQL service, you can use the root user to log on.

Windows

For more information, see this article.

Windows mysql prompt: 1045 access denied for user 'root' @ 'localhost' using password yes

Permission management

Various permissions in MYSQL

For GRANT and REVOKE statements, priv_type can be specified as any of the following:

Permission	Meaning
ALL [PRIVILEGES]	Set all simple permissions except GRANT OPTION
ALTER	Allow the use of ALTER TABLE
ALTER ROUTINE	Modify or cancel stored subroutines
CREATE	Allow the use of CREATE TABLE
CREATE ROUTINE	Create a stored subroutine
CREATE TEMPORARY TABLES	Allow the use of CREATE TEMPORARY TABLE
CREATE USER	Create user, drop user, rename user, and revoke all privileges are allowed.
CREATE VIEW	Allow the use of CREATE VIEW
DELETE	DELETE allowed
DROP	Allow DROP TABLE
EXECUTE	Allow users to run stored subroutines
FILE	Allow SELECT... into outfile and LOAD DATA INFILE
INDEX	Allow the use of create index and DROP INDEX
INSERT	INSERT allowed
LOCK TABLES	Allow the use of lock tables for TABLES with SELECT Permissions
PROCESS	Show full processlist allowed
REFERENCES	Not Implemented
RELOAD	Allow FLUSH
REPLICATION CLIENT	Allows the user to ask the address of the slave server or master server
REPLICATION SLAVE	Used for replication slave server (read binary log events from the master server)
SELECT	Allow SELECT
SHOW DATABASES	Show databases show all DATABASES
SHOW VIEW	Allow the use of SHOW CREATE VIEW
SHUTDOWN	AllowedMysqladmin shutdown
SUPER	Allow the use of change master, KILL, purge master logs, and set global statements,Mysqladmin debugCommand; allows you to connect (once), even if max_connections is reached.
UPDATE	Allow UPDATE
USAGE	Synonym for "no permission"
GRANT OPTION	Grant Permissions

When upgrading from an earlier version of MySQL, EXECUTE, create view, show view, create user, create routine, and alter routine permissions must be used.

Authorization

Authorization is to grant permissions to a user.

The granted permissions can be divided into multiple levels:

·Global level

Global permissions apply to all databases on a given server. These permissions are stored in the mysql. user table. Grant all on *. * and revoke all on *. * only GRANT and REVOKE Global permissions.

·Database level

The database Permission applies to all targets in a given database. These permissions are stored in the mysql. db and mysql. host tables. GRANT ALL ONDb_name. * And REVOKE ALL ONDb_name. * Only grant and revoke database permissions.

·Surface Level

The table Permission applies to all columns in a given table. These permissions are stored in the mysql. talbes_priv table. GRANT ALL ONDb_name.tbl_nameAnd REVOKE ALL ONDb_name.tbl_nameOnly grant and revoke table permissions.

·Column level

The column Permission applies to a single column in a given table. These permissions are stored in the mysql. columns_priv table. When using REVOKE, you must specify the same columns as the authorized columns.

·Subroutine level

The create routine, alter routine, EXECUTE, and GRANT permissions apply to stored subroutines. These permissions can be granted at the global level and database level. In addition to create routine, these permissions can be granted as a subroutine level and stored in the mysql. procs_priv table.

When the target is a table, a stored function, or a stored procedure,Object_typeThe clause should be specified as TABLE, FUNCTION, or PROCEDURE. When upgrading from an earlier version of MySQL, you must upgrade your authorization table to use this sub-statement.

Use the GRANT statement to create a new user grantUser with the password "grantpwd"

The user has the query and insert permissions for all data, and grants the GRANT permission.

GRANT SELECT ,INSERT ON *.*TO 'grantUser'@'localhost' identified BY '123456' WITH GRANT OPTION ;

The query shows that grantUser is successfully created and grant select, INSERT, and GRANT permissions. The corresponding field value is Y.

A user authorized to GRANT permissions can log on to MYSQL and create other user accounts. Here, it is a grantUser user.

Revoke permissions

Revoking permissions is to cancel some permissions granted to the user. Revoking unnecessary permissions of users can ensure the security of the system to a certain extent.

After the REVOKE is used to REVOKE permissions, the user account records will be deleted from the db, host, tables_priv, columns_priv tables, but the user account records are still

Save it in the user table.

Syntax

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...    ON [object_type] {tbl_name | * | *.* | db_name.*}    FROM user [, user] ...REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use the REVOKE statement, you must have the global CREATE permission or UPDATE permission for the mysql database.

Use the REVOKE statement to cancel the INSERT permission of the grantUser.

REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';

We can see that the INSERT permission of the grantUser user has been revoked.

Note: When upgrading from an earlier version of MYSQL, if you want to use EXECUTE, create view, show view, create user, create routine, ALTER ROUTINE

Permission. The authorization table must be upgraded first.

View Permissions

The show grant statement can display the user's permission information.

Syntax

show grants FOR 'user'@'host';

Use the show grant statement to query grantUser permissions.

show grants FOR 'grantUser'@'localhost';

 

The returned results show the account information in the user table. Next, assume that the account starts with the grant select on keyword, indicating that the user is granted the SELECT permission;

*. * Indicates that the SELECT Permission applies to all data tables in all databases;

Password after IDENTIFIED

Here, only individual user permissions are defined. GRANT can display more detailed permission information, including global and non-Global permissions.

If the permissions at the surface or column level are granted to users, they can also be displayed in the results.

View anonymous users in MYSQL

If anonymous users exist, the client can log on to the MYSQL database without a password, which poses a security risk.

How to check Anonymous Users

SELECT * FROM mysql.user WHERE `User`='';

If you find the record whose user field value is null, it indicates that an anonymous user exists and you need to delete this record.

If you use an anonymous user to log on to MYSQL, you can see that the user name is empty.

Delete statement

DELETE FROM mysql.user WHERE `User`='';SELECT * FROM mysql.user WHERE `User`='';

Summary

This article briefly describes the user management and permissions of MYSQL.

If you want to learn more about MYSQL access control

Refer to this article: MySQL permission Architecture

The core isTwo Graphs

1. Client Connection Request authentication stage

 

 

2. Client Operation Request authentication stage

 

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.