Security Benefit By Switching to MYSQL

From a security standpoint VFP is a nightmare. You can do pretty much anything on the program end with passwords etc but I never found a way around the fact that you pretty much have to give the client access to the Data directory. VFP being a flat file system (even with the introduction of DBC’s – databases) you had to give the client at the very least read access to the tables and if you wanted him/her to change something then write access too. Only way around that might have been to use VFPOLEDB and a web server but then why use VFP.

Now moving to MYSQL I want to point out some instant benefits you get migrating to MYSQL and that is security. One thing probably not immediatly visible to a newbie is the power of the “DEFINER” and INVOKER security options on stored procedures. This allows clients to do things under certain circumstances that they do have no right to. I want to use the following example to demonstrate the very usefulness of that feature.

First of course we wont give any of the administrative roles to the user accounts used to access the server. We probably wont even give that account the “DELETE” right on the schema. We do not want an errant DELETE…. to run havoc on our tables – accidental or malicious as it may be. But there might be a scenario where the client needs to delete something.

We can accomplish this by wrapping the DELETE statemente into a stored procedure We created the stored procedure with a statement something like

CREATE DEFINER = `user_with delete_rights`@`%` PROCEDURE `deletesomething` (<some parameters>)
BEGIN
-- some checking of the parameters and
DELETE ...... -- our delete statement probable a prepared one

We do not need to add “SECURITY DEFINER” as that is the default option. The above means that the statements within the procedure are executed as if the user was the user_with delete_rights user and not the regular user account that logged in. This way now our client can delete what he needs to delete and we can make sure that he cannot delete anything else accidentally or otherwise.

He needs the “EXECUTE” priviledge to run the “CALL …. ” statement. But that is all he needs for that. If one is so inclined one could wrap pretty much any statement into a STORED PROCEDURE wrapper and do the same for INSERT, UPDATE and maybe even SELECT statements to enhance the security and integrity of the whole system

3 Responses to “Security Benefit By Switching to MYSQL”

  1. Frank Rizzo says:

    Stored procedures do ok for security but will really REALLY kill your performance. You can’t scale a site or development wrapping all calls in a stored procedure.

  2. admin says:

    Thanks for the comment. I did not mean to suggest to wrap everything into a stored procedure – just the items that are sensitive. Unfortunatly until the complete system is switched to PHP VFP is the frontend. That means ODBC is installed. With that if the username password is know the MYSQL server is vulnerable to any program that can access it via ODBC from that machine. So a disgruntled employee with some knowledge could do a DELETE without a WHERE statement or an “UPDATE `invoice master`SET invamt = 0 or something like that. As this particular system is an accounting system this could be quite disasterous. Beyond that I agree that SP’s will tax the system.

  3. Accountant, Accountng solution, Accunting Consulting…

    Most companies don’t do their accounting by hand. For routine accounting, most companies use accounting software such as QuickBooks, Peachtree or more specialized programs for specific industries. Do some research to see if your industry has a dedicate…

Leave a Reply