[RESOLVED] MySQL admin question

[MechEng]

Regarding the admin of a MySQL database; I am a bit confused as to exactly what privileges must be granted for a user to have read/write/edit/delete/etc. to a database that is accessed in an ASP.NET application?

Trial and error has shown me that granting all privileges to a user for a particular database in the MySQL admin tool will allow the user to connect to the database, but not run a stored procedures in that database. If I grant access to the “Information Schema” and the”mysql” databases then the user can access the stored procedure from within ASP.NET.

So my question is… what exactly is too much permission and what is the minimum permissions that should be granted to allow full access to a MySQL database without giving away the keys to the shop?

Thanks

[Campion]

I suggest you start by reading this:

http://dev.mysql.com/doc/refman/5.1/...ge-system.html

[MechEng]

Thanks for the response, but one thing I have learned from visiting this forum is that you are expected to do your research first.

I have read that section as well as all the related sections. The Mysql reference manual is moderately ok documentation, but it is a terrible instructional manual.

Specific answers that would help me are similar to:

Q1: Why is it not enough to grant all database privileges to ‘myDatabase01’ to ‘User01’ for User01 to access the Mysql database and run stored procedures from within ASP.NET? I have found that I need to also assign privileges to the ‘mysql’ and ‘information_schema’ to all User01 to access the database ‘myDatabase01’ and run the stored procedure there. This seams to be overkill.

[Campion]

You should NEVER grant access to MySQL or Information_schema to a user account that is not administrative. That's asking for trouble.

For the average user, and in your case, the right to run stored procedures, the most you want to grant a user is:

DELETE, INSERT, EXECUTE, SELECT, and UPDATE.

Every other right is administrative.

GRANT SELECT, INSERT, DELETE, EXECUTE, UPDATE ON mydb.* TO 'someuser'@'somehost';

You'll have to do that for every host for a given user.

Also:

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

Each of these is covered on this specific page:

http://dev.mysql.com/doc/refman/5.1/en/grant.html

[MechEng]

Thanks Campion. I had already tried both query Grant method and the MySQL Administrator tool… both with the same results.

But, I did stumble onto the problem I am having… haven't found a solution yet.

This appears to be a bug with MySQL 5.X in granting privileges to run stored procedures when using the Net Connector for MySQL.

This is a mysql bug. The bug leads to a situation where using Connector/NET to call a stored procedure requires more privilege than simply calling the stored procedure... i.e., the user must have SELECT privilege on the mysql.proc table.
In a shared hosting environment, this is an unacceptable security risk. We will not enable this permission on our shared hosting servers. The bug has been acknowledged by mysql, but unfortunately after reviewing the multiple BUG entries listed at bugs.mysql.com regarding this, we do not see concrete information on if/how they intend to fix it...

Here is the link to the article. http://knowledge.3essentials.com/web...roc-mysql.html

[MechEng]

Ok... The web is a wonderful place. After reading about 1000 articles, I found a clue to fix this problem, and it works!

Add UseProcedureBodies = False to the NET connection string and keep up with the order of any parameters that get passed to the stored procedures.

[Campion]

Good deal. MySQL also has an issue with dates, in particular when reading them via the .NET connector (5.1).