MySQL account settings

usedockerinstallMySQLAnd quick start, now we’re indockerContainers.

➜  ~ docker exec -it mysql8 /bin/bash
[email protected]:/#

Mysql database connection

MySQL command syntax

The user name is the user you log in to, and the host name or IP address is optional. If it is a local connection, it does not need to be set. If it is a remote connection server, it needs to be filled in. The password is the password of the corresponding user.

MySQL – u user name [– h host name or IP address, - P port number] - P password

-u: the user name of the login.
-h: remote host name or IP address. If it is not filled in, the local address will be default.
-P：MySQLPort number, 3306 by default.
-p: the login password corresponding to the login user.

[email protected]:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL account view

becauserootPermissions are very high, so different accounts and permissions will be assigned to general projects for programmers to operate.

View existing accounts

mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.03 sec)

Why are there tworootInformation? Let’s look at it in detail.

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

herehostField represents that anyLogin MySQL with IP address。 at presentrootAccounts allow remote and local login.

View current account

mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)

If we use an external computer connection

mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| [email protected]% |
+----------------+
1 row in set (0.00 sec)

Indicates the current loginrootAccounts allow remote and local login.

MySQL account creation

MySQL command syntax

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

user: account name, syntax is'user_name'@'host_name'Where the host address can be written as%Means to accept connections from any address.
auth_option: authentication mode, you can specify password and authentication plug-in（mysql_native_password、sha256_password、caching_sha2_password）。
tls_option: encrypted connection option.
resource_option: user resource constraints, such as the maximum number of connections per hour.
password_optionPassword: additional control, such as setting expiration time.
lock_option: account lock option, locked or unlocked by the administrator（ACCOUNT LOCK | ACCOUNT UNLOCK）。

The simplest is to specify the account name + password

CREATE USER 'tian'@'localhost' IDENTIFIED BY 'password';

Add the authentication plug-in

CREATE USER 'tian'@'localhost' IDENTIFIED WITH sha256_password BY 'password';

Specifies that the password is expired so that users need to change it the first time they use it

CREATE USER 'tian'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

You can also specify that the new password be changed at intervals

CREATE USER 'tian'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 180 DAY;

You can specify an encrypted connection

--Do not use encrypted connections
CREATE USER 'tian'@'localhost' REQUIRE NONE；
--Use encrypted connection
CREATE USER 'tian'@'localhost' REQUIRE SSL;
--Use encrypted connection，并要求客户端提供有效证书
CREATE USER 'tian'@'localhost' REQUIRE X509;

A valid X.509 certificate issued by create user 'Tian' @'localhost 'request issuer' Ca ';

Create user 'Tian' @'localhost 'require subject' contains a valid X.509 certificate for the subject ';

Create user 'Tian' @'localhost 'request cipher' specified encryption method ';

Resource control can be specified

--Within the unit hour, the account is allowed to query 500 times and update 100 times, and the maximum number of connections within the unit hour is unlimited. The maximum number of concurrent connections is unlimited
CREATE USER 'tian'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0；

You can lock your account

--Lock in
CREATE USER 'tian'@'localhost' ACCOUNT LOCK
--Unlocking
ALTER USER 'tian'@'localhost' ACCOUNT UNLOCK

Finally, the complete command options look like this

CREATE USER 'user_name'@'host_name' IDENTIFIED [WITH auth_plugin] BY 'auth_string' [REQUIRE NONE(SSL,X509)] [WITH MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count] [PASSWORD EXPIRE] [ACCOUNT LOCK]

If you want to delete an account

DROP USER 'tian'@'localhost';

If you want to change the name

RENAME USER 'tian'@'localhost' TO 'tina'@'127.0.0.1';

MySQL role creation

Role management has been added to MySQL 8. Here’s how to use it
A role can be understood as a set of permissions, and then the role is assigned to an account, which has the corresponding permissions of the role. Each account can have multiple roles, just like in the game, you can have many titles.

--Name specification
'role_name'@'host_name'
--In general, only the user name part is used to specify the role name, and the host name part '%' is used implicitly. The host name part has no meaning
'admin'

Creating roles

--Omit the host name, default to% '
CREATE ROLE 'admin', 'dev';
--This is OK, but it doesn't make sense
CREATE ROLE 'app'@'localhost';

Remove role

DROP ROLE 'admin', 'dev';

MySQL account update

MySQL command syntax

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

