MySQL Utilities


The MySQL privilege system permits you to create a set of permissions for each user. Sometimes the set of permissions are complex and may require multiple GRANT statements to effect. Other times, the user may acquire privileges over time.

Regardless of how it came about, you may find yourself needing to create a new user that has the same privileges as another user.


The goal is to create one or more users whose permissions are identical to an original user on a single server.

Rather than discover what those privileges are using a SHOW GRANTS FOR statement, copy them into a script, modify it, copy and paste again for each user, etc., etc., we can use a single command to copy one user to a list of new users. We can even set different passwords for each user as we go.

Let's assume we have a user, joe@localhost, who has a long list of permissions. We need to create a clone of his user account for two new users, sally and john. Each of these users requires a new password.

Example Execution

shell> mysqluserclone --source=root@localhost \
          --destination=root@localhost \
          joe@localhost sally:secret1@localhost john:secret2@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 2 users...
# Cloning joe@localhost to user sally:secret1@localhost
# Cloning joe@localhost to user john:secret2@localhost
# ...done.


In the above example, we see the use of the mysqluserclone utility to clone the joe user to two new user accounts.

Notice we used the --source option to connect to the original server and --destination for the same server.

After that, we simply list the user we want to clone and the new users we want to create. In this case we use the format username:password@host to specify the user account name, password (optional), and host.

When the utility finishes, you have two new user accounts that have the same privileges as the original user; joe@localhost.

Permissions Required

On the source server, the user must have the SELECT privilege for the mysql database.

On the destination server, the user must have the global CREATE USER privilege or the INSERT privilege for the mysql database as well as the GRANT OPTION privilege, and the privileges that the original user has (you grant a privilege you do not have yourself).

Tips and Tricks


