Secular wanderer: a programmer who focuses on technical research

Say what I said before

After the installation is complete, there will be a default account: root, which has the highest authority of the database. If we send root account as the development account, it is easy to delete the system library by mistake. Therefore, we had better create a new account and grant the required authority

So let’s see, how do we do that

Operating the MySQL

Current MySQL environment: 8.0.23

Connect to MySQL

To recap, we talked about connecting to MySQL from the command line:

mysql -uroot -p
Copy the code

-u indicates the user name and -p indicates the password, which can be written like this:

mysql -uroot -p123456
Copy the code

This is only good for simple passwords, not if some passwords have special characters

Of course, we can also connect to a remote server and operate via -h:

Mysql - h127.0.0.1 - uroot - pCopy the code

To connect to local MySQL, omit -h

Into the MySQL

Once connected, we can see how many databases there are:

show databases;
Copy the code

MySQL has just been installed and contains these 5 libraries:

• Information_schema: stores all structures and information in the entire database, including table structures and fields

• Mysql: Stores information about users, roles, and so on in the database

• Sys: there is only one table that stores system configuration information

• Performance_schema: Stores MySQL monitoring information, such as SQL execution time, CPU usage, thread count, etc. If we want to make a MySQL monitoring tool, this library is very important

• Test: This library is a test library. We usually operate here or create a new library

If we want to access a library, use is used to access it

use test;
Copy the code

So we’re in the test library

If you don’t want to use test and want to create your own, you can view detailed instructions through Help Create Database

create database test1;
Copy the code

User management

Role: Create role

It is important to note that roles do not exist under MySQL 8.x

CREATE ROLE [IF NOT EXISTS] role [, role]...Copy the code

Creating a role is very simple and can be done with this SQL. You can also create a role and specify its Host mode, such as:

CREATE ROLE 'administrator'.'developer';
CREATE ROLE 'webapp'@'localhost';
Copy the code

So, where should we look at created characters? Enter the mysql library and run show tables to view all tables. In fact, the created roles are saved in the User table, isn’t that strange

select Host,User.plugin from user;
Copy the code

As you can see, we created three new characters

User: create user

Now that we have successfully entered MySQL, let’s start creating users.

Remember, to create a user, you must have specific permissions, otherwise you cannot create a user:

• The permission of CREATE USER
• Mysql > INSERT into user

help create user;
Copy the code

Create user is a very long format for creating a user, so let’s just look at the ones that we use, and you can try the rest

Let’s do this with an actual statement:

Create a user and set a password

CREATE USER 'test1'@The '%' IDENTIFIED BY 'ASDFghjk123! @ # ';
Copy the code

% represents the host name of the connection, indicating that any client can connect normally. If it is set to localhost, only the local client can connect. Currently, it can be set to IP, etc.

You can find that it is normal to enter, only one library, and no permissions

The user creation process is not yet complete

The created user is stored in the mysql/user table.

use mysql
select Host,User.plugin from user;
Copy the code

So, the mysql8.x version creates the default authentication method: caching_sha2_password. Let’s specify the authentication method to create this

If caching_sha2_password is used, some remote clients cannot pass the authentication. You need to specify the previous authentication mode

Create a user and specify authentication

CREATE USER
  'test2'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'ASDFghjk123! @ # ';
Copy the code

Create users and specify roles

As mentioned above, the creation user does not have any operation rights, so we give the user permissions by specifying roles during creation

CREATE USER 'test3'@The '%' IDENTIFIED WITH mysql_native_password BY 'ASDFghjk123! @ # ' DEFAULT ROLE administrator, developer;
Copy the code

When specifying a role, the role must exist, or an error will be reported

Relationship mappings are stored in Default_Roles

In this case, you can think of the RBAC authorization mode. The user only needs to authorize the role, and the current user can own all the resources under the role

Modify: alter user

If we already have an existing user, such as root, and need to modify the user, we can only alter user to modify the user

