MySQL copies user

Question

I want to create two users in my test MySQL database, one with read-only access to tables related to report generation, etc., and the other with read and write access to the same tables. This is for testing a subsystem that usually connects to a read-only user, but switches to a read and write user for specific tasks. I created a user to read and write with the correct privileges, and now I need the read-only version of the same user.

I would prefer not to create a read-only version from scratch, since I had to set many privileges, which was quite difficult. Is there a way to create a new user based on an existing user and then remove the INSERT / UPDATE / DELETE privileges from the new user? Something like CREATE USER 'user2' LIKE 'user1' or similar? I could not find it in MySQL docs if possible.

Answer 1

I found two options.

If you are a Windows user, you can use MySql Administrator. http://dev.mysql.com/doc/administrator/en/mysql-administrator-user-administration-user-accounts.html

2nd you can use the mysquserclone command from Mysql Utilities: http://wb.mysql.com/utilities/man/mysqluserclone.html

Good luck.

Answer 2

How to insert into another table, update columns? Sort of:

 CREATE TABLE user_tmp LIKE user; INSERT INTO user_tmp SELECT * FROM user WHERE host ='localhost' AND USER ='root'; UPDATE user_tmp SET user = 'readonlyuser', Insert_priv = 'N', Update_priv = 'N', Delete_priv = 'N', /* TODO: ADAPT TO YOUR SUITS */ LIMIT 1; INSERT INTO user select * FROM user_tmp; DROP TABLE user_tmp;