Default page

View File Name : connection_expire.test

## Password expired handling

## Preamble
--source ../include/xplugin_preamble.inc
call mtr.add_suppression("Plugin mysqlx reported: '1.1: Unsuccessful login attempt");
call mtr.add_suppression("Plugin mysqlx reported: '3.1: Unsuccessful login attempt");
call mtr.add_suppression("Plugin mysqlx reported: '4.1: Unsuccessful login attempt");
call mtr.add_suppression("Plugin mysqlx reported: '5.1: Unsuccessful login attempt");
call mtr.add_suppression("Plugin mysqlx reported: '6.1: Unsuccessful login attempt");
call mtr.add_suppression("Plugin mysqlx reported: '7.1: Unsuccessful login attempt");
## Test starts here
SELECT password_last_changed into @passwordchanged from mysql.user where host='localhost' and user='root';
create user foo@localhost;
alter user foo@localhost password expire;
create user xplugin@localhost identified by 'xplugin';
grant all on *.* to xplugin@localhost;
alter user xplugin@localhost password expire;

##
--echo
--echo Test: Expired password with no support for sandbox mode (should fail)
--write_file $MYSQL_TMP_DIR/connection_expire.tmp

-->expecterror ER_ACCESS_DENIED_ERROR
-->login foo
EOF
--exec $MYSQLXTEST -hlocalhost --no-auth --file=$MYSQL_TMP_DIR/connection_expire.tmp 2>&1
--remove_file $MYSQL_TMP_DIR/connection_expire.tmp

###
--echo
--echo Test: Expired password with support for sandbox mode (should succeed)
--write_file $MYSQL_TMP_DIR/connection_expire.tmp

Mysqlx.Connection.CapabilitiesSet {
  capabilities {
    capabilities {
      name: "client.pwd_expire_ok"
      value {
        type: SCALAR
        scalar {
          type: V_BOOL
          v_bool: true
        }
      }
    }
  }
}
# OK
-->recv

-->login foo
EOF
--exec $MYSQLXTEST -hlocalhost --no-auth --file=$MYSQL_TMP_DIR/connection_expire.tmp 2>&1

--write_file $MYSQL_TMP_DIR/connection_expire_root.tmp

Mysqlx.Connection.CapabilitiesSet {
  capabilities {
    capabilities {
      name: "client.pwd_expire_ok"
      value {
        type: SCALAR
        scalar {
          type: V_BOOL
          v_bool: true
        }
      }
    }
  }
}
# OK
-->recv

-->login root
EOF

--write_file $MYSQL_TMP_DIR/connection_expire_root_wrongpwd.tmp
Mysqlx.Connection.CapabilitiesSet {
  capabilities {
    capabilities {
      name: "client.pwd_expire_ok"
      value {
        type: SCALAR
        scalar {
          type: V_BOOL
          v_bool: true
        }
      }
    }
  }
}
-->recv

-->expecterror ER_ACCESS_DENIED_ERROR
-->login root root1
EOF

--write_file $MYSQL_TMP_DIR/connection_expire_user.tmp
-->expecterror %EXPECT_ERROR%
-->sql
select user(),current_user();
-->endsql
EOF


--write_file $MYSQL_TMP_DIR/connection_expire_user_xplugin.tmp
-->stmtsql select user(),current_user();
-->expecterror %EXPECT_ERROR%
-->recvresult

-->stmtsql create database xplugin;
-->expecterror %EXPECT_ERROR%
-->recvresult

-->sql
set password = 'xplugin';
create database xplugin;
drop database xplugin;
select user(),current_user();
alter user xplugin@localhost password expire;
-->endsql
EOF


--write_file $MYSQL_TMP_DIR/connection_expect_expired_user_xplugin.tmp
-->expecterror ER_ACCESS_DENIED_ERROR
-->newsession bla	xplugin	xplugin
EOF


--write_file $MYSQL_TMP_DIR/connection_expire_user_xplugin_collection.tmp
Mysqlx.Sql.StmtExecute {
  stmt: "create database xplugin;"
}
-->recvtype Mysqlx.Error

Mysqlx.Sql.StmtExecute {
  stmt: "create_collection"
  args {
    type: SCALAR
    scalar {
      type: V_STRING
      v_string {
        value: "xplugin"
      }
    }
  }
  args {
    type: SCALAR
    scalar {
      type: V_STRING
      v_string {
        value: "table2"
      }
    }
  }
  namespace: "xplugin"
}