help alter user;
Copy the code

Also, use help to see how it is currently being used

Viewing the Current User

If we want to view our current logged-in users, we can do so in the following ways

select CURRENT_USER(a);Copy the code

Example Modify the password and authentication of a specified user

ALTER USER 'test2'@'localhost'
  IDENTIFIED WITH mysql_native_password
             BY 'ZXCvbn457$%^';
Copy the code

Remember, if we’re modifying a user that doesn’t have one, we’ll get an error, so here’s the key: ‘test2’@’localhost’

We used this command in the previous section to enable the root user to connect remotely

Example Modify the role of a specified user

Similarly, if you need to change the user’s authorization role, you can do so in this way

ALTER USER 'test3'@The '%' DEFAULT ROLE administrator;
Copy the code

This allows us to query the final authorization by selecting * from default_roles

After the modification is complete, you need to refresh the resources to take effect

FLUSH PRIVILEGES;
Copy the code

Resources authorization

As I’m sure you’ve noticed, we’ve just created roles and users, but we can’t do anything with them, so we’re going to authorize them so that we can use our new user to do anything normal

The GRANT statement uses GRANT, and we can use help to see how it is used:

help grant
Copy the code

In fact, we can see from the help that the official not only gave and specific introduction, but also gave actual cases

We don’t do programs, we’re just porters of programs

Single user Authorization

Let’s start with a simple operation:

grant all on*. *to 'test2'@'localhost';
Copy the code

Refresh resource Flush PRIVILEGES after executing;

If we pass test2, the user can perform normal operations

Here’s what it means:

• All: indicates operations, including SELECT, UPDATE, INSERT, DELETE, and many other permissions

For details about permissions, see: Permissions supported by MySQL

• *. *: indicates the previous oneThe library, the latter one meansThe name of the table.*Represents all, can operate all libraries including system libraries. If you want to manipulate a library or a table under a library, you can restrict it by specifying names

grant all on test.user to 'test2'@'localhost';
Copy the code

If we want to grant permission to a field in the table, each permission granted must follow the column name

grant select(id), insert(login_name, login_pwd) on test.user to 'test2'@'localhost';
Copy the code

*_priv * priv * priv * priv * priv * priv * priv * priv * priv * priv

create user 'test1'@The '%' identified with mysql_native_password by 'ASDfghjkl456^&*';
grant update on test.user to 'test1'@The '%';
flush privileges;
Copy the code

The correspondence is stored here

And the corresponding effect is also here, you can clearly see it

On the user’s authorization way here, you can try it yourself

Role authorization

In addition to users, the authorization method of roles is also very important, so that the same set of authorized resources can be easily authorized by roles

First, let’s look at how to authorize roles to users

GRANT 'role1'.'role2' TO 'test3'@The '%';
Copy the code

Note that both roles and users must exist. Remember the statement used to create roles and users?

create role role1, role2;
create user 'test3'@The '%' identified with mysql_native_password by 'ASDfghjkl456^&*';
Copy the code

Next, we grant roles in the same way we grant users

grant select on test.user to 'role1';
grant delete on test.user to 'role2';
Copy the code

Now authorize the role to the user

GRANT 'role1'.'role2' TO 'test3'@The '%';
Copy the code

One difference from directly authorized users is that:

• In an account session, the role granted to the user account can be active or inactive. If the granted role is active in the session, its permissions apply; Otherwise, they do the opposite

If we check whether the current role is active, SELECT CURRENT_ROLE(); To view the

NONE indicates an inactive state, and we need to activate the role

SET DEFAULT ROLE role1, role2 TO 'test3'@The '%'
SET DEFAULT ROLE ALL TO 'test3'@The '%'
Copy the code

If you want to activate all permissions, do so through all

This is normal, the specific performance is not texture, we try next

The last word

For more information, see the MySQL website: MySQL account Settings