mirror of
https://github.com/MariaDB/server.git
synced 2025-08-17 07:51:36 +02:00
e3d9369774
let's always disconnect a user connection before dropping the said user. MariaDB is traditionally very tolerant to active connections of the dropped user, which isn't the case for most other databases. Let's avoid unintentionally spreading incompatible behavior and disconnect before drop. Except in cases when the test specifically tests such a behavior.
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
|
|
disconnect a;
|
|
# 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;
|
|
disconnect b;
|
|
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;
|
|
#
|
|
# 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';
|