MySQL copy a user

Question

I want to create two users on my MySQL test database, One with read-only access to tables relevant to generating reports, etc, the other with read-write access to the same tables. This is for testing a subsystem that normally connects with a read-only user but switches to a read-write user for certain tasks. I've created the read-write user with the correct privileges, and now I need a read-only version of the same user.

I'd rather not create the read-only version from scratch as I had to set a lot of privileges, which was rather laborious. Is there a way I can create a new user based on an existing user and then remove the INSERT/UPDATE/DELETE privilages from the new user? Something like CREATE USER 'user2' LIKE 'user1' or similar? I couldn't find it in the MySQL docs if it is possible to do this.

Moonshiner

Answer 1

I found two options.

1st if you are 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 mysquserclone command from Mysql Utilities: http://wb.mysql.com/utilities/man/mysqluserclone.html

Good luck.

Barograph

I went to download Administrator but according to MySQL.com it has ceased development. Does Workbench support that functionality instead? – Moonshiner

Both links are dead. – Softcover

Answer 2

How about inserting into another table, update columns? Something like:

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;

Kolnos

Thank you! I was looking for a way to batch-add in some localhost users, and this made it easy to copy the % users with their permissions and everything. +1 – Openair

Nice solution, but be warned that you're not supposed to update these tables. From dev.mysql.com/doc/refman/5.7/en/grant-tables.html : « Direct modification of grant tables using statements such as INSERT, UPDATE, or DELETE is discouraged and done at your own risk. The server is free to ignore rows that become malformed as a result of such modifications. » Anyway, you need a FLUSH PRIVILEGES after that : « At startup, and at runtime when FLUSH PRIVILEGES is executed, the server checks user table rows. » – Rolland

Also think to clone the specific privileges tables (tables_priv, columns_priv, procs_priv, proxies_priv) – Rolland