# MySQL probs: creating new user and granting privs to it
**URL:** https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415
**Category:** Factual Questions
**Created:** [May 23, 2009, 7:21pm UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415 "2009-05-23T19:21:54Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@AHunter3](https://boards.straightdope.com/u/AHunter3)
#### Post date: [May 23, 2009, 7:21pm UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/1 "2009-05-23T19:21:54Z")
It’s one of those subjects that is treated by both the course I took and the books I obtained as an “oh yeah and also” tangential subject, very briefly discussed with inadequate examples. :mad:
Hasn’t mattered much so far because the original bd I am replacing with a MySQL as my teeth-cutting / get-your-feet-wet project was one where everyone had equal access anyhow. But to run mysqldump (to make a backup) it really wants an account name and password. So I go to create at least one actual account with an actual password and grant it full permissions to do everything so it can be used for mysqldump.
As root I type _grant usage on . to ‘ahunter3’ identified by password ‘yaddayadda’_.
MySQL gets grouchy: “Password hash should be a 41-digit hexadecimal number”. Sheesh. Ok freaking hell. _grant usage on . to ‘ahunter3’ identified by password '123456789012345678901234567890A"_ (ya satisfied?). It took that. I exit and then go to log back in as ‘ahunter3’ with that horrid password and it won’t let me in.
TAKE II: _grant usage on . to ‘ahunter3’_ and in a separate command _set password for ahunter3 = password(‘yaddayadda’);_. It takes those.
It lets me log in as ahunter3 now. But when I try to ‘use MyDataBaseName’ (to switch to the schema I actually use) it says permission denied.
I log back in as root. _grant all privileges on ‘MyDataBaseName’ to ‘ahunter3’;_
MySQL: _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 ‘‘MyDataBaseName’ to ‘ahunter3’’ at line 1_
\*grant usage on _._ to ‘ahunter3’ \*
It takes that. I log out as root. Try to log in as ahunter3. MySQL: _Access denied for user ‘ahunter3’@‘localhost’ (using password: YES)_
I try not specifying the password, to see if it answers back _Password_ and waits on a non-echoing 2nd line for me to provide it (that’s how I log in as root, btw) and instead it just logs me straight in w/o a password. I enter _use MyDataBaseName_ and it says _Access denied for user ‘’@‘localhost’ to database ‘MyDataBaseName’_
Several attempts at Googling the secret answer were involved in getting this far. I remain stuckered. Anything obvious I’m doing wrong?
---
### Author:  [@Erasmus\_Darwin](https://boards.straightdope.com/u/Erasmus_Darwin)
#### Post date: [May 23, 2009, 8:20pm UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/2 "2009-05-23T20:20:44Z")
Have you done a “flush privileges”?
---
### Author:  [@minor7flat5](https://boards.straightdope.com/u/minor7flat5)
#### Post date: [May 23, 2009, 11:09pm UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/3 "2009-05-23T23:09:28Z")
I always have to mess around with this when fiddling with MySql.
This is how I would do it:
grant all on mydb.\* to ahunter3@"%" identified by ‘secret’;
Of course, this is granting everything (which is what I usually want), but you can whittle it down to taste.
Notes:
[ul][_]You have to mention DB|_ and OBJ|_. I did this by using mydb._[_]Do not enclose the user in quotes.[_]You need to include the host. I use “%” in double quotes to indicate that this user can connect from anywhere. You can use localhost (probably without quotes) and it should work.MySql should accept ‘secret’ as such, without being some long hexadecimal thing. It’s when you are updating the user tables that you use the Password(‘secret’) function that generates the long hash. The “identified by” syntax implicitly does the hash.[/ul]
---
### Author:  [@Sparklo](https://boards.straightdope.com/u/Sparklo)
#### Post date: [May 23, 2009, 11:57pm UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/4 "2009-05-23T23:57:12Z")
As mentioned by **minor7flat5** , a user in MySQL consists of both the username and the host. At this point, you might want to take a look at all the users you’ve got created. As root, you can do the query “select user, host from mysql.user” to see what users are defined. For each user, you can see what privileges are set with the command “show grants for ‘user’@‘host’”. Technically, you don’t need the single quotes around the username or host in some circumstances but it’ll always work to include them.
Doing this, you’ll be able to see which hosts ‘ahunter’ can log in from. You can also see which users have passwords and what privileges (and databases) the users have access to. That might help sort out what’s going on.
---
### Author:  [@AHunter3](https://boards.straightdope.com/u/AHunter3)
#### Post date: [May 24, 2009, 1:39am UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/5 "2009-05-24T01:39:32Z")
It appears that the main problem was that I was not identifying the HOST, as you said. I had to nuke the account and then recreate it and give it a password and grant it all on _._ but I can now log in as ahunter3 instead of root, and yes it requires (and takes) a password. (For real. I did a select query as ahunter3 and it worked).
Onward to problem TWO:
[QUOTE=OSX Command Line]
ahunter\_computer: /usr/local/mysql/bin ahunter$ mysqldump -u root -p ebcoa \> ebcoabkup.sql
-bash: ebcoabkup.sql: Permission denied
[/quote]
my actual database is named “ebcoa” in case that’s not obvious. It has several tables. It’s usually syntax (I’m new enough at this that wrong syntax doesn’t leap out at me)… OK lessee…
[QUOTE=Command Line]
ahunter\_computer: usr/local/mysql/bin ahunter$ mysqldump -u ahunter3 -p mypassword ebcoa \> ebcoabkup.sql
-bash: ebcoabkup.sql: Permission denied
ahunter\_computer: /usr/local/mysql/bin ahunter$ mysqldump -u ‘ahunter3’ -p ‘mypassword’ ebcoa \> ebcoabkup1.sql
-bash: ebcoabkup1.sql: Permission denied
ahunter\_computer: /usr/local/mysql/bin ahunter$ mysqldump user=‘ahunter3’ password=‘mypassword’ ebcoa \> ebcoabkup2.sql
-bash: ebcoabkup2.sql: Permission denied
ahunter\_computer: /usr/local/mysql/bin ahunter$ mysqldump user=ahunter3 password=mypassword ebcoa \> ebcoabkup2.sql
-bash: ebcoabkup2.sql: Permission denied
ahunter\_computer: /usr/local/mysql/bin ahunter$ mysqldump user=ahunter3 ebcoa \> ebcoabkup2.sql
-bash: ebcoabkup2.sql: Permission denied
pool-68-161-186-186:/usr/local/mysql/bin ahunter$ mysqldump -u ahunter3 -p ebcoa \> ebcoabkup2.sql
-bash: ebcoabkup2.sql: Permission denied
[/quote]
note that it did not query for password on the following line on those latter two.
Anyone got a light to shine here? Hmm maybe I should flush the privs sure enough, even though it lets me log in as ahunter3, can’t hurt, yes?
---
### Author:  [@SkipMagic](https://boards.straightdope.com/u/SkipMagic)
#### Post date: [May 24, 2009, 1:40am UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/6 "2009-05-24T01:40:28Z")
I know doing this through the command line is worth knowing all by itself, but have you tried the [GUI admin tools](http://dev.mysql.com/downloads/gui-tools/5.0.html)?
---
### Author:  [@AHunter3](https://boards.straightdope.com/u/AHunter3)
#### Post date: [May 24, 2009, 1:50am UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/7 "2009-05-24T01:50:54Z")
Flushing privs did not make a diff.
---
### Author:  [@AHunter3](https://boards.straightdope.com/u/AHunter3)
#### Post date: [May 24, 2009, 2:00am UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/8 "2009-05-24T02:00:48Z")
> [@SkipMagic](#):
>
> I know doing this through the command line is worth knowing all by itself, but have you tried the [GUI admin tools](http://dev.mysql.com/downloads/gui-tools/5.0.html)?
Hmm… (I have them installed but I keep forgetting they exist)…
::fires up MySQL Administrator::
hey whaddaya know! 🙂
[QUOTE=EBCOAbkup\_2009-05-23\_21.55.26.sql]
## – – Create schema ebcoa
CREATE DATABASE IF NOT EXISTS ebcoa;
USE ebcoa;
## – – Definition of table `ebcoa`.`cdguide`
DROP TABLE IF EXISTS `ebcoa`.`cdguide`;
CREATE TABLE `ebcoa`.`cdguide` (
`cdguideID` int(11) NOT NULL AUTO\_INCREMENT,
`StreetName` varchar(128) DEFAULT NULL,
`StreetNumberBeyond` int(11) DEFAULT NULL,
`StreetNumberUpTo` int(11) DEFAULT NULL,
`zipcode` int(11) DEFAULT NULL,
`cd` int(11) DEFAULT NULL,
PRIMARY KEY (`cdguideID`)
) ENGINE=MyISAM AUTO\_INCREMENT=287 DEFAULT CHARSET=latin1;
## – – Dumping data for table `ebcoa`.`cdguide`
/\*!40000 ALTER TABLE `cdguide` DISABLE KEYS \*/;
LOCK TABLES `cdguide` WRITE;
INSERT INTO `ebcoa`.`cdguide` VALUES (80,‘Wyatt St’,0,1000000000,10460,9),…
[/quote]
Works for me! (Don’t guess there’s any real reason I need to know how to do this from the cmd line).
---
### Author:  [@friedo](https://boards.straightdope.com/u/friedo)
#### Post date: [May 24, 2009, 2:01am UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/9 "2009-05-24T02:01:54Z")
Problem 2 has nothing to do with MySQL; it’s an OS permission problem. Whatever user you’re using (on your shell, not MySQL) does not have permission to write the file ebcoabkup2.sql.
Are you in a directory owned by that user, such as the user’s home dir?
---
### Author:  [@AHunter3](https://boards.straightdope.com/u/AHunter3)
#### Post date: [May 24, 2009, 3:46pm UTC](https://boards.straightdope.com/t/mysql-probs-creating-new-user-and-granting-privs-to-it/497415/10 "2009-05-24T15:46:54Z")
Yeah, that was indeed it.
I have MySQL installed to /usr/local which is not tolerant of ordinary users writing new files. As shown in terminal output in previous posts, I was in the bin folder of the MySQL folder when the mysqldump command was executed. It did not let me sudo. I even used “su” and logged in as root but mysqldump would not execute at all for me as su. (Weird!).
I realized my first attempt at specifying a complete path to backup location (to make it go somewhere other than right next to the db in /usr/local) was borked because the OS was of course going to start from where I was (therefore /usr/local/mysql/bin/Users/ahunter/Documents – no such place).
I know there are things you can do with dots to “go up the hierarchy” but I decided it was easier to cd to directory root and then specify the entire path to mysqldump to invoke it.
A bit of experimentation showed me it wanted -p but not to enter the password until it asked me for it:
> [@](#):
>
> ahunter3\_computer: / ahunter3$ /usr/local/mysql-5.1.30-osx10.4-powerpc/bin/mysqldump -u ‘ahunter3’ -p ebcoa \> ~/Documents/ebcoa.sql
> Enter password:
> ahunter3\_computer: / ahunter3$ cd ~/Documents
> ahunter3\_computer:~/Documents ahunter3$ ls -l
> total 22672
> …
> -rw-r–r-- 1 ahunter ahunter 10738297 May 24 11:35 ebcoa.sql
🙂
I stand by my original complaint: this was very poorly documented in all the books.