MySQL - different levels of permissions

Following from the MySQL creating and editing users article, we can have a look at what permissions we can set and what each level means and what it allows the user to do.

Data Manipulation

Once a base database structure has been created, the most common use for the database is data manipulation: 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 'pickled' user on the 'mytestdb' database:

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

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


Perhaps obviously, but 'pickled' 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 'pickled'@'localhost';

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


There is much, much more you can do with privileges and users and you can assign privileges from allowing everything to every database to as fine tuned as allowing a select on a single column in a single table.

However, I hope this clarifies how to assign privileges to a user.


Article Comments:

Aidan commented Sun Jan 18 17:09:16 UTC 2009:

With MySQL 5 you might find the following useful (note the use of single quotes):

grant all privileges on mytestdb.* to 'pickled@%' identified by 'picklepassword'; flush privileges;

Don't forget to flush!

Aidan commented Tue Jan 20 10:36:28 UTC 2009:

Ahem, that should be 'pickled'@'%' not 'pickled@%' :-)

Want to comment?

(not made public)


(use plain text or Markdown syntax)