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.
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 ‘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.
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.