MySQL user and data security topics

1 Brief introduction

1.1 Overview

1.2 Permissions Classification

2 adding users

The 2.1 syntax is for example the following:

CREATE user user_specification [, user_specification] ... user_specification:    user    [      | Identified with Auth_plugin [as ' auth_string ']        identified by [PASSWORD] ' PASSWORD '    ]

2.2 Examples:

mysql> create user [email protected] identified by ' Local ';mysql> create user simple;mysql> create user ' remote ' @ '% ' identified by ' remote ';mysql> insert to Mysql.user (Host, user, Password) values (' localhost ', ' simple ', Password (' simple '));

Note: Assume that the username or host name includes special characters. A single lead must be used before and after it. If special characters are not included. Single-cited can be omitted.

Username behind the host name localhost indicates that the local user is connected to MySQL, and the host name% indicates that all external hosts are connected to MySQL.

When creating username, do not specify the hostname, the default is%.

Create username again. Host names are different. MySQL thinks this is two different users.
Create user does not specify password, then agree that the relevant user does not have to pass password to ask.
2.3 Viewing User rights:

Mysql> SELECT * from Mysql.user where user= ' remote ' \g*************************** 1. Row *************************** Host:% user:remote Password: *123DD712CFD ed6313e0ddd2a6e0d62f12e580a6f select_priv:n insert_priv:n update_priv:n delete_ Priv:n create_priv:n drop_priv:n reload_priv:n shutdown_priv:n proce            Ss_priv:n file_priv:n grant_priv:n references_priv:n index_priv:n          Alter_priv:n show_db_priv:n super_priv:n create_tmp_table_priv:n lock_tables_priv:n Execute_priv:n repl_slave_priv:n repl_client_priv:n create_view_priv:n show_view_priv:n Cre Ate_routine_priv:n alter_routine_priv:n create_user_priv:n event_priv:n trigger_priv:ncre             Ate_tablespace_priv:n Ssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions:0 max_updates:0 Max_c onnections:0 max_user_connections:0 Plugin:authentication_string:NULL

Permissions are n, which means that at this point the user does not have much permission, they can only use the show statement to query the list of all storage engines and character sets. They can see the database information_schema, and can query the table. DDL and DML statements cannot be used.

3 Changing username and password

3.1 Change username Syntax:

RENAME USER Old_user to New_user    [, Old_user to New_user] ...

3.2 Examples:

mysql> rename user ' remote ' @ '% ' to ' remote1 ' @ ' percent ';mysql> rename user ' simple ' to ' simple ' @ ' 10.186.18.% ';

Note: This statement can only change the username word or host name. User password cannot be changed.

Instance 1 changed the username, and instance 2 changed the host name.

3.3 Changing user password syntax:

SET PASSWORD [for user] =    {        PASSWORD (' cleartext PASSWORD ')      | Old_password (' cleartext PASSWORD ')      | ' Encrypted password '    }

3.4 Examples:

Mysql> Set password for remote1 = password (' remote1 ');mysql> set password for [email protected] ' localhost ' = passwor D (' simple1 ');

Note: [for user] in user default feel remote users (such as%), to change the local user needs to specify localhost after username (such as instance 2).

If the user is not set password cannot join password through this command. Otherwise, an error will be indicated that there are no matching rows.

4 Deleting a user

4.1 Delete User syntax:

DROP user user [, user] ...

4.2 Examples:

mysql> Drop user remote1;mysql> drop user [email protected] ' localhost ';mysql> drop user [email protected] ' 10.186 .18.% ';

Note: If you specify only username, no host name is specified, the hostname defaults to '% '.

After the user is deleted, the user's built tables, indexes, or other database objects remain, because MySQL does not record who created those objects.

5 MySQL can be granted a classification of permissions

Note: The library level works for all tables in the library, and the table level only works for specific tables in the library.

5.1 List of authorized classifications

