Beta Draft: 2016-08-26
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
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 will require a new password.
mysqluserclone --source=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.
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 will have two new user accounts that have the same privileges as the original user, joe@localhost.
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).
You can use
--destination option to
specify a different server to copy a user account to another
option to include GRANT statements that the user@host
combination matches. This is useful for copying user accounts
from one server to another where there are global privileges