sql_user.cc File Reference

#include <stdio.h>
#include <string.h>
#include <sys/types.h>
#include <algorithm>
#include <memory>
#include <set>
#include <string>
#include <unordered_map>
#include <utility>
#include <vector>
#include "lex_string.h"
#include "m_ctype.h"
#include "m_string.h"
#include "map_helpers.h"
#include "mutex_lock.h"
#include "my_alloc.h"
#include "my_base.h"
#include "my_cleanse.h"
#include "my_compiler.h"
#include "my_dbug.h"
#include "my_inttypes.h"
#include "my_loglevel.h"
#include "my_sqlcommand.h"
#include "my_sys.h"
#include "my_time.h"
#include "mysql/components/my_service.h"
#include "mysql/components/services/bits/psi_bits.h"
#include "mysql/components/services/log_builtins.h"
#include "mysql/components/services/log_shared.h"
#include "mysql/components/services/validate_password.h"
#include "mysql/mysql_lex_string.h"
#include "mysql/plugin.h"
#include "mysql/plugin_audit.h"
#include "mysql/plugin_auth.h"
#include "mysql/psi/mysql_mutex.h"
#include "mysql_com.h"
#include "mysql_time.h"
#include "mysqld_error.h"
#include "password.h"
#include "scope_guard.h"
#include "sql/auth/auth_acls.h"
#include "sql/auth/auth_common.h"
#include "sql/auth/dynamic_privilege_table.h"
#include "sql/auth/sql_security_ctx.h"
#include "sql/dd/cache/dictionary_client.h"
#include "sql/dd/types/function.h"
#include "sql/dd/types/procedure.h"
#include "sql/dd/types/routine.h"
#include "sql/dd/types/table.h"
#include "sql/dd/types/trigger.h"
#include "sql/dd/types/view.h"
#include "sql/debug_sync.h"
#include "sql/field.h"
#include "sql/handler.h"
#include "sql/item.h"
#include "sql/key.h"
#include "sql/log_event.h"
#include "sql/protocol.h"
#include "sql/sql_audit.h"
#include "sql/sql_base.h"
#include "sql/sql_class.h"
#include "sql/sql_connect.h"
#include "sql/sql_const.h"
#include "sql/sql_error.h"
#include "sql/sql_lex.h"
#include "sql/sql_list.h"
#include "sql/sql_parse.h"
#include "sql/sql_plugin.h"
#include "sql/sql_plugin_ref.h"
#include "sql/strfunc.h"
#include "sql/system_variables.h"
#include "sql/table.h"
#include "sql/thd_raii.h"
#include "sql_string.h"
#include "violite.h"
#include "prealloced_array.h"
#include "sql/auth/auth_internal.h"
#include "sql/auth/sql_auth_cache.h"
#include "sql/auth/sql_authentication.h"
#include "sql/auth/sql_mfa.h"
#include "sql/auth/sql_user_table.h"
#include "sql/current_thd.h"
#include "sql/derror.h"
#include "sql/log.h"
#include "sql/mysqld.h"
#include "sql/sql_rewrite.h"
#include <openssl/rand.h>
#include "sql/query_result.h"
#include "sql/tztime.h"

Namespaces
namespace	anonymous_namespace{sql_user.cc}

Enumerations
enum	enum_acl_lists {   USER_ACL = 0 , DB_ACL , COLUMN_PRIVILEGES_HASH , PROC_PRIVILEGES_HASH ,   FUNC_PRIVILEGES_HASH , PROXY_USERS_ACL }

Functions
void	log_user (THD *thd, String *str, LEX_USER *user, bool comma=true)
	Auxiliary function for constructing a user list string. More...
bool	check_change_password (THD *thd, const char *host, const char *user, bool retain_current_password)
bool	acl_can_access_user (THD *thd, LEX_USER *user_arg)
	Auxiliary function for the CAN_ACCESS_USER internal function used to check if a row from mysql.user can be accessed or not by the current user. More...
bool	mysql_show_create_user (THD *thd, LEX_USER *user_name, bool are_both_users_same)
	Auxiliary function for constructing CREATE USER sql for a given user. More...
static bool	auth_verify_password_history (THD *thd, LEX_CSTRING *user, LEX_CSTRING *host, uint32 password_history, long password_reuse_interval, st_mysql_auth *auth, const char *cleartext, unsigned int cleartext_length, const char *cred_hash, unsigned int cred_hash_length, Table_ref *history_table, ulong what_to_set)
	Perform credentials history check and update the password history table. More...
