BIOS,Windows, Linux, Software,Security, Domains, Web Hosting

How to grant privileges in MySQL

When we create a new mysql table, usually we should assign a special user for the table for security instead of using the root. Here are some example of creating user and grant permissions in mysql. Of course these examples should be excuted in the the mysql command prompt. For all the users, setting a strong password in mysql is necessary.

 

1. Create and grant privileges for a user who named Tommy on table tbx

GRANT ALL ON tbx.* TO 'Tommy' IDENTIFIED BY 'mypass';

This statement will create the user account and grant privileges for him automatically(If NO_AUTO_CREATE_USER is closed in SQL mode. usually it is.). This statement is equal to the next two command.

a. Create a user who named tommy, password is mypass.

CREATE USER 'Tommy'@'localhost' IDENTIFIED BY 'mypass';

b. Grant all privileges for tommy on dbx

GRANT ALL ON dbx.* TO 'Tommy'@'localhost';

2.Limit Tommy usage(max connection = 100)

GRANT USAGE ON *.* TO 'Tommy'@'localhost' WITH MAX_USER_CONNECTIONS 100;

More GRANT_OPTIONS: http://dev.mysql.com/doc/refman/5.7/en/grant.html

3. Flush and reload privileges, and quit

mysql>FLUSH PRIVILEGES;
mysql>QUIT;

Reference: http://dev.mysql.com/doc/refman/5.7/en/grant.html

Related Articles

Comments(0)  

Leave a Reply

Subscribe to BootBeta Comments RSS feed to receive notification of latest comments posted.

Subscriptions

  • Enter Email Address:

Recent Post

Copyright@ 2010-2014 Bootbeta All Rights Reserved.
HooBlog Ver 1.5 Build 20140530