MySQL User and Rights management

Source: Internet
Author: User
Tags dba

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.