-->expecterror %EXPECT_ERROR%
-->recvresult
EOF

--write_file $MYSQL_TMP_DIR/connection_expire_empty.tmp
-->echo Empty
EOF


--echo Test: try connecting with non-root user when its password expird and root user password expired,expect password expired notice
alter user root@localhost password expire;
--let $expected_error_msg= Your password has expired. To log in you must change it using a client that supports expired passwords. \(code 1045\)
--source ../include/mysqlxtest_expected_error.inc
--exec $MYSQLXTEST -ufoo --file $MYSQL_TMP_DIR/connection_expire_empty.tmp 2>&1

--echo Test: try connecting with Expired password using root user and expect password expire notice
--exec $MYSQLXTEST -hlocalhost --no-auth --file=$MYSQL_TMP_DIR/connection_expire_root.tmp 2>&1

--echo Test: try connecting with non-root user when its password expird and root user password expired,expect password expired notice
--exec $MYSQLXTEST -hlocalhost --no-auth --file=$MYSQL_TMP_DIR/connection_expire.tmp 2>&1

--echo Test: try connecting with root user whose password expired with wrong password and expect invalid user or password error
--exec $MYSQLXTEST -hlocalhost --no-auth --file=$MYSQL_TMP_DIR/connection_expire_root_wrongpwd.tmp 2>&1

##try login with password expired user when server started with disconnect_on_expired_password=off option
set password for root@localhost = '';
let $restart_parameters = restart: --disconnect_on_expired_password=off;
--source ../include/restart_mysqld_and_wait_for_xplugin.inc
--echo Test: expect expired Notice for connection
--exec $MYSQLXTEST -ufoo -v%EXPECT_ERROR%=ER_MUST_CHANGE_PASSWORD --file=$MYSQL_TMP_DIR/connection_expire_user.tmp 2>&1

##try login with password expired user when server started with skip-disconnect_on_expired_password option
set password for root@localhost = '';
let $restart_parameters = restart: --skip-disconnect_on_expired_password;
--source ../include/restart_mysqld_and_wait_for_xplugin.inc
--echo Test: expect expired Notice for connection
--exec $MYSQLXTEST -ufoo -v%EXPECT_ERROR%=ER_MUST_CHANGE_PASSWORD --file=$MYSQL_TMP_DIR/connection_expire_user.tmp 2>&1

###try execute statements with password exipired user once connected,connect should be successful with --skip-disconnect_on_expired_password
--exec $MYSQLXTEST -uxplugin -pxplugin -v%EXPECT_ERROR%=ER_MUST_CHANGE_PASSWORD --file=$MYSQL_TMP_DIR/connection_expire_user_xplugin.tmp 2>&1

###try execute statements with password exipired user once connected,connect should be successful with --disconnect_on_expired_password and statement execution should be failed until password reset
let $restart_parameters = restart: --disconnect_on_expired_password;
--source ../include/restart_mysqld_and_wait_for_xplugin.inc
--exec $MYSQLXTEST --connect-expired-password -uxplugin -pxplugin -v%EXPECT_ERROR%=ER_MUST_CHANGE_PASSWORD --file=$MYSQL_TMP_DIR/connection_expire_user_xplugin.tmp 2>&1

## Connect with expired password without advertising connect on expired (should get kicked out)
--exec $MYSQLXTEST --no-auth --file=$MYSQL_TMP_DIR/connection_expect_expired_user_xplugin.tmp 2>&1

##try executing xplugin collection with password expired user,expect error
--exec $MYSQLXTEST --connect-expired-password -uxplugin -pxplugin -v%EXPECT_ERROR%=ER_MUST_CHANGE_PASSWORD --file=$MYSQL_TMP_DIR/connection_expire_user_xplugin_collection.tmp 2>&1

--remove_file $MYSQL_TMP_DIR/connection_expire_root.tmp
--remove_file $MYSQL_TMP_DIR/connection_expire.tmp
--remove_file $MYSQL_TMP_DIR/connection_expire_user.tmp
--remove_file $MYSQL_TMP_DIR/connection_expire_root_wrongpwd.tmp
--remove_file $MYSQL_TMP_DIR/connection_expire_user_xplugin.tmp
--remove_file $MYSQL_TMP_DIR/connection_expire_user_xplugin_collection.tmp

drop user foo@localhost;
drop user xplugin@localhost;
update mysql.user set password_last_changed = @passwordchanged where host = 'localhost' and user='root';
flush privileges;

## Postamble
uninstall plugin mysqlx;