ALTER USER [IF EXISTS] USER() user_func_auth_option

ALTER USER [IF EXISTS]
    user DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | DISCARD OLD PASSWORD
}

user_func_auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | DISCARD OLD PASSWORD
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

Parameter options refer to create account.

Change your current password

ALTER USER USER() IDENTIFIED BY 'new_password';

Change account password

ALTER USER 'tian'@'localhost' IDENTIFIED BY 'new_password';

Modify authentication plug in

ALTER USER 'tian'@'localhost' IDENTIFIED WITH mysql_native_password;

Change password and plug-in

ALTER USER 'tian'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

Modify roles

--Grant custom roles
ALTER USER 'tian'@'localhost' DEFAULT ROLE your_role_name;
--No role
ALTER USER 'tian'@'localhost' DEFAULT ROLE NONE;
--All roles
ALTER USER 'tian'@'localhost' DEFAULT ROLE ALL;

Modify encryption method

--Only the account password is correct, no need to encrypt the connection
ALTER USER 'tian'@'localhost' REQUIRE NONE;
--Encrypted connection required
ALTER USER 'tian'@'localhost' REQUIRE SSL;
...

Modify resource access

--Maximum number of queries and updates per hour
ALTER USER 'tian'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

The specified password has expired

ALTER USER 'tian'@'localhost' PASSWORD EXPIRE;

Modify lock unlock

ALTER USER 'tian'@'localhost' ACCOUNT LOCK;
ALTER USER 'tian'@'localhost' ACCOUNT UNLOCK;

MySQL account authorization

MySQL command syntax

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user
  | role
}

user:
    (see Section 6.2.4, “Specifying Account Names”)

role:
    (see Section 6.2.5, “Specifying Role Names”)

GRANTSyntax enables administrators to grant accountsPermissions or roles, butGRANTNoIn another statement, both permissions and roles are granted.

Yes, it is to grant permission
No on, it’s role granting

--Grant all permissions of database db1 to the specified account
GRANT ALL ON db1.* TO 'tian'@'localhost';
--Grant roles to specified accounts
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
--Grant the select permission of database world to the specified role
GRANT SELECT ON world.* TO 'role3';

Basic grammar

Grant [permission] on [database name]. [table name] to 'user]_ name'@'localhost' ...;
--Grant permissions to all databases
Grant [permission] on *. * to 'user_ name'@'localhost' ...;

Note: Global permissions are used to manage or apply to all databases on a given server. To assign global permissions, use theON *.*grammar

Here is a list of permissions

mysql> show privileges;
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege                  | Context                               | Comment                                               |
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                      | Tables                                | To alter the table                                    |
| Alter routine              | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                     | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine             | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create role                | Server Admin                          | To create new roles                                   |
| Create temporary tables    | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view                | Tables                                | To create new views                                   |
| Create user                | Server Admin                          | To create new users                                   |
| Delete                     | Tables                                | To delete existing rows                               |
| Drop                       | Databases,Tables                      | To drop databases, tables, and views                  |
| Drop role                  | Server Admin                          | To drop roles                                         |
| Event                      | Server Admin                          | To create, alter, drop and execute events             |
| Execute                    | Functions,Procedures                  | To execute stored routines                            |
| File                       | File access on server                 | To read and write files on the server                 |
| Grant option               | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                      | Tables                                | To create or drop indexes                             |
| Insert                     | Tables                                | To insert data into tables                            |
| Lock tables                | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                    | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                      | Server Admin                          | To make proxy user possible                           |
| References                 | Databases,Tables                      | To have references on tables                          |
| Reload                     | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client         | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave          | Server Admin                          | To read binary log events from the master             |
| Select                     | Tables                                | To retrieve rows from table                           |
| Show databases             | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view                  | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                   | Server Admin                          | To shut down the server                               |
| Super                      | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                    | Tables                                | To use triggers                                       |
| Create tablespace          | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                     | Tables                                | To update existing rows                               |
| Usage                      | Server Admin                          | No privileges - allow connect only                    |
| XA_RECOVER_ADMIN           | Server Admin                          |                                                       |
| SHOW_ROUTINE               | Server Admin                          |                                                       |
| RESOURCE_GROUP_USER        | Server Admin                          |                                                       |
| SET_USER_ID                | Server Admin                          |                                                       |
| SESSION_VARIABLES_ADMIN    | Server Admin                          |                                                       |
| CLONE_ADMIN                | Server Admin                          |                                                       |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin                          |                                                       |
| ROLE_ADMIN                 | Server Admin                          |                                                       |
| BACKUP_ADMIN               | Server Admin                          |                                                       |
| CONNECTION_ADMIN           | Server Admin                          |                                                       |
| RESOURCE_GROUP_ADMIN       | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ARCHIVE    | Server Admin                          |                                                       |
| BINLOG_ENCRYPTION_ADMIN    | Server Admin                          |                                                       |
| REPLICATION_SLAVE_ADMIN    | Server Admin                          |                                                       |
| SYSTEM_VARIABLES_ADMIN     | Server Admin                          |                                                       |
| GROUP_REPLICATION_ADMIN    | Server Admin                          |                                                       |
| SYSTEM_USER                | Server Admin                          |                                                       |
| APPLICATION_PASSWORD_ADMIN | Server Admin                          |                                                       |
| TABLE_ENCRYPTION_ADMIN     | Server Admin                          |                                                       |
| SERVICE_CONNECTION_ADMIN   | Server Admin                          |                                                       |
| AUDIT_ADMIN                | Server Admin                          |                                                       |
| BINLOG_ADMIN               | Server Admin                          |                                                       |
| ENCRYPTION_KEY_ADMIN       | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ENABLE     | Server Admin                          |                                                       |
| REPLICATION_APPLIER        | Server Admin                          |                                                       |
+----------------------------+---------------------------------------+-------------------------------------------------------+
58 rows in set (0.00 sec)

