This article will walk you through modifying permissions that are assigned to each user.

Data Manipulation

Once a base database structure has been created, the most common use for the database is data manipulation:

SELECT........read only
INSERT........insert rows/data
UPDATE........change inserted rows/data
DELETE........delete drop rows of data

I think the terms are self explanatory and these 4 actions form the base for many acronyms such as CRUD (Create, Read, Update, Delete).

So how do we actually assign individual permissions?

The procedure is very simple. Firstly, log into MySQL as the root user:

mysql -u root -p

In this example I am going to assign select, insert and update pivileges to the 'test' user on the 'mytestdb' database:

GRANT SELECT, INSERT, UPDATE ON `mytestdb` . * TO 'test'@'localhost';

As when changing editing permissions or users, flush the privileges:

FLUSH PRIVILEGES;

Perhaps obviously, but 'test' can now select, insert and update records. However, they do not have permission to delete records and they cannot adjust the structure of the database.

Table manipulation

There may be a time, especially as we develop our application that we want the user to be able to edit the tables and database structure itself.

It follows a very similar theme as above:

CREATE.......create new tables
ALTER........change table/column names
DROP.........drop columns/tables

And in the same way, to assign these permissions to the user:

GRANT CREATE, DROP, ALTER ON `mytestdb` . * TO 'test'@'localhost';

Again, fairly obvious but this allows the 'test' user to create, drop and alter tables on the 'mytestdb' database.