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