Implementing row-level permissions in mysql using a trigger.

Sorry, I meant MySQL doesn't support rollback or any kind of transaction stuff *within a trigger*.  Basically, I need the trigger to do something to cause the entire transaction to fail.

Sounds like RETURN FALSE is what you want.  Check here for the comment from Tim H.:

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html#c8079

Nope, "RETURN FALSE" is not supported in a trigger either (or in a stored procedure).  I already found that page.   Somebody mentions that a little further down on the same page.  Just to be crazy I tried it, and got an error message to that effect.

Yup, I saw Tom H.'s comment and stopped reading.  The next three comments on the page explain that strategy does not work.  

The only other method I've found is to create a deliberate error inside the trigger, like violating a primary key constraint or similar.  This is messy, but it would work since the failure of a trigger stops the execution and rollsback any work-to-date (on transaction-based tables, anyways).  You'll still have to deal with the failure of the trigger at some point...I'm guessing through the front-end app.

See bug 11661 for this issue.

http://bugs.mysql.com/bug.php?id=11661

Don't do this as a trigger - that's not really what they are there for (although, in theory, you could do something like execute a SQL statement that has a SQL error, which should cause th trigger to fail). Do this as a view, instead.  Views can be updated, and you can create a view for each user:

CREATE VIEW v1 AS SELECT * FROM t1 WHERE isUpdatable != 0;

Then give the user access to perform operations on t1 (you might want to hide the isUpdatable column so they can't change it, but the worst they could do would be to make something they can see into something they can't see).

Here's an example of how you *can* use a trigger to prevent an update - by making it do something it should not do:

mysql> CREATE TABLE trig_test (id INT NOT NULL);
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE const (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO const VALUES (1);
Query OK, 1 row affected (0.06 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER  check_row_priv BEFORE UPDATE ON trig_test
    -> FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO const VALUES (1);
    -> END; //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> INSERT INTO trig_test VALUES (1);
Query OK, 1 row affected (0.06 sec)

mysql> UPDATE trig_test SET id = 3;
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> SELECT * FROM trig_test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql>

You can modify the trigger to include your logic of checking the isUpdatable flag or whatever conditions you need to prevent updates.

Ultimately, you can't abort the transaction - you can only cause the statement being executed to fail.  It's up to the application to rollback the transaction as a whole, retry the statement that failed, or ignore it.

All that being said, I'd recommend you use views instead of triggers - row-based permission is what they are there for.

Todd,
The problem with views is that i do need the user to be able to update some rows, just not all of the rows.  How could a view allow the user to update or delete only certain rows?

Awsome thanks!  Views do more than I expected, I didn't realize you could update the rows in a view.  This is the perfect solution to row-level permissions. Thanks!