Example of permission scope

--Database permissions
GRANT ALL ON mydb.* TO 'user_name'@'host_name';
--Table permissions
GRANT ALL ON mydb.mytable TO 'user_name'@'host_name';
--Column permissions
GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytable TO 'user_name'@'host_name';

--Stored procedure permissions
GRANT CREATE ROUTINE ON mydb.* TO 'user_name'@'host_name';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'user_name'@'host_name';

It can be used after authorizationflushOrder it to take effect immediately

FLUSH PRIVILEGES

Flush syntax

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
    flush_option [, flush_option] ...
  | tables_option
}

flush_option: {
    BINARY LOGS
  | ENGINE LOGS
  | ERROR LOGS
  | GENERAL LOGS
  | HOSTS
  | LOGS
  | PRIVILEGES
  | OPTIMIZER_COSTS
  | RELAY LOGS [FOR CHANNEL channel]
  | SLOW LOGS
  | STATUS
  | USER_RESOURCES
}

tables_option: {
    TABLES
  | TABLES tbl_name [, tbl_name] ...
  | TABLES WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... FOR EXPORT
}

FLUSH PRIVILEGESInclude the following operations

ReloadmysqlIn the system databasegrantTable and clear itcaching_sha2_passwordThe memory cache used by the authentication plug-in.
The server reads theglobal_grantsTable and register any unregistered privileges in it.
Server throughGrant, create user, create server and install pluginStatement to cache information into memory. CorrespondingRevoke, drop user, drop server and unitallPlug in statements do not release this memory, so memory usage will increase for servers that execute many instances of statements that cause caching. You can use the refresh privilege to free this cache memory.

FLUSH TABLESInclude the following operations

Close all open tables, force all tables in use to close, and refresh the prepared statement cache.

Revoke syntax

Since it can be authorized, it can be revoked

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user_or_role [, user_or_role] ...

REVOKE PROXY ON user_or_role
    FROM user_or_role [, user_or_role] ...

REVOKE role [, role ] ...
    FROM user_or_role [, user_or_role ] ...

user_or_role: {
    user
  | role
}

user:
    (see Section 6.2.4, “Specifying Account Names”)

role:
    (see Section 6.2.5, “Specifying Role Names”.

REVOKECan realize the revocation of permissions or roles (premise: have grant permissions and revoke permissions)

--Revoke the insert permission of the user
REVOKE INSERT ON *.* FROM 'tian'@'localhost';
--Revoke the user's assigned role
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
--Revoke insert permission for role
REVOKE SELECT ON world.* FROM 'role3';

Revoke all permissions (only permissions can be revoked, not roles)

--Remove all permissions from the account or role
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_or_role [, user_or_role] ...
--Cancellation of account
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'tian'@'localhost'
--Remove role
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'role3'

Revoke permissions globally（.）

--Revoke all permissions globally
REVOKE ALL ON *.* FROM 'tian'@'localhost';

MySQL account settings