How to quickly disable account login in MySQL & how to copy/reuse account password

How to quickly and temporarily ban an account from logging in
Role ROLES management needs to be activated first
A few other additions about authorization
How to copy/reuse account password

1. Quickly and temporarily ban a user from logging in

There are several methods:

Modify its password ALTER USER x IDENTIFIED BY 'new_passwd', or change it to a random password ALTER USER x IDENTIFIED BY RANDOM PASSWORD;
Lock their account ALTER USER x ACCOUNT LOCK;

2. Role ROLES management

After granting a role/ROLES to an account, remember to activate it again:

# Create ROLE r1 and grant to user u1
mysql> CREATE ROLE r1;
mysql> GRANT SELECT ON sbtest.* TO r1;
mysql> GRANT r1 to u1;

# activation
mysql> SET DEFAULT ROLE r1 TO u1;

copy

There are several other interesting things about ROLES:

Like USERS, they are stored in the mysql.user table.
The newly created ROLE has no password by default & the password expires & is in LOCK state.
You can set a password for ROLE, and after UNLOCK it (execute the ALTER USER command), you can also log in normally like USER.
After the ROLE is granted to an account, the authorization cannot take effect immediately, and a new connection needs to be established (if the account GRANT is directly authorized, it will take effect immediately without reconnection).

3. Some other supplements about authorization

Permissions such as CREATE\DROP\ALTER can be granted individually to a table.
The authorization to create temporary tables (CREATE TEMPORARY TABLES) can only be directed to a certain DB, and specific data table names cannot be specified.
Unable to revoke USAGE permission. That is to say, if you want to disable an account, you can either DROP it, or refer to the previous method, modify its password or LOCK it, and you cannot disable it by reclaiming the USAGE permission.
After using GRANT authorization, it can take effect immediately. In other words, if you find insufficient permissions in a transaction, immediately ask the administrator to authorize it, and retry the transaction directly (without reconnecting) to succeed.
MySQL authorization support is specific to a certain column, but care must also be taken to control it.

Let's take an example:

# Grant the UPDATE permission to the account u1 to the column c1 of the test.t1 table
mysql> GRANT UPDATE(c1) ON test.t1 to u1;

# Switch to u1 account and log in
$ mysql -hxx -uu1 -pxx test
# These few SQL can be successful
mysql> UPDATE test.t1 SET c1='c1' LIMIT 1;
mysql> UPDATE test.t1 SET c1=CONCAT('c', rand());

# These SQL s failed because of insufficient permissions
mysql> UPDATE test.t1 SET c1=CONCAT ('c-new' , c1) ;
ERROR 1143 (42000): SELECT command denied to user 'u1'@'127.0.0.1' for column 'c' in table 't1'

mysql> UPDATE test.t1 SET c1='c1' WHERE id = 1;
ERROR 1143 (42000): SELECT command denied to user 'u1'@'127.0.0.1' for column 'id' in table 't1'

copy

In the above example, because the account u1 only has the UPDATE permission for the test.t1(c1) column, it cannot see other columns, even if it reads the c1 column. In a real production environment, you can add authorization to the primary key column or other search columns, which is convenient for updating after adding search conditions, for example:

mysql> SHOW GRANTS FOR u1;
| GRANT USAGE ON *.* TO `u1`@`%`                                      |
| GRANT SELECT (`id`, `c1`), UPDATE (`c1`) ON `test`.`t1` TO `u1`@`%` |

copy

4. How to copy/reuse account password

When using the mysql_native_password method to create a user, you can directly copy it from the password string of other accounts as the password of the new account, for example:

mysql> create user u4 identified with mysql_native_password by 'u4';

mysql> select host,user,plugin,authentication_string from mysql.user where user='u4';
+------+------+-----------------------+-------------------------------------------+
| host | user | plugin                | authentication_string                     |
+------+------+-----------------------+-------------------------------------------+
| %    | u4   | mysql_native_password | *06196708822D12C033A8BF492D3902405DF3C781 |
+------+------+-----------------------+-------------------------------------------+

mysql> create user u5 identified with mysql_native_password as '*06196708822D12C033A8BF492D3902405DF3C781';

mysql> select host,user,plugin,authentication_string from mysql.user where user in ('u4', 'u5');
+------+------+-----------------------+-------------------------------------------+
| host | user | plugin                | authentication_string                     |
+------+------+-----------------------+-------------------------------------------+
| %    | u4   | mysql_native_password | *06196708822D12C033A8BF492D3902405DF3C781 |
| %    | u5   | mysql_native_password | *06196708822D12C033A8BF492D3902405DF3C781 |
+------+------+-----------------------+-------------------------------------------+

copy

But when using caching_sha2_password to create a user, this cannot be done, otherwise an error similar to the following will be prompted:

mysql> select host,user,plugin,authentication_string from mysql.user where user='u1'\G
*************************** 1. row ***************************
                 host: %
                 user: u1
               plugin: caching_sha2_password
authentication_string: $A$005$OWA-ad3A,DOzIxrKgUCklxlU/Ty1OHKeGN7LG0QekszR9A6MicWq2

mysql> create user u3 identified with caching_sha2_password as '$A$005$OWA-ad3A,DOzIxrKgUCklxlU/Ty1OHKeGN7LG0QekszR9A6MicWq2';
ERROR 1827 (HY000): The password hash doesn't have the expected format.

copy

After consulting the manual, I found that the password string can be specified in hexadecimal, but the premise is that you need to set print_identified_with_as_hex=1 first, for example:

mysql> set print_identified_with_as_hex=1;

# Execute SHOW CREATE USER to view the existing account password string
mysql> show create user u4\G
show create user u4\G
*************************** 1. row ***************************
CREATE USER for u1@%: CREATE USER `u1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035244F574114162D6114176433411E1C1A2C44194F1B777A4978724B6755436B6C786C552F5479314F484B65474E374C473051656B737A523941364D6963577132 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT PASSWORD_LOCK_TIME 1

# Copy the hexadecimal password string and create a new account
mysql> create user u6 identified with caching_sha2_password as 0x244124303035244F574114162D6114176433411E1C1A2C44194F1B777A4978724B6755436B6C786C552F5479314F484B65474E374C473051656B737A523941364D6963577132;

copy

The relevant introduction in the MySQL manual is as follows:

Password hash values displayed in the IDENTIFIED WITH clause of output from SHOW CREATE USER may contain unprintable characters that have adverse effects on terminal displays and in other environments. Enabling the print_identified_with_as_hex system variable (available as of MySQL 8.0.17) causes SHOW CREATE USER to display such hash values as hexadecimal strings rather than as regular string literals. Hash values that do not contain unprintable characters still display as regular string literals, even with this variable enabled.

copy

Enjoy MySQL :)

"MGR in simple terms" video course

Click this applet to go directly to Station B

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0

Tags: Database SQL

Posted by Nexus10 on Fri, 02 Dec 2022 22:17:44 +1030

Programs Wiki

How to quickly disable account login in MySQL & how to copy/reuse account password

1. Quickly and temporarily ban a user from logging in

2. Role ROLES management

3. Some other supplements about authorization

4. How to copy/reuse account password

Popular Tags