Use newer style mysql syntax for users/passwords

The sytax used for creating users and setting passwords
was deprecated in mysql 5.7 and is not valid in mysql 8.0. [1]

Change to use CREATE USER to create users as opposed
to creating users with GRANT syntax.

Setting passwords also does not use the PASSWORD keyword
anymore. [2]

[1] https://dev.mysql.com/doc/refman/5.7/en/grant.html
[2] https://dev.mysql.com/doc/refman/5.7/en/set-password.html

Change-Id: I0387238ec434073f95dd76f13f1f40c7cec8d1eb

5 files changed, 32 insertions, 22 deletions

diff --git a/trove/guestagent/common/sql_query.py b/trove/guestagent/common/sql_query.py
index 2ad291e9..37dce923 100644
--- a/trove/guestagent/common/sql_query.py
+++ b/trove/guestagent/common/sql_query.py
@@ -382,7 +382,7 @@ class SetPassword(object):
                       'user_host': self.host,
                       'new_password': self.new_password}
         return ("SET PASSWORD FOR '%(user_name)s'@'%(user_host)s' = "
-                "PASSWORD('%(new_password)s');" % properties)
+                "'%(new_password)s';" % properties)
 
 
 class DropUser(object):
diff --git a/trove/guestagent/datastore/mysql_common/service.py b/trove/guestagent/datastore/mysql_common/service.py
index 5cef5a64..92b84578 100644
--- a/trove/guestagent/datastore/mysql_common/service.py
+++ b/trove/guestagent/datastore/mysql_common/service.py
@@ -308,17 +308,16 @@ class BaseMySqlAdmin(object):
             for item in users:
                 user = models.MySQLUser.deserialize(item)
                 user.check_create()
-                # TODO(cp16net):Should users be allowed to create users
-                # 'os_admin' or 'debian-sys-maint'
-                g = sql_query.Grant(user=user.name, host=user.host,
-                                    clear=user.password)
-                t = text(str(g))
-                client.execute(t)
+
+                cu = sql_query.CreateUser(user.name, host=user.host,
+                                          clear=user.password)
+                t = text(str(cu))
+                client.execute(t, **cu.keyArgs)
+
                 for database in user.databases:
                     mydb = models.MySQLSchema.deserialize(database)
                     g = sql_query.Grant(permissions='ALL', database=mydb.name,
-                                        user=user.name, host=user.host,
-                                        clear=user.password)
+                                        user=user.name, host=user.host)
                     t = text(str(g))
                     client.execute(t)
 