Permission types	Scope	Simple Introduction
SELECT	Global, library, table. Column	Enable users to use Select to access specific tables
INSERT	Global, library, table, column	Enables users to use Insert to add rows to a particular table
UPDATE	Global, library, table, column	Enables users to change values in a specific table using update
DELETE	Global, library. Table	Enables users to delete rows in a particular table using delete
CREATE	Global, library, table	Enables users to create databases and tables using
Alter	Global, library, table	Enables users to modify specific tables in the database using Alteer table
DROP	Global, library. Table	Enables users to delete libraries, tables, and views using drop
INDEX	Global. Library, table	Enables the user to have created on the table. Ability to delete indexes
REFERENCES	Global. Library. Table	Enables the user to have the ability to create foreign keys on a table
All [All privileges]	Global, library. Table, process. Proxy	Abbreviation for full permission name (except Grant OPTION)
CREATE VIEW	Global. Library, table	Give users the right to create and change views
EXECUTE	Global, library. Table	Giving users the right to run stored procedures
GRANT OPTION	Global, library, table, process, proxy	Give the user super privileges (rights can be granted or recycled to other users)
SHOW VIEW	Global, library, table	Give users permission to show CREATE view
TRIGGER	Global, library, table	Give the user full permission to operate trigger
ALTER ROUTINE	Global. Library. Process	Enables users to change and delete stored procedures and stored functions in a particular database
CREATE ROUTINE	Global, library	Gives users the ability to create new stored procedures and stored functions in a particular database
CREATE tablespace	Global	Enables users to create, change, delete tablespaces and log file groups
CREATE Temporary TABLES	Global, library	Enables the user to create temporary TABLE
CREATE USER	Global	Enables users to Create,rename,drop user and revoke all privileges
EVENT	Global. Library	Enable users to use event scheduling
FILE	Global	Enables a user to trigger a database to read or write files
LOCK TABLES	Global. Library	Enable the user to have lock table permissions on a table with SELECT permissions
PROCESS	Global	Enables users to view all threads using show processlist
PROXY	User to User	Enable users to use proxies
RELOAD	Global	Enables the user to flush operations
REPLICATION CLIENT	Global	Enables users to find the master-slave server
REPLICATION SLAVE	Global	Enable slave to read Binlog from host
SHOW DATABASES	Global	View all databases using show databases
SHUTDOWN	Global	Enables users to stop the database service using mysqladmin shutdown
SUPER	Global	Agree to use other administrative commands such as change MASTER to, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug
USAGE		Synonyms for no privileges

5.2 Authorization Syntax

