MySQL User management and Rights management

MySQL User management and Rights management

--Operating environment
Mysql> Show variables like ' version ';
+---------------+--------+
| variable_name | Value |
+---------------+--------+
| Version | 5.6.25 |
+---------------+--------+
1 row in Set (0.04 sec)

Background knowledge Supplement:
Meaning of the value of the host column in the 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

One: User management

1. View the user
Mysql> Select Host,user,password from Mysql.user where the user like ' Andy ';

2. Create a database user
--Specify the password to create a new user,% means any, that is, andy02 can access the database from any host
mysql> create user ' andy02 ' @ '% ' identified by ' Oracle ';
Query OK, 0 rows affected (0.03 sec)

3. User Password change
Mysql> Set password for Andy02=password (' MySQL ');
mysql> flush Privileges;

4. Delete a user
--view users that already exist on the current system
Mysql> select User,host,password from Mysql.user;
--Use the Drop user command to delete users
mysql> drop user ' andy02 ' @ '% '; Note: If you do not specify @ '% ', the default default is '% ' is deleted.
Mysql> Select User,host,password from Mysql.user where the user like ' andy% ';
Empty Set (0.00 sec)

5. Renaming accounts
--rename user Command
mysql> rename user ' andy02 ' @ '% ' to ' andy01 ' @ '% ';
Query OK, 0 rows Affected (0.00 sec)
--Check
Mysql> Select User,host,password from Mysql.user where the user like ' andy% ';

II: Rights Management

Note: MySQL has relatively simple permissions relative to Oracle, and does not involve role-related definitions and configuration

--grant command syntax

1. Types of permissions
(see here directly, Root account all permissions)

Mysql> SELECT * from Mysql.user where user= ' root ' and host= '% ' \g;
1. Row ***************************
Host:%
User:root
Password: *2447d497b9a6a15f2776055cb2d1e9f86758182f
Select_priv:y
Insert_priv:y
Update_priv:y
Delete_priv:y
Create_priv:y
Drop_priv:y
Reload_priv:y
Shutdown_priv:y
Process_priv:y
File_priv:y
Grant_priv:y
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
Create_routine_priv:y
Alter_routine_priv:y
Create_user_priv:y
Event_priv:y
Trigger_priv:y
Create_tablespace_priv:y
Ssl_type:
Ssl_cipher:
X509_issuer:
X509_subject:
max_questions:0
max_updates:0
max_connections:0
max_user_connections:0
Plugin:mysql_native_password
Authentication_string:
Password_expired:n
1 row in Set (0.00 sec)

2. Permission to store

The MySQL server controls user access to the database via the MySQL permissions table, which is stored in the MySQL database by the mysql_install_db script
Start. These MySQL permission tables are User,db,table_priv,columns_priv and host, respectively.

User Permission table: records the users account information that is allowed to connect to the server, and the permissions are global-level.
DB Permission table: Records the operation permissions of each account on each database.
Table_priv Permissions table: Records operation permissions at the data table level.
Columns_priv Permissions table: Records the operation permissions at the data column level.
Host permission table: The database-level operation permissions on a given host are controlled more carefully with the DB permission table. This permission table is not affected by the GRANT and REVOKE statements.

3. Scope of authority Priv_level:
*
| *.*
| Db_name.*
| Db_name.tbl_name
| Tbl_name
| Db_name.routine_name

4. Authorization
Mysql>grant all privileges on * * to ' andy01 ' @ '% '; (--with GRANT OPTION is passed, default is not passed)
Mysql>flush privileges;

5. View the permissions of the current user
Mysql> Show grants;

6. View permissions for a specified user

Mysql> Show grants for ' andy01 ' @ '% ';

--Revoke REVOKE permissions

Revocation and authorization are basically similar, not discussed here.

MySQL User management and Rights management