How to grant all privileges to a user from root user in MySQL on command line

Question

I want to execute a command as root to grant privileges to a certain user to a certain "empty" database.

I have tried many attempts and cannot get the syntax to work:

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO `otft_omsUser`@`localhost` IDENTIFIED BY `{user password}` WITH GRANT OPTION"

-bash: {part of user password}`: event not found

mysql --host=localhost --user=root --password={root password} --execute='GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO `otft_omsUser`@`localhost` IDENTIFIED BY `{user password}` WITH GRANT OPTION'

ERROR 1046 (3D000) at line 1: No database selected

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON 'otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' IDENTIFIED BY '{user password}' WITH GRANT OPTION"

-bash: {part of user password}`: event not found

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON 'otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' WITH GRANT OPTION' at line 1046

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON otft_oms_dev_userID-001.* TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1046 (3D000) at line 1: No database selected

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TO 'otft_omsUser'@'localhost' WITH GRANT OPTION' at line 1

Answer 1

This worked for me:

mysql test -e "grant all privileges on \`test\` to 'testy'@'%' identified by 'pass\!word' with grant option"

1. Name a database to connect to. In this example I named test before the -e flag. Many SQL statements won't run if you don't "use" a default database.

2. If you grant the privileges to a database that has special characters (e.g. yours has a dash - character, you need to delimit the database name in back-ticks, not single-quotes.

3. Back-ticks also have meaning to the shell, that is command-substitution. You need to use backslash to escape the back-ticks so the shell won't do command substitution. The shell will strip off the backslashes before it passes the SQL command to mysql.

4. If you grant to all the tables in a database, you don't need to use database.*. You can just name the database.

5. If the password string contains some characters that are special to the shell, you need to backslash those too. For example, ! is the shell character for history substitution.