On Tuesday, January 3, 2006 1711, [EMAIL PROTECTED] wrote: > >You are going to need to setup your own application-level >locking scheme and rollback procedures if you don't want to use >or cannot use the locking and transaction support built into >InnoDb.
Well, I WANT to use InnoDB, but I guess that I'll have to roll my own scheme to handle the (relatively few) inserts that I need on the MySQL tables. >For the tables that do not support transactions >(because they are MyISAM or something else) you will probably >need to take snapshots of the initial state of each table, >ensuring that no other process tries to update or insert to >that table while you have it snap-shotted (or you will >invalidate the OTHER process should you need to roll back). The >table-wide locks are generally good enough for that kind of >protection but they are performance killers if you have any >sort of concurrency or if your transaction takes too long. It shouldn't take very long - just enough to insert a new user and the corresponding privileges. Also, new users should be relatively infrequent once we get the initial data loaded. > >I would strongly recommend NOT using the mysql tables for your >application's security needs. I would roll my own >application-level permission tables and use them to control >access through the front end. My tentative plan is to have my own security levels (none, guest, user and admin) enforced by logic in my application, but I also want to restrict privileges to the minimum necessary at the database level. >Generally, my end users do not >get direct read-write access to the tables behind any >application. They may get read-only access but that's through >their own accounts, not the accounts I use to access the DB >with from the application itself. I'm not sure that I understand. Are you saying that all users connect from your application using a small set of access accounts (known to MySQL) and then you use your own tables to look up the actual permissions for each person (not known to MySQL)? >If you still need to create >user accounts on the fly, stick with the GRANT and REVOKE >statements and do that part of the processing either first or >last (outside of your other transactions) That way you can know >for sure if you got the account changed or not either before >you start the transaction or just before you commit or >rollback. That was the plan. >Lookup and be aware that certain commands contain an implicit >COMMIT when they are executed so your transaction may end >earlier than you planned if you use one of those commands. >These are usually data definition statements (ALTER TABLE, etc) >but it's better for you if you know them all. Good point. I knew that some commands issue an implicit COMMIT, but hadn't thought to check all of them. >Sorry I can't be more specific but it's time to blast and >dinner is waiting. Thanks for the suggestions. >Cheers! > >Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine -- John Hoover [EMAIL PROTECTED] 301-890-6932 (H) 202-767-2335 (W) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
