User & Privilege Management in MySQL
 

Most of the programmers are provided username/password pair which is used in their relevant programs to work with even in a development environment, which is replaced by deployment team when making the code live. So, generally a lot of programmers have no idea how users are created or how permissions are granted, etc. In this article we'll look into how these are done, so that next time you set up your own test database or you switch to a job where you'll need to do these things you'll have an idea of what needs to be done.

In all these examples below, I am assuming you would be using the root account.

Creating Users

First step would be to create a new user and set passwords for them. The username part is divided into 2 parts like this '<user_name>'@'<host_name_ip_or_mask>' (quotes are required), the username is the name that'll be used for logging in, the hostname/IP/IP mask decides from where the user may login, say you'd need the user to login only from any office. Here's how the commands look like varying on the scenario:

Create an user with password who can login from anywhere:

Create an user without password who can login from a specific IP address:

Create an user with password who can login from a specific IP address range (using wildcard):

Create an user with password who can login from a specific IP address range (using IP mask):

Now, anjali can login from any IP address 192.168.1.0 to 192.168.1.255

Modifying Users

This will most likely happen where you will need to update passwords, change allow hostname/IP/IP masks or change the user name. See the following SQL statements:

Update the user's password:

Modify the user's allowed hostname:

Rename the user:

Granting Permissions

Now, we would need to grant some privileges to the user, may be on a whole database or a single table, the privileges may range from creating tables, dropping, selecting rows, or deleting row etc, you can get a full list of permissions you can grant here.

The few examples given below should give you a basic idea, the rest you can imagine on your own.

Grant all privileges to a user on a single database:

Grant selective privileges to a user on a single database:

Grant selective privileges to a user on a single table:

Removing Privileges

Last but not the least that you might do while managing users on MySQL is to revoke the specific or all permissions provided to a user.

Remove delete privilege from a user on a database: