Knowledgebase
emscom > emscom Help Desk > Knowledgebase

Search help:


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
Related articles MySQL SSH tunnel on OSX
MySQL backup and restore
Linux Mount SMB shared folder
OSX MSSQL Server ODBC drivers
Sky broadband with 3rd party router
Article details
Article ID: 17
Category: SQL
Date added: 02-12-2011 11:50:28
Views: 340
Rating (Votes): Article rated 3.0/5.0 (12)

 
« Go back

 
Powered by Help Desk Software HESK, in partnership with SysAid Technologies