@@ -658,8 +657,22 @@ class BaseMySqlApp(object):
         """
         LOG.debug("Creating Trove admin user '%s'.", ADMIN_USER_NAME)
         host = "127.0.0.1"
+        try:
+            cu = sql_query.CreateUser(ADMIN_USER_NAME, host=host,
+                                      clear=password)
+            t = text(str(cu))
+            client.execute(t, **cu.keyArgs)
+        except (exc.OperationalError, exc.InternalError) as err:
+            # Ignore, user is already created, just reset the password
+            # (user will already exist in a restore from backup)
+            LOG.debug(err)
+            uu = sql_query.SetPassword(ADMIN_USER_NAME, host=host,
+                                       new_password=password)
+            t = text(str(uu))
+            client.execute(t)
+
         g = sql_query.Grant(permissions='ALL', user=ADMIN_USER_NAME,
-                            host=host, grant_option=True, clear=password)
+                            host=host, grant_option=True)
         t = text(str(g))
         client.execute(t)
         LOG.debug("Trove admin user '%s' created.", ADMIN_USER_NAME)
@@ -1087,8 +1100,7 @@ class BaseMySqlRootAccess(object):
             g = sql_query.Grant(permissions=CONF.root_grant,
                                 user=user.name,
                                 host=user.host,
-                                grant_option=CONF.root_grant_option,
-                                clear=user.password)
+                                grant_option=CONF.root_grant_option)
 
             t = text(str(g))
             client.execute(t)
diff --git a/trove/guestagent/strategies/restore/mysql_impl.py b/trove/guestagent/strategies/restore/mysql_impl.py
index 5a64efe8..3c9bcebb 100644
--- a/trove/guestagent/strategies/restore/mysql_impl.py
+++ b/trove/guestagent/strategies/restore/mysql_impl.py
@@ -39,7 +39,7 @@ class MySQLRestoreMixin(object):
     RESET_ROOT_SLEEP_INTERVAL = 10
 
     RESET_ROOT_MYSQL_COMMANDS = ("SET PASSWORD FOR "
-                                 "'root'@'localhost'=PASSWORD('');")
+                                 "'root'@'localhost'='';")
     # This is a suffix MySQL appends to the file name given in
     # the '--log-error' startup parameter.
     _ERROR_LOG_SUFFIX = '.err'
diff --git a/trove/tests/unittests/guestagent/test_dbaas.py b/trove/tests/unittests/guestagent/test_dbaas.py
index bf5e1ce7..76b0eff8 100644
--- a/trove/tests/unittests/guestagent/test_dbaas.py
+++ b/trove/tests/unittests/guestagent/test_dbaas.py
@@ -468,13 +468,13 @@ class MySqlAdminTest(trove_testtools.TestCase):
 
     def test_change_passwords(self):
         user = [{"name": "test_user", "host": "%", "password": "password"}]
-        expected = ("SET PASSWORD FOR 'test_user'@'%' = PASSWORD('password');")
+        expected = ("SET PASSWORD FOR 'test_user'@'%' = 'password';")
         with patch.object(self.mock_client, 'execute') as mock_execute:
             self.mySqlAdmin.change_passwords(user)
             self._assert_execute_call(expected, mock_execute)
 
     def test_update_attributes_password(self):
-        expected = ("SET PASSWORD FOR 'test_user'@'%' = PASSWORD('password');")
+        expected = ("SET PASSWORD FOR 'test_user'@'%' = 'password';")
         user = MagicMock()
         user.name = "test_user"
         user.host = "%"
@@ -558,14 +558,12 @@ class MySqlAdminTest(trove_testtools.TestCase):
 
     def test_create_user(self):
         access_grants_expected = ("GRANT ALL PRIVILEGES ON `testDB`.* TO "
-                                  "`random`@`%` IDENTIFIED BY 'guesswhat';")
-        create_user_expected = ("GRANT USAGE ON *.* TO `random`@`%` "
-                                "IDENTIFIED BY 'guesswhat';")
+                                  "`random`@`%`;")
 
         with patch.object(self.mock_client, 'execute') as mock_execute:
             self.mySqlAdmin.create_user(FAKE_USER)
-            self._assert_execute_call(create_user_expected,
-                                      mock_execute, call_idx=0)
+            mock_execute.assert_any_call(TextClauseMatcher('CREATE USER'),
+                                         user='random', host='%')
             self._assert_execute_call(access_grants_expected,
                                       mock_execute, call_idx=1)
 
@@ -1411,7 +1409,7 @@ class MySqlAppTest(trove_testtools.TestCase):
             self.mySqlApp.secure_root()
         update_root_password, _ = self.mock_execute.call_args_list[0]
         update_expected = ("SET PASSWORD FOR 'root'@'localhost' = "
-                           "PASSWORD('some_password');")
+                           "'some_password';")
 
         remove_root, _ = self.mock_execute.call_args_list[1]
         remove_expected = ("DELETE FROM mysql.user WHERE "
diff --git a/trove/tests/unittests/guestagent/test_query.py b/trove/tests/unittests/guestagent/test_query.py
index 162d4470..991cca7b 100644
--- a/trove/tests/unittests/guestagent/test_query.py
+++ b/trove/tests/unittests/guestagent/test_query.py
@@ -403,7 +403,7 @@ class SetPasswordTest(QueryTestBase):
         uu = sql_query.SetPassword(user=username, host=hostname,
                                    new_password=new_password)
         self.assertEqual("SET PASSWORD FOR 'root'@'localhost' = "
-                         "PASSWORD('new_password');", str(uu))
+                         "'new_password';", str(uu))
 
 
 class DropUserTest(QueryTestBase):