Creating Users & Granting Permissions in MySQL

One of the first tasks when setting up a new MySQL server is creating users and granting them permissions, or giving them the ability to do stuff in MySQL. By default, a single user, the root user, is created when you setup MySQL. This user is granted all privileges on the entire system, which means you should create separate logins for administrators and applications. Let’s walk through all the basic steps you need to create a new user and give them permissions.

Create a User

The first command I’ll show you is pretty simple:

Create User bob@'%' Identified By 'Astr0ngPhr@$e';

Before we move on, let’s check that user’s permissions:

Show Grants for bob@'%';

Which should show something like:

+----------------------------------------------------------------------------------------------------+
| Grants for bob@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bob'@'%' IDENTIFIED BY PASSWORD '*FE49395F631E017359454D62ED8331199B8B0EB1' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This just means our user can connect to the server (that’s what Usage means). Bob can’t do anything else right now.

The other item to note is the @’%’ of the username. Think of this as an e-mail address: username@host. This is a feature of MySQL security: you can specify that a user can only connect to MySQL from a specific IP address or hostname. The trouble with this is that there are much better firewalls out there and it slows down logins when MySQL has to perform a reverse DNS lookup just to verify that the user’s IP is the right host. Use iptables (or the equivalent) and just use a wildcard (%) which means MySQL lets the user connect from any host.

Phenomenal Cosmic Power

If you want to create a superuser, that is, a user with all privileges, here’s the command:

Grant All On *.* To bob@'%' With Grant Option;

This gives the user access to anything to any object in any database. Oh, and it gives the user the ability to give others any privilege.

key.fw

Permissions on tables, views, and routines

The basic objects in MySQL are tables, views, functions, and stored procedures. I’ll quickly run through the statements for those. First, the humble table:

Grant Insert, Select, Update, Delete On TableA To bob@'%';

This gives bob create, read, update, and delete (CRUD) access. You mix and match these privileges as you’d like. Here’s just a single privilege grant:

Grant Select On TableA To bob@'%';

You can use the same statements for views. Now what about stored routines (functions and procedures)? Here’s the syntax for functions:

Grant Execute On Function CreateOrder To bob@'%';

And now for procedures:

Grant Execute On Procedure CreateOrder To bob@'%';

If you’re giving a database developer privileges, you can grant them permission to create new objects, either to the whole server (*.*) or just one database (db1.*):

  1. Tables
    1. Create (Grant Create On db1.* To bob@’%’)
    2. Alter (Grant Alter On db1.* To bob@’%’)
    3. Drop (Grant Drop On db1.* To bob@’%’)
  2. Functions and stored procedures
    1. Create Routine (Grant Create Routine On db1.* To bob@’%’)
    2. Alter Routine (Grant Alter Routine On db1.* To bob@’%’)
  3. Views
    1. Create View (Grant Create View On db1.* To bob@’%’)

There’s one note: a user can be granted the privilege to create a table, but then not be able to insert into it.

Revoking

When you need to take a permission back, the statement is the reverse of the Grant statement. Just replace Grant with Revoke and To with From:

Revoke Select On TableA From bob@'%';

When you revoke, you also should run:

Flush privileges;

This purges the MySQL cache of all its privileges so it’s up-to-date.

Grant the least amount

When you’re creating a new user, whether it’s for an app or a person, make sure to give the least amount of privileges. Take the time to figure out what’s really needed. This minimizes the surface area that an attacker can use to steal or destroy your data.

If you learned something from this, sign-up below and get new posts, as well as updates on the book I’m goes further in depth about MySQL administration and how to keep it secure.

Want to hear more about what I’ve learned about designing databases in MySQL? You can subscribe below and I’ll send you updates on a new book I’m writing that helps you navigate past the gotchas in MySQL. If you sign-up, I’ll also send out stuff on MySQL like tips and tutorials.



 No spam


Tagged with:
Posted in Databases, IT
3 comments on “Creating Users & Granting Permissions in MySQL
  1. Simon J Mudd says:

    Do not forget to talk about replicated databases. If you have one or more slaves connected to a master you can simplify grant management by ensuring that the mysql database is replicated and thus you only need to make the grant changes on the master and they will be pushed “downstream” to the slaves. The more slaves you have the better this is. If you want to have different grants on master and slave(s) then you need to take this into account and you may need to either disable binlogging while running grant statements on the master or only replicate other databases but not the mysql.% database.

  2. Ajith says:

    Thanks this helped! I think if the user is quoted with an ‘(apostrophe), the subsequent grant creates another user and messes the permission.

    Create User ‘bob’@’%’ Identified By ‘Astr0ngPhr@$e’;

  3. geoff says:

    I like this, short and sweet. Hope the rest of the book is like this.

    I just imported a SQL Server database into MySQL and wanted to make a EER diagram for it. Being new to MySQL Workbench it wasn’t obvious. Wading through the online help which just seemed to miss the point of what I wanted to do.

    Simple answer found on stackoverflow:

    Select Database from the menu then Reverse engineer. Follow the obvious steps from there. Easy as falling off a log.

    My tip of the day.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>