Edit File: access_credential_control.result
======================================================================= Checking the password plugin assignment with create user command ======================================================================= set @@global.secure_auth=0; **** Creating user with mysql_native_password plugin select @@session.old_passwords; @@session.old_passwords 0 0 Expected CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the plugin names select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password' 1 1 Expected Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; ======================================================================= Checking the password plugin assignment with grant user command ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; GRANT usage on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; GRANT usage on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; GRANT usage on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the plugin names select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password' 1 1 Expected Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; ======================================================================= Checking the functionality and integrity of the DROP user command ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); Connecting a session each with the above 3 users dropping the above 3 users with each having a open session Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; Now trying opening a new session for the above dropped user ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES) Accessing the open sessions for the above dropped user select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost ======================================================================= Checking the privellages post renaming the user ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the plugin names select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password' 1 1 Expected select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost **** Validating the integrity of the rename user command RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost'; RENAME USER 'Tanjotuser2'@'localhost' to 'user2'@'localhost'; RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost'; **** Validating the plugin names select (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='user2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='user2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password' 1 1 Expected **** Validating the user connections post renaming select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES) select user(), current_user(); user() current_user() user1@localhost user1@localhost select user(), current_user(); user() current_user() user2@localhost user2@localhost select user(), current_user(); user() current_user() user3@localhost user3@localhost **** Disconnecting the open sessions and dropping the created users Drop user 'user1'@'localhost'; Drop user 'user2'@'localhost'; Drop user 'user3'@'localhost'; ======================================================================= Checking the privillages with grant user command ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; GRANT select on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; GRANT select on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; GRANT select on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the granted privilleges select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; plugin mysql_native_password mysql_native_password Expected select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; plugin mysql_old_password mysql_old_password Expected select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; plugin sha256_password sha256_password Expected **** Revoking select on mysql database Revoke select on mysql.* from 'Tanjotuser1'@'localhost'; Revoke select on mysql.* from 'Tanjotuser2'@'localhost'; Revoke select on mysql.* from 'Tanjotuser3'@'localhost'; **** Validating the Revoked privilleges select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser2'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost Grant usage on mysql.* to 'Tanjotuser1'@'localhost'; Grant usage on mysql.* to 'Tanjotuser2'@'localhost'; Grant usage on mysql.* to 'Tanjotuser3'@'localhost'; **** Validating the granted privilleges select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser2'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost **** Validating the granted privilleges Grant All on mysql.* to 'Tanjotuser1'@'localhost'; Grant All on mysql.* to 'Tanjotuser2'@'localhost'; Grant All on mysql.* to 'Tanjotuser3'@'localhost'; select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; plugin mysql_native_password mysql_native_password Expected select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; plugin mysql_old_password mysql_old_password Expected select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; plugin sha256_password sha256_password Expected **** Disconnecting the open sessions and dropping the created users Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; ============================================================================= Checking the password assignment using the update command on the user table ============================================================================= **** Creating user with mysql_native_password plugin CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password'; set @@session.old_passwords=0; UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser1' AND Host='localhost'; FLUSH PRIVILEGES; **** Creating user with mysql_old_password plugin CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; set @@session.old_passwords=1; UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser2' AND Host='localhost'; FLUSH PRIVILEGES; **** Creating user with sha256_password plugin CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; set @@session.old_passwords=2; UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser3' AND Host='localhost'; Warnings: Warning 1265 Data truncated for column 'Password' at row 1 FLUSH PRIVILEGES; select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost set password=password('abcd'); select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost set password=password('abcd'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost **** Disconnecting the open sessions and dropping the created users Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; =================================================================================================== Starting the server with default auth as sha256_password and checking the above validations again. =================================================================================================== # Restart server with default-authentication-plugin=sha256_password; ======================================================================= Checking the password plugin assignment with create user command ======================================================================= set @@global.secure_auth=0; **** Creating user with mysql_native_password plugin select @@session.old_passwords; @@session.old_passwords 0 0 Expected CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password'; SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc'); **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the plugin names select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password' 1 1 Expected Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; ======================================================================= Checking the password plugin assignment with grant user command ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; GRANT usage on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password'; SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc'); **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; GRANT usage on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; GRANT usage on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the plugin names select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password' 1 1 Expected Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; ======================================================================= Checking the functionality and integrity of the DROP user command ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); Connecting a session each with the above 3 users dropping the above 3 users with each having a open session Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; Now trying opening a new session for the above dropped user ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES) Accessing the open sessions for the above dropped user select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost ======================================================================= Checking the privellages post renaming the user ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password'; SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc'); **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the plugin names select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password' 1 1 Expected select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost **** Validating the integrity of the rename user command RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost'; RENAME USER 'Tanjotuser2'@'localhost' to 'user2'@'localhost'; RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost'; **** Validating the plugin names select (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password'; (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password' 1 1 Expected select (select plugin from mysql.user where User='user2' and Host='localhost')='mysql_old_password'; (select plugin from mysql.user where User='user2' and Host='localhost')='mysql_old_password' 1 1 Expected select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password'; (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password' 1 1 Expected **** Validating the user connections post renaming select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser2'@'localhost' (using password: YES) ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES) select user(), current_user(); user() current_user() user1@localhost user1@localhost select user(), current_user(); user() current_user() user2@localhost user2@localhost select user(), current_user(); user() current_user() user3@localhost user3@localhost **** Disconnecting the open sessions and dropping the created users Drop user 'user1'@'localhost'; Drop user 'user2'@'localhost'; Drop user 'user3'@'localhost'; ======================================================================= Checking the privillages with grant user command ======================================================================= **** Creating user with mysql_native_password plugin set @@session.old_passwords=0; GRANT select on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password'; SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc'); **** Creating user with mysql_old_password plugin set @@session.old_passwords=1; GRANT select on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead **** Creating user with sha256_password plugin set @@session.old_passwords=2; GRANT select on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc'); **** Validating the granted privilleges select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; plugin mysql_native_password mysql_native_password Expected select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; plugin mysql_old_password mysql_old_password Expected select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; plugin sha256_password sha256_password Expected **** Revoking select on mysql database Revoke select on mysql.* from 'Tanjotuser1'@'localhost'; Revoke select on mysql.* from 'Tanjotuser2'@'localhost'; Revoke select on mysql.* from 'Tanjotuser3'@'localhost'; **** Validating the Revoked privilleges select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser2'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost Grant usage on mysql.* to 'Tanjotuser1'@'localhost'; Grant usage on mysql.* to 'Tanjotuser2'@'localhost'; Grant usage on mysql.* to 'Tanjotuser3'@'localhost'; **** Validating the granted privilleges select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser2'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user' select user(), current_user(); user() current_user() Tanjotuser3@localhost Tanjotuser3@localhost **** Validating the granted privilleges Grant All on mysql.* to 'Tanjotuser1'@'localhost'; Grant All on mysql.* to 'Tanjotuser2'@'localhost'; Grant All on mysql.* to 'Tanjotuser3'@'localhost'; select plugin from mysql.user where user='Tanjotuser1' and host='localhost'; plugin mysql_native_password mysql_native_password Expected select plugin from mysql.user where user='Tanjotuser2' and host='localhost'; plugin mysql_old_password mysql_old_password Expected select plugin from mysql.user where user='Tanjotuser3' and host='localhost'; plugin sha256_password sha256_password Expected **** Disconnecting the open sessions and dropping the created users Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; ============================================================================= Checking the password assignment using the update command on the user table ============================================================================= **** Creating user with mysql_native_password plugin CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password'; set @@session.old_passwords=0; UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser1' AND Host='localhost'; FLUSH PRIVILEGES; **** Creating user with mysql_old_password plugin CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password'; set @@session.old_passwords=1; UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser2' AND Host='localhost'; FLUSH PRIVILEGES; **** Creating user with sha256_password plugin CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; set @@session.old_passwords=2; UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser3' AND Host='localhost'; Warnings: Warning 1265 Data truncated for column 'Password' at row 1 FLUSH PRIVILEGES; select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost set password=password('abcd'); select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost set password=password('abcd'); Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead select user(), current_user(); user() current_user() Tanjotuser1@localhost Tanjotuser1@localhost select user(), current_user(); user() current_user() Tanjotuser2@localhost Tanjotuser2@localhost **** Disconnecting the open sessions and dropping the created users Drop user 'Tanjotuser1'@'localhost'; Drop user 'Tanjotuser2'@'localhost'; Drop user 'Tanjotuser3'@'localhost'; set @@session.old_passwords=default;