Shulou(Shulou.com)06/01 Report--

The following is about how to manage the users and permissions of MySQL. The secret of the text is that it is close to the topic. So, no gossip, let's just read the following, I believe you will benefit from reading this article on how to manage the users and permissions of MySQL.

User account of MySQL: user name @ host

Host format:

Hostname www.node1.com

IP: 172.16.139.2

172.16.0.0/255.255.255

192.16%.%

Wildcard: 172.16%.%

%. Node1.com

The sole purpose of the Mysql account is to authenticate the user's login when they connect to the MySQL cloud server, and to carry out some data access, operations and other authorization after logging in.

The MySQL process reads the following six permission tables when it starts

User: user account, global permissions

Db: library-level permission

Tables_priv: table-level permission

Columns_priv: column-level permissions

Procs_priv: permissions related to stored procedures and stored functions

Proxies_priv: proxy user rights

Permission levels: global level libraries, table columns, stored procedures and stored functions

Trigger: active database, other custom actions will be triggered automatically when insert, delete and update are executed. For example, when inserting data into user table, define inserting time value into log table and inserting data into user table.

Create a user called faker with a password of 123

> create user faker@'127.0.0.1' identified by '123'

Mysql > show grants for faker@'127.0.0.1'; View the permission of faker at 127.0.0.1 is usage. You can only log in and simply view it.

+-+

| | Grants for faker@127.0.0.1 |

+-+

| | GRANT USAGE ON *. * TO 'faker'@'127.0.0.1' |

IDENTIFIED BY PASSWORD'* 23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |

Authorize the user:

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

Priv_level:

* all libraries

*. * all tables for all libraries

All tables under the test.* test library

Test1 test1 table

Db_name.toutine_name stored procedures / stored functions for a table

The following information must be given when setting permissions

1, permissions to be granted

2, the database or table to which access is granted

3, user name

Grant and revoke can control access at several levels

1. The entire CVM, using grant ALL and revoke ALL (revolve withdraws permissions)

2, the whole database, using on database.*

3, feature table, using on database.table

4, specific column

5, specific stored procedures

Grant command on library. Table to user

> grant select, insert, update, delete on testdb.* to faker@'%'

Grant ordinary data users, the right to query, insert, update and delete all table data in the database.

> grant select on testdb.* to common_user@'%'

> grant insert on testdb.* to common_user@'%'

> grant update on testdb.* to common_user@'%'

> grant delete on testdb.* to common_user@'%'

Or, replace it with a MySQL command:

> grant select, insert, update, delete on testdb.* to common_user@'%'

Grant database developer, create permissions for tables, indexes, views, stored procedures, functions, and so on.

Grant creates, modifies, and deletes MySQL data table structure permissions.

> grant create on testdb.* to developer@'192.168.0.%'

> grant alter on testdb.* to developer@'192.168.0.%'

> grant drop on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL foreign key permissions.

> grant references on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL temporary table permissions.

> grant create temporary tables on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL index permissions.

> grant index on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL view and view view source code permissions.

> grant create view on testdb.* to developer@'192.168.0.%'

> grant show view on testdb.* to developer@'192.168.0.%'

Last

> flush privileges

After refreshing permissions, you may have to log in again to take effect.

Delete user

> drop user faker@'127.0.0.1'; removes faker users

Change to a household name

> rename user faker@'127.0.0.1' to sky@'127.0.0.1'

Query OK, 0 rows affected (0.00 sec)

Authority revocation

> revoke 'select' on test.my from faker@'127.0.0.1'

Revoke the select rights of faker users in the my table in test library

What if I forget my password when I log in to MySQL?

[root@node1 ~] # vim / etc/init.d/mysqld

1. Find the following line

$bindir/mysqld_safe-datadir= "$datadir"-pid-file= "$mysqld_pid_file_path" $other_args > / dev/null &

two。 Add-- skip-grant-tables to the above line (skip authentication)

3. Restarting the mysqld service

4. Finally, log in again and you can connect without a password.

Is there anything you don't understand about how the users and permissions of the above MySQL manage the relevant content? Or if you want to know more about it, you can continue to follow our industry information section.

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.