mirror of
https://github.com/MariaDB/server.git
synced 2025-10-07 00:09:16 +02:00
eeb00ceffd
Follow-up patch with adjustments of test files and updates of result files for tests. Some of tests were rewritten slighlty. Everywhere where common pattern used: ----- CREATE USER userA; --connect con1 ... userA ... <sql statements...> --disconnect con1 DROP USER userA; ----- the DROP USER statement has been eclosed into the directive --disable_warnings --enable_warnings This change is caused by the race conddition between --disconnect and DROP USER since a number of currently running sessions established on behalf the user being dropped is counted by holding the rw_lock THD_list_iterator::lock that is not acquired on execution the DROP USER statement but the lock is taken as the last step on handling disconnection (when the client is already sending the next statement). Therefore, for the cases where the command --disconnect precedes the DROP USER statement we hide the possible warnings about presence of active sessions for the user being deleted to make tests deterministic.
127 lines
15 KiB
Text
127 lines
15 KiB
Text
select priv into @root_priv from mysql.global_priv where user='root' and host='localhost';
|
|
select * from mysql.user where user = 'root' and host = 'localhost';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 N N 0.000000
|
|
# Test syntax
|
|
#
|
|
# These 2 selects should have no changes from the first one.
|
|
alter user CURRENT_USER;
|
|
select * from mysql.user where user = 'root' and host = 'localhost';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N N 0.000000
|
|
alter user CURRENT_USER();
|
|
select * from mysql.user where user = 'root' and host = 'localhost';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N N 0.000000
|
|
create user foo;
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password N N 0.000000
|
|
alter user foo;
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password N N 0.000000
|
|
#
|
|
# Test READ_ONLY privilege works correctly with a read only database.
|
|
#
|
|
SET @start_read_only = @@global.read_only;
|
|
SET GLOBAL read_only=1;
|
|
grant create user on *.* to foo;
|
|
# Currently no READ_ONLY ADMIN privileges.
|
|
connect a, localhost, foo;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
ON
|
|
alter user foo;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
# Grant READ_ONLY ADMIN privilege to the user.
|
|
connection default;
|
|
grant READ_ONLY ADMIN on *.* to foo;
|
|
# We now have READ_ONLY ADMIN privilege. We should be able to run alter user.
|
|
connect b, localhost, foo;
|
|
alter user foo;
|
|
connection default;
|
|
SET GLOBAL read_only = @start_read_only;
|
|
#
|
|
# Test inexistant user.
|
|
#
|
|
alter user boo;
|
|
ERROR HY000: Operation ALTER USER failed for 'boo'
|
|
#--warning ER_CANNOT_USER
|
|
alter user if exists boo;
|
|
Warnings:
|
|
Error 1133 Can't find any matching row in the user table
|
|
Note 1396 Operation ALTER USER failed for 'boo'
|
|
#
|
|
# Test password related altering.
|
|
#
|
|
alter user foo identified by 'something';
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N 0 0 0 0 mysql_native_password *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N 0.000000
|
|
alter user foo identified by 'something2';
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo *9CD58369E930E28C8996A89DB18B63294E6DC10C N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N 0 0 0 0 mysql_native_password *9CD58369E930E28C8996A89DB18B63294E6DC10C N N 0.000000
|
|
alter user foo identified by password '*88C89BE093D4ECF72D039F62EBB7477EA1FD4D63';
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N 0 0 0 0 mysql_native_password *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N 0.000000
|
|
alter user foo identified by password 'invalid';
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo invalid N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N 0 0 0 0 mysql_native_password invalid N N 0.000000
|
|
alter user foo identified with 'somecoolplugin';
|
|
ERROR HY000: Operation ALTER USER failed for 'foo'@'%'
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1524 Plugin 'somecoolplugin' is not loaded
|
|
Error 1396 Operation ALTER USER failed for 'foo'@'%'
|
|
alter user foo identified with 'mysql_old_password';
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N 0 0 0 0 mysql_old_password N N 0.000000
|
|
alter user foo identified with 'mysql_old_password' using '0123456789ABCDEF';
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo 0123456789ABCDEF N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N 0 0 0 0 mysql_old_password 0123456789ABCDEF N N 0.000000
|
|
#
|
|
# Test ssl related altering.
|
|
#
|
|
alter user foo identified by 'something' require SSL;
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N ANY 0 0 0 0 mysql_native_password *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N 0.000000
|
|
alter user foo identified by 'something' require X509;
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N X509 0 0 0 0 mysql_native_password *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N 0.000000
|
|
alter user foo identified by 'something'
|
|
require cipher 'text' issuer 'foo_issuer' subject 'foo_subject';
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N SPECIFIED text foo_issuer foo_subject 0 0 0 0 mysql_native_password *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N 0.000000
|
|
#
|
|
# Test resource limits altering.
|
|
#
|
|
alter user foo with MAX_QUERIES_PER_HOUR 10
|
|
MAX_UPDATES_PER_HOUR 20
|
|
MAX_CONNECTIONS_PER_HOUR 30
|
|
MAX_USER_CONNECTIONS 40;
|
|
select * from mysql.user where user = 'foo';
|
|
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
|
|
% foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N SPECIFIED text foo_issuer foo_subject 10 20 30 40 mysql_native_password *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N 0.000000
|
|
drop user foo;
|
|
Warnings:
|
|
Note 4226 Dropped users ['foo'@'%'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
#
|
|
# Bug #29882299: ALTER USER ... IDENTIFIED WITH ... BY ... SHOULD BE A PRIVILEGED OPERATION
|
|
#
|
|
create user foo@localhost;
|
|
connect x,localhost,foo;
|
|
alter user current_user identified with 'something';
|
|
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
|
|
connection default;
|
|
disconnect x;
|
|
drop user foo@localhost;
|
|
update mysql.global_priv set priv=@root_priv where user='root' and host='localhost';
|