static bool	handle_password_history_table (THD *thd, Table_ref *tables, bool drop, LEX_USER *user_from, LEX_USER *user_to, bool *row_existed)
	Updates the password history table for cases of deleting or renaming users. More...
static bool	validate_password_require_current (THD *thd, LEX_USER *Str, ACL_USER *acl_user, st_mysql_auth *auth, const char *new_password, unsigned int new_password_length, bool is_privileged_user, bool user_exists)
	Checks, if the REPLACE clause is required, optional or not required. More...
char	translate_byte_to_password_char (unsigned char c)
void	generate_random_password (std::string *password, uint32_t length)
	Generates a random password of the length decided by the system variable generated_random_password_length. More...
bool	send_password_result_set (THD *thd, const Userhostpassword_list &generated_passwords)
	Sends the result set of generated passwords to the client. More...
bool	turn_off_sandbox_mode (THD *thd, LEX_USER *user)
	Helper method to turn off sandbox mode once registration step is complete. More...
static bool	compare_plugin_with_policy (const uint factor, const std::string plugin, std::vector< std::string > &list)
	Helper method to compare plugin name with authentication_policy for the nth factor. More...
static bool	check_for_authentication_policy (THD *thd, LEX_USER *user_name, enum_sql_command command, I_multi_factor_auth *mfa)
	Method to validate CREATE/ALTER USER sql against authentication policy. More...
bool	set_and_validate_user_attributes (THD *thd, LEX_USER *Str, acl_table::Pod_user_what_to_update &what_to_set, bool is_privileged_user, bool is_role, Table_ref *history_table, bool *history_check_done, const char *cmd, Userhostpassword_list &generated_passwords, I_multi_factor_auth **i_mfa, bool if_not_exists)
	This function does following: More...
bool	change_password (THD *thd, LEX_USER *lex_user, const char *new_password, const char *current_password, bool retain_current_password)
	Change a password hash for a user. More...
template<class T , class Matcher >
void	anonymous_namespace{sql_user.cc}::search_for_matching_grant (const T *hash, Matcher &matches)
template<class T , class Matcher >
void	anonymous_namespace{sql_user.cc}::remove_matching_grants (T *hash, Matcher &matches)
template<class T , class Matcher >
bool	anonymous_namespace{sql_user.cc}::rename_matching_grants (T *hash, Matcher &matches, LEX_USER *user_to)
static int	handle_grant_struct (enum enum_acl_lists struct_no, bool drop, LEX_USER *user_from, LEX_USER *user_to, bool on_drop_role_priv)
	Handle an in-memory privilege structure. More...
static int	handle_grant_data (THD *thd, Table_ref *tables, bool drop, LEX_USER *user_from, LEX_USER *user_to, bool on_drop_role_priv)
	Handle all privilege tables and in-memory privilege structures. More...
bool	check_set_user_id_priv (THD *thd, const LEX_USER *user_name, const std::string &object_type)
	This function checks if a user which is referenced as a definer account in objects like view, trigger, event, procedure or function has SET_USER_ID privilege or not and report error or warning based on that. More...
static bool	check_orphaned_definers (THD *thd, List< LEX_USER > &list)
	Check if CREATE/RENAME/DROP USER should be allowed or not by checking if the user is referenced as definer in stored programs like procedures, functions, triggers, events and views or not. More...
bool	mysql_create_user (THD *thd, List< LEX_USER > &list, bool if_not_exists, bool is_role)
bool	mysql_drop_user (THD *thd, List< LEX_USER > &list, bool if_exists, bool on_drop_role_priv)
	Drop a list of users and all their privileges. More...
bool	mysql_rename_user (THD *thd, List< LEX_USER > &list)
bool	mysql_alter_user (THD *thd, List< LEX_USER > &list, bool if_exists)

Variables
bool	initialized

Enumeration Type Documentation

enum_acl_lists

enum enum_acl_lists

Enumerator
USER_ACL
DB_ACL
COLUMN_PRIVILEGES_HASH
PROC_PRIVILEGES_HASH
FUNC_PRIVILEGES_HASH
PROXY_USERS_ACL

Function Documentation

acl_can_access_user()

bool acl_can_access_user	(	THD *	thd,
		LEX_USER *	user_arg
	)

Auxiliary function for the CAN_ACCESS_USER internal function used to check if a row from mysql.user can be accessed or not by the current user.

