User & Privilege Management in MySQL

pradeep's Avatar author of User & Privilege Management in MySQL
This is an article on User & Privilege Management in MySQL 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:
Code: SQL
CREATE USER 'anjali'@'%' IDENTIFIED BY 'MyMoMMy'

Create an user without password who can login from a specific IP address:
Code: SQL
CREATE USER 'anjali'@'192.168.1.2'

Create an user with password who can login from a specific IP address range (using wildcard):
Code: SQL
CREATE USER 'anjali'@'192.168.1.%' IDENTIFIED BY 'MyMoMMy'

Create an user with password who can login from a specific IP address range (using IP mask):
Code: SQL
CREATE USER 'anjali'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'MyMoMMy'

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:
Code: SQL
SET PASSWORD FOR 'anjali'@'%' = PASSWORD('MyDaDDy')

Modify the user's allowed hostname:
Code: SQL
RENAME USER 'anjali'@'%' TO 'anjali'@'localhost'

Rename the user:
Code: SQL
RENAME USER 'anjali'@'%' TO 'anjali_pradeep'@'%'

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:
Code: SQL
GRANT ALL PRIVILEGES ON bestofperl.* TO 'anjali'@'%'

Grant selective privileges to a user on a single database:
Code: SQL
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON bestofperl.* TO 'anjali'@'%'

Grant selective privileges to a user on a single table:
Code: SQL
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON bestofperl.posts TO 'anjali'@'%'

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:
Code: SQL
REVOKE DELETE ON bestofperl.* FROM 'anjali'@'%'