Indiana University

IU Webmaster

MySQL User Privileges

You must never use the 'root' mysql username to make a connection from your web applications, since this allows the application to do *anything* to any of your databases, even delete them entirely. Other mysql users with reduced privileges granted to them should be created.

This can be accomplished with statements like the following: 

mysql> GRANT select,insert,update,delete on firstdb.* 
-> to firstuser@'129.79.78.%' identified by 'passwd';
mysql> GRANT select,insert,update,delete on firstdb.* 
-> to firstuser@'129.79.13.%' identified by 'passwd';

This GRANT statement specifies that the user is only allowed to run a limited amount of statements on the MySQL server. This user will be allowed to: select, insert, update, and delete records. This user is not allowed to create or drop tables. More importantly, this user is not permitted to create users and set privileges.

You could further reduce a user's privileges by removing other items from the GRANT statement. The best policy is that the user should only be given permission to access the functions that are necessary to perform their tasks.

The database access privileges that MySQL supports are shown below.

Privilege

Operations Allowed by Privilege
ALTER

Modify tables with ALTER TABLE
CREATE

Make new database, table, or index

DELETE

Remove rows from tables

DROP

Remove databases or tables

INDEX

Create or remove indexes for tables

INSERT

Add rows to tables

SELECT

Select records from tables

UPDATE

Modify records in tables