How to create a new user and grant permissions in MySQL

What is the Red

The lines that the user needs to enter or customize will be red in this tutorial! The remainder can mostly be copied and pasted.

 

About MySQL

MySQL is an open source database management system that helps users store, organize, and subsequently retrieve data. It has a variety of options for granting specific users distinct permissions within tables and databases - this tutorial will give you a brief summary of some of the many options.

 

How to create a New User

In part 1 of the MySQL tutorial, we did all editing in MySQL as a root user, with full access to all databases. However, in cases where more restrictions may be required, there are ways to create users with custom permissions.

Let's start by creating a new user inside the MySQL shell:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Unfortunately, at this point the newuser is not allowed to do anything with the databases. In fact, if the newuser tries to log in (with the password password), he will not be able to get to the MySQL shell.

So the first thing to do is to give the user access to the information he will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access - this specific command allows the user to read, edit, execute, and perform all tasks on all databases and tables.

Once you have finished the permissions that you want to set for your new users, always make sure you reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now take effect.

 

How to Grant Different User Permissions

Here is a short list of other possible common permissions that users can use.

ALL PRIVILEGES- as we saw earlier, this would give a MySQL user all access to a particular database (or if no database is selected, the whole system)
CREATE- allows you to create new tables or databases
DROP- allows deleting tables or databases
DELETE- allows delete table rows
INSERT- allows you to insert rows into tables
SELECT- allows you to use the Select command to read databases
UPDATE- allows you to update table rows
GRANT OPTION- allows you to grant or revoke privileges of other users

To give a specific user permission, you can use this structure:

GRANT [permission type] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give it access to any database or any table, be sure to put an asterisk (*) in place of the database name or table name.

Each time you update or change a permission make sure you use the command Flush Privileges.

If you need to revoke a permission, the structure is almost identical to granting it:

REVOKE [permission type] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Just as you can delete databases with the DROP, you can use the DROP to delete a user completely:

DROP USER ‘demo’@‘localhost’;

To test your new user, log out by typing

quit 

and log back in with this command on the terminal:

mysql -u [username]-p

 

  • 18060 Users Found This Useful
Was this answer helpful?