Setting up a MySQL database and database user for a new Yii app.
From a security point of view it’s best to give the database user only the privileges required by the Yii framework; I assume SELECT, INSERT, UPDATE, DELETE and… any others?
What is the minimal set of privileges that should be granted to the user?
Hi,
The first step is to restrict the rights to just the database the app is using.
The four privileges you listed are the main statements used by a working app. But if you’re using migrations, you will need more. You will know when you create the new migration.
Other important statements:
SHOW CREATE TABLE: Privilege SELECT also allows to execute SHOW CREATE TABLE statement (from a comment in MySQL documentation).
SHOW COLUMNS: This one I don’t know, but should be the same as above.
You can easily see a list of queries issued by your app by using the CWebLogRoute.
Thanks Rodrigo;
It also appears that ALTER is required for unit testing, otherwise Db test fail with the message:
CDbCommand::execute() failed: SQLSTATE[42000]: Syntax error or access violation: 1142 ALTER command denied to user [username]
Thanks for the feedback!
Please post again if you have more feedback or questions. I’m happy to help.