Error with MySQL Grant Command for other users

JockVSJock · 02-17-2006, 06:17 PM

Have MySQL up and runnin on my Slackware laptop, just using it to play around and have fun.

I have two users under MySQL, root and a regular user. I create a database as root and grant rights to the regular user to the database.

As root...

Code:

grant * on foo to regularuser@localhost;

When I login to MySQL as the regular user and say try to create or drop a table, I get the command denied output.

What am I doing wrong, or can only root drop/create tables?

thanks

accessrichard · 02-17-2006, 06:25 PM

Did you flush privileges;

Code:

mysql>grant all on * to user@"localhost" identified by 'password' with grant option;
mysql>flush privileges;
mysql>quit;

gbonvehi · 02-17-2006, 07:28 PM

It should be:

Code:

grant all on foo.* to regularuser@localhost;

JockVSJock · 02-17-2006, 07:43 PM

Quote:

Originally Posted by accessrichard

Did you flush privileges;

Code:

mysql>grant all on * to user@"localhost" identified by 'password' with grant option;

I don't believe these worked.

Quote:

Originally Posted by accessrichard

Code:

mysql>flush privileges;
mysql>quit;

Interesting, I didin't know about the flush command, is this a standard SQL command or something at a different level?

JockVSJock · 02-17-2006, 07:45 PM

Quote:

Originally Posted by gbonvehi

It should be:

Code:

grant all on foo.* to regularuser@localhost;

This gave the regular user the ability to create/drop or DDL commands.

But the regular user still can't create a database. Can only root do this?

thanks

mdarby · 02-17-2006, 07:54 PM

Have you reloaded MySQL?

gbonvehi · 02-17-2006, 11:25 PM

I've never tried, but I guess *.* would do the trick.

accessrichard · 02-18-2006, 04:32 PM

From mysql.com

Quote:

The FLUSH statement clears or reloads various internal caches used by MySQL

The FLUSH PRIVILEGES command basically reloads accounts and passwords

When I create a user for myself I believe I use
GRANT ALL PRIVILEGES ON etc.
and I am able to create and delete databases.

MySQL howto add new users
http://dev.mysql.com/doc/refman/5.0/...ing-users.html

Thanks gbonvehi, I never actualy used the * I always specify a db name, it is good to know that that wont work for my future references.

JockVSJock · 02-19-2006, 02:57 PM

Quote:

Originally Posted by mdarby

Have you reloaded MySQL?

What do you mean by 'reload?'

Stopping and starting the service?

thanks

mdarby · 02-19-2006, 03:37 PM

yep, restart the actual program.