Create New User and Grant Permissions in MySQL

by | 13 Aug 2015 | Database, MySQL, Server, Technology | 0 comments

MySQL is an open source relational database management software. It helps to store, organise and retrieve data. MySQL has a feature for you to create users to manage different databases or tables with certain permissions.

MySQL Permissions

The different types of permissions available in MySQL are as follows.

[su_table]

Permission Description
ALL PRIVILEGES allows user to perform all the following permissions on tables or databases
CREATE allows user to create tables or databases
DROP allows user to delete tables or databases
DELETE allows user to delete rows from tables
INSERT allows user to insert rows to tables
SELECT allows user to retrieve rows to tables
UPDATE allows user to update rows to tables
GRANT OPTION allows user to grant or remove other users’ privileges

[/su_table]

How to create a new user

Before being able to run any of these commands, you will have to login to your MySQL first, which usually we will use root to login at command line.

mysql -u root -p

It should prompt you to enter your root password for MySQL. After that, you will be logged into your MySQL.

Command to create a new user

CREATE USER 'new_username'@'localhost' IDENTIFIED BY 'password';
  • new_username – username of user
  • localhost – location in terms of ip address of MySQL to create user at
  • password – password for user to login to MySQL

After creating a new user in MySQL, we need to give the user permissions in order for the user to do anything.

GRANT ALL PRIVILEGES ON * . * TO 'new_username'@'localhost';
  • new_username – username of user created previously
  • localhost – location in terms of ip address of MySQL where user was created at previously

Once you are done with the two commands above, you need to reload the permissions for MySQL.

FLUSH PRIVILEGES;

 

That’s it! You are all set.

Feel free to drop me an email at [email protected] if you have any questions.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Related Posts

BrowserGoogle ChromeProgrammingTechnology
[Google Chrome Developer Ti] Prevent Warning "Your connection is not private" from appearing 5
[Google Chrome Developer Tip] Prevent Warning “Your connection is not private” from appearing

[Google Chrome Developer Tip] Prevent Warning “Your connection is not private” from appearing

This is probably more applicable to web developers. Many times when we try to run a localhost site with https in our local environment to emulate the production environment as close as possible, we will always run into a Google Chrome warning message "Your...

AndroidAppleBrowserGoogle ChromeInternet ExplorerLinuxMicrosoft EdgeMobile PhonesMozilla FirefoxOSSafariTechnology
How to do a hard refresh for Chrome, Safari, Firefox, Internet Explorer or Edge in Windows & Mac?

How to do a hard refresh for Chrome, Safari, Firefox, Internet Explorer or Edge in Windows & Mac?

Browser Hard Refresh in Google Chrome, Safari, Mozilla Firefox, Internet Explorer and Microsoft Edge Sometimes just by refreshing your browser alone doesn't help in letting you see the latest changes of a website. Any web developers will know this best. So below...

Amazon Web Services (AWS)Elastic BeanstalkServer
Unable to delete AWS Elastic Beanstalk Environment "resource XX-XXXXXXX has a dependent object" - MervCodes
Unable to delete AWS Elastic Beanstalk Environment “resource XX-XXXXXXX has a dependent object”

Unable to delete AWS Elastic Beanstalk Environment “resource XX-XXXXXXX has a dependent object”

Unable to delete AWS Elastic Beanstalk Environment "resource XX-XXXXXXX has a dependent object" Problem You will usually get this error when you are trying to delete an Elastic Beanstalk environment. Solution As the message in the Events log states, the security...

DatabaseMagentoMySQLOpen SourcePHPPhpMyAdminTechnology
Magento 1.9 Fix for Sending Double Emails or Sending Email to Wrong Recipients

Magento 1.9 Fix for Sending Double Emails or Sending Email to Wrong Recipients

As we know, Magento uses a cron job system to send out email from core_email_queue table. There is an additional table called core_email_queue_recipients, which as the name states, will store all recipients for emails there. But these records do not get removed at...

ApacheCentOSLinuxOSServerTechnology
How to Setup Let's Encrypt FREE SSL Certificate on Centos 6 Apache using Certbot? | MervCodes
How to Setup Let’s Encrypt FREE SSL Certificate on Centos 6 Apache using Certbot?

How to Setup Let’s Encrypt FREE SSL Certificate on Centos 6 Apache using Certbot?

Having SSL on your website these days is important especially if you want your website to rank better on Google search, and that's when Let's Encrypt come into the picture. Let's Encrypt is a free, automated, and open certificate authority (CA), run for the...