{"id":370,"date":"2018-03-01T07:44:53","date_gmt":"2018-03-01T07:44:53","guid":{"rendered":"http:\/\/info.juliusgoh.life\/?p=370"},"modified":"2018-04-24T02:45:58","modified_gmt":"2018-04-24T02:45:58","slug":"mysql-db-allowing-old-user-from-different-ip-with-same-grants","status":"publish","type":"post","link":"https:\/\/info.juliusgoh.life\/?p=370","title":{"rendered":"Mysql DB allowing old user from different IP with same grants"},"content":{"rendered":"<p>Firstly, We have to create a new user from different ip , so we have to run the below command.<br \/>\nThe first &#8220;Select&#8221; query is to get all column in user table and the thing we wanna replace is the Host ( the ip that client is going to connect from ).<\/p>\n<pre>#User Table\r\n#To get field for user table for inserting into user table (User Access)\r\nSELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user'\r\n\r\nINSERT INTO mysql.user (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,ssl_type,ssl_cipher,x509_issuer,x509_subject,max_questions,max_updates,max_connections,max_user_connections,plugin,authentication_string,password_expired,is_role) SELECT '[NEW_IP]',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,ssl_type,ssl_cipher,x509_issuer,x509_subject,max_questions,max_updates,max_connections,max_user_connections,plugin,authentication_string,password_expired,is_role FROM mysql.user where Host='[OLD_IP]' and User='[USER]';\r\n<\/pre>\n<p>After having the new user &amp; from ip (&#8220;USER&#8221;@&#8221;IP&#8221;) , we must give it the same grants as the user from the old ip by running the command below :-<\/p>\n<p>The first query is also the same functionality as the above, to get all column name from db table<\/p>\n<pre>#DB Table\r\n#To get field for db table for inserting into db table (User Grant Purposes)\r\nSELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='db'\r\n\r\nINSERT INTO mysql.db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Create_tmp_table_priv,Lock_tables_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Execute_priv,Event_priv,Trigger_priv) SELECT '[NEW_IP]',Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Create_tmp_table_priv,Lock_tables_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Execute_priv,Event_priv,Trigger_priv FROM mysql.db where Host='[OLD_IP]' and User='[USER]';\r\n<\/pre>\n<p>And lastly , remember to run the code below :-<\/p>\n<pre>FLUSH PRIVILEGES;\r\n<\/pre>\n<p>EDITED ON 2018-03-12 :<br \/>\nRemember to check out this tables too<\/p>\n<pre>SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='tables_priv';\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Firstly, We have to create a new user from different ip , so we have to run the below command. The first &#8220;Select&#8221; query is to get all column in user table and the thing we wanna replace is the Host ( the ip that client is going to connect from ). #User Table #To [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":225,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"image","meta":{"footnotes":""},"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/370"}],"collection":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=370"}],"version-history":[{"count":5,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/370\/revisions"}],"predecessor-version":[{"id":396,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/370\/revisions\/396"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/media\/225"}],"wp:attachment":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}