• thd the current thread
• user_arg the user account to check

  Return values

  true	the current user can access the user
  false	the current user can't access the user

  See also

  Item_func_can_access_user, dd::system_views::User_attributes

auth_verify_password_history()

static bool auth_verify_password_history ( THD *  thd, LEX_CSTRING *  user, LEX_CSTRING *  host, uint32  password_history, long  password_reuse_interval, st_mysql_auth *  auth, const char *  cleartext, unsigned int  cleartext_length, const char *  cred_hash, unsigned int  cred_hash_length, Table_ref *  history_table, ulong  what_to_set  )

static

Perform credentials history check and update the password history table.

Note that the data for the checks are extracted from LEX_USER. So these need to be up to date in all cases.

How credential history checks are performed:

count= 0;
FOR SELECT * FROM mysql.password_history ORDER BY USER,HOST,TS DESC
  WHERE USER=::current_user AND HOST=::current_host
{
   if (count >= ::password_history && (NOW() - ts) > ::password_reuse_time)
   {
     delete row;
     continue;
   }
 
   if (CRED was produced by ::password)
     signal("wrong password");
 
   count = count + 1;
}
 
INSERT INTO mysql.password_history (USER,HOST,TS,CRED)
  VALUES (::current_user, ::current_host, NOW(), ::hashed_password);

Parameters

thd	The current thread
user	The user account user to operate on
host	The user account host to operate on
password_history	The effective password history value
password_reuse_interval	The effective password reuse interval value
auth	Auth plugin to use for verification
cleartext	The clear text password supplied
cleartext_length	length of cleartext password
cred_hash	hash of the credential to be inserted into the history
cred_hash_length	Length of cred_hash
history_table	The opened history table
what_to_set	The mask of what to set

Return values

false	Password is OK
true	Password is not OK

change_password()

bool change_password	(	THD *	thd,
		LEX_USER *	lex_user,
		const char *	new_password,
		const char *	current_password,
		bool	retain_current_password
	)

Change a password hash for a user.

Parameters

thd	Thread handle
lex_user	LEX_USER
new_password	New password hash for host@user
current_password	Current password for host@user
retain_current_password	Preference to retain current password

Note : it will also reset the change_password flag. This is safe to do unconditionally since the simple userless form SET PASSWORD = 'text' will be the only allowed form when this flag is on. So we don't need to check user names here.

See also

set_var_password::update(THD *thd)

Returns

Error code

Return values

0	ok
1	ERROR; In this case the error is sent to the client.

check_change_password()

bool check_change_password	(	THD *	thd,
		const char *	host,
		const char *	user,
		bool	retain_current_password
	)

check_for_authentication_policy()

static bool check_for_authentication_policy ( THD *  thd, LEX_USER *  user_name, enum_sql_command  command, I_multi_factor_auth *  mfa  )

static

Method to validate CREATE/ALTER USER sql against authentication policy.

Authentication policy holds list of atmost 3 auth plugin names where each plugin name should match against nth factor in CREATE/ALTER USER. Value * in policy indicates any plugin is allowed. Value 'empty string' indicates an optional value.

Parameters

thd	connection handle
user_name	user on which authentication policy has to be verified
command	sql command to refer to CREATE or ALTER USER
mfa	handler to mfa attributes for given user_name

Return values

false	success allow CREATE/ALTER user
true	failure CREATE/ALTER user should report error

check_orphaned_definers()

static bool check_orphaned_definers ( THD *  thd, List< LEX_USER > &  list  )

static

Check if CREATE/RENAME/DROP USER should be allowed or not by checking if the user is referenced as definer in stored programs like procedures, functions, triggers, events and views or not.

If the executing user does not have the SET_USER_ID privilege, and the user in the argument list is referenced as the definer of some entity, we will report an error and return.

If the executing user has the SET_USER_ID privilege, and the user in the argument list is referenced as the definer of some entity, we will report a warning and continue execution. In this case we will not return, because there may be additional users in the argument list, and if we ignore them, it may mean that a relevant warning would not be reported.

Parameters

thd	The current thread.
list	The users to check for.

Return values

false	OK
true	Error

check_set_user_id_priv()

bool check_set_user_id_priv	(	THD *	thd,
		const LEX_USER *	user_name,
		const std::string &	object_type
	)

This function checks if a user which is referenced as a definer account in objects like view, trigger, event, procedure or function has SET_USER_ID privilege or not and report error or warning based on that.

