WL#6054: Temporarily disablement of users

Affects: Server-5.7   —   Status: Complete

Add a feature that extend user account property that allows temporal enablement and disablement of login permission into server.

The feature will extend SQL syntax:

  • Creation of the user (CREATE USER)
  • Modification of the user (ALTER USER)

In both cases the user account login permission can be enabled or disabled.

The modifications are to be made on a top of WL#6409, which introduces significant changes to the CREATE USER and ALTER USER syntax.

Functional Requirements

F-1 There must be introduced a new property of the user account that keeps login enabled or disabled state (permission) in the non-volatile area.

F-2 SQL syntax must be extended that allows specification of the login permission during creation of the user (CREATE USER) and modification of the user (ALTER USER).

SHOW CREATE USER syntax should be extended to support these modifications.

F-3 Server must deny access after successful authentication to user accounts which lack the LOGIN permission.

F-4 Server must NOT deny access to authenticated user sessions if the session switch identity using either definer privileges or proxy and the new identity lack the LOGIN permission.

Proxy example: If a client authenticate to user account A and then assumes the identity of account B through a proxy, access must not be denied to account B because it lacks LOGIN permission.

Definer example: Stored procedures or views which are executed using definer credentials must not be denied access because the definer lacks the LOGIN permission.

F-5 A disabled user account will accept credentials and verify password before the LOGIN permission is checked.

F-6 A disabled user account will deny access using a distinguishable error message.

Example: "Access denied for user 'user'@'localhost'. Login not granted."

F-7 A new status variable should should be introduced that counts the number of times login access has been denied because of missing LOGIN permission.

F-8 Account access permission information for a user should NOT be available through INFORMATION_SCHEMA.user_privileges. The LOGIN permission is NOT a privilege.

F-9 Login permission cannot be disabled for anonymous user.

Non-functional Requirements

NF-1 Specification of the login permission in SQL should be optional.

  • Lack of specification of the login permission during creation assumes that user has login permission enabled.
  • Lack of specification of the login permission during modification does not modify login permission state.

NF-2 If the server metadata does not define login permission state, enabled login should be assumed. This guarantees backward compatibility of the server behaviour.

NF-3 The implementation of the ALTER USER ACCOUNT must fit into existing design of the already supported ALTER USER PASSWORD command. The design includes SQL grammar extension, command handling and processing.

NF-4 Keep as minimal changes as possible.

NF-5 COM_CHANGE USER should behave just as an ordinary authentication and check the LOGIN permission.

Contents

MySQL Database Schema

I-1 A new column (account_enabled) is introduced in the mysql.user table (F-1). The column is of type ENUM('N','Y') with default value set to 'Y'.

SQL Syntax

I-2 ACCOUNT [ENABLE|DISABLE] extension is to be added to the CREATE USER and ALTER USER syntax (F-2). The extension is optional and does need to be specified during execution (NF-1).

I-2-1 CREATE USER 

CREATE USER user_specification [, user_specification] ...
       [REQUIRE <opt_constraints_attr>]
       [WITH <opt_connect_attr>]
       [ACCOUNT ENABLE|DISABLE]
user_specification:
     user [ IDENTIFIED <opt_identified_attr> ]

opt_identified_attr:
      BY [PASSWORD] 'auth_string'
    | WITH auth_plugin
    | WITH auth_plugin BY 'auth_string'
    | WITH auth_plugin AS 'auth_string'
opt_constraints_attr:
     SSL
   | X509
   | CIPHER 'cipher'
   | ISSUER 'issuer'
   | SUBJECT 'subject'
opt_connect_attr:
      MAX_QUERIES_PER_HOUR count
    | MAX_UPDATES_PER_HOUR count
    | MAX_CONNECTIONS_PER_HOUR count
    | MAX_USER_CONNECTIONS count

Examples:

  • Create the user in the default enabled login permission state:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass';
  • Create the user in the explicit enabled login permission state:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass' ACCOUNT ENABLE;
  • Create the user in the explicit disabled login permission state:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass' ACCOUNT DISABLE;
  • Create multiple users in the explicit disabled login permission state:
CREATE USER 'testuser1'@'localhost' IDENTIFIED BY 'pass', 'testuser2'@'localhost' IDENTIFIED BY 'pass' ACCOUNT DISABLE;

I-2-2 SHOW CREATE USER syntax is extended to support ACCOUNT ENABLE|DISABLE used in CREATE USER query. Because the user account is created in the enabled (ACCOUNT ENABLE) state by default, ACCOUNT ENABLE never appears in CREATE USER acquired by using the SHOW CREATE USER query.

Examples:

  • Create the user in the default enabled login permission state:
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass';
mysql> SHOW CREATE USER 'testuser'@'localhost';
CREATE USER 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*B0368119E07B7A5F21334460715B4A99BF29A8B4' REQUIRE NONE
  • Create the user in the explicit enabled login permission state:
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass' ACCOUNT ENABLE;
mysql> SHOW CREATE USER 'testuser'@'localhost';
CREATE USER 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*B0368119E07B7A5F21334460715B4A99BF29A8B4' REQUIRE NONE
  • Create the user in the disabled login permission state:
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass' ACCOUNT DISABLE;
mysql> SHOW CREATE USER 'testuser'@'localhost';
CREATE USER 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*B0368119E07B7A5F21334460715B4A99BF29A8B4' REQUIRE NONE ACCOUNT DISABLE

