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