MySQL create restricted user

Solution 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 "restricted" 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

Was this article helpful? yes / no

MySQL SSH tunnel on OSX

MySQL backup and restore

OSX MSSQL Server ODBC drivers

Linux Mount SMB shared folder

Sky broadband with 3rd party router

Article details

Article ID:	17
Category:	SQL
Date added:	02-12-2011 14:50:28
Views:	413
Rating (Votes):	(12)

« Go back