Call us Toll-Free:
1-800-218-1525
Live ChatEmail us

 Sponsors

How to restrict MySQL user access to a single database

Dawn Rossi, 02-01-2009
Use these two queries to restrict MySQL user access to a single database, so that the user can only see / update / delete tables from that single database.


REVOKE ALL PRIVILEGES
,GRANT OPTION from USERNAME;

GRANT ALL ON USERDATABASE.* TO 'USERNAME';

Replace USERNAME with the MySQL username
Replace USERDATABASE with the single MySQL database you wish the user to have access to.

-

To create a new MySQL user with "standard" privileges:


insert into mysql
.user values ('%','USERNAME',password('USERPASSWORD'),'y','y','y','y','y','y','y','y','y','y','y',
'y','y','y','y','y','y','y','y','y','y','y','y','y','y','y',
'y','y','','','','',0,0,0,0);

update mysql.user set shutdown_priv="N",process_priv="N",grant_priv="N",
show_db_priv="N",super_priv="N",create_user_priv="N",
show_view_priv="N",create_view_priv="N",execute_priv="N",
super_priv="N",Show_db_priv="N" where user like 'USERNAME';

Replace USERNAME with the MySQL username
Replace USERPASSWORD with the new password

Abhijeet Desai, 09-24-2009
Hi, Thanks for the query to restrict user to a single database. I wanted to know more about securing a database. To be more specific how to enable the Secure Socket Layer in mysql. You can reply me back to my mail ID abs2003_in@yahoo.com.

Thanks,
Abhijeet.

Waleed Eissa, 11-17-2009
Thanks, that was really helpful.

But why not use the "create user" statement (added in MySQL 5.0.2) to create the user account? Any specific reason for doing it this way?

Dawn Rossi, 11-23-2009
Hi Waleed,

Great question! The CREATE USER method requires several queries to create a user and assign all the proper privileges.

The approach I described is a way to accomplish all of that in one single atomic operation.

Jose Alberto, 03-08-2010
I'm sorry, but that don't work for me.

I create a new user, "revoke all privileges, grant option from 'username';", then I connect using username and password, tried to "use somedb", "select * from sometable" and it works :(

Really I don't understand MySQL user system.

shakara mozambi, 08-15-2010
@Jose

did you do FLUSH PRIVILEGES ?

paparazzi, 07-20-2011
GRANT ALL PRIVILEGES ON DBNAME.* TO ‘USERNAME’@'%' IDENTIFIED BY ‘PASSWORD';
you can even add WITH GRANT OPTION if you want

GRANT ALL PRIVILEGES ON DBNAME.* TO ‘USERNAME’@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

also possible to replace % with the user IP
Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions