MySQL Optimization--Rights management

Reprint Please specify source: http://blog.csdn.net/l1028386804/article/details/46763767

In the MySQL database, there are mysql_install_db scripts to initialize the permissions table, and the tables that store the permissions are:

1. User table

2. DB table

3. Host Table

4. Table_priv table

5. Columns_priv table

6. Proc_priv Table

MySQL access control consists of 2 stages:

• Phase 1: The server checks if you are allowed to connect.
• Phase 2: Assuming you can connect, the server checks every request you make. See if you have enough permissions to implement it. For example, if you discard a table from a table selection (select) row in a database or from a database, the server determines that you have select permissions on the table or drop permissions on the database.

The server uses the, and tables in the database in the two stages of access control, mysql user and the fields in db host These authorization tables are as follows:

Table name	user	db	host
Range field	Host	Host	Host
	User	Db	Db
	Password	User
Permission fields	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 phase of access control (request confirmation), if the request involves a table, the server can refer to the tables_priv columns_priv table separately. The fields for these tables are as follows:

table name	tables_priv	columns_priv
range field	host	host
&n BSP;	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 a range field and a permission field.

The user table is mainly divided into: Users column, permission column, security column, Resource control column

The host table is mainly divided into: User columns, permission columns

The drawback here is that Mysql.user does not have a column that saves the user's creation time.

Sometimes when troubleshooting a user problem, such as a customer at some time that the connection is not on the database, we can only find out in the user table is the existence of the users

But do not know the user's creation time, that is, the customer said that the time when the user has been created we do not know

Account Management

MySQL provides a number of statements to manage user accounts, which can be used to include logging in and exiting MySQL server, creating users, deleting users, password management, rights management

The security of MySQL database requires account management to ensure

Log in and exit MySQL

Common parameters for MySQL commands

-H: hostname or IP, default is localhost, preferably specify-H parameter

-U: User name

-P: Password, note: The string after this parameter and-p cannot have spaces

-P: Port number, default is 3306

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

-e: Executes the SQL statement and, if specified, executes the command or SQL statement following-e after login and exits

Returns the structure of the Book table after the command is executed, and automatically exits MySQL after the query returns

User

CREATE user User [identified by [PASSWORD] ' PASSWORD ']    

New Normal User

CREATE USER ' Jeffrey ' @ ' localhost ' identified by ' mypass ';

The username section is "Jeffrey" and the hostname defaults to "%" (that is, open permissions on all hosts)

If you do not require a password for the specified user login, you can omit the identified by section

For users that use the plug-in authentication connection, the server invokes the plug-in that specifies the name, and the client needs to provide the credentials required for the authentication method.

If the server cannot find the corresponding plug-in when the user is created or when the server is connected, an error is returned

Identified with syntax

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

Identified with can only be used in MYSQL5.5.7 and later versions.

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

The operation of the CREATE user statement is logged to the server log file or the operation history file

such as ~/.mysql_history. This means that anyone with read access to these files can read the plaintext password of the newly added user

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

CREATE user ' tom ' @ ' localhost ' identified by password ' *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 '; SELECT password (' Mypass '); SELECT * from ' MySQL '. ' User ' WHERE ' user ' = ' tom ';

Find the hash of your password first, and then enter the hash value when creating a new user

Then you can only see the hash value in the log.

To create a new user by using the GRANT statement

The GRANT user statement can be used to create an account through which a new record can be added to the user table

You also need to use the GRANT statement to give user permissions than the new user created by the CREATE USER statement

You must have grant permissions when you create a new user by using the GRANT statement.

Grammar