Parameters

thd	The current thread
user_name	user name which is referenced as a definer account
object_type	Can be a view, trigger, event, procedure or function

Return values

false	user_name has SET_USER_ID privilege
true	user_name does not have SET_USER_ID privilege

compare_plugin_with_policy()

static bool compare_plugin_with_policy ( const uint  factor, const std::string  plugin, std::vector< std::string > &  list  )

static

Helper method to compare plugin name with authentication_policy for the nth factor.

Parameters

factor	nth factor which needs to be checked.
plugin	plugin name for nth factor.
list	authentication_policy value

Return values

false	if plugin matches the policy at nth_factor
true	if plugin names does not match

generate_random_password()

void generate_random_password	(	std::string *	password,
		uint32_t	length
	)

Generates a random password of the length decided by the system variable generated_random_password_length.

Parameters

[out]	password	The generated password.
	length	The length of the generated password.

handle_grant_data()

static int handle_grant_data ( THD *  thd, Table_ref *  tables, bool  drop, LEX_USER *  user_from, LEX_USER *  user_to, bool  on_drop_role_priv  )

static

Handle all privilege tables and in-memory privilege structures.

Parameters

thd	Thread handle
tables	The array with the seven open tables.
drop	If user_from is to be dropped.
user_from	The the user to be searched/dropped/renamed.
user_to	The new name for the user if to be renamed, NULL otherwise.
on_drop_role_priv	Enabled via the DROP ROLE privilege

Note

Go through all grant tables and in-memory grant structures and apply the requested operation. Delete from grant data if drop is true. Update in grant data if drop is false and user_to is not NULL. Search in grant data if drop is false and user_to is NULL.

Returns

operation result

Return values

>	0 At least one element matched.
0	OK, but no element matched.
<	0 System error (OOM, error from storage engine).

handle_grant_struct()

static int handle_grant_struct ( enum enum_acl_lists  struct_no, bool  drop, LEX_USER *  user_from, LEX_USER *  user_to, bool  on_drop_role_priv  )

static

Handle an in-memory privilege structure.

Parameters

struct_no	The number of the structure to handle (0..5).
drop	If user_from is to be dropped.
user_from	The the user to be searched/dropped/renamed.
user_to	The new name for the user if to be renamed, NULL otherwise.
on_drop_role_priv	true enabled by the DROP ROLE privilege

Note

Scan through all elements in an in-memory grant structure and apply the requested operation. Delete from grant structure if drop is true. Update in grant structure if drop is false and user_to is not NULL. Search in grant structure if drop is false and user_to is NULL. Structures are enumerated as follows: 0 ACL_USER 1 ACL_DB 2 COLUMN_PRIVILIGES_HASH 3 PROC_PRIVILEGES_HASH 4 FUNC_PRIVILEGES_HASH 5 ACL_PROXY_USERS

Return values

>	0 At least one element matched.
0	OK, but no element matched.
-1	Wrong arguments to function or Out of Memory.

handle_password_history_table()

static bool handle_password_history_table ( THD *  thd, Table_ref *  tables, bool  drop, LEX_USER *  user_from, LEX_USER *  user_to, bool *  row_existed  )

static

Updates the password history table for cases of deleting or renaming users.

This function, unlike the other "update" functions does not handle the addition of new data. That's done by auth_verify_password_history(). The function only handles renames and deletes of user accounts. It does not go via the normal non-mysql.user handle_grant_data() route since there is a (partial) key on user/host and hence no need to do a full table scan.

Parameters

	thd	The execution context
	tables	The list of opened ACL tables
	drop	True if it's a drop operation
	user_from	The user to rename from or the user to drop
	user_to	The user to rename to or the user to add
[out]	row_existed	Set to true if row matching user_from existed

Return values

true	operation failed
false	success

log_user()

void log_user	(	THD *	thd,
		String *	str,
		LEX_USER *	user,
		bool	comma = true
	)

Auxiliary function for constructing a user list string.

This function is used for error reporting and logging.

Parameters

thd	Thread context
str	A String to store the user list.
user	A LEX_USER which will be appended into user list.
comma	If true, append a ',' before the the user.

mysql_alter_user()

bool mysql_alter_user	(	THD *	thd,
		List< LEX_USER > &	list,
		bool	if_exists
	)

mysql_create_user()

bool mysql_create_user	(	THD *	thd,
		List< LEX_USER > &	list,
		bool	if_not_exists,
		bool	is_role
	)

