MySQL Account management

MySQL Account management

These days have been the muddle, toss MYSQLINNODB data engine hot backup, toss for a long time, the results did not toss out, the heart has a lot of frustration. Then in the master replication to slave configuration on the learning, learning the process of stepping on a lot of pits, and finally successfully completed the master-slave configuration. After learning the master-slave, continue to toss the hot backup things, their own computer also do not make up, the virtual machine often drop line. Maybe this is not a good time to learn hot backup, and then turn to the MySQL account management, this piece of content, in the previous work and study life often used, also asked the DBA uncle to help me configure permissions. Remember the DBA uncle, in fact, the age is not very big, a look is a good temper. The work area placed three monitors, the monitor is all constantly changing management information, suddenly feel very strong also very admire. At that time, I was proud to be at work, using stored procedures to fetch data, and then using a program to invoke it, using long SQL commands to encapsulate complex business requirements. At that time, the platform's traffic was too small, and the inefficient SQL would not be discovered. No one cares about the efficiency of code execution, and no one is concerned about the efficiency of SQL execution. Just get on the line and run OK and we'll be relieved. At that time I was a junior programmer, thought that all the complex logic, can be solved through if else, did not encounter large concurrency, did not encounter big data. After entering the current company, began to deliberately learn the content, but also gradually understand the solution of big data and high concurrency. Since I haven't written a blog in nearly a week, the topic is a bit far away, so let's look at how MySQL manages the account.

First, MySQL account system

Depending on the permissions the account has, the MySQL account can be divided into the following categories:

Service Instance level account

If a user, such as root, has permission to assign the service instance level, the account can delete all the databases, along with the tables in those libraries.

Database level Account

The account can perform all actions (if these permissions are assigned) in a database that has permissions to delete and remove changes.

Data Table level Account

The account can perform all actions, such as adding and removing changes on a table with permissions (if these permissions are assigned).

Field-level permissions

The account can operate on fields that have permissions in some tables (depending on the assigned permissions).

Store Program-level accounts

The account can be added to the storage program to check the operation (depending on the assigned permissions)

Second, configure permissions

The grant command syntax format

Grant Permission name [Field List] on [database resource type] Database resource to MySQL account 1,[mysql account 2] [with GRANT OPTION]

*.*Represents all databases, all data tables

‘account_4‘@‘%‘Indicates that the Account_4 account can be logged on to any host.

Create a service instance level account

grant all privileges on *.* to ‘account_4‘@‘%‘ identified by ‘123456‘ with grant option;将创建一个名字为account_4的账号，拥有所有的数据库权限，并且具有grant 权限，可以创建其他拥有服务实例权限的其他用户。

Create a DB instance account

grant all privileges on liangzi.* to ‘account_5‘@‘%‘ identified by ‘123456‘ with grant option;将创建一个名字为account_5的账号。拥有liangzi 数据库的所有权限，可以随该库中的表进行所有操作。

Create a data table level account

grant all privileges on table liangzi.test_1 to ‘account_6‘@‘%‘ identified by ‘123456‘;将创建一个名字为account_6 的用户，对langzi数据库中test_1拥有所有的权限。

Because field-level permissions are not used frequently, you do not have to enumerate them. Field settings Mandarin Redundancy is complex, each time MySQL in the execution of SQL back to the same complex permission to judge, resulting in inefficient performance degradation, colleagues will also have a security risk. So simple, manageable, and highly available permissions are desirable.

Permission to delete and change the search

1. Increase permissions

比如原本的权限为：grant select,insert on liangzi.* to ‘account_5‘@‘%‘ identified by ‘123456‘ with grant option;想要增加update, delete,alter 权限可以如下操作：grant update,delete,alter on liangzi.* to ‘account_5‘@‘%‘ identified by ‘123456‘ with grant option;然后使用show privileges for account_5;mysql> show grants for account_5;+-------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                          |+-------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘account_5‘@‘%‘                                                           || GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `liangzi`.* TO ‘account_5‘@‘%‘ WITH GRANT OPTION |+-------------------------------------------------------------------------------------------------+

2. Remove Permissions

比如原本的权限为：grant select,insert on liangzi.* to ‘account_6‘@‘%‘ identified by ‘123456‘ with grant option;想要移除insert 权限：mysql> revoke insert on liangzi.* from  ‘account_6‘@‘%‘;mysql> show grants for account_6;+------------------------------------------------------------------+| Grants for [email protected]%                                           |+------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘account_6‘@‘%‘                            || GRANT SELECT ON `liangzi`.* TO ‘account_6‘@‘%‘ WITH GRANT OPTION |+------------------------------------------------------------------+

Third, user management

User management is nothing more than adding users, deleting users, and so on. Remember when I installed MySQL first use, the use of the default password, after entering the system, no matter what the operation requires me to set the password. But how to set it, the hint message is not written. Then I knew it. You can use:

set password=password(‘123456‘);

In fact, this command can not only set the current user's password, but also set other user's password, the full format of this command is:

set password [for USER] = passowrd(‘新密码‘);

For example, a colleague in the company left, I need to delete his MySQL permissions, you can use the following command:

drop user USER_NAME；drop user roverliang;

Additional users can use:

create user ‘roverliang‘@‘%‘ identified by ‘123456‘;添加的格式为：create user ‘USER_NAME‘@‘HOST‘ identified by ‘PASSOWRD‘;其实这个命令相当复杂，由这个命令创建出来的用户不具有任何权限，仅仅能够登录系统。因此创建用户的时候，可以使用grant 命令，加用户加权限一步到位。

MySQL Account management