392 lines
17 KiB
Plaintext
392 lines
17 KiB
Plaintext
|
|
|
|
=======================================================================
|
|
Checking the password plugin assignment with create user command
|
|
=======================================================================
|
|
|
|
set @default_plugin=@@global.default_authentication_plugin;
|
|
**** Creating user with default_plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
|
|
**** Validating the plugin names
|
|
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
|
|
(select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
|
|
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 'Tanjotuser3'@'localhost';
|
|
|
|
|
|
=======================================================================
|
|
Checking the functionality and integrity of the DROP user command
|
|
=======================================================================
|
|
|
|
**** Creating user with default_plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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 '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 '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()
|
|
Tanjotuser3@localhost Tanjotuser3@localhost
|
|
|
|
|
|
=======================================================================
|
|
Checking the privellages post renaming the user
|
|
=======================================================================
|
|
|
|
**** Creating user with default_plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
|
|
**** Validating the plugin names
|
|
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
|
|
(select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
|
|
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()
|
|
Tanjotuser3@localhost Tanjotuser3@localhost
|
|
**** Validating the integrity of the rename user command
|
|
RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost';
|
|
RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost';
|
|
**** Validating the plugin names
|
|
select (select plugin from mysql.user where User='user1' and Host='localhost')=@default_plugin;
|
|
(select plugin from mysql.user where User='user1' and Host='localhost')=@default_plugin
|
|
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()
|
|
Tanjotuser3@localhost Tanjotuser3@localhost
|
|
ERROR 28000: Access denied for user 'Tanjotuser1'@'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()
|
|
user3@localhost user3@localhost
|
|
**** Disconnecting the open sessions and dropping the created users
|
|
Drop user 'user1'@'localhost';
|
|
Drop user 'user3'@'localhost';
|
|
|
|
|
|
=======================================================================
|
|
Checking the privillages with grant user command
|
|
=======================================================================
|
|
|
|
**** Creating user with default_plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
|
|
GRANT select on mysql.* to 'Tanjotuser1'@'localhost';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
|
|
GRANT select on mysql.* to 'Tanjotuser3'@'localhost';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
|
|
**** Validating the granted privilleges
|
|
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
|
|
plugin
|
|
default_plugin
|
|
default_plugin 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 '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='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 '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='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 'Tanjotuser3'@'localhost';
|
|
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
|
|
plugin
|
|
default_plugin
|
|
default_plugin 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 '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';
|
|
UPDATE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser1' AND Host='localhost';
|
|
FLUSH PRIVILEGES;
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
UPDATE IGNORE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser3' AND Host='localhost';
|
|
FLUSH PRIVILEGES;
|
|
select user(), current_user();
|
|
user() current_user()
|
|
Tanjotuser1@localhost Tanjotuser1@localhost
|
|
set password='abcd';
|
|
select user(), current_user();
|
|
user() current_user()
|
|
Tanjotuser1@localhost Tanjotuser1@localhost
|
|
**** Disconnecting the open sessions and dropping the created users
|
|
Drop user 'Tanjotuser1'@'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
|
|
=======================================================================
|
|
|
|
**** Creating user with mysql_native_password plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
|
|
SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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='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 'Tanjotuser3'@'localhost';
|
|
|
|
|
|
=======================================================================
|
|
Checking the functionality and integrity of the DROP user command
|
|
=======================================================================
|
|
|
|
**** Creating user with default_plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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 '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 '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()
|
|
Tanjotuser3@localhost Tanjotuser3@localhost
|
|
|
|
|
|
=======================================================================
|
|
Checking the privellages post renaming the user
|
|
=======================================================================
|
|
|
|
**** Creating user with mysql_native_password plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
|
|
SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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='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()
|
|
Tanjotuser3@localhost Tanjotuser3@localhost
|
|
**** Validating the integrity of the rename user command
|
|
RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'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='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()
|
|
Tanjotuser3@localhost Tanjotuser3@localhost
|
|
ERROR 28000: Access denied for user 'Tanjotuser1'@'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()
|
|
user3@localhost user3@localhost
|
|
**** Disconnecting the open sessions and dropping the created users
|
|
Drop user 'user1'@'localhost';
|
|
Drop user 'user3'@'localhost';
|
|
|
|
|
|
=======================================================================
|
|
Checking the privillages with grant user command
|
|
=======================================================================
|
|
|
|
**** Creating user with mysql_native_password plugin
|
|
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123';
|
|
GRANT select on mysql.* to 'Tanjotuser1'@'localhost';
|
|
SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
|
|
GRANT select on mysql.* to 'Tanjotuser3'@'localhost';
|
|
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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='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 '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='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 '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='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 '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='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 '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';
|
|
UPDATE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser1' AND Host='localhost';
|
|
FLUSH PRIVILEGES;
|
|
**** Creating user with sha256_password plugin
|
|
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
|
|
UPDATE IGNORE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser3' AND Host='localhost';
|
|
FLUSH PRIVILEGES;
|
|
select user(), current_user();
|
|
user() current_user()
|
|
Tanjotuser1@localhost Tanjotuser1@localhost
|
|
set password='abcd';
|
|
select user(), current_user();
|
|
user() current_user()
|
|
Tanjotuser1@localhost Tanjotuser1@localhost
|
|
**** Disconnecting the open sessions and dropping the created users
|
|
Drop user 'Tanjotuser1'@'localhost';
|
|
Drop user 'Tanjotuser3'@'localhost';
|