MySQL User and Rights management

One, user rights

The grant command is used to create a new user, specify a user password, and increase user permissions

Mysql> Grant <privileges> on <what> to <user> [identified by ' <password> '] [with GRANT OPTION];

Parameter description:

1.<privileges> is a comma-delimited list of MySQL user rights that you want to give. The permissions you can specify can be divided into three types:

Database/data Table/Data column permissions:

Alter: Modifies an existing data table (for example, add/Remove Columns) and index.

Create: Create a new database or data table.

Delete: Deletes the record for the table.

Drop: Deletes a data table or database.

Index: Establish or delete the indexes.

Insert: Adds a table record.

Select: Displays/searches the table's records.

Update: Modifies a record that already exists in the table.

2. Manage MySQL user rights globally:

File: Read and write files on the MySQL server.

PROCESS: Displays or kills service threads belonging to other users.

RELOAD: Overloads the Access Control table, refreshes the log, and so on.

SHUTDOWN: Turn off the MySQL service.

3. Special privileges:

All: Allow to do anything (as root).

USAGE: Only allow login-nothing else is allowed.

Meaning of the value of the host column in the 4.user table

% matches all hosts

localhost localhost will not be parsed into an IP address and connected directly via Unixsocket

The 127.0.0.1 will be connected via TCP/IP protocol and can only be accessed natively;

:: 1:: 1 is compatible with support IPv6, indicating the 127.0.0.1 with IPv4

For example:mysql> grant all privileges on * * to ' test ' @ '% ' identified by ' 123456 ' with GRANT option;

All privileges: Indicates that all permissions are granted to the user. You can also specify specific permissions, such as: SELECT, CREATE, drop, and so on.

On: Indicates which databases and tables are in effect for these permissions, in the format: Database name. Table name, where the "*" is written to represent all databases, all tables. If I want to specify that permissions should be applied to the user table of the test library, you can write this: Test.user

To: Which user to grant permissions to. Format: "User name" @ "Login IP or domain name". % means no limit and can be logged in on any host. For example: "Test" @ "192.168.0.%", indicating that the test user can only log in the 192.168.0IP segment

Identified by: Specify the user's login password

With GRANT OPTION: Allows users to authorize their own permissions to other users

Permissions are automatically superimposed and do not overwrite the permissions previously granted.

Example:

1. Grant acts on the entire MySQL server:

Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL.

Grant all on * * to [email protected]; --DBA can manage all databases in MySQL

2. Grant acts on a single database:

Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB.

3. Grant acts on a single data table:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];

5. Grant acts on stored procedures, functions:

Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '

Grant execute on function testdb.fn_add to ' dba ' @ ' localhost '

Second, refresh permissions

After you have made permission changes to the user, you must remember to reload the permissions and write the permissions information from memory to the database.

mysql> flush Privileges;

Third, view user rights

View All Users

SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;

View User Permissions

Show grants for ' Test ' @ ' 192.168.1.% ';

Iv. Collection of rights

Delete Test this user's create permission, the user will not be able to create the database and the table.

Mysql> REVOKE Create on * * from ' [email protected] ';

mysql> flush Privileges;

Delete User

mysql> drop user ' test ' @ ' localhost ';

Five, user rename

shell> rename user ' test3 ' @ '% ' to ' test1 ' @ '% ';

Six, change the password

Update Mysql.user Table

mysql> use MySQL;

# before mysql5.7

mysql> Update user Set Password=password (' 123456 ') where user= ' root ';

# after mysql5.7

mysql> Update user Set Authentication_string=password (' 123456 ') where user= ' root ';

mysql> flush Privileges;

Using the Set password command

mysql> set password for ' root ' @ ' localhost ' =password (' 123456 ');

Mysqladmin

mysql> mysqladmin-uroot-p123456 Password 1234ABCD

Vii. Description of Rights

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.

Permissible privileges for GRANT and REVOKE