GRANT    Priv_type [(column_list)]      [, Priv_type [(column_list]] ...    On [object_type] priv_level to    user_specification [, user_specification] ...    [REQUIRE {NONE | ssl_option [[and] ssl_option] ...}]    [With With_option ...] GRANT PROXY on User_specification    to User_specification [, user_specification] ...    [With GRANT option]object_type:    TABLE  | FUNCTION  | Procedurepriv_level:    *  | * *  | db_name.* |  db_name.tbl_name  | tbl_name  | db_ Name.routine_nameuser_specification:    user    [      | Identified with Auth_plugin [as ' auth_string ']        identified by [PASSWORD] ' PASSWORD '    ]ssl_option:    SSL  | X509  | CIPHER ' CIPHER '  | ISSUER ' ISSUER '  | SUBJECT ' SUBJECT ' with_option:    GRANT Option  | Max_queries_per_hour Count  | Max_updates_per_hour Count  | Max_connections_per_hour Count  | Max_user_connections Count

5.3 Creating frequently and data

mysql> use test;mysql> CREATE TABLE pri_test (c1 int (ten), C2 varchar ());mysql> insert into pri_test values (1, ' Test1 '), (2, ' test2 ');

6 Granting User table and column-level permissions

6.1 Creating a test user

Create user [email protected] '% ' identified by ' User_tab_insert ';

6.2 Authorized Instances

Grant insert on Test.pri_test to user_tab_insert;grant Update (C1) on test.pri_test to [email protected] ' localhost ' identi Fied by ' user_tab_update ';

Note: User user_tab_insert can insert data into table Pri_test after authorization, user user_tab_update can update the table pri_test. And no matter who created this table.

assume that a user is authorized to not exist. such as User_tab_update. MySQL will voluntarily create this user, default host bit '% ', and no password specified.

So it is best to understand the designation when authorizing.

For several permissions, such as Update,reference, you can indicate the columns used by the permission, such as the C1 column of table Pri_test.

7 granting user Database-level permissions

7.1 Creating a test user

Create user [email protected] '% ' identified by ' User_db_delete ', create user [email protected] '% ' identified by ' User_db_al Ter ';

7.2 Authorized Instances

Grant Delete on test.* to user_db_delete;grant alter on test.* to User_db_alter;

NOTE: Granting database permissions is similar to table permissions, granting a permission does not imply having a permission. For example, granting the user delete and alter permissions does not imply that the user can select a table in the database.

8. Grant user global level permissions

8.1 Creating a test user

Create user [email protected] '% ' identified by ' User_alter ', create user [email protected] '% ' identified by ' User_all ';

8.2 Authorized Instances

Grant Alter on *. user_alter;grant all on *. * to User_all;

9 Viewing permissions

9.1 View Current User rights:

Mysql> Show grants;+----------------------------------------------------------------------------------------- ---------------------------------------+| Grants for [email protected]%                                                                                                              |+----------------------------------------------------------------------------- ---------------------------------------------------+| GRANT all privileges on * * to ' root ' @ '% ' identified by PASSWORD ' *8f5fd68db2095e8c849c884a05ec8e2b75c418b2 ' with GRANT OP tion |+--------------------------------------------------------------------------------------------------------- -----------------------+

9.2 To view the specified user rights:

Method 1:

Mysql> Show grants for [email protected] '% '; +------------------------------------------------------------------- ----------------------------------------+| Grants for [email protected]%                                                                                   |+----------------------------------------------------------------------------- ------------------------------+| GRANT ALTER on *. user_alter ' @ '% ' identified by PASSWORD ' *c0d3f7283734bf5f77e41352adfc9a307ac8a344 ' |+------------ -----------------------------------------------------------------------------------------------+

Method 2:

Mysql> SELECT * from Mysql.user where user= ' User_all ' \g*************************** 1. Row *************************** Host:% user:user_all Password: *f02c912c6 8b67b7097baec8fe76ba6f50357895c select_priv:y insert_priv:y update_priv:y delet E_priv:y create_priv:y drop_priv:y reload_priv:y shutdown_priv:y Pro            Cess_priv:y file_priv:y grant_priv:n references_priv:y index_priv:y          Alter_priv:y show_db_priv:y super_priv:y create_tmp_table_priv:y lock_tables_priv:y Execute_priv:y repl_slave_priv:y repl_client_priv:y create_view_priv:y show_view_priv:y C Reate_routine_priv:y alter_routine_priv:y create_user_priv:y event_priv:y trigger_priv:yc         Reate_tablespace_priv:y Ssl_type:    ssl_cipher:x509_issuer:x509_subject:max_questions:0 max_updates:0 m ax_connections:0 max_user_connections:0 Plugin:authentication_string:NULL

10 Permission passing

The GRANT statement can finally be followed with GRANT option, which allows the authorized user to pass the acquired permission to a third-party user, regardless of whether the other user has the permission.

Control 1: Using with GRANT option

Log in to MySQL through the root user, create a user select_grant1,select_grant2 and authorize Select_grant1

/mysql-5.5.28/bin/mysql-uroot-pjesse-h10.186.18.108-p3355mysql> create user [email protected] '% ', [email Protected] '% ';mysql> grant SELECT on * * to [e-mail protected] '% ' with GRANT option;

Log in to MySQL via select_grant1 user and authorize for Select_grant2

/mysql-5.5.28/bin/mysql  -uselect_grant1-h10.186.18.108-p3355mysql> Grant SELECT on * * to [email protected] '% ' with GRANT option; Query OK, 0 rows Affected (0.00 sec)

Authorized Success!

Control 2: Do not use with GRANT option

Log in to MySQL through the root user, create a user select_grant1,select_grant2 and authorize Select_grant1

/mysql-5.5.28/bin/mysql-uroot-pjesse-h10.186.18.108-p3355mysql> create user select_no_g[email protected] '% ', [ Email protected] '% ';mysql> grant SELECT on * * to [email protected] '% ';

Log in to MySQL via select_grant1 user and authorize for Select_grant2

/mysql-5.5.28/bin/mysql-uselect_no_grant1-h10.186.18.108-p3355mysql> Grant SELECT On *. * to [email protected] '% '; ERROR 1045 (28000): Access denied for user ' select_no_grant1 ' @ '% ' (using Password:no)

11 Restricting permissions

The ability to grant usage restrictions to users, such as the ability to query the database 5 times per hour.

Mysql> Grant Select on test.* to [email protected] '% ' with max_queries_per_hour 5;

There are 3 other parameters Max_updates_per_hour, Max_connections_per_hour, Max_user_connections, respectively, to update the number of MySQL per hour, The number of connections to MySQL per hour and the maximum number of users per hour, assuming 0, means there is no limit.

12 Return permission

Revoke SELECT On * * FROM [email protected] '% ', revoke select on *. * FROM [email protected] '% '; Revoke grant OPTION on * * f Rom [email protected] '% ';

The WITH GRANT option was not used when the user granted permission. Direct revoke is Possible (demo Example 1).

With GRANT option, Grant option (Demo sample 2,3) is used to reclaim permissions.

13 Views and security

The GRANT statement is not only able to reference a table, but also to reference a view. All types of table permissions can be granted on the view.

**************************************************************************************************
Original address: http://blog.csdn.net/jesseyoung/article/details/38052519
**************************************************************************************************

MySQL user and data security topics