I-2-3 ALTER USER 

ALTER USER user_specification [, user_specification] ...
       [REQUIRE <opt_constraints_attr>]
       [WITH <opt_connect_attr>]
       [<opt_password_attr>]
       [ACCOUNT ENABLE|DISABLE]
user_specification:
     user [ IDENTIFIED <opt_identified_attr> ]

opt_identified_attr:
      BY 'auth_string'
    | WITH auth_plugin
    | WITH auth_plugin BY 'auth_string'
    | WITH auth_plugin AS 'hash_string'
opt_constraints_attr:
     SSL
   | X509
   | CIPHER 'cipher'
   | ISSUER 'issuer'
   | SUBJECT 'subject'
opt_connect_attr:
      MAX_QUERIES_PER_HOUR count
    | MAX_UPDATES_PER_HOUR count
    | MAX_CONNECTIONS_PER_HOUR count
    | MAX_USER_CONNECTIONS count 
opt_password_attr:
      PASSWORD EXPIRE
    | PASSWORD EXPIRE DEFAULT
    | PASSWORD EXPIRE NEVER
    | PASSWORD EXPIRE INTERVAL N DAY
<pre>

Examples:
* Enable the user's login permission state:
<pre>ALTER USER 'testuser'@'localhost' ACCOUNT ENABLE;
  • Disable the user's login permission state:
ALTER USER 'testuser'@'localhost' ACCOUNT DISABLE;
  • Disable multiple user's login permission state:
ALTER USER 'testuser1'@'localhost', 'testuser2'@'localhost' ACCOUNT DISABLE;

I-2-4 In both cases <user> must be specified as <exact user name>@<exact host name>. Using masks are not supported.

Error messages

I-3 A message appears during the authentication process, if the user account has lack of login permission (F-6):

"Access denied for user 'user'@'localhost'. Login not granted."

The message has assigned 1974 ID number (ER_ACCOUNT_HAS_BEEN_DISABLED).

I-4 Existing message (1901;ER_ALTER_USER_ANONYMOUS_USER) of ALTER USER query has been renamed from:

"The password for anonymous user cannot be expired."

to:

"This operation cannot be performed for anonymous user."

Status variable

I-5 Disabled_connects status variable that indicates number of connection attempts to the users, whose accounts has been disabled (F-7).

mysql> show status like 'disable%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Disabled_connects | 3     |
+-------------------+-------+
1 row in set (0.00 sec)

Contents

SQL Grammar

TC-1 SQL grammar has been extended by performing modifications in the sql/sql_yacc.yy file (F-2).

CREATE USER clear_privileges grant_list require_clause connect_options
            account_enable_options
...

ALTER USER clear_privileges alter_user_list require_clause
           connect_options password_expire_options account_enable_options
...

account_enable_options:
  /* empty */ {}
| ACCOUNT ENABLE_SYM
  {
    LEX *lex=Lex;
    lex->alter_password.update_account_enabled_column= true;
    lex->alter_password.account_enabled= true;
  }
| ACCOUNT DISABLE_SYM
  {
    LEX *lex=Lex;
    lex->alter_password.update_account_enabled_column= true;
    lex->alter_password.account_enabled= false;
  }
;

User clause must be specified as <exact_user_name>[@<exact_host_name>]. The find_acl_user function from sql_auth_cache.cc is responsible for user matching. Masks are not allowed at this point.

The mysql.user Table

TC-2 The value indicating whether the user (the account) is enabled or disabled must be stored in the non-volatile area. For this purpose, the account_enabled column been added to the mysql.user table (F-1).

account_enabled ENUM('N', 'Y') DEFAULT 'Y' NOT NULL

The column has been appended to the end of the mysql.user table.

Authentication

TC-3 The check against the login privilege is performed in the acl_authenticate function. The privilege check is performed after the authentication process (F-5).

Granting Login Permission Right

TC-4 Enablement or disablement of the login permission can be granted only by the user that is enable to modify the mysql.user table. This check has been inherited from the existing implementation (sql/sql_parse.cc):

case SQLCOM_ALTER_USER:
  if (check_access(thd, UPDATE_ACL, "mysql", NULL, NULL, 1, 1) &&
      check_global_access(thd, CREATE_USER_ACL))
    break;

Status variable

TC-5 Number of failed connection attempts is stored in the Disabled_connects status variable (F-7). The implementation was based on the simmilar Aborted_connects variable.

The Authentication_handler (sql/authentication_handler.h) interface was added to bridge Connection_handler_manager with the acl_authenticate function, where the connection is rejected in the case of disabled user. This allows to keep the code as elegant and dependent free as possible.

Limitations

TC.6 The login privilege cannot be disabled for anonymous users (F-9).



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