GRANT Priv_type [(column_list)] [, Priv_type [(column_list]] ...    On [object_type] {tbl_name | * | *. * | db_name.*}    to user [identified by [PASSWORD] ' PASSWORD ']        [, User [Identifie D 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 GRANT statement, Password testpwd, and give the user 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 ';

Execution results show successful execution, query user testuser permissions using SELECT statement

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

Note: The user and Host fields in the user table are case-sensitive, specifying the correct username or host name when querying

Direct operation of MySQL user table

Both the create user and the grant user, when creating users, actually add a new record in the user table.

Use the INSERT statement to insert a record into the Mysql.user table to create a new user

Insert permission is required when inserting

INSERT into Mysql.user (Host,user,password,[privilegelist]) VALUES (' Host ', ' username ', password (' password '), Privilegevaluelist)

Use Insert to create a new user whose user name is Customer1, hostname is localhost, password is customer1

INSERT into Mysql.user (Host,user,password) VALUES (' localhost ', ' customer1 ', password (' customer1 '))

Statement execution failed, review the warning message as follows:

Show WARNINGS;

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

Affects the execution of INSERT statements, using the SELECT statement to view records in the user table

You can see that the insert failed

Delete a normal user

Delete users by using the DROP USER statement or delete the corresponding record from the Mysql.user table directly from the delete user

The drop USER statement is used to delete one or more MySQL accounts. To use the drop USER, you must have the MySQL database's global

CREATE USER permissions or delete permissions.

Delete TestUser This user

DROP user ' testUser ' @ ' localhost ';

You can find testuser this user has deleted the

To delete a user by using the DELETE statement

DELETE from Mysql.user WHERE ' Host ' = ' localhost ' and ' user ' = ' testUser '

Root user to modify their own password

There are several ways to change the root password

1. Use the mysqladmin command to specify a 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's password. The flush privileges statement is required to reload user rights after an update is performed

3. Use the Set statement to modify the root user's password

The Set Password statement can be used to reset another user's login password or the account password they use

Grammar

SET Password=password ("Rootpwd")

The new password must be encrypted with the password function

Execute the following statement after logging in to MySQL with the root user

SET Password=password (' 123456 ')

Execute FLUSH Privileges statement or restart MySQL reload user right after execution

Root user to modify normal user password

1. Use the SET statement to modify the password of the ordinary user

2. Use the UPDATE statement to modify the password of the normal user

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

Use flush privileges statement or restart MySQL to reload user rights after execution

3. Use the GRANT statement to modify the normal user password

GRANT USAGE on *. someuser ' @ '% '  identified by ' Somepwd '

Use the following statement to change the password of the testuser user to 123456

Grant USAGE on *testuser*to ' localhost ' identified by ' 123456 ';

Note: Using the GRANT statement and mysqladmin to set the password, they will encrypt the password, in which case you do not need to use the password () function

Normal User Change Password

Use the SET statement to modify your password

For example, to modify the TestUser user's password, you need to use testuser this user login to MySQL, and then perform

SET Password=password (' 123456 ');

Solution to root user password loss

Start the MySQL service using the--skip-grant-tables option

When you start MySQL with the--skip-grant-tables option, the server will not load permissions to determine that any user can access the database

Linux under

Use Mysqld_safe to start the MySQL service, or you can 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 you start the MySQL service, you can log on using the root user

Windows under

You can read this article in detail

Windows MySQL Tip: 1045 Access denied for user ' root ' @ ' localhost ' using password Yes

Rights Management

Various permissions in MySQL

For the grant and REVOKE statements, Priv_type can be specified as any of the following:

Permissions	Significance
all [privileges]	To set all simple permissions except Grant option
Alter	Allow alter TABLE to be used
ALTER ROUTINE	To change or cancel a stored subroutine
CREATE	Allow use of Create TABLE
CREATE ROUTINE	To create a stored sub-program
CREATE Temporary TABLES	Allow use of Create temporary TABLE
CREATE USER	Allow use of the Create user, DROP user, RENAME user and revoke all privileges.
CREATE VIEW	Allow use of Create VIEW
DELETE	Allow use of delete
DROP	Allow drop TABLE to be used
EXECUTE	Allow users to run stored subroutines
FILE	Allow use of SELECT ... into outfile and load DATA INFILE
INDEX	Allow use of CREATE INDEX and drop index
INSERT	Allow use of Insert
LOCK TABLES	Allows you to use lock TABLES for tables for which you have SELECT permissions
PROCESS	Allow show full processlist to be used
REFERENCES	has not been implemented
RELOAD	Allow flush to be used
REPLICATION CLIENT	Allow users to ask the address of a subordinate server or home server
REPLICATION SLAVE	For replicated slave servers (read binary log events from the primary server)
SELECT	Allow use of Select
SHOW DATABASES	Show databases display all databases
SHOW VIEW	Allow use of show CREATE VIEW
SHUTDOWN	Allow the use of mysqladmin shutdown
SUPER	Allows the use of change master, KILL, PURGE master Logs and set global statements,mysqladmin debug commands, allowing you to connect (once) even if the max_connections has been reached.
UPDATE	Allow use of update
USAGE	Synonyms for "No permissions"
GRANT OPTION	Allow permissions to be granted

When upgrading from an older version of MySQL, to use Execute, create VIEW, SHOW view, create USER, create routine and alter routine permissions

Authorized

Authorization is granting permissions to a user

The permissions granted can be divided into multiple tiers:

· Global Hierarchy

Global permissions apply to all databases in 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 Hierarchy

Database permissions apply 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 on db_name. * Grant and REVOKE database permissions only.

· table Level

Table permissions apply to all columns in a given table. These permissions are stored in the Mysql.talbes_priv table. Grant all on db_name.tbl_name and revoke all on db_name.tbl_name only grant and revoke table permissions.

· Column Hierarchy

Column permissions apply 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 column.

· Sub-Program level

CREATE ROUTINE, ALTER ROUTINE, execute and grant permissions apply to stored subroutines. These permissions can be granted at the global level and at the database level. Furthermore, in addition to the Create routine, these permissions can be granted as sub-program levels and stored in the Mysql.procs_priv table.

When a subsequent target is a table, a stored function, or a stored procedure, theobject_type clause should be specified as table, function, or procedure. When upgrading from an older version of MySQL, to use the phrase, you must upgrade your authorization form

Create a new user Grantuser with the GRANT statement with the password "grantpwd"

User has query, insert permission on all data, and grant grant permission

GRANT SELECT, INSERT on *.*to ' grantuser ' @ ' localhost ' identified by ' 123456 ' with GRANT OPTION;

The query shows that Grantuser was created successfully and gave Select, INSERT, grant permissions with the corresponding field value y

Users granted grant permissions can log in to MySQL and create additional user accounts, where Grantuser users

Revoke permissions

To revoke a permission is to revoke certain permissions that have been given to the user. Recovering the user's unnecessary permissions can guarantee the security of the system to some extent.

With revoke, the records of the user account are deleted from the DB, host, Tables_priv, Columns_priv tables, but the user account records remain

Saved in the user table.

Grammar

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] ...

With the REVOKE statement, you must have global Create or update permissions for the MySQL database

Use the REVOKE statement to cancel the Insert permission for a user Grantuser

REVOKE INSERT on * * from ' grantuser ' @ ' localhost ';

You can see that the INSERT permission for the Grantuser user has been retracted.

Note: When upgrading from an older version of MySQL, if you want to use Execute, create VIEW, SHOW view, create USER, create ROUTINE, ALTER ROUTINE

permissions, you must first upgrade the authorization form

View Permissions

The show Grant statement can display the user's permission information

Grammar

Show grants for ' user ' @ ' host ';

Querying user Grantuser for permission information using the show GRANT statement

Show grants for ' grantuser ' @ ' localhost ';

The return result shows the account information in the user table, followed by the grant SELECT on keyword, which indicates that the user was granted SELECT permission;

*. * indicates that SELECT permission acts on all data tables of all databases;

Encrypted password for user after identified by

Here, only individual user permissions are defined, and grant can display more detailed permission information, including global-level and non-global-level permissions

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

View the anonymous user inside MySQL

If there are anonymous users, then the client can log in to the MySQL database without a password, so there is a security risk

Methods for checking anonymous users

SELECT * from Mysql.user WHERE ' user ' = ';

If you find the record that the user field value is empty, it means that there is an anonymous user, and you need to delete this record

If you log in to MySQL with an anonymous user, you can see that the user name is empty.

Delete statement

DELETE from Mysql.user WHERE ' user ' = '; SELECT * from Mysql.user WHERE ' user ' = ';

Summarize

This article simply elaborated the MySQL user management and the authority aspect content, hoped to be helpful to everybody

If you want to learn more about MySQL access control

You can refer to this article: The architecture of MySQL permissions

The core is two graphs .

1. Client connection Request Authentication phase

2. Client Operation request Authentication phase

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Optimization--Rights management