Shulou(Shulou.com)05/31 Report--

This article mainly shows you "MySQL how to achieve user management and rights management", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MySQL how to achieve user management and rights management" this article.

-- operating environment

Mysql > show variables like 'version'

+-+ +

| | Variable_name | Value |

+-+ +

| | version | 5.6.25 | |

+-+ +

1 row in set (0.04 sec)

Background knowledge supplement:

The meaning of the value of host column in user Table

% match all hosts

Localhost localhost will not be resolved to an IP address, but will be connected directly through UNIXsocket

127.0.0.1 will connect through the TCP/IP protocol and can only be accessed locally

:: 1:: 1 is compatible with ipv6, representing 127.0.0.1 of the same as ipv4

One: user management

1. View users

Mysql > select host,user,password from mysql.user where user like 'andy'

two。 Create a database user

-- specify a password to create a new user.% means arbitrary, 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 changes password

Mysql > set password for andy02=password ('mysql')

Mysql > flush privileges

4. Delete user

-- View the users that already exist in the current system

Mysql > select user,host,Password from mysql.user

-use the drop user command to delete a user

Mysql > drop user 'andy02'@'%'; Note: if @'%'is not specified, the default is'%'.

Mysql > select user,host,Password from mysql.user where user like 'andy%'

Empty set (0.00 sec)

5. Rename account

-- 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 user like 'andy%'

Second: rights management

Note: the permissions of mysql are relatively simple compared with oracle, and it does not involve the definition and configuration of roles.

-- grant command syntax

1. Types of permissions

(check directly here, all permissions of the root account)

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)

two。 Authority storage

The MySQL server controls users' access to the database through the MySQL permission table. The MySQL permission table is stored in the mysql database, which is started by the mysql_install_db script.

Initialize. These MySQL permission tables are user,db,table_priv,columns_priv and host, respectively.

User permission table: records the user account information that is allowed to connect to the server, in which permissions are at the global level.

Db permission table: record the operation permissions of each account on each database.

Table_priv permission table: record the operation permissions at the data table level.

Columns_priv permission table: record the operation permissions at the data column level.

Host permission table: cooperate with db permission table to control database-level operation permissions on a given host in more detail. This permission table is not affected by GRANT and REVOKE statements.

3. Scope of permission priv_level:

*

| *. *

| | db_name.* |

| | db_name.tbl_name |

| | tbl_name |

| | db_name.routine_name |

4. Authorization

Mysql > grant all privileges on *. * to 'andy01'@'%'; (--whether 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 the permissions of the specified user

Mysql > show grants for 'andy01'@'%'

-- revoke withdraws permissions

The above is all the contents of the article "how to achieve user Management and Rights Management in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.