WL#9591: Caching sha2 authentication plugin
Affects: Server-8.0
—
Status: Complete
In 5.7, sha256_password_authentication introduced a more secure way to store password data, but the authentication process is slower than it needs to be. This worklog will address latency issues as well as issues with trust-on-first-use.
FRs/NFRs
- FR1: A new authentication plugin shall be introduced which uses caching mechanism to perform faster authentication.
- FR1.1: A new server side authentication plugin : caching_sha2_password shall be introduced
- FR1.2: A new client side authentication plugin : caching_sha2_password shall be introduced
- FR2: New plugin shall store hashed password in mysql.user.authentication_string column
- FR2.1: It should use technique similar to sha256_password plugin
- FR3: New plugin should maintain in-memory cache to store hashed password for faster authentication
- FR4: Plugin shall support authentication through two methods:
- FR4.1: Fast authentication which utilizes multiple rounds of SHA256 hashing along with a session level salt to generate a scramble
- FR4.2: Expensive authentication which involves passing plaintext password to server
- FR5: A new audit class - MYSQL_AUDIT_AUTHENTICATION_CLASS shall be introduced
- FR5.1: Following subclass events should be introduced for MYSQL_AUDIT_AUTHENTICATION_CLASS:
- MYSQL_AUDIT_AUTHENTICATION_FLUSH
- MYSQL_AUDIT_AUTHENTICATION_AUTHID_CREATE
- MYSQL_AUDIT_AUTHENTICATION_CREDENTIAL_CHANGE
- MYSQL_AUDIT_AUTHENTICATION_AUTHID_RENAME
- MYSQL_AUDIT_AUTHENTICATION_AUTHID_DROP
- FR5.1: Following subclass events should be introduced for MYSQL_AUDIT_AUTHENTICATION_CLASS:
- FR6: Server shall notify audit plugins for events mentioned in FR5.1 in following manner:
- MYSQL_AUDIT_AUTHENTICATION_FLUSH : In case of FLUSH PRIVILEGES
- MYSQL_AUDIT_AUTHENTICATION_AUTHID_CREATE : In case of User/Role creation (CREATE [IF NOT EXISTS] USER / CREATE [IF NOT EXISTS] ROLE)
- MYSQL_AUDIT_AUTHENTICATION_CREDENTIAL_CHANGE : In case of credential change through ALTER USER [IF EXISTS] / SET PASSWORD / GRANT
- MYSQL_AUDIT_AUTHENTICATION_AUTHID_RENAME : In case of RENAME USER
- MYSQL_AUDIT_AUTHENTICATION_AUTHID_DROP : In case of DROP USER [IF EXISTS]
- FR7: A new audit plugin - sha2_cache_cleaner shall be introduced which should be responsible for clearing cache created by caching_sha2_password
- FR 7.1: caching_sha2_password's cache shall be cleared in following cases:
- FLUSH PRIVILEGES
- FR 7.2: Cache entry for a user who uses caching_sha2_password shall be removed if any of the following statement involves that particular user:
- Change in credentials through ALTER USER [IF EXISTS]
- SET PASSWORD
- RENAME USER
- Change in credentials through GRANT
- Removal of user account through DROP USER [IF EXISTS]
- FR 7.1: caching_sha2_password's cache shall be cleared in following cases:
- FR8: A new boolean option: MYSQL_OPT_GET_SERVER_PUBLIC_KEY shall be introduced for libmysql - To indicate client's preference receive RSA public key from server as part of authentication process for caching_sha2_password.
- FR8.1: Default value of the option should be false - i.e. client should not ask public key from server by default
- FR9: In case of FR4.2, connection between server and client must have one of the following properties:
- Connection type should be socket,shared memory or TLS.
- Server and client should support password exchange using RSA keypair.
- NFR1: As part of upgrade, existing user accounts must not be modified to use caching_sha2_authentication plugin
Contents |
Server
- S-I1: New authentication plugin : caching_sha2_password will be introduced. It will be a built-in plugin (similar to mysql_native_password and sha256_password)
{
MYSQL_AUTHENTICATION_PLUGIN, /* type constant */
&caching_sha2_auth_handler, /* type descriptor */
caching_sha2_password_plugin_name.str, /* Name */
"Oracle", /* Author */
"Caching sha2 authentication", /* Description */
PLUGIN_LICENSE_GPL, /* License */
caching_sha2_authentication_init, /* Init function */
caching_sha2_authentication_deinit, /* Deinit function */
0x0100, /* Version (1.0) */
NULL, /* status variables */
#if !defined(HAVE_YASSL)
caching_sha2_password_sysvars, /* system variables */
#else
NULL, /* system variables */
#endif // HAVE_YASSL
NULL, /* config options */
0, /* flags */
}
- S-I2: caching_sha2_password will support two types of authentication
- A. Based on in-memory hash lookup (std::map to store `user`@`host` => SHA256(SHA256(PASSWORD) mapping)
- B. Based on my_crypt_genhash()
- S-I3: caching_sha2_password will accept RSA keypair through options.
- S-I3.1: New option: caching_sha2_password_private_key_path : Global, Read Only option - Defaults to data directory similar to sha256_password_private_key_path
- S-I3.2: New option: caching_sha2_password_public_key_path : Global, Read Only option - Defaults to data directory similar to sha256_password_public_key_path
- S-I4: caching_sha2_password will store hash in this format: $A$005$<SALT><HASH>
- A => SHA256 digest
- 005 => 005*1000 Rounds
- SALT => 20 CHAR long random number - Generated using generate_user_salt()
- HASH => 43 CHAR long digest - Generated using my_crypt_genhash()
- Idea is to make it easy to add new plugin based on different hash/rounds mechanism
- S-I5: Function generate_sha256_scramble will be exposed to create SHA2 based scramble on client side.
- S-I6: caching_sha2_password will maintain an in-memory hash of `user`@`host` => SHA256(SHA256(PASSWORD))
- Addition to the hash : Upon successful authentication
- Removal from the hash : Upon user deletion/renaming/credential change
- Clearing the hash : FLUSH PRIVILEGES or server shutdown
{
MYSQL_AUDIT_PLUGIN, /* plugin type */
&sha2_cache_cleaner, /* type specific descriptor */
"sha2_cache_cleaner", /* plugin name */
"Oracle Inc", /* author */
"Cache cleaner for Caching sha2 authentication", /* description */
PLUGIN_LICENSE_GPL, /* license */
caching_sha2_cache_cleaner_init, /* plugin initializer */
caching_sha2_cache_cleaner_deinit, /* plugin deinitializer */
0x0100, /* version (1.0) */
NULL, /* status variables */
NULL, /* system variables */
NULL, /* reserverd */
0 /* flags */
}
- S-I7: Scramble validation
- Server expects a scramble from client of the format: XOR(SHA256(PASSWORD), SHA256(SHA256(SHA256(PASSWORD)), NONCE))
- Fetch SHA2(SHA2(STORED_PASSWORD) from the in-memory hash
- Using NONCE, generate : SHA256(SHA256(SHA256(STORED_PASSWORD)), NONCE).
- If STORED_PASSWORD == PASSWORD, then XOR between XOR(SHA256(PASSWORD), SHA256(SHA256(SHA256(PASSWORD)), NONCE)) and SHA256(SHA256(SHA256(STORED_PASSWORD)), NONCE) should result into SHA256(PASSWORD)
- If SHA256(SHA256(PASSWORD) == SHA256(SHA256(STORED_PASSWORD), then supplied password and stored password are the same.
- S-I8: caching_sha2_password and sha2_cache_cleaner are declared as builtin plugins. sql_builtin.cc will be updated to include both these plugins.
- S-I9: In case of FLUSH PRIVILEGES/DROP USER/ALTER USER/Grant change, SHA2 cache will be updated accordingly by calling appropriate audit API.
Client
- C-I1: A new SHA256 based scramble generation will be introduced to be used by client. It will use nonce supplied by server and create a scramble with this format: XOR(SHA256(PASSWORD), SHA256(SHA256(SHA256(PASSWORD)), NONCE))
- C-I2: A new client option : MYSQL_OPT_GET_SERVER_PUBLIC_KEY will be introduced. It will enable client to request public key from server.
- C-I2.1: mysql_options() and mysql_get_option() will be updated to set/get new option
- C-I2.2: Value of new option will be stored in new bool variable - get_server_public_key in st_mysql_options_extention
- C-I3: A new client option : --get_server_public_key will be introduced. Type of the option is boolean. By default this option will be set to OFF. If set to ON, it will allow client to request public key from server.
- Following client programs will allow setting --get_server_public_key:
- mysql
- mysqltest
- mysqladmin
- mysqlbinlog
- mysqlcheck
- mysqldump
- mysqlimport
- mysqlpump
- mysqlshow
- mysqlslap
- mysql_update
- Following client programs will allow setting --get_server_public_key:
- C-I4: New authentication plugin : caching_sha2_password will be introduced. It will be a built-in plugin (similar to mysql_native_password and sha256_password). Plugin will:
- Attempt to perform authentication by sending scramble as suggested in C-I1
- If the attempt is unsuccessful, it will send password over TLS connection or using RSA public key
static auth_plugin_t caching_sha2_password_client_plugin=
{
MYSQL_CLIENT_AUTHENTICATION_PLUGIN,
MYSQL_CLIENT_AUTHENTICATION_PLUGIN_INTERFACE_VERSION,
"caching_sha2_password",
"Oracle Inc",
"SHA2 based authentication with salt",
{1, 0, 0},
"GPL",
NULL,
caching_sha2_password_init,
caching_sha2_password_deinit,
NULL,
caching_sha2_password_auth_client
};
Audit APIs
- A-I1: New audit class - MYSQL_AUDIT_AUTHENTICATION_CLASS will be introduced to notify audit plugin about authentication events.
typedef enum
{
MYSQL_AUDIT_GENERAL_CLASS = 0,
MYSQL_AUDIT_CONNECTION_CLASS = 1,
MYSQL_AUDIT_PARSE_CLASS = 2,
MYSQL_AUDIT_AUTHORIZATION_CLASS = 3,
MYSQL_AUDIT_TABLE_ACCESS_CLASS = 4,
MYSQL_AUDIT_GLOBAL_VARIABLE_CLASS = 5,
MYSQL_AUDIT_SERVER_STARTUP_CLASS = 6,
MYSQL_AUDIT_SERVER_SHUTDOWN_CLASS = 7,
MYSQL_AUDIT_COMMAND_CLASS = 8,
MYSQL_AUDIT_QUERY_CLASS = 9,
MYSQL_AUDIT_STORED_PROGRAM_CLASS = 10,
MYSQL_AUDIT_AUTHENTICATION_CLASS = 11,
/* This item must be last in the list. */
MYSQL_AUDIT_CLASS_MASK_SIZE
} mysql_event_class_t;
- A-I2: MYSQL_AUDIT_AUTHENTICATION_CLASS will have following subclasses
/**
@enum mysql_event_authentication_subclass_t
Events for MYSQL_AUDIT_AUTHENTICATION_CLASS event class.
*/
typedef enum
{
MYSQL_AUDIT_AUTHENTICATION_FLUSH = 1 << 0,
MYSQL_AUDIT_AUTHENTICATION_AUTHID_CREATE = 1 << 1,
MYSQL_AUDIT_AUTHENTICATION_CREDENTIAL_CHANGE = 1 << 2,
MYSQL_AUDIT_AUTHENTICATION_AUTHID_RENAME = 1 << 3,
MYSQL_AUDIT_AUTHENTICATION_AUTHID_DROP = 1 << 4
} mysql_event_authentication_subclass_t;
#define MYSQL_AUDIT_AUTHENTICATION_ALL (MYSQL_AUDIT_AUTHENTICATION_FLUSH | \
MYSQL_AUDIT_AUTHENTICATION_AUTHID_CREATE | \
MYSQL_AUDIT_AUTHENTICATION_CREDENTIAL_CHANGE | \
MYSQL_AUDIT_AUTHENTICATION_AUTHID_RENAME | \
MYSQL_AUDIT_AUTHENTICATION_AUTHID_DROP)
- A-I3: Information passed by server to plugin are following:
/**
@enum enum_authorization_id_type
Enum for AuthorizationID in mysql_event_authentication.
*/
typedef enum
{
MYSQL_AUTHID_USER = 1,
MYSQL_AUTHID_ROLE
} enum_authorization_id_type;
/**
@struct mysql_event_authentication
Structure for MYSQL_AUDIT_AUTHENTICATION_CLASS event class.
*/
struct mysql_event_authentication
{
/** Event subclass. */
mysql_event_authentication_subclass_t event_subclass;
/** Event status */
int status;
/** Connection id. */
unsigned int connection_id;
/** SQL command id. */
enum_sql_command_t sql_command_id;
/** SQL query text. */
MYSQL_LEX_CSTRING query;
/** SQL query charset. */
const struct charset_info_st *query_charset;
/** User name */
MYSQL_LEX_CSTRING user;
/** Host name */
MYSQL_LEX_CSTRING host;
/** Authentication plugin */
MYSQL_LEX_CSTRING authentication_plugin;
/** AuthorizationID type */
enum_authorization_id_type authid_type;
/** New user name */
MYSQL_LEX_CSTRING new_user;
/** New host name */
MYSQL_LEX_CSTRING new_host;
};
- A-I4: New APIs to report Authentication events:
/**
Call audit plugins of AUTHENTICATION audit class
@param[in] thd Current thread data.
@param[in] subclass Type of the authentication audit event.
@param[in] subclass_name Name of the subclass.
@param[in] status Status of the event.
@param[in] user Name of the user.
@param[in] host Name of the host.
@param[in] is_role Whether given AuthID is a role or not
@param[in] new_user Name of the new user - In case of rename
@param[in] new_host Name of the new host - In case of rename
@return 0 continue server flow, otherwise abort.
*/
int mysql_audit_notify(THD *thd,
mysql_event_authentication_subclass_t subclass,
const char *subclass_name,
int status,
const char * user,
const char * host,
const char * authentication_plugin,
bool is_role,
const char * new_user,
const char * new_host);
Server-Client Communication
- COMM-I1: New message type - request_server_public_key
- Message flow : Client to Server
- Represented by '\2'
- Indicates a request to get public key from server
- COMM-I2: New message type - fast_auth_success
- Message flow : Server to Client
- Represented by '\3'
- Indicates that Authentication is success and an OK packet will follow
- COMM-I3: New message type - perform_full_authentication
- Message flow : Server to Client
- Represented by '\4'
- Indicates that client should send actual password as authentication based on scramble is inconclusive due to
- Missing hash entry
- Mismatched hash entry
- COMM-I4: Attached file caching_sha2.pdf provides information about message exchange between server and client side plugins for caching_sha2_password.
Testing Considerations
- T-I1: gunit tests will be added for:
- Digest generation
- Scramble generation
- Scramble validation
- Cache management
- Fast authentication
- Full authentication
- T-I2: Following changes will be made:
- Private/Public key options will be added to mysql-test-run.pl
- --get-server-public-key will be added for mysqltest and mysql config for mtr.
X Plugin
- X-I1: X Plugin implements SHA1 based authentication which is similar to mysql native password. This is the default authentication mechanism supported on plaintext connection. It also implements SHA2 password plugin but it is restricted to encrypted connection. Similarly a plugin to authenticate caching_sha2_password will be added to xplugin.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.