Privilege	Column	Context
CREATE	Create_priv	databases, tables, or indexes
DROP	Drop_priv	databases, tables, or views
GRANT OPTION	Grant_priv	databases, tables, or stored routines
LOCK TABLES	Lock_tables_priv	Databases
REFERENCES	References_priv	Databases or tables
EVENT	Event_priv	Databases
Alter	Alter_priv	Tables
DELETE	Delete_priv	Tables
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or Columns
SELECT	Select_priv	Tables or Columns
UPDATE	Update_priv	Tables or Columns
CREATE Temporary TABLES	Create_tmp_table_priv	Tables
TRIGGER	Trigger_priv	Tables
CREATE VIEW	Create_view_priv	Views
SHOW VIEW	Show_view_priv	Views
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE ROUTINE	Create_routine_priv	Stored routines
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
CREATE tablespace	Create_tablespace_priv	Server Administration
CREATE USER	Create_user_priv	Server Administration
PROCESS	Process_priv	Server Administration
PROXY	See Proxies_priv table	Server Administration
RELOAD	Reload_priv	Server Administration
REPLICATION CLIENT	Repl_client_priv	Server Administration
REPLICATION SLAVE	Repl_slave_priv	Server Administration
SHOW DATABASES	Show_db_priv	Server Administration
SHUTDOWN	Shutdown_priv	Server Administration
SUPER	Super_priv	Server Administration
all [privileges]		Server Administration
USAGE		Server Administration

Permissions List

Permissions	Permission levels	Permission Description
CREATE	Database, table, or index	Create database, table, or index permissions
DROP	Database or table	Delete database or table permissions
GRANT OPTION	A database, table, or saved program	Granting permission Options
REFERENCES	Database or table
Alter	Table	Change tables, such as adding fields, indexes, etc.
DELETE	Table	Delete Data permissions
INDEX	Table	Index permissions
INSERT	Table	Insert Permissions
SELECT	Table	Query permissions
UPDATE	Table	Update permissions
CREATE VIEW	View	CREATE VIEW Permissions
SHOW VIEW	View	View View Permissions
ALTER ROUTINE	Stored Procedures	Change stored procedure permissions
CREATE ROUTINE	Stored Procedures	Create Stored Procedure permissions
EXECUTE	Stored Procedures	Execute Stored Procedure permissions
FILE	File access on the server host	File access Permissions
CREATE Temporary TABLES	Server Management	Create temporary table permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Rights
PROCESS	Server Management	View Process Permissions
RELOAD	Server Management	Perform flush-hosts, Flush-logs, Flush-privileges, Flush-status, Flush-tables, flush-threads, refresh, reload, and so on command permissions
REPLICATION CLIENT	Server Management	Copy Permissions
REPLICATION SLAVE	Server Management	Copy Permissions
SHOW DATABASES	Server Management	View Database Permissions
SHUTDOWN	Server Management	Turn off database permissions
SUPER	Server Management	Execute Kill Thread Permissions

Distribution of permissions

Distribution of permissions	Possible settings for permissions
Table Permissions	' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter '
Column permissions	' Select ', ' Insert ', ' Update ', ' References '
Process permissions	' Execute ', ' Alter Routine ', ' Grant '

Viii. MySQL Privilege principle

Permissions control is primarily for security reasons, so you need to follow a few rules of thumb:

1, only to meet the needs of the minimum permissions to prevent users from doing bad things. For example, users just need to query, then only give select permission on it, do not give the user update, insert or delete permissions.

2, when the user is created to restrict the user's login host, is generally limited to the designated IP or intranet IP segment.

3. When initializing the database, delete the user without password. Some users are automatically created when the database is installed, and these users do not have a password by default.

4. Set a password that satisfies the complexity of the password for each user.

5, regularly clean up the unwanted users. Reclaim permissions or delete users.

This article is from the "Big Cloud Technology" blog, please be sure to keep this source http://hdlptz.blog.51cto.com/12553181/1897584

MySQL User and Rights management