Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Richard Walton
3,639 PointsDatabase setting up for multiple users
I’m trying to follow along in the MySQL database program section “Securing and Maintaining a MySQL Database”. In the lesson that covers “Setting up Multiple Users” , Andrew uses a remote database in his examples. I have tried to us the local database (127.0.0.1) to follow along. After I had created “user1 – Select” and “user2 – CRUD” connections neither of them had treehouse_movie_db.
Therefore I thought I would just export and then import the database using the “Server > Data Import” and “Server > Data Export” utility in Workbench. I had no problems exporting treehouse_movie_db. However, when I tried to import the database I got the following errors immediately after I entered my password. I tried both the user1 and user2 which is ‘password’ and the password for the local instance which is ‘beau1234’.
What am I doing wrong?
ERROR Messages below
10:44:06 Restoring treehouse_movie_db (actors)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpwsffpq.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_actors.sql"
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
Operation failed with exitcode 1
10:44:06 Restoring treehouse_movie_db (genres)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmp18agaz.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_genres.sql"
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
Operation failed with exitcode 1
10:44:07 Restoring treehouse_movie_db (movies)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpjzdrin.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_movies.sql"
10:44:14 Restoring treehouse_movie_db (actors)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpoefwso.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_actors.sql"
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
Operation failed with exitcode 1
10:44:14 Restoring treehouse_movie_db (genres)
Operation failed with exitcode 1
10:44:14 Restoring treehouse_movie_db (reviews)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpnu8lxo.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_reviews.sql"
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpiw4hkc.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_genres.sql"
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
Operation failed with exitcode 1
10:44:14 Restoring treehouse_movie_db (users)
Operation failed with exitcode 1
10:44:14 Restoring treehouse_movie_db (movies)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpfve9gq.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_users.sql"
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpvg8cnc.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_movies.sql"
10:44:45 Restoring treehouse_movie_db (actors)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpmie5w8.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_actors.sql"
ERROR 1045 (28000): Access denied for user 'user2'@'localhost' (using password: YES)
Operation failed with exitcode 1
10:44:45 Restoring treehouse_movie_db (genres)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpjd_mvl.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_genres.sql"
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
ERROR 1044 (42000) at line 1: Access denied for user 'user2'@'%' to database 'treehouse_movie_db'
Operation failed with exitcode 1
Operation failed with exitcode 1
10:44:45 Restoring treehouse_movie_db (reviews)
Running: mysql.exe --defaults-extra-file="c:\users\rick\appdata\local\temp\tmpjfx3x0.cnf" --host=127.0.0.1 --user=user2 --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\Rick\\Documents\\dumps\\Dump20140128-1\\treehouse_movie_db_reviews.sql"
Password Input Cancelled
3 Answers
Ryan Boone
26,518 PointsI'm having the same problem. I have two localhost connections, on as root and the other as user1 with SELECT privileges. I cannot access the treehouse_movie_db while logged in as user1.
Ryan Boone
26,518 PointsI just figured it out. All local connections are created via local pipes. They need to share the same socket; they do not by default. You must first figure out the socket of the original connection (for me it was /tmp/mysql.sock) and explicitly name the socket in the new connections. Flush privileges in the root connection again then reconnect via the new users. All I had to do was reenter my password for each account and I gained access to treehouse_movie_db!
Ryan Boone
26,518 PointsI should point out I'm using MySQL Workbench 6.0.9 on OS X Mavericks.
Christopher Bijalba
12,446 PointsI tried doing this, but still got a "access denied" message.
Martin Mekhaiel
14,297 PointsHi guys, i am having the same problem, i was able to get access trough when i was setting the hostname to localhost instead of '%'.. as i a have a local xampp running i did it trough the phpmyadmin console, but i want to get it running with commands, can you post your commands here? thanks in advance..
Christopher Bijalba
12,446 PointsThe only way I could get it running was by replacing % with localhost as well.