MySQL - Modify user permissions
This article will walk you through modifying permissions that are assigned to each user.
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:
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.
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.
© 2015 Rackspace US, Inc.
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License
See license specifics and DISCLAIMER