mysql_drop_user()

bool mysql_drop_user	(	THD *	thd,
		List< LEX_USER > &	list,
		bool	if_exists,
		bool	on_drop_role_priv
	)

Drop a list of users and all their privileges.

Parameters

thd	The current thread.
list	The users to drop.
if_exists	The if exists flag
on_drop_role_priv	enabled by the DROP ROLE privilege

Return values

false	OK
true	Error

mysql_rename_user()

bool mysql_rename_user	(	THD *	thd,
		List< LEX_USER > &	list
	)

mysql_show_create_user()

bool mysql_show_create_user	(	THD *	thd,
		LEX_USER *	user_name,
		bool	are_both_users_same
	)

Auxiliary function for constructing CREATE USER sql for a given user.

Parameters

thd	Thread context
user_name	user for which the sql should be constructed.
are_both_users_same	If the command is issued for self or not.

Return values

0	OK. 1 Error.

send_password_result_set()

bool send_password_result_set	(	THD *	thd,
		const Userhostpassword_list &	generated_passwords
	)

Sends the result set of generated passwords to the client.

Parameters

thd	The thread handler
generated_passwords	A list of 3-tuple strings containing user, host and plaintext password.

Returns

success state

Return values

true	An error occurred (DA is set)
false	Success (my_eof)

set_and_validate_user_attributes()

bool set_and_validate_user_attributes	(	THD *	thd,
		LEX_USER *	Str,
		acl_table::Pod_user_what_to_update &	what_to_set,
		bool	is_privileged_user,
		bool	is_role,
		Table_ref *	history_table,
		bool *	history_check_done,
		const char *	cmd,
		Userhostpassword_list &	generated_passwords,
		I_multi_factor_auth **	i_mfa,
		bool	if_not_exists
	)

This function does following:

1. Convert plain text password to hash and update the same in user definition.
2. Validate hash string if specified in user definition.
3. Identify what all fields needs to be updated in mysql.user table based on user definition.

If the is_role flag is set, the password validation is not used.

The function perform some semantic parsing of the original statement by investigating the syntactic elements found in the LEX_USER object not-so-appropriately named Str.

The code fits the purpose as a helper function to mysql_create_user() but it is used from mysql_alter_user(), mysql_grant(), change_password() and mysql_routine_grant() with a slightly varying semantic meaning.

Parameters

	thd	Thread context
	Str	user on which attributes has to be applied
	what_to_set	User attributes
	is_privileged_user	Whether caller has CREATE_USER_ACL or UPDATE_ACL over mysql.*
	is_role	CREATE ROLE was used to create the authid.
	history_table	The table to verify history against.
[out]	history_check_done	Set to on if the history table is updated
	cmd	Command information
[out]	generated_passwords	A list of generated random passwords. Depends on LEX_USER.
[out]	i_mfa	Interface to Multi factor authentication methods.
	if_not_exists	True if this is a CREATE ... IF NOT EXISTS type of statement. Valid for CREATE USER/ROLE.

Return values

0	ok
1	ERROR;

translate_byte_to_password_char()

char translate_byte_to_password_char

(

unsigned char

c

)

turn_off_sandbox_mode()

bool turn_off_sandbox_mode	(	THD *	thd,
		LEX_USER *	user
	)

Helper method to turn off sandbox mode once registration step is complete.

Parameters

thd	connection handle
user	user account for which registration is completed

Return values

false	registration successful
true	error

validate_password_require_current()

static bool validate_password_require_current ( THD *  thd, LEX_USER *  Str, ACL_USER *  acl_user, st_mysql_auth *  auth, const char *  new_password, unsigned int  new_password_length, bool  is_privileged_user, bool  user_exists  )

static

Checks, if the REPLACE clause is required, optional or not required.

It throws error: If REPLACE clause is required but not specified. If REPLACE clause is not required but specified. If current password specified in the REPLACE clause does not match with authentication string of the user.

The plaintext current password is erased from LEX_USER, iff its length > 0 .

Parameters

thd	The execution context
Str	LEX user
acl_user	The associated user which carries the ACL
auth	Auth plugin to use for verification
new_password	New password buffer
new_password_length	Length of new password
is_privileged_user	Whether caller has CREATE_USER_ACL or UPDATE_ACL over mysql.*
user_exists	Whether user already exists

Return values

true	operation failed
false	success

Variable Documentation

initialized

bool initialized

extern